UPchieve

In the backdrop of a worldwide pandemic, Upchieve was growing faster than ever. More students required tutoring at home. During the early stages of building the application MongoDB was chosen for ease of implementation.

Dave Sudia, CTO of Upchieve, was faced with constant requests from the analytics team to deliver insights. The queries on Mongo DB were quite slow. It is understandable because, without joins and strict foreign key relationships of a relational database, complex queries on mongo or any document-based database would be quite slow. The data models were designed relationally, but implemented using a document database.

Thanks to Dave’s foresight, a decision to move to a relational database was made when the amount of data and complexity of the application was still reasonably small. It would have been much more difficult to migrate down the line.

PostgresQL was chosen as the database for its great performance and support for JSONB, which would help take care of vestiges of the Mongo database.

The first few weeks of engagement involved going through their business and figuring out what is important to them and how the ideas flow in the system. This process helped us understand how the data would flow and would ideally be structured.

We started helping the client build a schema that would be efficient for their functioning instead of what would be easiest to migrate. We were not deterred even if the transformation would be complicated. In contrast, most automated migration tools are only able to flatten the mongo data and migrate it over.

As we were thinking in terms of concepts, we could come up with apt ways to implement a system where the availability of the volunteer is determined based on the timezone, calendars, and availability, which was in line with the core feature of the application. 

“UPchieve serves as a connector between volunteers and students. Volunteers are compelled by the flexible hours and virtual component, and students can receive tutoring and college counseling for free.”

We did realize the existing system didn’t work quite correctly as expected when daylight saving time kicked in. Our suggestions covered all edge cases.

There were a lot more cases where we assisted UPchieve in designing an efficient schema, such as allowing flexibility for their future use cases. For example, in the old design, users could not move from being a student to a volunteer once they graduated. In the new design there is a flexible system for users to have different roles.

We came across multiple challenges while migrating the database. A process like this will generally make us comb through years of technical debt. 

There were cases where there was inconsistent data in the same key. This is usually the case in the Mongo based development cycle. We sat down with the client and made them aware of the different options available.

As the schema was initially decided, we did have to deal with the constraints that came with the schema but weren’t being followed when in Mongo. The constraints include uniqueness, non-nullable, etc. Similarly, there were cases where seeds from the schema weren’t matching the associated data.

Overall, the main challenge was putting everything together with numerous variables and inconsistencies.

All through the process, we were transparent in giving reports from time to time, including a progress bar of the migration on how much data was still pending. We participated in UPchieve’s Slack so communicating was easiest for them and part of their regular workflow.

As we had to communicate the different decisions to be taken for managing constraints, missing seeds, etc we had a process where we routinely communicated. We made sure no stone was left unturned.

• Smooth transition from Mongo to PostgresQL with minimal downtime.
• 98% of the data was migrated accurately. The remaining was invalid data that UPchieve decided not to migrate.
• Significant improvement in query performance. The worst performing analytics query went from an average of 7 minutes to 6 seconds.
• Average CPU efficiency during peak hours increased 8x, from 60% utilization on the Mongo servers to 15% on Postgres servers, with half the CPU cores on the new server.

This is what Dave Sudia, the CTO of UPchieve, has to say about it:

Mongo2Sql was exactly the service I needed. After looking at a bunch of tools and services that just wanted to flatten our documents, they were the only people I could find who fundamentally understood what migrating from Mongo to SQL really requires. They basically poured a new, stronger foundation for our house while we were living in it. Akshay and Suhas were extremely collaborative, respecting our business requirements and our schedule while providing excellent input on how to design our new schema and working with our developers to perfect it. I can’t recommend them strongly enough!