Database Interview Questions, Answered

Read it inside out, bottom-up, and compare two numbers per node: rows estimated versus rows actual. A plan is a tree; the deepest nodes run first and hand their rows upward.

EXPLAIN alone prints the planner's guess. EXPLAIN (ANALYZE, BUFFERS) actually runs the query and prints what happened, including how many blocks came from cache versus disk. Because it runs, wrap it in a transaction you roll back before pointing it at an UPDATE.

The estimate gap is the tell. A node that expected 12 rows and got 400,000 did not just misreport itself: the planner chose the join strategy above it based on that 12, so a nested loop is now running 400,000 times.

1. Estimated vs actual rowsthe gap explains almost every bad plan
2. ↓
   actual time and loopsper-loop time multiplies by loops
3. ↓
   Rows Removed by Filterwork done to throw work away
4. ↓
   Sort Methodexternal merge Disk means work_mem is too small

Costs are unitless and only useful for comparing two candidate plans. Wall-clock time on the node, and the row gap that caused it, are what you act on.

Usually because the index cannot answer the query as written, or because a sequential scan is genuinely cheaper. The planner is a cost model, not a rule engine, and it is right more often than it is wrong.

The four causes worth memorizing:

• The predicate is not sargable. Wrapping the column in a function or cast defeats the index: WHERE lower(email) = $1 needs an expression index on lower(email), and WHERE created_at::date = $1 needs a range predicate instead.
• Type mismatch. Comparing a bigint column to a string literal, or text to varchar across a join, can force a cast on the indexed side.
• Low selectivity. If the predicate matches 40% of the table, walking the index and then fetching those heap rows randomly costs more than reading the table sequentially.
• Stale statistics. After a bulk load the planner still believes the table has 1,000 rows. ANALYZE the table before concluding anything.

-- defeats the index on email
WHERE lower(email) = 'a@b.com'
-- uses it
WHERE email = 'a@b.com'

Treat it as a hypothesis test: EXPLAIN the query, disable the seq scan for one session to see what the alternative would have cost, then fix the predicate or the statistics rather than the planner.

Equality columns first, then the range or sort column. An index on (a, b) is a single sorted structure ordered by a, then b within each a, so it can serve a alone or a plus b, but never b alone. That is the leftmost-prefix rule, and it decides everything.

For WHERE tenant_id = $1 AND created_at > $2 ORDER BY created_at DESC, the index is (tenant_id, created_at). Put created_at first and every tenant's rows are scattered through the whole index.

CREATE INDEX ON events (tenant_id, created_at DESC) INCLUDE (status);

The INCLUDE columns are the second half of the answer. If every column the query touches lives in the index, Postgres can serve it with an index-only scan and never visit the table. That only works when the visibility map says the pages are all-visible, so an index-only scan on a heavily updated table quietly stops being index-only until vacuum catches up.

One composite index usually replaces two single-column ones. Adding both instead is how tables end up with eight indexes and a write path that pays for all of them.

Because OFFSET does not skip rows, it reads and discards them. Page 5,000 at 20 rows per page fetches 100,020 rows, throws away 100,000, and returns 20. The cost grows linearly with how deep the user scrolls, and the deep pages are exactly the ones a crawler hits.

Keyset pagination (also called seek pagination) remembers where the last page ended instead:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)   -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

That reads 20 rows on page one and 20 rows on page 5,000, because the index seeks straight to the cursor. The tuple comparison needs a total order, which is why id rides along: created_at alone has ties, and ties either skip rows or repeat them.

What you give up is jumping to an arbitrary page number, so this fits infinite scroll and API cursors, not a numbered pager.

The other half of slow pagination is COUNT(*) for the total. If it is only there to render "page 1 of 4,182", an estimate from the planner statistics is usually a fair trade.

An UPDATE in Postgres does not overwrite a row. It writes a new version of it and marks the old one dead, each version tagged with the transaction that created it (xmin) and the one that removed it (xmax). Your snapshot decides which version you are allowed to see.

That is what buys the property everyone quotes: readers never block writers, and writers never block readers. Nobody waits on a shared read lock because nobody needs one.

The costs all follow from the same mechanism:

• Tables and indexes grow with dead versions, so vacuum is not optional maintenance, it is how space gets reused.
• Every index entry points at a version, so an update to one indexed column can mean writes to several indexes.
• A long-running transaction pins the oldest snapshot still in use, which keeps every dead version newer than it alive.

InnoDB reaches the same guarantee differently, keeping old versions in an undo log rather than in the table, which is why bloat behaves differently there. The mental model transfers; the operational symptoms do not.

A deadlock is two transactions holding what the other needs next. Transaction A locks row 1 then asks for row 2; transaction B locked row 2 and asks for row 1. Neither can proceed, so the database detects the cycle and kills one of them with a deadlock error.

The database's job is detection. Yours is ordering. Almost every application deadlock comes from two code paths touching the same rows in different orders, and the fix is to make the order deterministic:

-- lock in a fixed order, always
SELECT * FROM accounts
WHERE id = ANY($1)
ORDER BY id
FOR UPDATE;

A transfer that locks the lower account id first and a refund that locks the higher one first will deadlock under load, and only under load, which is why this shows up in production and not in tests.

Three habits remove most of the rest: keep transactions short (never hold a lock across an HTTP call), prefer a single statement that does the whole update over read-then-write round trips, and retry the loser with jitter, because a deadlock is a serialization failure and the operation is usually still valid.

All three prevent lost updates. They differ in what you are locking and how long you hold it.

The optimistic version is the one people forget, and it is often the right answer for user-facing edits:

UPDATE documents SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;   -- 0 rows updated means someone else won

Zero rows affected is a 409 for the user, not a retry loop, because the two edits genuinely conflict and a human should decide.

Rule of thumb: pessimistic locks when contention is expected and conflicts are expensive (inventory, balances), optimistic when contention is rare and a conflict is a UI problem, advisory locks when the thing being protected is not a row at all.

Because it pins the oldest snapshot the database must keep visible. Every dead row version newer than that snapshot has to stay on disk in case the long transaction reads it, so vacuum cannot reclaim anything, and the tables and indexes it never touches bloat anyway.

The damage compounds in a specific order:

1. snapshot heldthe transaction's view must stay valid
2. ↓
   vacuum blockeddead versions cannot be reclaimed
3. ↓
   tables and indexes bloatmore pages read for the same rows
4. ↓
   plans degrade, replicas conflictand xid wraparound risk climbs

The usual culprit is not a slow query. It is a transaction opened by an ORM, left idle in transaction while the application waits on an HTTP call, a lock, or a user.

Two defenses. Set idle_in_transaction_session_timeout so a leaked transaction dies on its own, and never open a transaction around anything that is not database work. pg_stat_activity sorted by xact_start tells you within seconds whether this is what you are looking at.

In steps, never in one statement. ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ... is safe on modern Postgres, but ALTER COLUMN ... SET NOT NULL on an existing column scans the whole table while holding an ACCESS EXCLUSIVE lock, and every query queues behind it.

The sequence:

1. Add the column nullableinstant, no rewrite
2. ↓
   Deploy code that writes itboth old and new rows now populated
3. ↓
   Backfill in batchesa few thousand rows per transaction
4. ↓
   ADD CONSTRAINT ... NOT VALID, then VALIDATEvalidation takes a weaker lock
5. ↓
   SET NOT NULLcheap: the validated constraint proves it

Two details make or break it. Set lock_timeout before any DDL (a couple of seconds) so a migration that cannot get its lock fails fast instead of freezing the table behind a long read. And batch the backfill, because one UPDATE over 50 million rows is one transaction, one giant WAL burst, and 50 million dead tuples for vacuum to clean up.

The same expand-migrate-contract shape covers renames, type changes, and splitting a column. The schema change is never the risky part; the lock is.

The real difference is write locality, not size. A bigint from a sequence always inserts at the right edge of the B-tree, so the hot pages stay in cache. A random UUIDv4 inserts everywhere at once, which means page splits, more WAL, and a working set that no longer fits in memory.

UUIDv7 resolves most of the fight. It is time-ordered, so it inserts sequentially like a bigint while staying globally unique and opaque. If you want UUIDs, use v7 and stop paying the random-insert tax.

What actually breaks in practice: someone picks v4, the table reaches a hundred million rows, and insert throughput falls off a cliff with no query to blame, because the cost is in index maintenance rather than in any statement you can find in pg_stat_statements.

Columns for anything you filter, join, sort, or constrain. JSONB for what you only store and hand back. The moment a JSONB key shows up in a WHERE clause across a large table, it wanted to be a column.

What you give up inside JSONB is the machinery that makes a relational database useful: no foreign keys, no NOT NULL, no CHECK on a nested key, no per-column statistics. That last one is quietly the worst. The planner has good estimates for a column and crude guesses for data->>'status', and a crude guess is how you get a nested loop over 400,000 rows.

The costs that surprise people:

• GIN indexes are large and write-amplifying. Indexing a whole document to make one key searchable is a bad trade against one expression index or one real column.
• Updates rewrite the whole document. Changing one key in a 40KB JSONB value writes a new 40KB row version.
• Nothing stops a typo. {"stauts": "paid"} inserts happily and reads as NULL forever.

Where it genuinely fits: third-party webhook payloads kept verbatim for audit, sparse per-tenant custom fields, and event bodies you replay but never query. A common shape is both, with the queried keys promoted to generated columns.

A deleted_at column moves the delete from the database into every query you will ever write, and one forgotten predicate is a data leak rather than a bug.

The costs, in the order teams discover them:

• Correctness. Every read needs WHERE deleted_at IS NULL, including joins two levels deep. Views or a repository layer help; discipline does not.
• Unique constraints break. UNIQUE (email) now blocks re-registering a deleted address, so it becomes a partial index: UNIQUE (email) WHERE deleted_at IS NULL.
• Indexes carry the dead rows. The table only grows, and index scans keep paying for rows nobody will ever read.
• Foreign keys still resolve. Children of a soft-deleted parent are perfectly valid to the database, so orphan-by-flag is invisible.
• Erasure is not a flag. A GDPR or CCPA deletion request is not satisfied by hiding the row, so you end up implementing a real delete anyway.

What people actually want is usually one of three narrower things: an audit trail (an append-only history table), undo (a short-lived pending-delete state with a job that finalizes it), or archival (move the row to a cold table). Each of those is cheaper and more honest than a flag on the hot table.

When reads are the bottleneck and staleness is acceptable for those reads. A replica scales read throughput and isolates heavy analytical queries. It does nothing for write throughput, and it is not a backup.

What breaks first is always the same thing: read-your-writes. A user saves a profile, the app redirects, the read lands on a replica 200ms behind, and the page shows the old name. It looks like a caching bug and it is a routing bug.

The next two failures: lag grows exactly when you need the replica most, because the same load that saturates reads also produces the write volume being replayed, and connections multiply, since every app instance now needs a pool per endpoint.

Route deliberately per query, not per service. A blanket "reads go to replicas" setting is how the profile bug ships.

Partitioning splits a table inside one database. Sharding splits the data across many databases. One is a storage layout decision, the other is a distributed systems decision, and they are not steps on the same ladder.

The honest order of operations: index properly, then partition, then move the heaviest table to its own database, and only then shard. Most teams that shard early are paying distributed-system costs for a problem a covering index would have solved.

If the interview asks "when would you shard?", the strongest answer names the trigger: one machine can no longer absorb the write volume or hold the working set, and there is a key almost every query filters on.

Start from what the database can run at once, not from how many requests you hope to serve. A database with 8 cores executes roughly 8 queries at a time; every connection beyond that is a query waiting, and it waits more expensively inside the database than in your pool.

A widely used starting point is connections = (cores * 2) + effective_spindles, which lands near 20 for a modern 8-core server with SSDs. That number is usually much smaller than teams expect, and smaller pools frequently produce lower p99 latency, because queueing at the pool is cheap and queueing on CPU is not.

The multiplication is what actually breaks production:

pool_size 20  x  40 app instances  =  800 connections
max_connections 200                =  outage

That is what PgBouncer exists for. In transaction pooling mode a client holds a server connection only for the duration of a transaction, which collapses 800 into a few dozen, at the cost of anything session-scoped: session-level SET, advisory locks, and some prepared-statement patterns.

Also give the pool an acquisition timeout. Without one, a database slowdown turns into every request thread blocking on checkout, which is how one slow query takes down a service that had a healthy database.

Because each statement pays the same fixed costs: a network round trip, statement planning, and, if it is its own transaction, a WAL flush to disk. Loading 100,000 rows one at a time is 100,000 of each. Batched, it is a few hundred.

Three levels, in increasing order of speed:

-- 1. one transaction instead of 100,000 (amortizes the fsync)
BEGIN; INSERT ...; INSERT ...; COMMIT;
 
-- 2. multi-row INSERT (one round trip, one plan)
INSERT INTO events (id, kind, body) VALUES ($1,$2,$3), ($4,$5,$6), ...;
 
-- 3. COPY, the bulk path, for large loads
COPY events (id, kind, body) FROM STDIN;

Two limits decide the batch size. The protocol caps bound parameters per statement (65,535 in Postgres), so rows per batch is that cap divided by columns per row. And a batch is one transaction, so an enormous batch means a long-held lock, a big WAL burst, and a lot of work to redo if it fails. A few thousand rows per batch is the usual sweet spot.

For upserts, INSERT ... ON CONFLICT DO UPDATE keeps the batch shape and stays idempotent, which matters when the retry that follows a timeout replays the whole batch.

Three jobs, not one. It reclaims dead row versions so space is reused, it updates the visibility map that makes index-only scans possible, and it freezes old transaction ids to keep the 32-bit counter from wrapping around.

When it falls behind, the symptoms arrive in that order too:

1. Table and index bloatsame rows, more pages to read
2. ↓
   Index-only scans stop being index-onlythe visibility map is stale
3. ↓
   Plans driftautovacuum also runs ANALYZE; stats go stale with it
4. ↓
   Wraparound protection kicks inand refuses new writes to save the data

The default thresholds are proportional (autovacuum_vacuum_scale_factor of 0.2), which means a 500-million-row table waits for 100 million dead tuples before it is even considered. Hot tables need per-table settings, not global ones.

When vacuum runs but nothing improves, the cause is almost always something holding the oldest snapshot: a long-running transaction, an abandoned replication slot, or a stalled prepared transaction. Vacuum is doing its job and being told nothing is safe to remove yet.

All of them, at different layers, with different values per role. Each one bounds a different failure, and a database with none of them set will happily let one client stall everything.

lock_timeout is the one people skip and the one that prevents the worst outage. Without it, a migration waiting for ACCESS EXCLUSIVE sits in the lock queue, and every subsequent query on that table queues behind the migration, including the plain reads that were working a second earlier.

Set these per role (ALTER ROLE web SET statement_timeout = '5s') rather than cluster-wide, so the migration path and the request path can have different rules.

Two questions, in this order: what is running right now, and what consumes the most time overall. They usually have different answers, and the fix depends on which one is the emergency.

-- right now: longest-running, and what they are waiting on
SELECT pid, now() - query_start AS dur, wait_event_type, wait_event, left(query, 80)
FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY dur DESC LIMIT 10;
 
-- overall: rank by TOTAL time, not average
SELECT calls, total_exec_time, mean_exec_time, left(query, 80)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Ranking by total time is the part that separates experience from instinct. The 4ms query called 900,000 times an hour is the outage; the 8-second report run twice a day is not, even though it looks worse in a slow-query log.

If queries are waiting rather than working, wait_event names the reason (Lock, IO, LWLock), and pg_locks joined to pg_stat_activity gives the blocker.

The three-step habit: find the blocker or the top consumer, EXPLAIN (ANALYZE, BUFFERS) it, then fix the predicate, the index, or the call volume. Killing the query with pg_terminate_backend buys minutes, not a fix.

Two numbers define the whole design. RPO is how much data you can afford to lose; RTO is how long you can be down. A nightly dump is an RPO of 24 hours, no matter how reassuring the green checkmark looks.

A replica is not a backup. It faithfully replays your mistakes: the DELETE without a WHERE clause arrives there in milliseconds. Replicas cover hardware failure, not logical corruption or a compromised credential.

The part that decides whether any of this works: restore on a schedule and time it. An untested backup is a hypothesis. Restore into a scratch environment monthly, record how long it took (that is your real RTO), and check retention against the worst case, which is logical corruption discovered eleven days later.