Databases in system design

The axis is access pattern, not scale

Candidates are taught a false dichotomy: relational databases are for small consistent things, NoSQL is for big fast things, so pick NoSQL when the prompt says "millions of users." Interviewers at senior level are specifically listening for this and will push back, because it is wrong on the facts. Relational systems run at enormous scale (a large fraction of the world's payment and social infrastructure is sharded MySQL or Postgres), and plenty of NoSQL deployments are small.

The honest framing is that these systems made different bargains. Relational databases keep joins, ad-hoc queries, and multi-row transactions — features that are hard to distribute — so distributing them is work you do yourself. Early NoSQL systems dropped those features up front, which is exactly what lets them partition automatically. Many have since added limited versions: MongoDB supports multi-document ACID transactions (replica sets since 4.0, sharded clusters since 4.2) and DynamoDB offers all-or-nothing TransactWriteItems across up to 100 items within one region. Both document a performance cost, and both still assume a data model built around single-key access. So the question is never "how much data?" It is: do I know my access patterns in advance, and what do I need to be true across rows at once?

Dry run: the six questions applied to a chat app's message history

Four of six questions point the same way (one is neutral, one only constrains the write path), which is the signal: a wide-column store keyed by (conversation_id, bucket), clustered by message id descending, matches every access pattern here without paying for joins or multi-row transactions the workload never uses. A relational store sharded by the same key would also work; the argument is about what you no longer have to operate and pay for, not about what is impossible.

The families, and what each is genuinely good at

Product facts move, so check currency before you name a vendor. Redis changed to source-available licences in March 2024, the Linux Foundation forked it into a community project, and Redis 8 added AGPLv3 as an option in May 2025. CockroachDB retired its free self-hosted Core edition in late 2024 in favour of one enterprise licence (free for small companies). In an interview the distinction that matters is the data model and consistency behaviour; the licence is a procurement detail you can mention in one clause.

Transactions: what "ACID" hides

"ACID" is a bundle, and the letter that surprises people is the I. Isolation is a dial, not a switch. PostgreSQL defaults to Read Committed, where two statements in one transaction can see different committed data. Repeatable Read is snapshot isolation: a stable view, but two transactions can each read a shared fact and write different rows based on it (write skew), for example two on-call doctors each going off call after checking that the other is still on. Serializable (implemented in PostgreSQL as serializable snapshot isolation) rules that out, at the price that the database may abort a transaction with a serialization failure, which your code must retry. Say which isolation level a money-moving path needs, and what retries it. The transaction chapters go further.

The same data, modelled two ways

Normalize by default: one fact in one place means an update is one write and contradictions are impossible. Denormalize deliberately, when a measured read path is too expensive and the data is read far more often than it changes. The three costs you must name when you propose it are write amplification (one logical change touches many records), update anomalies (copies drift, and you now need a repair job), and growth (an embedded array of comments is unbounded, and most document stores have a hard per-document size limit).

Notice the symmetry with feed design: a normalized model is fan-out-on-read, a denormalized one is fan-out-on-write. It is the same tradeoff at a different altitude, which is worth saying out loud — interviewers reward candidates who recognise a pattern they have already discussed.

Indexes: what they buy and what they cost

An index is a second data structure — usually a B-tree — that maps column values to row locations in sorted order, turning a full table scan into a logarithmic descent. That is the benefit, and it is enormous: on a ten-million-row table it is the difference between seconds and microseconds. The cost is paid on every write, and candidates almost never mention it unprompted.

• Write amplification. Each secondary index adds a structural update per write, often at a random location in the tree. On a write-heavy table the cost grows with every index you add (benchmark your own table rather than trusting a rule of thumb), and index maintenance also inflates the WAL.
• Selectivity decides everything. An index only helps when it eliminates most rows. An index on a boolean column with a 50/50 split is worse than useless — the planner will correctly ignore it, because random-access lookups for half the table cost more than a sequential scan.
• Composite indexes are left-prefix. An index on (a, b, c) serves queries filtering on a, on a and b, or on all three; classically it does not serve a query filtering only on b. Order the columns by equality-filters first, then the range or sort column. Newer engines can sometimes bend the rule: PostgreSQL 18 added skip scan for multicolumn B-trees, and MySQL 8.0.13 added a limited skip-scan range method. Both probe once per distinct value of the skipped leading column, so they help only when that column has few distinct values. Design for the left-prefix rule and treat skip scan as a bonus.
• Covering indexes. If the index contains every column the query needs, the database never touches the table — an index-only scan, often several times faster.
• The planner can be wrong. It chooses from cost estimates based on statistics, and stale statistics or a skewed distribution produce catastrophically bad plans, which is why "read the query plan" is a real skill.

-- The query the product actually runs
SELECT id, body FROM posts
WHERE author_id = 42 AND created_at > now() - interval '7 days'
ORDER BY created_at DESC LIMIT 20;

-- Wrong order: created_at first means every recent post by anyone is scanned
CREATE INDEX ON posts (created_at, author_id);

-- Right: equality column first, then the range/sort column
CREATE INDEX ON posts (author_id, created_at DESC);
-- Now the rows are already sorted within author_id -- the LIMIT stops early,
-- and EXPLAIN ANALYZE shows an index scan with no sort node at all.

Read-heavy and write-heavy are different problems

Being able to say "B-tree for read-optimised in-place updates, LSM for write-optimised sequential flushes, and the LSM's price is read amplification plus compaction" is one of the highest-value-per-word things you can put on the whiteboard. It explains a whole category of database choices in one sentence.

Connection pooling, the bottleneck nobody draws

Databases do not accept unlimited connections, and the limit is far lower than people expect. Postgres runs a process per connection, and typical configurations cap out between 100 and 500. The memory per connection is smaller than folklore says (a Postgres core developer measured under 2 MiB with huge pages enabled); the bigger cost is CPU, because the work of taking a snapshot grows with the number of connections, so even idle ones slow the active ones (improved in Postgres 14, but not gone). MySQL uses threads and is cheaper, but the shape is identical. Meanwhile a fleet of 50 app servers each holding a 20-connection pool wants 1,000 connections, and the database falls over — not from query load, but from connection count.

Where "just use Postgres" is the right senior answer

A candidate who reaches for six specialised stores in a 45-minute design is describing an operational burden, not an architecture. Every additional datastore is another failure mode, another backup and restore story, another consistency boundary, and another thing your on-call has to understand at 3 a.m. One mature relational database covers a startling amount of ground:

• Document store — JSONB columns with GIN indexes handle schemaless data honestly well
• Key-value — a two-column table with a primary key is a fine key-value store at any scale a single node handles
• Search — built-in full-text is genuinely good up to millions of documents before Elasticsearch earns its keep
• Queue — SELECT ... FOR UPDATE SKIP LOCKED gives you a correct work queue at modest job rates. Each job costs at least three writes (enqueue, claim, complete), so every job per second spends part of your ~5,000 writes/sec budget
• Geospatial, time-series, vectors — PostGIS, partitioned time ranges, and pgvector all exist and are used in production
• Analytics — a read replica keeps reporting queries off the primary until data volumes genuinely demand a columnar engine

The point is not that Postgres is always right. The point is that consolidating on one store buys operational simplicity, and simplicity is a legitimate design goal you are allowed to argue for. State the exit conditions and you get the credit without sounding dogmatic.

Recognizing it in an unseen problem

• Before choosing a store, write the two or three highest-volume queries as literal key lookups — the key you need is usually the partition key, and it decides the store more than the store decides the key
• "Transaction", "balance", "inventory", "booking", "must not double-charge" → relational with real ACID, or an explicit idempotency and reconciliation design; do not hand-wave this one
• "Timeline", "feed", "events", "telemetry", "messages" with huge write volume and partition-scoped reads → wide-column on an LSM engine; expect the follow-up about hot partitions
• If the prompt implies free-text search or analytics, propose a derived index fed from the source of truth, and be ready to explain how it stays in sync (change data capture, dual writes plus reconciliation, or a periodic rebuild)
• Any time you propose denormalization, name the write amplification and the repair path in the same breath — proposing it without them is the most common way to lose the data-modelling signal
• Distinguish "this table is a bottleneck" from "this database is a bottleneck": the first is solved with an index, a cache, or moving one table, and the first is far more often the truth