Relational Databases and ACID

In one line: relational databases are the default for a reason, and "we'll use NoSQL for scale" is a weak answer if you cannot articulate what you are giving up. ACID is the thing you are giving up.

Why they dominate

Relational databases are dominant due to their simplicity, robustness, flexibility, and scalability. To maintain data integrity they provide ACID properties — a powerful abstraction that automatically handles complex anomalies such as dirty reads, lost updates, and phantom reads, so application logic does not have to.

The four properties

Popular relational database management systems include MySQL, Oracle Database, Microsoft SQL Server, IBM DB2, PostgreSQL, and SQLite.

Isolation is a dial, not a switch

The table above states isolation as a single guarantee: concurrent transactions behave as if they ran one after another. That is the strongest version, and almost no production database is running it by default.

Full serializability is expensive — it requires the database to detect and prevent every possible interleaving, which means either heavy locking or aborting transactions that conflict. So databases offer levels, and each one permits a specific set of anomalies in exchange for throughput.

The consequence worth internalising is that the default is not the strongest. A transaction that reads a balance, does arithmetic, and writes it back is not automatically safe under read committed — two of them can interleave and lose an update, and the database will not complain, because it is providing exactly what was asked of it.

Three ways to fix that specific shape, and knowing which to reach for is the signal:

Do the arithmetic in the database — UPDATE ... SET balance = balance - 10 — so the read and write are one atomic statement and never interleave. Cheapest and easiest to miss.

Take an explicit lock on the row for the duration (SELECT ... FOR UPDATE), which serialises just the transactions touching that row.

Raise the isolation level for that transaction, and be prepared to retry it, because a serializable database refuses conflicting transactions rather than blocking them.

The framing to carry: ACID's "I" is a menu, and picking from it is a design decision with a throughput price. Saying "we use a relational database, so we get isolation" is the answer that misses this entirely.

Why relational databases stay the default

Their primary strengths are the abstraction of ACID transactions and the standardization of programming semantics — SQL means the skill transfers between systems. Beyond that:

Normalization is the point people undersell

"Information lives in one place" sounds like a storage optimization. It is really a correctness property: if a customer's address exists in one row, it cannot disagree with itself. Denormalized systems — which most NoSQL designs are — trade that guarantee for read performance, and the cost reappears as update anomalies you now handle in application code.

Indexes: what makes a query fast, and what it costs

Everything above is about correctness. The reason a database is usable at all is the index, and it is worth understanding as a data structure because the later lessons on secondary indexes and partitioning assume it.

Without one, answering "find the row where email = x" means reading every row — a full table scan, whose cost grows linearly with the table. An index is a separate structure, sorted by the column you search on, that turns that scan into a small number of lookups.

The default structure is a B-tree, and the reason is that it is good at more than one thing. Because it keeps entries sorted, it answers exact matches, range queries (created_at > '2024-01-01'), and ordering (ORDER BY) from the same structure — a hash index would be faster for exact matches alone and useless for the other two.

Every index makes writes slower

This is the part people leave out, and it is the half that matters in a design discussion.

An index is a copy of a column, kept sorted. It must be maintained on every insert, update, and delete that touches the indexed column, so each one adds write cost and disk space. That gives the rule of thumb worth carrying: indexes trade write throughput for read latency, and adding one to "just in case" is a real, permanent cost.

The corollary is that a composite index on the columns you actually query together usually beats separate indexes on each — one structure maintained instead of several, and it can satisfy the query on its own. Column order matters, though: an index on (user_id, created_at) helps a query filtering by user_id, and does nothing for one filtering only by created_at.

Indexes are how relational databases absorb specialised workloads

The other thing worth knowing is that "index" is a family, not one structure — and the family is why you can often avoid adding a second database.

The judgement to offer in an interview: reach for an index before reaching for another database. A second datastore means a second thing to operate, keep in sync, and reason about during an incident — so the bar for adding one should be a requirement the index genuinely cannot meet, such as fuzzy matching, sophisticated relevance ranking, or a corpus far larger than the primary database should hold.

The drawback: impedance mismatch

Impedance mismatch occurs when the relational model (tables) and in-memory data structures (objects) are not aligned. Application code uses complex, nested structures; databases use flat tables. Bridging them requires a translation layer that maps objects to rows, adding complexity and overhead.

A single aggregated value in the application's view is composed of several rows across several tables. Every read reassembles it; every write decomposes it.

This is the specific pain that document databases were built to remove — and it is the honest reason to reach for one, far more than "NoSQL is faster."

Interview signal by level

Next: the four families that relax those guarantees, and what each buys instead.