How DBMS actually gets asked

SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employees) t
WHERE rk = 2;

Why DENSE_RANK and not ROW_NUMBER: if two people share the top salary, the "second highest salary" should be the next distinct value, not the second row. Also be ready for the follow-up "what if there is no second salary?" — this returns zero rows; a LIMIT 1 OFFSET 1 subquery wrapped in SELECT (…) would return NULL instead, which may be what's wanted.

-- Find them
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

-- Delete all but the newest per email
DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email
                                ORDER BY created_at DESC, id DESC) AS rn
    FROM users) t
  WHERE rn > 1);

ROW_NUMBER here, not DENSE_RANK — you need a strict ordering so exactly one row survives. Note the id DESC tiebreaker: without it, two rows with the same timestamp make the result non-deterministic and you could delete both or neither.

WITH ranked AS (
  SELECT e.name, d.name AS dept, e.salary,
         ROW_NUMBER() OVER (PARTITION BY e.department_id
                            ORDER BY e.salary DESC) AS rn
  FROM employees e JOIN departments d ON d.id = e.department_id
)
SELECT * FROM ranked WHERE rn <= 3 ORDER BY dept, rn;

The follow-up is always "why the CTE?" — because you cannot reference a window function in WHERE. Windows are computed after WHERE in the execution order, so the filter has to happen in an outer query. Mention the LATERAL alternative too: it can be faster when you have an index on (department_id, salary DESC), because it stops after 3 rows per department instead of ranking everything.

SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Say why not NOT IN: if orders.customer_id is nullable and contains a single NULL, NOT IN returns zero rows, because id != NULL is UNKNOWN and the whole AND chain can never be true. NOT EXISTS is NULL-safe and can short-circuit. The LEFT JOIN … WHERE o.id IS NULL form is also correct and worth mentioning.

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day)                       AS running_total,
       AVG(revenue) OVER (ORDER BY day
                     ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_revenue
ORDER BY day;

With ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is what makes the running total work. Be careful: RANGE groups peer rows with equal sort keys, while ROWS counts physical rows, so with duplicate dates the two give different answers.

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month,
         SUM(total_cents) AS revenue
  FROM orders WHERE status = 'paid'
  GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS pct
FROM monthly ORDER BY month;

Two details that earn credit: NULLIF(…, 0) guards against division by zero, and 100.0 rather than 100 forces float arithmetic — integer division would silently truncate the percentage to 0.

-- Window version — one pass, no self-join
SELECT name, department_id, salary, dept_avg FROM (
  SELECT name, department_id, salary,
         AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
  FROM employees) t
WHERE salary > dept_avg;

-- Correlated subquery version — correct, usually slower
SELECT e.* FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees
                    WHERE department_id = e.department_id);

Offering both and saying which you'd prefer is the point. The window version scans once; the correlated version conceptually re-aggregates per row (though optimisers often rewrite it).

SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'pending')  AS pending,
       COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
       COUNT(*) FILTER (WHERE status = 'shipped')  AS shipped,
       COUNT(*)                                     AS total
FROM orders GROUP BY customer_id;

-- Portable form for databases without FILTER:
       SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid

Conditional aggregation — one table scan producing several answers. Note that SUM(CASE … END) with ELSE 0 gives 0 for empty groups while SUM(CASE … ELSE NULL) would give NULL.

SELECT d.day, COALESCE(COUNT(o.id), 0) AS orders
FROM generate_series('2026-01-01'::date, '2026-01-31'::date,
                     '1 day') AS d(day)
LEFT JOIN orders o ON o.created_at::date = d.day
GROUP BY d.day ORDER BY d.day;

The classic "gaps in a time series" problem: aggregating the table alone can only produce rows for days that have data. You need a generated calendar and a LEFT JOIN. Two things to flag: the join must be LEFT from the calendar side, and o.created_at::date is a function on a column so it can't use an index — better in production to write o.created_at >= d.day AND o.created_at < d.day + 1.

BEGIN;
  -- Do the arithmetic IN the database: atomic, no lost update.
  UPDATE accounts SET balance = balance - 100
   WHERE id = 'alice' AND balance >= 100;
  -- if 0 rows updated → insufficient funds → ROLLBACK

  UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
  INSERT INTO transfers (from_id, to_id, amount_cents)
       VALUES ('alice', 'bob', 10000);
COMMIT;

Four things the interviewer is listening for. (1) balance = balance - 100 rather than reading then writing — that's what prevents the lost update, with no locking needed. (2) The AND balance >= 100 guard makes the overdraft check atomic with the deduction. (3) Check the affected row count and roll back on 0. (4) If you must lock explicitly, SELECT … FOR UPDATE in a consistent order (e.g. by ascending id) — otherwise two concurrent opposite transfers deadlock. Bonus: mention that a real ledger would use immutable double-entry rows rather than a mutable balance column.

Name five things you'd otherwise build badly: controlled redundancy and integrity constraints; a declarative query language with an optimiser instead of hand-written scans; concurrency control so two writers don't corrupt each other; atomicity and durability so a crash mid-write doesn't leave half a record; and access control per table and column. Plus indexes, backup and point-in-time recovery, and data independence — the schema is separate from the programs, so changing storage doesn't break every application.

A super key is any attribute set that uniquely identifies a row — including ones with redundant extra attributes. A candidate key is a minimal super key: remove any attribute and uniqueness is lost. The primary key is the candidate key you chose, implying NOT NULL and UNIQUE. The remaining candidate keys are alternate keys. So {id, name} is a super key but not a candidate key, because {id} alone suffices.

Prefer a surrogate key with a UNIQUE constraint on the natural key. Natural keys change — emails, postcodes, "unique" supplier codes get reused — and a changing primary key means cascading updates through every referencing table. Surrogates are also narrower, which matters in InnoDB because the primary key is stored inside every secondary index. Keeping the natural key as a unique constraint means the database still enforces the real-world rule.

Referential integrity: the value must either be NULL or match an existing row in the referenced table, so you can't create orphans. Options: RESTRICT/NO ACTION refuse the parent delete (the safe default), CASCADE deletes the children too, SET NULL orphans them deliberately, SET DEFAULT repoints them. Be wary of CASCADE: deleting one customer can silently remove millions of rows across many tables, inside your transaction, holding locks throughout.

1:1 — foreign key on either side, preferably the one with total participation, plus a UNIQUE constraint on it. 1:N — foreign key on the N side, always; no junction table needed. M:N — you must create a junction table whose primary key is the pair of foreign keys, and it's the natural home for attributes of the relationship (a grade, an enrolment date). Missing the M:N and collapsing it to 1:N loses data.

An entity that can't be identified without its owner. An order line has no meaning independent of its order — line_no 3 is only unique within order 9812. It maps to a table whose primary key is composite, including the owner's key: PRIMARY KEY (order_id, line_no). The relationship to the owner is called an identifying relationship, and it's necessarily total.

It's a multivalued attribute forced into a fixed shape — a repeating group, which violates 1NF in spirit. You'll always need a fourth; querying "who has this number" means checking three columns; you can't record which is primary; and you can't add metadata like a label or verification status. The fix is a phone_numbers(user_id, number, label) table. Same argument applies even more strongly to comma-separated values in one column, which are unqueryable, unindexable and unenforceable.

External (what each user or application sees — views), conceptual (the whole logical schema: tables, columns, constraints), internal (files, pages, indexes). The point is the two independences: physical data independence means you can add an index or change the storage engine without touching any query, and logical data independence means you can restructure tables and redefine views so applications querying the views still work. Physical independence is near-complete in practice; logical is only partial, which is why migrations are real work.

DDL defines structure (CREATE, ALTER, DROP, TRUNCATE); DML manipulates data (SELECT, INSERT, UPDATE, DELETE); DCL grants permissions; TCL controls transactions. The behavioural difference worth knowing: DDL is transactional in PostgreSQL but auto-commits in MySQL, so in Postgres you can wrap a migration in a transaction and roll it back, and in MySQL you can't. That single fact changes how you write migrations.

Because NULL means "unknown", and two unknowns aren't provably equal — so the constraint can't conclude they're duplicates. Most databases (Postgres, MySQL, Oracle) therefore permit any number of NULLs in a unique column, which surprises people: UNIQUE(email) does not stop a thousand rows with a NULL email. If you need at most one, add NOT NULL, or use a partial unique index. Postgres 15+ also offers UNIQUE NULLS NOT DISTINCT.

Redundancy is a correctness problem, not mainly a storage one. Three anomalies: update — a department name repeated on 500 employee rows means renaming it is 500 updates, and missing one leaves the database asserting two contradictory things; insertion — you can't record a new department until it has an employee; deletion — removing the last employee destroys the department's information. Normalisation makes these structurally impossible rather than relying on the application being careful.

1NF: atomic values, no repeating groups — one value per cell. 2NF: 1NF plus no partial dependency, i.e. no non-key attribute depends on only part of a composite key. 3NF: 2NF plus no transitive dependency — no non-prime attribute depends on another non-prime attribute. The mnemonic ties it together: every non-key attribute depends on "the key, the whole key, and nothing but the key" — 2NF fixes "the whole key", 3NF fixes "nothing but the key".

BCNF requires every determinant to be a superkey. 3NF has an exception: it permits a non-superkey determinant if the dependent attribute is prime (part of some candidate key). So a relation can be in 3NF and still have redundancy. The classic example: (student, course, instructor) where each instructor teaches only one course — instructor → course holds but instructor isn't a superkey. That's 3NF but not BCNF.

No, and this is the good answer. BCNF decomposition is always lossless but may not be dependency-preserving — you can end up unable to enforce a functional dependency with a single-table constraint, so it has to be checked by a join or in application code. 3NF decomposition can always be both lossless and dependency-preserving. So there's a real tradeoff, and "3NF is usually enough in practice" is a defensible engineering position rather than laziness.

X → Y means any two rows agreeing on X must agree on Y — it's a rule about the real world that the data must obey. student_id → name holds because one student has one name; name → student_id doesn't, because two students can share a name. It's trivial if Y is a subset of X. Partial means depending on part of a composite key; transitive means X → Y and Y → Z. Normalisation is defined entirely in these terms, which is why you can't do it properly without them.

Compute attribute closures. Start with a candidate set, repeatedly apply any FD whose left side is contained in your set, and add its right side; if the closure reaches all attributes, it's a super key — and if no proper subset does, it's a candidate key. Two shortcuts: an attribute that never appears on any right-hand side must be in every candidate key, and one that appears only on right-hand sides can't be in any.

Joining the pieces back must yield exactly the original relation, with no spurious extra rows. The condition: the intersection of the two schemas must be a superkey of at least one of them. If you split on a non-key attribute, the rejoin manufactures combinations that never existed — which is strictly worse than the redundancy you were removing, because now the data is wrong rather than repetitive. Always check this when decomposing in an exam.

When measurement shows a join is too expensive for a hot read path, or when joins are impossible because the tables live on different shards. The discipline that makes it safe: normalise the source of truth and denormalise into derived structures you can rebuild — a materialised view, a cache, a read model fed by an event stream. Then drift is recoverable by re-deriving. Denormalising the primary tables gives you two candidate truths with no way to choose. And distinguish real denormalisation from point-in-time facts: the price on an order line isn't a duplicate, it's the price at time of sale.

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This explains almost every error message: WHERE can't use a SELECT alias because SELECT hasn't run yet (but ORDER BY can, because it runs after); WHERE can't use an aggregate because groups don't exist yet, hence HAVING; and a non-aggregated column must be in GROUP BY because the database can't know which of the group's values you meant.

WHERE filters rows before grouping; HAVING filters groups after. So WHERE cannot contain an aggregate and HAVING can. Performance note worth adding: filter in WHERE whenever you can, because it reduces the number of rows that reach the grouping step — HAVING works on already-formed groups, so it does more work for the same result.

COUNT(*) counts rows, including rows where every column is NULL. COUNT(col) counts non-NULL values of that column. COUNT(DISTINCT col) counts distinct non-NULL values. This is a real bug source: two "counts" of the same table legitimately disagree. Same family of trap: AVG divides by the non-NULL count, so missing data inflates the average rather than dragging it down, and SUM over zero rows returns NULL rather than 0.

INNER — only matching rows. LEFT — all left rows, NULLs where the right has no match. RIGHT — mirror image; prefer flipping the tables and using LEFT for readability. FULL OUTER — all rows from both sides, for reconciliation. CROSS — every combination. SELF — a table joined to itself, for hierarchies. ANTI-JOIN — left rows with no match, via NOT EXISTS. SEMI-JOIN — left rows with at least one match, without duplicating them, via EXISTS.

Because the join produces NULLs for unmatched right-side rows, and then WHERE o.status = 'paid' evaluates NULL = 'paid' as UNKNOWN, which WHERE discards — so every unmatched row vanishes and your LEFT JOIN has silently become an INNER JOIN. The fix is to filter the right table in the join condition: ON o.customer_id = c.id AND o.status = 'paid'. This is one of the most common silently-wrong-answer bugs in SQL.

Row multiplication from joining two one-to-many relationships. Joining a customer to 5 orders and then to 3 addresses gives 15 rows, so each order total is counted three times. It's not a SQL syntax problem — the query is valid and the arithmetic is correct on the rows it was given. The fix is to aggregate each side separately in subqueries or CTEs and then join the aggregates. Adding DISTINCT is the wrong fix: it hides the symptom, and it makes the query slower.

EXISTS is usually best: it can short-circuit on the first match and it's NULL-safe. IN is fine and readable for a small literal list. NOT IN with a subquery is dangerous — a single NULL in the subquery result makes it return zero rows. A plain JOIN works but multiplies rows when there are several matches, so you'd need DISTINCT, which is slower than a semi-join. Modern optimisers often rewrite between these forms, but the NULL semantics differ and that difference is real.

One that references a column from the outer query, so conceptually it's evaluated once per outer row rather than once overall. WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) is correlated because of e.department_id. Optimisers frequently rewrite them into joins or hash aggregates, but they can be genuinely slow, and a window function is often the cleaner and faster expression of the same intent.

Keep every row while computing across related rows. GROUP BY collapses rows, so you lose the detail; a window function lets you show each employee and their department average on the same line, which otherwise needs a self-join. It also gives you ranking, offset access (LAG/LEAD), running totals and moving averages over an explicit frame. It's the highest-value SQL topic for interviews.

For values 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4 — always distinct, arbitrary among ties. RANK gives 1, 2, 2, 4 — ties share a rank and leave a gap. DENSE_RANK gives 1, 2, 2, 3 — ties share, no gap. Use ROW_NUMBER for deduplication and top-N-per-group, DENSE_RANK for "the Nth distinct value" questions like second-highest salary.

Because window functions are evaluated after WHERE in the execution order — they operate on the rows that survived filtering, so they don't exist yet when WHERE runs. Wrap the query in a CTE or subquery and filter in the outer level. (The same reasoning explains why you can use them in ORDER BY, which runs later still.)

A named intermediate result introduced with WITH — same expressive power as a derived table, far more readable, and it can be recursive. On performance: in PostgreSQL before version 12 a CTE was an optimisation fence, always materialised, so predicates couldn't be pushed into it and it could be slower than the equivalent subquery. From 12 onward simple CTEs are inlined, and you can force either behaviour with MATERIALIZED / NOT MATERIALIZED. Knowing this signals real tuning experience.

An anchor term for the starting rows, UNION ALL, then a recursive term that joins back to the CTE itself. The two mistakes: using UNION instead of UNION ALL — which deduplicates on every iteration, is slow and usually not what you want — and omitting a depth guard, so a cycle in the data (an employee who is transitively their own manager) loops forever. Add WHERE depth < n.

A view stores no data — it's a stored query expanded at query time, so it's always current but costs the full underlying query on every read. A materialised view physically stores the result, so reads are cheap and it can be indexed, but it's only as fresh as the last refresh. Use views for simplifying queries, restricting columns for access control, and providing a stable interface over a changing schema; use materialised views for expensive aggregations read far more often than they change.

Almost whenever application logic would do, because triggers are invisible side effects: someone reads the INSERT and cannot see that it also wrote three other tables. They run inside your transaction so they extend lock duration and can deadlock, they cascade, they're hard to test, and they don't appear in application stack traces. They're right when the guarantee must hold no matter which client did the write — audit logging, or an invariant a CHECK can't express.

DELETE is DML: removes rows, accepts WHERE, fires row triggers, logs each row (so it's slow on large tables), fully rollback-able. TRUNCATE is DDL: removes all rows by deallocating pages, no WHERE, doesn't fire row triggers, resets identity sequences, dramatically faster. DROP removes the table definition itself. In Postgres TRUNCATE is transactional; in MySQL it auto-commits.

vs B-tree: a B+ tree keeps data only in the leaves, so internal nodes are pure separators with fan-out in the hundreds — a table of millions of rows is 3–4 levels deep and the upper levels stay cached. Its leaves are also linked, so a range scan walks sideways instead of re-descending. vs hash: hash gives O(1) equality but supports only equality — no ranges, no ORDER BY, no prefix matching. Real workloads mix both, so B+ trees win.

A clustered index's leaves are the rows — the table is stored in index order, so there's only one per table and range scans on the key are very fast. A secondary index's leaves hold the key plus a pointer, so a query needing other columns does an extra lookup: in InnoDB the leaf stores the primary key, so it's a second B+ tree descent; in Postgres it's a heap fetch. InnoDB always clusters on the primary key; PostgreSQL has no clustered index at all — rows live in a heap and every index is secondary.

An index on (a, b, c) is sorted by a, then by b within equal a, then c. So it serves queries constraining a, a+b, or a+b+c — but not b alone, because b's values are scattered throughout. Same reason a phone book sorted by (surname, forename) can't find everyone called "Priya".

Equality predicates first, then the range or sort column last. An index on (status, created_at) serves WHERE status='paid' AND created_at > x ORDER BY created_at completely — it finds the status range and the rows are already in date order, so the sort step disappears and a LIMIT can stop early. Reversed as (created_at, status), the status part can't be used efficiently. All else equal, put higher-selectivity columns earlier.

One containing every column the query needs, so it can be answered from the index alone with no table access — an "index-only scan". Often the single biggest available win, because it eliminates the random I/O of fetching rows. Add non-key columns with INCLUDE so they don't bloat the tree's search structure. It's also the concrete reason to avoid SELECT *: one unnecessary column defeats the covering index and reintroduces the heap fetches.

When selectivity is low — above roughly 5–10% of rows a sequential scan is cheaper, because sequential reads beat thousands of random fetches, so an index on a 50/50 boolean is correctly ignored. When there's a function or cast on the column (WHERE YEAR(created_at) = 2026). With a leading wildcard (LIKE '%term'). On small tables. And when statistics are stale — the index is suitable but the planner's estimates are wrong, which is the most common real cause of "why isn't it using my index?"

Every INSERT, UPDATE and DELETE must maintain every index, so ten indexes on a hot table means eleven write operations per insert, plus memory in the buffer pool and disk space (often 30–100% of the table size). It also adds planning time. Unused indexes are pure tax — find them in pg_stat_user_indexes and drop them. So indexing is a read/write tradeoff, not a free speed-up.

In InnoDB the table is physically ordered by the primary key, so random keys mean each insert lands in the middle of the B+ tree — causing page splits, fragmentation, poor cache locality (every insert dirties a different page) and a larger index. A monotonic key appends cleanly to the rightmost page. UUIDv4 is also 16 bytes and is copied into every secondary index. Prefer a bigint sequence, a Snowflake ID, or UUIDv7, which is time-ordered and fixes the locality problem while keeping global uniqueness.

Nested loop: for each outer row, look up matches in the inner — O(n log m) with an index on the inner side. Chosen when the outer side is small; ideal for OLTP point queries, catastrophic if the row estimate is wrong. Hash join: build a hash table on the smaller input and probe with the larger, O(n+m); the workhorse for large equality joins, but it needs memory and spills to disk if the table doesn't fit. Merge join: sort both and walk together — free if an index already provides the order, and the only one of the three that handles inequality joins well besides nested loop.

Inside-out and bottom-up — children execute before parents. The first thing to check is estimated vs actual rows: an order-of-magnitude gap means the plan was chosen on bad information, so fix statistics before touching indexes. Then look for a Seq Scan with high "Rows Removed by Filter" (missing index), a Nested Loop with a huge actual outer count (bad estimate), a Sort with "external merge Disk" (raise work_mem), or a Hash with multiple batches (it spilled). Note EXPLAIN alone only estimates; ANALYZE actually runs the query.

It's the database's own cache of disk pages in memory — the single most important performance structure, since a hit costs ~100 ns and a miss ~100 µs. It manages it itself because it knows things the kernel can't: that a sequential scan's pages won't be reused, so plain LRU would wrongly evict the hot index pages; which pages are dirty and in what order they may be written (the WAL rule); and which pages must be pinned while a query uses them. Hence InnoDB's segmented LRU and Postgres's clock-sweep.

A row store keeps a whole row contiguously, so fetching one complete record is a single page read — right for OLTP. A column store keeps each column contiguously, so aggregating one column over a billion rows reads only that column, and adjacent values share a type and often repeat, making compression dramatically better — right for OLAP. Hence the standard architecture of a row store for transactions streaming into a column store for analytics: running big scans on the transactional database evicts the buffer pool's hot set and slows every user-facing query.

Atomicity — all or nothing, via the undo log. Consistency — constraints hold at commit, via constraints you declared. Isolation — concurrent transactions don't corrupt each other, via locking or MVCC. Durability — committed survives a crash, via the write-ahead log forced to stable storage before commit returns. The costs are worth naming too: isolation costs throughput and produces retryable failures, and durability costs an fsync per commit, which is why commit rate is bounded by disk latency rather than CPU.

Consistency. A, I and D are properties the database implements; C is largely something you define — the engine enforces the constraints you declared, but it has no idea that "total debits must equal total credits" unless you express it. Many people argue it's there to make the acronym pronounceable. Also worth saying: the C in ACID is about integrity constraints, while the C in CAP is about replica agreement — different concepts with the same name.

A concurrent schedule is serialisable if it produces the same result as some serial order of the transactions — note "some", not a particular one. Test conflict-serialisability with a precedence graph: a node per transaction, and an edge from the transaction that acts first to the one that acts second for each conflicting pair (same item, different transactions, at least one write). A cycle means not serialisable. View serialisability is a weaker, larger class but testing it is NP-complete, so real systems use the conflict test.

2PL has a growing phase where you only acquire locks and a shrinking phase where you only release; once you release your first lock you may not acquire another. That rule alone guarantees serialisability. Strict 2PL additionally holds all exclusive locks until commit or abort, which produces strict schedules and therefore prevents cascading rollback — no other transaction ever read your uncommitted data, so aborting you can't force others to abort. That combination of serialisability plus simple recovery is why strict 2PL is the standard.

Writers create new row versions instead of overwriting, so readers never block writers and writers never block readers. Each transaction sees a consistent snapshot as of its start without taking any read locks — which is why a long analytical query can't block writes. That's why MVCC displaced pure locking for general-purpose databases. It does not remove write-write contention: two transactions updating the same row still conflict.

Old versions accumulate as garbage. Postgres needs VACUUM to reclaim them, and without it you get table bloat and eventually transaction-ID wraparound problems; MySQL keeps them in an undo log instead. The specific failure to name: a long-running transaction prevents cleanup of any version newer than its snapshot, so one forgotten open transaction — an idle session in a REPL, a connection leaked by an app — can bloat the whole database. That's the most common MVCC incident in practice.

Dirty read — reading uncommitted data. Non-repeatable read — the same row gives different values within one transaction. Phantom read — the same query returns new rows. Lost update — two read-modify-writes and one silently overwrites the other. Write skew — two transactions each read a valid state, write different rows, and jointly break an invariant. Snapshot isolation prevents the first four and permits the last, which is the interesting case.

Read uncommitted allows everything; read committed prevents dirty reads; repeatable read also prevents non-repeatable reads; serialisable prevents everything. The caveats matter more than the grid: Postgres has no read uncommitted (it silently gives read committed, because MVCC makes dirty reads impossible); MySQL's repeatable read prevents phantoms via gap locks, stronger than the standard requires; Postgres's repeatable read is really snapshot isolation, so it allows write skew; and Postgres's SERIALIZABLE uses SSI, which detects dangerous patterns and aborts, so the cost is serialisation failures you must retry.

Invariant: at least one doctor on call. Alice and Bob are both on call. Both transactions read COUNT(*) WHERE on_call = true and see 2, both conclude it's safe to go off call, both update their own row, both commit — and nobody is on call. Snapshot isolation can't catch it because neither read stale data and they wrote different rows, so there's no write-write conflict to detect. Fixes: SERIALIZABLE, or materialise the conflict with SELECT … FOR UPDATE on the rows the decision depends on, or express the invariant as a constraint.

Three ways, in order of preference. Do the arithmetic in the database — UPDATE … SET balance = balance - 100 — which is atomic and needs no extra machinery. SELECT … FOR UPDATE to hold a row lock across the read-modify-write. Or optimistic concurrency: UPDATE … WHERE version = 7, and if zero rows were updated, reload and retry. Naming the first shows you'd avoid the problem rather than manage it.

It builds a wait-for graph, detects the cycle, and aborts a victim (typically the one with least work done), which the application retries. It can do this because it can roll a transaction back safely — recovery is essentially free for a database and impossible for an OS, which is why general-purpose operating systems deliberately ignore deadlock while databases resolve it. Practical prevention: access rows in a consistent order (e.g. ascending primary key) and keep transactions short; most application deadlocks are two code paths locking the same tables in opposite orders.

Before modifying a data page, write a log record describing the change to a sequential log, and flush the log up to the commit record before returning from commit. The rule: the log record must reach stable storage before the data page does. The point is that one sequential fsync is far cheaper than scattered random page writes, which can then be deferred and batched. That's why commit throughput is bounded by fsync latency, and why group commit — batching several transactions into one fsync — is such an effective optimisation.

Log records hold both the before-image (enabling undo of uncommitted work) and the after-image (enabling redo of committed work). Both are needed because real systems use STEAL + NO-FORCE buffer policies: STEAL means an uncommitted page may be written to disk, so undo is required; NO-FORCE means committed pages need not be written at commit, so redo is required. ARIES therefore runs analysis, then redo (repeating all history to restore the exact crash state), then undo of the losers — writing compensation records so a crash during recovery doesn't undo twice.

Recovery time against steady-state I/O. A checkpoint flushes dirty pages and records a known-good point, so recovery only replays from there rather than from the beginning of the log. Frequent checkpoints mean fast recovery and more constant background write I/O; infrequent checkpoints mean less I/O during normal operation and a longer, more painful restart. It's the RTO dial.

Start with Postgres unless something specific rules it out. A single primary handles tens of thousands of writes a second — past most estimates — and transactions, joins and ad-hoc queries are enormously valuable while access patterns are still moving. Move for a named reason: write volume beyond one node, a genuinely heterogeneous schema, or a purely key-based access pattern at massive scale. And move one table, not the whole system. Reaching for Cassandra at 300 writes/second is the classic over-engineering flag.

Joins across shards (do them in the application or denormalise), transactions across shards (2PC or sagas), global unique constraints, global ordering, and cheap aggregates — COUNT(*) becomes a scatter-gather whose latency is the slowest shard's. Choose a shard key that is high-cardinality, evenly accessed (not just evenly distributed), and present in your hot queries — that last one is what people get wrong, because without it every read becomes a scatter-gather.

Phase 1: the coordinator asks all participants to prepare; each does the work durably and votes yes (becoming bound — obliged to be able to commit) or no. Phase 2: the coordinator records its decision and tells everyone to commit or abort. The flaw: if the coordinator crashes after participants voted yes but before delivering the decision, participants are blocked — holding locks, unable to commit or abort, because only the coordinator knows the outcome. So one node's failure becomes everyone's outage, which is why high-throughput systems prefer keeping transactions inside one shard, or use sagas with compensating actions.

During a network partition, a distributed system must choose between consistency (every read sees the latest write) and availability (every request gets a non-error response). The correction most people miss: you don't choose P — networks partition regardless, so real systems are CP or AP, and anything described as "CA" is a single node. It also applies per operation, not per product: a shopping cart can be AP while the payment ledger is CP. PACELC is the more useful framing, because it adds "else, when there's no partition, choose latency or consistency" — which is the decision you actually make on every request.