SQL vs. NoSQL: Choosing a Data Model

"SQL vs. NoSQL" gets framed as a single decision, but it collapses several independent trade-offs — schema strictness, how related data is stored, how the system scales, and what guarantees a write gives you — into one label. Treating "NoSQL" as a single alternative to relational databases is where most of the bad decisions start.

"NoSQL" isn't one thing

The term groups together at least four genuinely different data models, and they don't share much beyond "not relational." Document stores (MongoDB, Couchbase) store self-contained JSON-like records. Key-value stores (Redis, DynamoDB in key-value mode) map a key to an opaque blob with no structure the database understands. Columnar/wide-column stores (Cassandra, HBase, Bigtable) organize data by column family for fast writes and range scans over huge datasets. Graph databases (Neo4j, Neptune) make relationships between records first-class, queryable objects rather than foreign keys.

A document store and a graph database solve completely different problems and make completely different trade-offs. Lumping them together as "the NoSQL option" is a bit like lumping Postgres and a spreadsheet together as "the row-based option" — technically true, practically useless for making a decision.

Schema-on-write vs. schema-on-read

A relational database enforces its schema on write: every row has to match the table's columns and types before it's accepted, and a migration is a deliberate, tracked event. Most document and key-value stores enforce schema on read instead — the database will happily accept whatever shape of document you hand it, and it's up to the application code reading it back to know what to expect.

Schema-on-read buys real iteration speed early on: you can add a field to half your records without a migration, ship faster, and figure out the "real" shape of the data as the product evolves. What it quietly costs you is data quality drift — six months in, it's common to find three different shapes of the "same" document, because three different code paths wrote it at three different points in the product's history, and nothing stopped any of them. Schema-on-write pays that cost up front, as friction on every migration, in exchange for a guarantee that every row in the table actually looks like every other row.

Joins vs. denormalization

Relational databases handle related data by normalizing it into separate tables and reassembling it at query time with a join — an order references a customer row and a product row rather than copying their data in. This keeps each fact stored exactly once, so an update to a customer's address is a single write that every order automatically reflects.

Most NoSQL data models avoid joins almost entirely, because they weren't built to do them efficiently across a distributed cluster. Instead, related data is denormalized — embedded directly into the document that will be read together. An order document might embed a snapshot of the customer's shipping address and the product's name and price at the time of purchase. That's often exactly what you want for an order (youwant the historical price, not today's), but it means the same fact can now live in thousands of places, and updating it everywhere is your application's problem, not the database's.

Scaling: vertical roots vs. horizontal by design

Most relational databases grew up assuming a single primary node: scale by buying a bigger machine, adding read replicas for read traffic, and — only when that stops working — sharding writes across multiple primaries by hand. That last step is possible and common in practice, but it's bolted on rather than native, and it reshapes how joins and transactions work once data lives on different machines, which is its own deep topic covered elsewhere in this series.

Most NoSQL stores were designed the other way around: horizontal scaling across many commodity nodes was a day-one requirement, not a later retrofit. Cassandra and DynamoDB partition data by key across the cluster automatically; adding capacity is closer to adding another node than redesigning your data layout. The trade-off is that the features that make single-node relational databases convenient — arbitrary joins, strong global consistency — get harder to offer cheaply once your data is scattered across a hundred machines, so most of these systems relax one of them on purpose.

Transactions: ACID by default vs. by exception

Relational databases default to full ACID transactions — atomicity, consistency, isolation, durability — across arbitrary sets of rows and tables. You can update an order, decrement inventory, and record a payment in one transaction, and the database guarantees all three happen or none do, even under concurrent load.

Many NoSQL stores historically offered weaker guarantees by default — atomic writes to a single document or row, but no multi-document transaction, in exchange for higher throughput and simpler distributed coordination. That line has blurred considerably: MongoDB has supported multi-document ACID transactions since version 4.0, and DynamoDB offers transactional write APIs across multiple items. The guarantee is available in more places than it used to be — but it's usually still the exception you opt into for a specific operation, not the default posture of the system the way it is in a relational database.

A practical rule of thumb

Start relational unless you have a specific, articulable reason not to. "NoSQL for scale" is cargo-culting if you can't name the access pattern or the scaling ceiling that a relational database would actually hit — Postgres comfortably handles workloads far larger than most applications will ever reach, and it gives you joins, strong transactions, and a mature ecosystem for free while you find out. Reach for a specific NoSQL data model when you have a concrete reason that matches its strength: a cache or session store that needs key-value speed, an analytics pipeline that needs wide-column ingest at massive write volume, a recommendation engine where relationship traversal is the query. Choosing the data model to match the access pattern, rather than the label that sounds more "web-scale," is the same discipline either way.