Case Study: To SQL or Not to SQL?


by Michael Hwang

Lean and MEAN

With the advent of the MEAN stack in 2010 and its subsequent explosion in popularity, many of our past clients adopted the rapid development technical architecture powered by MongoDB, Express, Angular, and NodeJS. In particular, we noticed that the rising popularity of NoSQL data stores like MongoDB were gaining traction at unprecedented speeds - while relational databases were the tried and true solution for data persistence, many companies were eager and ready to jump ship.

One of our past clients, a software startup building a suite of real-time collaboration tools, was a strong proponent of using MongoDB for all storage purposes. However, as their platform grew and scaled, they began to run into problems.

The Problem: Race Conditions

Our client’s software platform processes requests for users modifying the same objects collaboratively. As their user base grew, so did their QPS. As traffic spiked, they began to experience inconsistent behavior in their application as users were not seeing their expected changes being stored properly.

At a surface level, SQL and MongoDB databases are great at solving the same issue - data storage. However, while MongoDB brought the promise of faster development cycles with “schemaless” documents, faster reads through the denormalization of data instead of using joins, and more scalability with built-in sharding mechanisms, these features come with tradeoffs.

In this case, the tradeoff causing issues was having no transactional writes in MongoDB across different collections. In traditional RDBMS, database operations are guaranteed to be atomic if they are performed in a single transaction. In MongoDB, writes can only be atomic at the document or collection level. The writes made by our client were incorrectly assuming cross-collection locking would happen at the database, which was causing race conditions in the application. The changes that users were making were happening in the incorrect order or not at all.

The Solution: Application-level Locking

After investigating the problem, we designed a “Two Phase Commit” algorithm performed at the application-level to simulate the transactions of relational databases. This involved maintaining a separate collection in MongoDB for storing the state of pending writes, and only finalizing the writes after all operations had been completed.

While the implementation of the two phase commit was underway, we redesigned critical portions of the schema to be contained within a single MongoDB document to take advantage of the inherent document-level locking. Through A/B testing, we discovered that this alleviated 85% of the race conditions users were experiencing while the longer-term solution was being built. Once Two Phase commits were rolled out into production two weeks later, the race conditions were resolved.

Conclusion

At NearTheBox, we believe that there is no One-Size-Fits-All tool. Every technology is created to solve a specific problem, and identifying the problems you need to solve is the first step to choosing the right building blocks of your applications. In this case, we recommended that the schema be redesigned in a fashion where relational data would be stored in a SQL database, and data that can be optimized in a non-relational manner be stored in MongoDB.

MongoDB and NoSQL databases provide a newer paradigm for storing data in the age of Web 2.0 and cloud computing. No platform had to worry about storing one billion comments attached to a single Tweet in 2000. As applications evolve, so do the frameworks we keep in our toolbelt. However, quality software is built incrementally on top of well designed infrastructure, and choosing the right tool for the task at hand is how to lay a solid foundation.