SQL vs NoSQL — and why, not just names — in depth

Data lives in tables of rows and columns, the shape is fixed by a schema, and rows in different tables reference each other by id. That is the familiar part. The valuable part is what the database promises about *changes*.

A transaction is a group of changes that happen together or not at all. Transfer money between two accounts and you have two writes: subtract from one, add to the other. A transaction guarantees you never get one without the other, even if the machine loses power between them.

That guarantee has a four-letter name, and it is worth taking one word at a time rather than reciting the acronym.

Normalisation means storing each fact exactly once. A customer's address lives in the customers table, and orders point at the customer by id rather than copying the address. Change the address once and every order reflects it.

The benefit is that data cannot contradict itself, because there is only one copy to be wrong. The cost is joins: assembling a useful view means reading several tables and matching them up, and joins get expensive as tables grow, particularly once the tables live on different machines.

Denormalisation stores a fact more than once on purpose to avoid the join. Copy the customer name onto the order row so displaying an order needs one read instead of two.

You are trading write complexity and correctness risk for read speed. Now a name change has to update many rows, and if one update fails you have two versions of the truth. Denormalise when reads massively outnumber writes and you have measured the join as the problem. Not before.

People jump from "Postgres" to "we need NoSQL" while skipping steps that would often have solved the problem. Take them in order, because each is dramatically cheaper than the next.

1. Tune what you have. Add the missing index. Fix the N+1 query. Stop selecting columns you do not use. A missing index alone can turn a full table scan into a lookup. This costs an afternoon and needs no architectural change.

2. Add read replicas. Copies of the database that stay in step and serve reads. Read capacity multiplies; write capacity does not change at all, because every replica applies every write. The cost is replication lag: a replica is always slightly behind, so a user can write and then not see their own change.

3. Federation: split by feature. Move whole tables to their own database: users here, products there, orders somewhere else. Each database gets smaller, its working set is more likely to fit in memory, and total write capacity grows because writes are spread across several primaries. The cost is that you can no longer join across the boundary, and no transaction spans two of them.

4. Sharding: split by row. Same table, split across machines by a key: users A–M here, N–Z there. This is the only move that raises write throughput for a single table, and it is by far the most expensive to do and to live with.

"NoSQL" is not one thing. It is four quite different families that share only the property of not being relational tables. Treating them as interchangeable is a common mistake.

ACID describes what relational databases promise. BASE describes what many distributed stores offer instead, and it is weaker on purpose.

Basically Available: the system answers, even during failures, possibly with stale or partial data. Soft state: the data can change on its own as copies reconcile, without a write happening. Eventually consistent: stop writing and all copies converge, with no promise about when.

This is not laziness; it is the direct consequence of wanting to survive machines and networks failing while still answering. If you insist every copy agrees before you answer, you cannot answer when a copy is unreachable. BASE chooses to answer.

The practical consequence is that correctness moves into your application. If two people can edit the same thing, you decide what winning means. If a counter can be incremented in two places, you decide how to merge. The database will not stop you getting this wrong.

A procedure you can run out loud, rather than a preference you assert.

First: is there an invariant that must never break under concurrency? Seats, stock, balances, unique usernames. If yes, you want real transactions where that invariant lives, and that means relational unless you have a very good reason.

Second: what is the one query this system runs ten thousand times a second? If it is "give me everything for this key", a key-value or wide-column store fits and its limits will not hurt. If you cannot predict the queries, you want the flexibility of SQL.

Third: do the numbers force it? Estimate the write rate. If a single primary can commit it (and one modern machine commits far more than people assume), then the scale argument for going distributed does not exist yet, and you should say so.

Fourth: can you use both? Many production systems do. Transactional core in Postgres, the enormous append-only event stream in a wide-column store, the cache in Redis, the search index in something built for search. Saying this is not indecision; it is how many production systems are built.