Why I Left NoSQL Behind and Returned to SQL for Primary Databases

The early 2010s — a simpler time, and one when I was knee-deep in PHP, crafting SQL queries line by line, navigating migrations with a mix of optimism and trepidation. Every schema change was a careful balancing act: disabling foreign keys, making adjustments, re-enabling constraints, and hoping the integrity held. It was challenging, yes, and at times it felt like maintaining a moving engine while the car was still in motion.

The Allure and Limits of MongoDB

Then came MongoDB, a promise of freedom in an era when database management felt anything but flexible. Its appeal was clear: “Store anything, in any structure, at any time.” We welcomed the simplicity of document storage, leaving behind the careful schema designs and relational modeling that had defined our SQL days. But this flexibility came at a cost. Validation logic grew uncomfortably complex, index performance slowed to a crawl, and MongoDB’s aggregation capabilities, while versatile, couldn’t replace the elegance of SQL’s JOIN operations. I’d written enough intricate JOINs to appreciate their power, even if they were unwieldy at times.

ArangoDB and Cloud Firestore: Honorable Mentions

ArangoDB and Cloud Firestore offered interesting alternatives. ArangoDB’s graph model was refreshing, and Firestore’s sub-collections were genuinely innovative. But each had its limits. ArangoDB’s custom DSL added a learning curve that felt excessive, while Firestore’s security rules required a meticulous approach that made management cumbersome. While Firestore remains a viable option for specific projects, it’s always with measured expectations.

A Revelation in Data Modeling at Amazon

Working at Amazon changed my approach to databases entirely. Data modeling there is both methodical and essential, analyzed through lenses ranging from legal to privacy implications. Here, I realized the issue wasn’t SQL versus NoSQL but our often casual approach to Schema Design and Data Modeling. These practices, I learned, are not just technical preferences but fundamental to scalable, secure, and efficient data systems.

Designing with Purpose

Since then, I’ve adopted a disciplined approach to service design, one where data modeling and schema development are at the forefront. Before any code is written, I map out the problem space, refine assumptions, and construct database schemas with the same care as application logic. This is more than an exercise in planning; it’s about creating an architecture where the database and the application are deeply aligned, each choice informed by the other.

A Cautionary Lesson in ACID Compliance

The importance of ACID compliance became unmistakably clear in one experience where a bank verification API was exploited. Due to eventual consistency, a delay in transaction reflection allowed an attacker to manipulate micro-deposits, turning our system into an inadvertent payout service. Despite our safeguards, the inconsistency in reflecting real-time balances proved costly. It was a stark lesson in the risks inherent in systems without guaranteed consistency.

Returning to SQL

Armed with these insights, I returned to SQL, with its robustness, transactional guarantees, and predictable reliability. Recent benchmarks showing Postgres outperforming MongoDB in transactional workloads validated the decision further. Despite the familiar challenges — migration management, injection safeguards — SQL’s strengths are undeniable in maintaining structural integrity and performance.

Creating a Balanced Database Strategy

Today, I manage a hybrid system where SQL serves as the backbone, overseeing core data with its well-defined integrity. NoSQL still has a place, managing unstructured data and metrics where strict consistency is less critical. It’s a balanced approach that leverages the strengths of both models without compromising on system reliability.

Reflecting on this journey, my path through the database landscape has underscored the importance of thoughtful, deliberate design. SQL, with its balance of structure and flexibility, is a system that has proven to be both familiar and fundamentally reliable, offering a foundation that keeps evolving with the demands of modern data architectures. And while it might not be flashy, there’s a certain satisfaction in its enduring, pragmatic reliability.