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. Most NoSQL systems removed those features up front, which is exactly what lets them partition automatically. 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?

The families, and what each is genuinely good at

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. Five indexes on a hot table can cut write throughput by half or more, 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; it does not serve a query filtering only on b. Order the columns by equality-filters first, then the range or sort column.
• 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 forks a process per connection at roughly 5-10 MB of overhead each, and typical configurations cap out between 100 and 500. 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, comfortably into the thousands of jobs per second
• 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