Relational Vs. Non-Relational Databases

The real split is not SQL vs. no-SQL or one big server vs. many small ones — it is how related data is laid out on disk: a relational engine normalizes data into separate tables and reconstructs answers at read time with joins and multi-row transactions, which are cheap on one node but require coordination once data is spread across machines; a NoSQL engine instead pre-groups everything a query needs into one self-contained unit (a document, a row keyed by partition, a value) so a read or write touches exactly one node — and then refuses or weakens the cross-unit joins and transactions that would force coordination.

Everything else in the comparison flows from that one decision. Both families can run on many servers; what differs is what each makes easy once they do.

Worked example: "show an order with its 3 line items and the customer's name"

Take a tiny e-commerce dataset. The relational design normalizes it into three tables; the document design embeds the same facts into one order document.

Relational layout (PostgreSQL), normalized into 3 tables

customers:  id=42   name='Asha Rao'
orders:     id=1001 customer_id=42  total=58.00
order_items:                       (3 rows for order 1001)
            order_id=1001  sku='PEN-01'  qty=2  price=3.00
            order_id=1001  sku='MUG-07'  qty=1  price=12.00
            order_id=1001  sku='LMP-22'  qty=1  price=40.00

To render the order page you join across all three:

SELECT o.id, o.total, c.name, i.sku, i.qty, i.price
FROM orders o
JOIN customers  c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.id = 1001;

On a single node this is fast: the planner uses the primary-key index on customers, the index on orders.id, and the index on order_items.order_id, then merges the matched rows in memory. The customer name 'Asha Rao' is stored once — update it in one place and every order reflects it.

Document layout (MongoDB), one self-contained order

{ "_id": 1001, "customer_id": 42, "customer_name": "Asha Rao", "total": 58.00,
  "items": [ { "sku": "PEN-01", "qty": 2, "price": 3.00 },
             { "sku": "MUG-07", "qty": 1, "price": 12.00 },
             { "sku": "LMP-22", "qty": 1, "price": 40.00 } ] }

db.orders.findOne({ _id: 1001 })   // one document, one node, no join

The whole order page is one read of one document. The trade is now explicit: customer_name is duplicated into every order Asha ever placed. If she changes her name, you must rewrite N documents — the engine will not do it transactionally for you across all of them.

Step-by-step: what each engine pays

Corrected comparison

The original table framed "vertical vs. horizontal scaling" as an intrinsic property and claimed NoSQL has "no query language." Both are wrong — see the corrected rows below.

Why the naive "vertical vs. horizontal" row is wrong

It conflates a deployment choice with the data model. Relational systems shard and replicate routinely (Vitess shards MySQL across thousands of nodes; Spanner is a globally distributed SQL database). The honest statement is causal, not categorical: the relational model makes cross-entity joins and multi-row transactions easy, and those are exactly the operations that get expensive to distribute — so going horizontal costs relational systems more. NoSQL pre-pays that cost by denormalizing, which is why it distributes cheaply.

Pitfalls

• "NoSQL is web-scale, SQL is not." A single Postgres node handles tens of thousands of writes/sec and terabytes; most apps never outgrow it. Choosing NoSQL for imagined scale usually buys denormalization pain you didn't need.
• The denormalization update trap. Embedding customer_name into every order is fast to read but means a rename is a fan-out write across N documents — with no transaction guaranteeing all-or-nothing. Stale, inconsistent copies are the classic production bug.
• Assuming a single document/row write is the same as a transaction. Per-document atomicity does not span documents. "Decrement inventory and create the order" across two documents needs an explicit multi-document transaction — and if your store doesn't support it, you get oversold stock under concurrency.
• Designing the document model around the wrong query. Embedding optimizes one access pattern. The day product needs "all orders containing SKU LMP-22" or "total spend per customer," an embedded-order schema forces a full scan, while the normalized relational schema answers it with an index and a GROUP BY.
• Forgetting relational can be distributed too. Reaching for Cassandra purely to "go horizontal" when CockroachDB, Spanner, or a sharded Postgres would keep your joins and transactions is a common over-correction.

Takeaways

• The defining axis is data layout: normalized + joined at read time (relational) vs. pre-grouped into one self-contained unit (NoSQL) — not SQL vs. no-SQL and not one server vs. many.
• Relational makes joins and multi-row ACID cheap on a node and expensive to distribute; NoSQL gives those up so each read/write hits one node, making sharding cheap.
• NoSQL stores do have query languages (MQL, CQL, N1QL, Cypher) and relational stores do scale horizontally — the difference is what stays cheap after you distribute.
• Pick by access pattern and consistency need: many related entities + ad-hoc queries + money → relational; one dominant pattern + denormalizable + extreme volume → NoSQL.

Sources: Martin Kleppmann, Designing Data-Intensive Applications, ch. 2–3 (relational vs. document models, normalization vs. locality, the "join is the app's join" argument); PostgreSQL documentation (query planning, joins, MVCC transactions); MongoDB Manual (document model, embedding vs. referencing, multi-document transactions); Apache Cassandra CQL and DataStax data-modeling guides; Google Cloud Spanner and CockroachDB docs (distributed SQL with joins and transactions). Re-authored and deepened for this guide: replaced the misleading "vertical vs. horizontal scaling" row and the "no query language" claim with a mechanism-first explanation, a normalized-vs-embedded worked example, a diagram, and production pitfalls.