SQL vs NoSQL: The Real Answer

“SQL is for structured data, NoSQL is for flexible data.” You have probably heard this framing. It is not wrong exactly, but it is not useful either. Teams have picked MongoDB because their data “might change” and ended up with inconsistent documents and no transactions. Teams have stayed with PostgreSQL when they needed to handle hundreds of thousands of writes per second and paid the price in slow queries and scaling headaches.

The choice between SQL and NoSQL is not about structure versus flexibility. It is about which failure modes you can live with.

What SQL Actually Guarantees

Relational databases give you ACID: atomicity, consistency, isolation, durability. These are not abstract properties. They have concrete consequences.

Atomicity means a transaction either fully succeeds or fully fails. If you are transferring money between accounts and the server crashes mid-operation, the database rolls back cleanly. No money disappears.

Consistency means the database enforces your rules. Foreign keys, unique constraints, not-null columns - these are checked at write time, not at read time. The database will reject invalid data rather than storing it and letting you deal with it later.

Isolation means concurrent transactions do not see each other’s half-finished work. Two users booking the last seat on a flight will not both get a confirmation.

Durability means once the database says a write succeeded, it actually persisted to disk. A crash afterward does not lose that data.

These guarantees matter enormously for financial data, inventory, user accounts, order processing - anything where incorrect data causes real problems. PostgreSQL, MySQL, SQLite all provide ACID by default.

What SQL Struggles With

SQL databases scale vertically well. Add more RAM, faster disks, more CPU to the same machine and performance improves. But they are harder to scale horizontally across multiple machines. The relational model requires joins across tables, and joining across machines distributed in different data centers is expensive.

Schema migrations are the other pain point. Altering a table with 200 million rows to add a column can lock the table for hours. Teams running at scale deal with this constantly, using tools like pgroll or gh-ost to run schema changes without downtime, but it adds operational overhead.

These are not dealbreakers for most teams. PostgreSQL handles millions of rows without breaking a sweat on modern hardware, and schema migration tooling has improved substantially. But there is a ceiling, and some workloads hit it.

What NoSQL Actually Trades Away

NoSQL databases give up some of the ACID guarantees in exchange for something else, usually horizontal scalability or flexibility in data shape.

The specific tradeoff depends on the type of NoSQL database:

Document stores (MongoDB, Firestore) store JSON-like documents. Each document can have a different shape. There is no enforced schema. This is genuinely useful for data that varies significantly across records, like product catalogs where a jacket has sleeve length and a laptop has RAM. The tradeoff is that consistency enforcement moves from the database to your application code. The database will happily store {"price": "free"} next to {"price": 49.99}. Your application has to handle both.

Wide-column stores (Cassandra, HBase) are built for write-heavy workloads at massive scale. Cassandra can handle hundreds of thousands of writes per second distributed across many nodes. The tradeoff is that your data model is tightly coupled to your query patterns. You design tables around the queries you need to run, not around the relationships in your data. Changing query patterns often requires new tables.

Key-value stores (Redis, DynamoDB) are extremely fast for lookups by a single key. Redis can return a value in under a millisecond. The tradeoff is that anything more complex than a key lookup becomes your problem. There are no joins. Aggregations require pulling data into your application.

When SQL Breaks

SQL is the wrong tool in a few specific situations.

When you need to write at genuinely massive scale across many nodes. A single PostgreSQL primary can handle tens of thousands of writes per second, which is enough for most products. But if you are handling hundreds of thousands per second across regions, the architecture needed to make SQL work horizontally (Citus, Vitess, or a rewrite to NewSQL like CockroachDB) may be more complex than switching to a database designed for it.

When your data genuinely has no consistent structure. The “flexible schema” argument is usually overstated - most applications have more consistent data than developers predict. But there are exceptions. Storing user-generated metadata, configuring arbitrary attributes for a multi-tenant SaaS, handling data from heterogeneous external sources - these cases do benefit from document storage.

When you need sub-millisecond read performance at scale. For session storage, rate limiting counters, leaderboards, and caching hot data, a key-value store will always be faster than a relational query, even with indexes.

When NoSQL Breaks

NoSQL is the wrong tool more often than people expect.

When you need transactions across multiple records. MongoDB added multi-document transactions in 2018, but they are slower than the default single-document operations and not all teams use them. If your data model requires updating multiple records atomically, a relational database with built-in ACID is cleaner.

When your data is relational. Most data is relational. Users have orders. Orders have items. Items have products. Modeling this in a document store leads to either deeply nested documents (hard to query and update) or references between documents (at which point you have rebuilt a relational database without the tools to manage it well).

When your query patterns are not predictable. Cassandra requires you to know your queries upfront. Add a new requirement that queries data in a different way and you are building a new table. PostgreSQL’s query planner handles ad-hoc queries well. Cassandra does not.

When the team is small. Operational complexity is a real cost. Every additional database technology is another system to monitor, back up, and troubleshoot. For most startups and small engineering teams, picking PostgreSQL for relational data plus Redis for caching covers the vast majority of use cases without adding a third or fourth database to the stack.

The Actual Decision Framework

Start with PostgreSQL. It is the most deployed database among professional developers for a reason. It handles JSON columns natively, has excellent full-text search, and pgvector makes it viable for vector similarity search without a separate database. PostgreSQL handles more scale than most products ever reach.

Add Redis if you need caching, session storage, or rate limiting counters. This combination covers most web applications completely.

Consider a document store when your data is genuinely unstructured across records and you are not doing complex relational queries.

Consider Cassandra or DynamoDB when you have confirmed that write throughput at scale is the actual bottleneck, not a projected one.

The teams that regret their database choices are usually the ones who chose for scale they never reached, or for flexibility they never needed, rather than for the failure modes their application actually cannot tolerate.

The Part Interviews Get Wrong

System design interviews ask “SQL or NoSQL?” as if it is a binary. Real systems use both. An e-commerce platform might run PostgreSQL for orders and inventory, Redis for cart state and rate limiting, and Elasticsearch for product search. These are not competing choices. They are tools with different strengths.

The right answer to “SQL or NoSQL?” is almost always “for what, and at what scale?” If the interviewer is looking for a binary, push back and explain the tradeoff. That response demonstrates more systems understanding than a confident wrong answer.