SQL vs NoSQL: How to Actually Choose a Database
A practical SQL vs NoSQL guide — ACID, schemas, sharding, query-first modeling, and the interview trade-offs that decide which database fits your workload.
This article has an interactive companion
Don't just read it — step through it interactively, one state change at a time.
"Should I use SQL or NoSQL?" is the wrong question, and interviewers know it. There is no NoSQL — there are four families of stores that share only the fact that they aren't relational. The real question is: what does my data look like, how will I query it, and how much am I willing to give up to scale?
Get that framing right and the choice makes itself. Get it wrong and you either bolt joins onto a key-value store that has none, or shard a payments ledger that needed one strong transaction the whole time.
What SQL and NoSQL actually mean
A SQL database — Postgres, MySQL, SQL Server — stores data in tables with a fixed schema, enforces relationships with foreign keys, and lets you ask arbitrary questions in a declarative language. A cost-based optimizer figures out how to run your query; you only say what you want. Joins, aggregations, and transactions are first-class.
"NoSQL" is an umbrella over four unrelated designs:
| Family | Examples | Data shape | Best at |
|---|---|---|---|
| Document | MongoDB, Couchbase | JSON-like aggregates | Fetching/updating a whole object by ID |
| Key-value | DynamoDB, Redis | Opaque value under a key | Ultra-low-latency lookups by exact key |
| Wide-column | Cassandra, HBase | Rows partitioned + clustered | Massive write throughput, time-series |
| Graph | Neo4j, Neptune | Nodes and edges | Multi-hop relationship traversal |
The pattern across all four: they drop joins and (often) strong consistency for horizontal scale, flexible schemas, and access paths tuned to known queries. SQL optimizes for ad-hoc questions over normalized data; NoSQL optimizes for known access patterns at scale. That one sentence resolves most of the debate.
Why ACID is the thing you're really trading
The most important property SQL databases give you is ACID, and it's worth spelling out because it's what you surrender first when you scale out.
- Atomicity — a transaction commits fully or not at all. Debit and credit both land, or neither does.
- Consistency — declared invariants (a balance can't go negative, an email must be unique) hold before and after every transaction.
- Isolation — concurrent transactions behave as if they ran one at a time, at whatever level you pick.
- Durability — once committed, data survives a crash, guaranteed by a write-ahead log and
fsyncto disk.
ACID matters wherever correctness beats raw throughput: payments, inventory, authentication, double-entry bookkeeping. Without it, you rebuild these guarantees by hand — compensation logic, reconciliation jobs, application-level fights with lost updates and phantom reads. That code is subtle, and it's the kind of subtle that leaks money.
The catch is that "Isolation" is a dial, not a switch. Most databases don't run fully serializable by default because it's expensive.
| Isolation level | Prevents | Still allows | Typical use |
|---|---|---|---|
| Read Committed | Dirty reads | Non-repeatable reads, phantoms | Postgres default, most OLTP |
| Repeatable Read | Non-repeatable reads | Write skew | MySQL default |
| Serializable | Everything, incl. write skew | (nothing) | Financial critical sections |
Postgres defaults to Read Committed; MySQL InnoDB to Repeatable Read. Serializable is the only level that stops write skew — two transactions each reading a shared invariant and independently violating it — and Postgres implements it via Serializable Snapshot Isolation, aborting transactions when it detects a dangerous read-write cycle. Higher isolation means more aborts and lower throughput, so the common move is Read Committed plus SELECT ... FOR UPDATE on the few rows that truly need locking.
When a document store beats a relational one
Reach for MongoDB (or Postgres' own JSONB, often the smarter answer) when three things line up:
- The data is naturally hierarchical. A product with nested variants, a CMS page, a user-submitted form — things you almost always load as one whole aggregate.
- The schema varies per record. Different tenants, different fields, no clean table to migrate.
- Access is by ID, not by join. You fetch or update the whole document; you rarely join it against other collections.
A document store rewards this shape: writes to a single document are atomic, sharding is built in, and a flexible schema saves migration pain in a product's volatile early life.
But be honest about the ceiling. The moment you need a transaction spanning several documents, complex joins, or real relational invariants, you're fighting the tool — and a lot of teams reach for MongoDB when a Postgres JSONB column would have done the job with transactions intact.
Why NoSQL modeling is "query-first," not "entity-first"
This is the concept people get most wrong, and it's where a NoSQL choice quietly fails in production.
In SQL you model entities — normalize users, orders, and products into their own tables, then join at read time. Wide-column and key-value stores don't work that way: in Cassandra or DynamoDB, the partition key decides which node physically holds the data, and the sort key decides order within that partition. There are no efficient joins and no cheap full scans at scale — model normalized entities there and every query turns into a cross-partition scatter-gather, exploding latency and cost.
So you invert the process. Enumerate your access patterns first, then build one denormalized table per pattern:
Access patterns you support:
- get a user by id -> table: user_by_id (PK: user_id)
- list a user's orders -> table: orders_by_user (PK: user_id, SK: order_ts)
- list orders on a given day -> table: orders_by_date (PK: date, SK: order_ts)You accept duplicated data and write amplification (one logical write fans out to several tables) in exchange for every read being a single-partition lookup. DynamoDB formalizes this as single-table design: one table, overloaded partition/sort keys, and global secondary indexes for alternate access paths. If you can't list your queries up front, you're not ready to model in a wide-column store yet.
Indexes aren't free — especially on writes
Every secondary index is a second data structure updated on each write. One logical insert becomes 1 + N physical writes and fsyncs, so a table with five indexes can run at roughly half the write throughput of one with none.
SQL and NoSQL handle this differently:
- Postgres / MySQL update indexes synchronously inside the transaction — reads stay consistent, writes pay the full cost immediately.
- DynamoDB GSIs update asynchronously and are eventually consistent — writes stay fast, but an index read can briefly return stale results.
- Cassandra local secondary indexes live per node and fan out on query — fine for low-cardinality columns, a trap for high-cardinality ones.
Index only for queries you actually run, and use covering indexes so a read never bounces back to the heap.
Can you have ACID and horizontal scale? NewSQL's pitch
NewSQL systems — CockroachDB, Google Spanner — claim to give you SQL, serializable transactions, and horizontal scale. They aren't lying, but the guarantees have a price.
They shard data into small ranges (Spanner tablets, Cockroach ranges around 64MB), replicate each with a consensus protocol (Raft or Paxos), and stamp every transaction with a globally ordered commit timestamp — Spanner via TrueTime atomic/GPS clocks, Cockroach via hybrid-logical clocks. Distributed transactions run two-phase commit through a range's Raft leader, with tricks like parallel commits to skip a round-trip.
What you actually pay:
- Writes cost a consensus round-trip — roughly 2–10ms within a region, 50–150ms across regions. There's a latency floor you can't optimize away.
- Hot rows serialize through a single leaseholder, so contention on a popular key still bottlenecks.
You genuinely get relational semantics at scale, but the physics of coordination don't disappear.
When OLTP and OLAP collide
One more decision that masquerades as a database choice: analysts running heavy reporting queries against your production database and slowing the app to a crawl.
The fix isn't a bigger primary — it's recognizing these are two different workloads. OLTP (transactions) wants row-oriented storage, low latency, and high-concurrency point reads and writes. OLAP (analytics) wants column-oriented storage that scans billions of rows fast.
Stream changes out via change data capture (Debezium, logical replication) into a columnar warehouse — Snowflake, BigQuery, ClickHouse — which compresses data 10–100x and vectorizes scans. Analysts query the warehouse; the production primary only ever serves app traffic. You accept minutes of freshness lag for analytical queries that run 100–1000x faster with zero impact on production.
How this shows up in interviews
When you say "I'll use Postgres" or "I'll use DynamoDB," the interviewer's real question is why. Expect follow-ups: what's your partition key, and does it create hot partitions? Do you need a transaction across these two entities? How do secondary indexes affect write throughput? What happens at 10x the load?
Answer from the data and the access patterns, not the product name: say which operations need ACID and which tolerate eventual consistency, name the one query that drives your partition-key choice, then state the trade-off you'd reverse if the workload shifted. That reasoning — not memorized vendor trivia — is what signals seniority.
Further learning
- SQL vs. NoSQL Explained (in 4 Minutes) — Exponent's fast, clear primer on the core distinction.
- SQL vs NoSQL: Choosing the Right Database — ByteByteGo's deeper decision framework with diagrams.
- Related roadmap topics: CAP Theorem, Caching, Load Balancing, and CDN.
- Practice this topic on YeetCode to work through the decision interactively.
FAQ
Is SQL or NoSQL faster?
Neither is universally faster — they're fast at different things. A NoSQL key-value store beats SQL on a single-key lookup because it does no query planning and no joins. A SQL database beats NoSQL when the answer requires combining several entities, because it can join them in one query instead of making the application stitch results together. Speed is a property of matching the store to the access pattern, not the label on the box.
When should I avoid NoSQL?
Avoid it when correctness depends on multi-row transactions or your queries aren't known in advance. Payments, inventory, and bookkeeping need ACID guarantees that most NoSQL stores weaken. And because wide-column and key-value stores force you to model tables per query, an app whose access patterns are still shifting will keep outgrowing its schema. If you can't list your queries up front, a relational database — Postgres with JSONB for the flexible parts — is usually the safer default.
What is the difference between ACID and BASE?
ACID (Atomicity, Consistency, Isolation, Durability) is the relational contract: transactions are all-or-nothing and immediately consistent. BASE (Basically Available, Soft state, Eventual consistency) is the NoSQL trade-off: the system stays available under partitions and returns correct data eventually, but a read right after a write might be stale. ACID favors correctness; BASE favors availability and scale — which you want depends entirely on whether stale reads are a bug or an acceptable cost.
Can Postgres replace MongoDB?
For most document workloads, yes. Postgres' JSONB type stores and indexes schemaless JSON while keeping full ACID transactions and joins with your relational tables. It handles roughly 80% of what teams reach for MongoDB to do. MongoDB still wins when you need built-in horizontal sharding out of the box or truly massive document throughput, but "our schema is flexible" alone rarely justifies giving up transactions.
How do I choose a partition key?
Pick a key that is high-cardinality (many distinct values so load spreads evenly) and aligned with your dominant query (so data you read together lives in one partition). A user_id is a good key for per-user data; a low-cardinality field like status or country is a bad one because a few hot partitions absorb all the traffic. The wrong partition key doesn't show up until scale, when one node melts while the rest sit idle — exactly why interviewers ask.
Make it stick: run this one yourself
Don't just read it — step through it interactively, one state change at a time.