A database handbook · first SELECT to sharding

Databases Ground Up

A database exists to give you three things a file cannot: concurrent access without corruption, durability across a crash, and indexed access to data larger than memory. Every chapter here is in service of one of those three, ordered from your first query to the point where one machine is no longer enough.

I. Foundations
Chapter 01

What a database actually gives you

Not storage — you already have files. What you are buying is concurrent access without corruption, durability across a crash, and a query language that survives your data outgrowing memory.

Almost everyone learns databases as a place to put data, which makes the whole subject feel arbitrary. A file can store data. What a file cannot do is let fifty processes write to it at once without destroying each other, guarantee that a write which was acknowledged survives someone pulling the power cable, and answer find every order over ₹1000 from last week without reading all of it.

Those three things — concurrency, durability, and indexed access — are the entire product. Every feature in this book is in service of one of them. When you cannot remember why something exists, ask which of the three it is protecting.

The three jobs
  • Concurrency → transactions, isolation levels, locking, MVCC.
  • Durability → the write-ahead log, checkpoints, fsync, backups.
  • Indexed access → B+trees, the query planner, statistics.

A useful consequence: when a database is slow, it is almost always paying for one of these three on your behalf, and the fix is to stop asking it to. A slow query is usually missing indexed access. A stalled write is usually contending on concurrency. A mysterious pause is often durability catching up.

SQL
-- This is the whole pitch in four lines.
-- Two users run this at the same moment on the same row:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- With files: both read 500, both write 400, ₹100 vanishes.
-- With a database: one waits, both succeed, the money is conserved,
-- and if the machine dies after COMMIT the transfer is still there.
Interview angle

“Why not just use files?” is a real interview opener, and the wrong answer is “databases are faster” — for a single sequential read a file is faster. The right answer is concurrent correctness and crash durability, which a file gives you neither of.

Chapter 02

The relational model in one page

A table is a set of rows with a fixed shape. Relationships are not pointers — they are values in a column that happen to match values in another table. That one idea is why SQL composes.

In most programming, a relationship is a reference: an Order object holds a pointer to a Customer object. In the relational model there are no pointers. An order stores a value — the customer's id — and you recover the relationship by matching values at query time. That is what a join is.

This feels like a downgrade until you notice what it buys. Values can be indexed, compared, aggregated and constrained. A pointer can only be followed. Because relationships are just values, you can ask questions nobody anticipated when the schema was designed, which is precisely what a pointer graph cannot do.

SQL
-- Two tables, no pointers anywhere.
CREATE TABLE customers (
  id    bigserial PRIMARY KEY,
  name  text NOT NULL,
  city  text
);

CREATE TABLE orders (
  id           bigserial PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers(id),
  total_cents  bigint NOT NULL,
  placed_at    timestamptz NOT NULL DEFAULT now()
);

-- The relationship is recovered by matching values:
SELECT c.name, o.total_cents
FROM   orders o
JOIN   customers c ON c.id = o.customer_id;

Rows are a set, not a list

A table has no inherent order. If you do not write ORDER BY, the database is free to hand rows back in whatever order was cheapest, and that order can change when the data grows or the plan changes. This is the single most common source of it worked yesterday bugs.

Principle

A query without ORDER BY has no defined order, even if it looks stable in testing. If order matters to your code, say so in the query - never rely on insertion order, primary key order, or what you observed once.

Chapter 03

Keys: primary, foreign, and the natural/surrogate argument

A primary key identifies a row uniquely and forever. A foreign key says a value must exist elsewhere. Natural keys are meaningful but change; surrogate keys are meaningless and therefore safe.

The primary key answers which row is this. Its two requirements are that it is unique and that it never changes — the second one is where people get hurt. An email address is unique, so it looks like a fine key, right up to the day a user changes their email and you discover forty tables referencing the old one.

That is the natural-versus-surrogate argument in full. A natural key is drawn from the real world (email, ISBN, country code). A surrogate key is a meaningless number or UUID the database invents. Surrogates win in almost every application schema, precisely because meaninglessness is what makes them stable.

SQL
-- Surrogate: meaningless, stable, safe to reference everywhere.
id bigserial PRIMARY KEY

-- Natural: meaningful, and it WILL change on you.
email text PRIMARY KEY          -- do not do this

-- The usual right answer: surrogate PK, natural UNIQUE constraint.
CREATE TABLE users (
  id     bigserial PRIMARY KEY,   -- what other tables reference
  email  citext UNIQUE NOT NULL,  -- still enforced, still queryable
  ...
);

-- Foreign key: this value must exist over there.
customer_id bigint NOT NULL REFERENCES customers(id)

-- And say what happens when the parent disappears:
  REFERENCES customers(id) ON DELETE RESTRICT  -- refuse (safe default)
  REFERENCES customers(id) ON DELETE CASCADE   -- delete children too
  REFERENCES customers(id) ON DELETE SET NULL  -- orphan them
Interview angle

“Why not use email as the primary key?” is a common interview probe. The answer is not “performance” — it is that primary keys are referenced by other tables, and a key that can change turns one UPDATE into a cascade across the schema.

A composite key — more than one column together — is genuinely right for join tables, where the pair itself is the identity. For everything else, a single surrogate column keeps joins and foreign keys simple.

Chapter 04

Types, and the ones people get wrong

Pick the narrowest type that fits. The database enforces it for free, forever, on every future write — which is far cheaper than validating in five services.

Types are the first constraint you get. A column typed date can never hold "tomorrow-ish", no matter what a future intern's script tries. This is not pedantry; it is the cheapest correctness available, because it is enforced at the one place all writes must pass through.

The four that are usually wrong

Type mistakes
  • Money as float. 0.1 + 0.2 ≠ 0.3 in binary floating point. Use numeric, or store integer paise/cents. Never float or double for currency.
  • Timestamps without a zone. timestamp stores a wall-clock reading with no meaning attached. Use timestamptz, which stores an actual instant.
  • varchar(n) with an arbitrary n. The length is rarely a real business rule, and changing it later is a migration. Use text unless a genuine limit exists.
  • Booleans as varchar. 'Y', 'yes', 'TRUE', '1' will all end up in that column.
SQL
-- Wrong                          -- Right
price     float                    price_cents  bigint    -- or numeric(12,2)
created   timestamp                created_at   timestamptz
name      varchar(255)             name         text
is_active varchar(10)              is_active    boolean NOT NULL DEFAULT true
status    text                     status       text NOT NULL
                                     CHECK (status IN ('draft','live','archived'))

-- Postgres extras worth knowing
uuid        -- 16 bytes, not a 36-char string
jsonb       -- binary JSON, indexable with GIN
text[]      -- real arrays
numeric     -- exact decimal, arbitrary precision, slower arithmetic
inet, cidr  -- IP addresses that actually compare correctly
tstzrange   -- a time range, with overlap operators and exclusion constraints

On NOT NULL: make it the default posture. Every nullable column is a branch every reader must handle forever. Add nullability when you have a reason, rather than removing it when you are burned.

Chapter 05

SELECT, and the order things actually happen

SQL is written SELECT-first but evaluated FROM-first. Knowing the real order explains why you cannot use a column alias in WHERE, and why HAVING and WHERE are different.

SQL is declarative: you describe the result you want, not the steps. But there is a logical evaluation order, and almost every beginner confusion about SQL dissolves once you know it.

SQL
-- Written in this order:
SELECT   c.city, count(*) AS order_count
FROM     orders o
JOIN     customers c ON c.id = o.customer_id
WHERE    o.placed_at >= now() - interval '30 days'
GROUP BY c.city
HAVING   count(*) > 10
ORDER BY order_count DESC
LIMIT    20;

-- Evaluated in THIS order:
--  1. FROM      pick the tables
--  2. JOIN      combine them
--  3. WHERE     filter individual rows      <- before grouping
--  4. GROUP BY  collapse into groups
--  5. HAVING    filter the groups           <- after grouping
--  6. SELECT    compute the output columns  <- aliases born HERE
--  7. ORDER BY  sort  (can see aliases, because SELECT already ran)
--  8. LIMIT     cut

Two consequences you will hit immediately

Why this matters
  • You cannot use a SELECT alias in WHERE — WHERE runs before the alias exists. You can use it in ORDER BY, which runs after.
  • WHERE filters rows, HAVING filters groups. Filtering in WHERE is nearly always faster, because it removes rows before the expensive grouping step.

A practical habit: push every filter you can into WHERE. HAVING is only for conditions on aggregates — things that do not exist until after grouping.

Chapter 06

NULL, and why it breaks your intuition

NULL means unknown, not empty and not zero. Every comparison with an unknown is itself unknown, which is why NULL = NULL is not true and why NOT IN with NULLs returns nothing.

SQL has three-valued logic: true, false, and unknown. NULL is not a value — it is the absence of one. So NULL = NULL is not true, because is this unknown thing equal to that unknown thing is itself unknown. Filters keep only rows where the condition is true, so unknown rows are silently dropped.

SQL
SELECT NULL = NULL;        -- NULL, not true
SELECT NULL <> 5;          -- NULL, not true
SELECT NULL + 1;           -- NULL  (anything + unknown = unknown)

-- So you must use IS:
WHERE deleted_at IS NULL
WHERE deleted_at IS NOT NULL
WHERE a IS NOT DISTINCT FROM b   -- equality that treats NULL = NULL

-- THE classic trap: one NULL in the subquery kills the whole result.
SELECT * FROM users
WHERE  id NOT IN (SELECT owner_id FROM projects);
-- If ANY owner_id is NULL, this returns ZERO rows. Always.
-- Because "id <> NULL" is unknown, so no row is ever definitely true.

-- Safe rewrite:
SELECT * FROM users u
WHERE  NOT EXISTS (SELECT 1 FROM projects p WHERE p.owner_id = u.id);

Where NULL behaves differently again

Inconsistencies worth memorising
  • Aggregates skip NULLs: count(col) ignores them, count(*) does not. avg divides by the non-null count.
  • GROUP BY treats all NULLs as one group — the opposite of =.
  • UNIQUE normally permits many NULLs, since they are not equal to each other.
  • ORDER BY sorts NULLs to one end; control it with NULLS FIRST / NULLS LAST.
Interview angle

“What does NOT IN do when the subquery contains NULL?” separates people who have read about SQL from people who have been paged by it. The answer — zero rows, silently — is a genuine production outage pattern. Prefer NOT EXISTS.

II. Querying
Chapter 07

Filtering: WHERE and what it costs

How you write a condition decides whether an index can be used. Wrapping the column in a function is the most common way to accidentally disable one.

A filter is only fast if the database can use it to seek rather than scan. The rule is simple and mechanical: the indexed column must appear bare on one side of the comparison. The moment you wrap it in a function or arithmetic, the index is unusable, because the index stores the column's values, not the function's output.

SQL
-- SARGABLE (can use an index on placed_at)
WHERE placed_at >= '2026-01-01' AND placed_at < '2026-02-01'
WHERE status = 'shipped'
WHERE total_cents BETWEEN 1000 AND 5000
WHERE name LIKE 'Jat%'          -- prefix: index works

-- NOT SARGABLE (forces a full scan)
WHERE date_trunc('month', placed_at) = '2026-01-01'   -- function on column
WHERE total_cents / 100 > 50                          -- arithmetic on column
WHERE lower(email) = 'a@b.com'                        -- function on column
WHERE name LIKE '%jat%'                               -- leading wildcard
WHERE EXTRACT(year FROM placed_at) = 2026

-- Fixes: move the work to the OTHER side, or index the expression.
WHERE total_cents > 5000                        -- rearranged
CREATE INDEX ON users (lower(email));           -- expression index
CREATE INDEX ON products USING gin (name gin_trgm_ops);  -- for %infix%

Operators worth knowing

Behaviour
  • IN (...) is fine and indexable; the planner treats it as a set of equality probes.
  • OR across different columns often defeats a single index — the planner may need a bitmap OR of two indexes, or you rewrite as UNION ALL.
  • <> and NOT are rarely selective enough to be worth an index — excluding 5% of rows still means reading 95%.
  • BETWEEN is inclusive on both ends, which is almost never what you want for timestamps. Use >= start AND < end.
Interview angle

“This query got slow after we added a WHERE clause” is usually a non-sargable predicate. Ask to see the exact expression before theorising about hardware.

Chapter 08

Sorting and paging, and why OFFSET hurts

OFFSET 100000 makes the database produce a hundred thousand rows and throw them away. Keyset pagination remembers where you stopped and seeks straight there.

LIMIT 20 OFFSET 100000 reads as skip a hundred thousand, but the database cannot skip what it has not produced. It generates all 100,020 rows in order, discards the first 100,000, and returns twenty. Page one is instant, page five thousand times out — and it degrades gradually, so nobody notices until it is in production.

SQL
-- OFFSET: cost grows with page number
SELECT * FROM orders ORDER BY placed_at DESC LIMIT 20 OFFSET 100000;

-- KEYSET / SEEK: constant cost, any page
SELECT * FROM orders
WHERE  (placed_at, id) < ('2026-03-01 10:00:00+00', 918273)  -- last row seen
ORDER  BY placed_at DESC, id DESC
LIMIT  20;

-- Needs a matching index to be a pure seek:
CREATE INDEX ON orders (placed_at DESC, id DESC);
Principle

Always break ties in ORDER BY with a unique column. Sorting only by placed_at when timestamps collide gives an unstable order - rows can appear on two pages or none, and the bug is nearly impossible to reproduce.

Keyset pagination trades one capability for its speed: you cannot jump to an arbitrary page number, only forward and back. For infinite scroll and APIs that is no loss. For a UI with numbered pages, cap the reachable depth or accept OFFSET for the shallow pages only.

Chapter 09

JOINs, and the ones you actually need

INNER keeps matches, LEFT keeps everything on the left. Ninety percent of real queries need only those two — and the most common join bug is row multiplication, not a wrong join type.

A join matches rows from two tables on a condition. INNER JOIN keeps only pairs that matched. LEFT JOIN keeps every row from the left table, filling the right side with NULLs when nothing matched. RIGHT is a LEFT written backwards, and FULL keeps unmatched rows from both — both are rare enough that seeing one is usually a sign the query could be clearer.

SQL
-- Everyone's orders, only if they have some
FROM customers c JOIN orders o ON o.customer_id = c.id

-- Everyone, with orders if any (NULLs otherwise)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id

-- Customers with NO orders - the anti-join idiom
FROM   customers c
LEFT   JOIN orders o ON o.customer_id = c.id
WHERE  o.id IS NULL

-- THE classic bug: a filter on the right table in WHERE
-- silently turns a LEFT JOIN back into an INNER JOIN.
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'          -- kills the NULL rows. Now it's INNER.

LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'
                                    -- correct: condition belongs in ON

Row multiplication

If a customer has three orders, joining customers to orders gives you three rows for that customer. Now add a join to order_items with four items each, and your count(*) or sum(c.credit_limit) is quietly counting the same customer twelve times. This is the single most common wrong-number bug in SQL.

Principle

Any join that is not many-to-one multiplies rows. Before aggregating over a multi-join, either aggregate in a subquery first, or use count(DISTINCT ...) - and be sure you know which one the question actually asks for.

Chapter 10

Aggregation: GROUP BY and HAVING

GROUP BY collapses rows into one row per distinct key. Every selected column must either be in the GROUP BY or wrapped in an aggregate — because otherwise the question has no answer.

Grouping partitions rows by the key you name, then reduces each partition to a single row. This is why the rule about selected columns exists: if you group by city and also select customer_name, you are asking for one name out of many, and there is no defensible answer. Postgres refuses; MySQL historically picked one arbitrarily, which caused years of quiet bugs.

SQL
SELECT   c.city,
         count(*)                          AS orders,
         count(DISTINCT o.customer_id)     AS customers,
         sum(o.total_cents)                AS revenue,
         avg(o.total_cents)::int           AS avg_order,
         max(o.placed_at)                  AS last_order
FROM     orders o
JOIN     customers c ON c.id = o.customer_id
WHERE    o.placed_at >= now() - interval '90 days'   -- filter ROWS
GROUP BY c.city
HAVING   sum(o.total_cents) > 100000                 -- filter GROUPS
ORDER BY revenue DESC;

-- FILTER: conditional aggregates without CASE gymnastics
SELECT city,
       count(*)                                  AS total,
       count(*) FILTER (WHERE status = 'shipped') AS shipped,
       count(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders GROUP BY city;
Aggregate gotchas
  • count(*) counts rows; count(col) counts non-NULL values; count(DISTINCT col) counts distinct non-NULL values, and is much more expensive.
  • sum of no rows is NULL, not 0. Wrap it in coalesce(sum(x), 0) when a zero is what you mean.
  • A GROUP BY with no matching rows returns no row, not a row of zeros. This surprises dashboards constantly.
Chapter 11

Subqueries, and the correlated trap

An uncorrelated subquery runs once. A correlated one runs per outer row, which is how a clean-looking query becomes an accidental loop over a million rows.

A subquery that does not reference the outer query runs a single time and its result is reused. A correlated subquery references a column from the outer query, so logically it must be re-evaluated for every outer row. Modern planners often rewrite these into joins — but often is not always, and when they cannot, you have written a nested loop in disguise.

SQL
-- Uncorrelated: evaluated once
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Pune')

-- Correlated: re-evaluated per outer row (note it uses o.customer_id)
SELECT o.*,
       (SELECT count(*) FROM returns r WHERE r.customer_id = o.customer_id)
FROM orders o;

-- EXISTS stops at the first match - usually the best anti/semi-join form
WHERE EXISTS     (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

-- LATERAL: a correlated subquery in FROM. The idiomatic top-N-per-group.
SELECT c.name, recent.*
FROM   customers c
CROSS  JOIN LATERAL (
  SELECT o.id, o.total_cents
  FROM   orders o
  WHERE  o.customer_id = c.id      -- can see c
  ORDER  BY o.placed_at DESC
  LIMIT  3
) recent;
Choosing the form
  • EXISTS over IN for subqueries — it short-circuits on the first match and is NULL-safe.
  • NOT EXISTS over NOT IN, always. NOT IN returns zero rows if the subquery yields a single NULL.
  • A subquery in the SELECT list is the easiest place to hide an N+1 — check the plan, not the appearance.
Chapter 12

CTEs, including recursive ones

WITH names a subquery so a complex query reads top-to-bottom instead of inside-out. RECURSIVE walks trees and graphs — org charts, threaded comments, category hierarchies.

A common table expression is a named subquery defined before the query that uses it. Its value is almost entirely readability: a four-level nested subquery becomes four named steps that a colleague can read in order. That is not a small thing on a query someone will debug at 2am.

SQL
WITH recent AS (
  SELECT * FROM orders WHERE placed_at >= now() - interval '30 days'
), per_customer AS (
  SELECT customer_id, sum(total_cents) AS spend
  FROM recent GROUP BY customer_id
)
SELECT c.name, p.spend
FROM   per_customer p JOIN customers c ON c.id = p.customer_id
WHERE  p.spend > 50000;

-- RECURSIVE: an org chart, or any tree
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth       -- anchor: the start
  FROM   employees WHERE id = 42
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1  -- step: one level out
  FROM   employees e JOIN chain c ON e.manager_id = c.id
  WHERE  c.depth < 10                             -- ALWAYS bound the depth
)
SELECT * FROM chain;
Interview angle

Before Postgres 12 a CTE was an optimisation fence — always materialised, never inlined into the outer query. That made WITH a performance tool (deliberately blocking a bad plan) and a performance trap (blocking a good one). Modern Postgres inlines by default; force the old behaviour with AS MATERIALIZED.

Principle

Always bound a recursive CTE with a depth limit or a visited-set check. One cycle in the data - an employee who is their own grandparent - turns it into an infinite loop that consumes the machine.

Chapter 13

Window functions

An aggregate that does not collapse rows. Running totals, rankings, and comparing a row to its neighbours — all in one pass, where the alternative is a self-join per row.

GROUP BY reduces many rows to one. A window function computes across a set of related rows while keeping every row. That single difference solves an entire class of problems that otherwise require correlated subqueries or self-joins: running totals, rank within category, percentage of group, and the gap to the previous row.

SQL
SELECT
  customer_id, placed_at, total_cents,

  -- ranking within each customer
  row_number() OVER w   AS n,          -- 1,2,3,4  (arbitrary on ties)
  rank()       OVER w   AS rnk,        -- 1,2,2,4  (gaps after ties)
  dense_rank() OVER w   AS drnk,       -- 1,2,2,3  (no gaps)

  -- neighbours
  lag(total_cents)  OVER w  AS prev_order,
  lead(total_cents) OVER w  AS next_order,
  total_cents - lag(total_cents) OVER w AS delta,

  -- running and whole-partition aggregates side by side
  sum(total_cents) OVER (PARTITION BY customer_id
                         ORDER BY placed_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                            AS running_total,
  sum(total_cents) OVER (PARTITION BY customer_id) AS lifetime_total

FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at);

-- Top 3 per customer: window in a subquery, filter outside.
-- (You cannot use a window function in WHERE - it is computed after.)
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id
                                 ORDER BY total_cents DESC) AS rn
  FROM orders o
) t WHERE rn <= 3;
The three things to remember
  • PARTITION BY is group by for the window; omit it and the window is the whole result set.
  • ORDER BY inside OVER defines the running order and implies a default frame of RANGE UNBOUNDED PRECEDING AND CURRENT ROW.
  • Window functions run after WHERE and GROUP BY, so you cannot filter on one — wrap it in a subquery or CTE.
Chapter 14

Set operations, and writing an UPSERT

UNION deduplicates and UNION ALL does not, which makes ALL both faster and usually what you meant. UPSERT is the atomic insert-or-update you would otherwise get wrong under concurrency.

UNION stacks two result sets and removes duplicates — which requires a sort or hash over everything. UNION ALL just concatenates. If you know the inputs are disjoint, or you do not care about duplicates, UNION ALL is strictly cheaper and should be your default.

SQL
SELECT id FROM a UNION     SELECT id FROM b;   -- dedupes (costs a sort)
SELECT id FROM a UNION ALL SELECT id FROM b;   -- concatenates (cheap)
SELECT id FROM a INTERSECT SELECT id FROM b;   -- in both
SELECT id FROM a EXCEPT    SELECT id FROM b;   -- in a, not in b

Insert or update, atomically

The naive version — SELECT, then INSERT if absent — has a race between the two statements. Two concurrent requests both find nothing and both insert, and you get either a duplicate or a unique-violation error at random. INSERT ... ON CONFLICT makes it one atomic statement.

SQL
-- Postgres
INSERT INTO counters (key, count, updated_at)
VALUES ('page:home', 1, now())
ON CONFLICT (key) DO UPDATE
  SET count      = counters.count + 1,   -- EXCLUDED = the row you tried to insert
      updated_at = now()
RETURNING count;

-- Insert only if absent, silently
ON CONFLICT (key) DO NOTHING

-- MySQL
INSERT INTO counters (`key`, count) VALUES ('page:home', 1)
ON DUPLICATE KEY UPDATE count = count + 1;

-- The conflict target MUST have a unique index. Without one there is
-- nothing to conflict on, and the statement is just an INSERT.
Interview angle

“How do you implement insert-or-update safely?” is a favourite because the obvious answer contains a race. Naming ON CONFLICT — and explaining that it needs a unique constraint to work — is the complete answer.

III. Schema design
Chapter 15

Normalisation, without the jargon

Store every fact exactly once. That is the whole idea — the numbered normal forms are just increasingly precise statements of it.

Normalisation has a reputation for being academic, which is a shame, because the motivation is entirely practical: if a fact lives in two places, the two places will eventually disagree. Every normal form is a rule for eliminating a particular way that duplication sneaks in.

The three that matter

Normal forms in plain terms
  • 1NF — one value per cell. No comma-separated lists in a column, no phone1, phone2, phone3. If it repeats, it is a row in another table.
  • 2NF — no column depends on only part of a composite key. If your key is (order_id, product_id) and you store product_name, that depends on the product alone, so it belongs in products.
  • 3NF — no column depends on another non-key column. Storing city and city_population together is 3NF's classic violation: population is a fact about the city, not about this row.
SQL
-- Unnormalised: the same facts repeated on every row
orders(id, customer_name, customer_email, customer_city,
       product_name, product_price, qty)

-- Change a customer's email and you must find every order they ever
-- placed. Miss one and the database now disagrees with itself.

-- 3NF: every fact stored once
customers(id, name, email, city_id)
cities(id, name, population)
products(id, name, price_cents)
orders(id, customer_id, placed_at)
order_items(order_id, product_id, qty, unit_price_cents)
                                       -- ^ price AT TIME OF SALE.
-- Not a normalisation failure: it is a genuinely different fact from
-- the product's current price, and it must not change when the price does.
Interview angle

That last line is the nuance interviewers listen for. Copying a value is only denormalisation if it is the SAME fact. A historical price is a different fact from a current price, and storing it is correct modelling, not duplication.

Chapter 16

When to denormalise on purpose

Denormalise when a read is hot, the join is expensive, and you can name the thing that keeps the copy correct. Without that third part you are just introducing a bug with a schedule.

Normalise by default, denormalise on evidence. The evidence is a measured slow query, not a suspicion. And the cost is always the same: you now have two copies of a fact and you own the job of keeping them equal, forever, including during failures and backfills.

The four that are usually justified
  • Counter caches — posts.comment_count instead of counting every time. Kept correct by a trigger or by the same transaction that inserts the comment.
  • Materialised views — the database owns the copy and you refresh it. Correct by construction, stale by design.
  • Copied-down columns — order_items.product_name so the receipt still reads correctly after the product is renamed. Often not denormalisation at all, but a historical snapshot.
  • Precomputed rollups — a nightly daily_revenue table. Cheap, and the staleness is explicit.
SQL
-- Counter cache kept correct in the SAME transaction
BEGIN;
  INSERT INTO comments (post_id, body) VALUES (7, '...');
  UPDATE posts SET comment_count = comment_count + 1 WHERE id = 7;
COMMIT;
-- Both or neither. This is the part people skip, and it is the part
-- that makes the copy trustworthy.

-- Materialised view: the database keeps the copy, you choose when
CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', placed_at) AS day, sum(total_cents) AS cents
  FROM orders GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (day);   -- required for CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;  -- no read lock
Principle

Before you denormalise, write down the exact mechanism that keeps the copy correct, and a query that detects drift. If you cannot write the detection query, you will not find out when it breaks - you will hear it from a customer.

Chapter 17

Constraints are the cheapest correctness you can buy

A constraint is validated on every write, by every client, forever, with no code to maintain. Application-level validation is a suggestion; a constraint is a guarantee.

Application validation protects the paths you remembered. It does not protect the migration script, the admin console, the data-fix someone ran by hand at midnight, or the second service that was written last quarter. The database is the one place every write must pass through, which makes it the only place a rule can actually be guaranteed.

SQL
CREATE TABLE bookings (
  id          bigserial PRIMARY KEY,
  room_id     bigint NOT NULL REFERENCES rooms(id),
  guest_email citext NOT NULL,
  guests      int    NOT NULL,
  price_cents bigint NOT NULL,
  period      tstzrange NOT NULL,
  status      text   NOT NULL DEFAULT 'held',

  CONSTRAINT guests_positive  CHECK (guests BETWEEN 1 AND 8),
  CONSTRAINT price_nonneg     CHECK (price_cents >= 0),
  CONSTRAINT status_known     CHECK (status IN ('held','paid','cancelled')),
  CONSTRAINT email_shaped     CHECK (guest_email ~ '^[^@]+@[^@]+$'),

  -- one row per room per email
  UNIQUE (room_id, guest_email),

  -- and the good one: no two bookings for a room may overlap in time.
  -- Enforced by the database, atomically, under any concurrency.
  EXCLUDE USING gist (room_id WITH =, period WITH &&)
);
Interview angle

That EXCLUDE constraint is worth knowing by name. “How do you prevent double-booking?” usually gets an answer involving a lock or a SELECT-then-INSERT race. An exclusion constraint solves it declaratively and cannot be raced.

Practicalities
  • Add constraints as NOT VALID first on a large table, then VALIDATE separately — the first is instant, the second takes a weak lock instead of blocking writes.
  • Name your constraints. bookings_guests_positive in an error log tells you what happened; chk_2891a does not.
  • A CHECK cannot reference another table or use a volatile function — those need a trigger or an exclusion constraint.
Chapter 18

Modelling relationships

One-to-many puts the foreign key on the many side. Many-to-many needs a join table. One-to-one usually means you should have used one table.

Three shapes cover nearly every relationship, and picking the wrong one is a structural mistake that gets expensive to reverse. The deciding question is always how many of B can one A have, and how many of A can one B have.

SQL
-- ONE-TO-MANY: FK on the many side. A customer has many orders.
orders(id, customer_id REFERENCES customers(id), ...)

-- MANY-TO-MANY: a join table, keyed on the pair.
CREATE TABLE student_courses (
  student_id bigint REFERENCES students(id) ON DELETE CASCADE,
  course_id  bigint REFERENCES courses(id)  ON DELETE CASCADE,
  enrolled_at timestamptz NOT NULL DEFAULT now(),
  grade      text,
  PRIMARY KEY (student_id, course_id)     -- the pair IS the identity
);
CREATE INDEX ON student_courses (course_id);  -- for the reverse lookup

-- ONE-TO-ONE: usually a smell. Justified when:
--   the columns are genuinely optional for most rows, or
--   they are large (a blob) and rarely selected, or
--   they have a different access-control or retention policy.
user_kyc_documents(user_id PRIMARY KEY REFERENCES users(id), ...)
Principle

A join table's primary key should be the pair, and it needs an index on the SECOND column too. The primary key index serves student_id lookups; without a separate index, "who is in this course" is a full scan. This is the most commonly missed index in any schema.

Polymorphic associations

The tempting shape — commentable_type plus commentable_id, so a comment can attach to a post or a photo — cannot have a foreign key, because the target table is not known until runtime. You have given up referential integrity for convenience. The alternatives are separate tables per parent (post_comments, photo_comments), or a shared parent table that both inherit from. Both are less elegant and both actually work.

Chapter 19

Modelling time: history, soft deletes, audit

Rows that change lose their past. If anyone will ever ask “what was it on Tuesday”, you must model time explicitly — retrofitting history is close to impossible.

A plain UPDATE destroys the previous value. That is fine for a session token and catastrophic for a price, a permission, or an address that appeared on an invoice. Decide up front which columns need a past, because the past you did not record cannot be recovered.

SQL
-- 1. SOFT DELETE - the row stays, marked
deleted_at timestamptz            -- NULL means live

-- Every query must now remember to filter. They will forget.
-- Make it structural instead:
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;
                       -- ^ partial index: uniqueness only among live rows

-- 2. AUDIT LOG - append-only record of what changed
CREATE TABLE audit_log (
  id         bigserial PRIMARY KEY,
  table_name text NOT NULL,
  row_id     bigint NOT NULL,
  action     text NOT NULL,
  old_row    jsonb,
  new_row    jsonb,
  actor_id   bigint,
  at         timestamptz NOT NULL DEFAULT now()
);

-- 3. TEMPORAL TABLE - the row itself is versioned
CREATE TABLE prices (
  product_id bigint NOT NULL,
  cents      bigint NOT NULL,
  valid      tstzrange NOT NULL,
  EXCLUDE USING gist (product_id WITH =, valid WITH &&)  -- no overlaps
);
-- "What was the price on 3 March?"
SELECT cents FROM prices
WHERE product_id = 7 AND valid @> '2026-03-03'::timestamptz;
Principle

Soft deletes are viral. Every query, every join, every unique constraint and every count must now account for them, forever, and one forgotten filter leaks deleted data to a user. Use them where a legal or recovery requirement demands it - not as a default.

Chapter 20

Choosing a primary key: bigint vs UUID

Sequential integers index beautifully and leak your row count. Random UUIDs leak nothing and fragment your index. UUIDv7 is time-ordered and gets you both.

This looks like a taste question and is actually a physical one. In a clustered index — MySQL's InnoDB, and any Postgres table with an index on the key — rows are inserted in key order. A sequential key always appends to the rightmost page, which is hot in cache. A random key inserts into a random page, which means reading a cold page, splitting it, and dirtying it. At scale the difference in write throughput is large.

SQL
-- bigserial / AUTO_INCREMENT
+ 8 bytes, sequential inserts, dense index, fast range scans
- reveals volume and ordering (/orders/1042 → you have ~1042 orders)
- cannot generate client-side; needs a round trip or a sequence
- painful to merge data from two systems

-- UUIDv4 (random)
+ 16 bytes, generate anywhere, no coordination, leaks nothing
- random insert position → page splits, index bloat, cold cache
- 2x the key size, and it is copied into EVERY secondary index

-- UUIDv7 (time-ordered, RFC 9562) - the modern default
+ random-looking to clients, but sortable and sequential on disk
+ keeps insert locality; the timestamp prefix is the ordering
- newer; check your driver and DB version support it

-- Common production compromise:
CREATE TABLE orders (
  id         bigserial PRIMARY KEY,       -- internal, joins, FKs
  public_id  uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE  -- in URLs
);
Interview angle

“Why is UUID a bad primary key?” expects page splits and index fragmentation in a clustered index — and the follow-up, that in InnoDB every secondary index stores the full primary key, so a 16-byte key inflates every index on the table, not just one.

Chapter 21

Migrations that do not take the site down

The danger is not the change — it is the lock it takes and how long it holds it. A migration that needs an exclusive lock for thirty seconds is a thirty-second outage.

Every schema change takes a lock. Most are instant. A few rewrite the whole table while holding a lock that blocks reads and writes, and those are the ones that turn a routine deploy into an incident. The skill is knowing which is which, and rewriting the dangerous ones as a sequence of safe steps.

SQL
-- SAFE (metadata only, instant)
ADD COLUMN foo text                    -- nullable, no default
ADD COLUMN foo text DEFAULT 'x'        -- PG 11+, MySQL 8+: no rewrite
DROP COLUMN foo
CREATE INDEX CONCURRENTLY ...          -- no write lock (PG)
ALTER TABLE ... VALIDATE CONSTRAINT    -- weak lock

-- DANGEROUS (rewrites the table, blocks everything)
ADD COLUMN foo text NOT NULL           -- must fill every existing row
ALTER COLUMN foo TYPE bigint           -- rewrite
CREATE INDEX ...                       -- WITHOUT concurrently: blocks writes
ADD CONSTRAINT ... CHECK (...)         -- without NOT VALID: full scan

-- The safe pattern for a NOT NULL column on a big table:
1. ALTER TABLE t ADD COLUMN foo text;                    -- instant
2. backfill in batches, committing each   -- see below
3. ALTER TABLE t ADD CONSTRAINT foo_nn CHECK (foo IS NOT NULL) NOT VALID;
4. ALTER TABLE t VALIDATE CONSTRAINT foo_nn;             -- weak lock

-- Backfill in batches so no single transaction is long
UPDATE t SET foo = 'x'
WHERE id IN (SELECT id FROM t WHERE foo IS NULL LIMIT 5000);
-- repeat until zero rows affected, sleeping briefly between batches

Expand and contract

Renaming a column with zero downtime
  • Expand — add the new column or table. Deploy code that writes both and reads the old.
  • Migrate — backfill the new one in batches.
  • Switch — deploy code that reads the new one.
  • Contract — stop writing the old one, then drop it, in a later deploy.
Principle

Always set a lock_timeout on migrations. Without one, a migration waiting behind a long-running query joins the lock queue and blocks every subsequent query on that table - so a change that needed a 5ms lock takes the site down for as long as that one slow read runs.

IV. Storage and indexes
Chapter 22

Pages, heap files, and the row on disk

The database reads and writes fixed-size pages, typically 8KB, never individual rows. Almost every performance property follows from that one fact.

A table is a file of fixed-size pages. To read one 200-byte row, the database reads the whole 8KB page containing it. To change one byte, it dirties the whole page and eventually writes all 8KB back. Once you internalise this, most performance advice stops being a list of tips and becomes arithmetic.

Consequences
  • Narrow rows are faster — more rows per page means fewer pages read for the same result.
  • Related rows on the same page are nearly free — this is what clustering and index locality are buying you.
  • A random row costs a whole page — which is why 1,000 scattered rows can be slower than scanning 100,000 sequential ones.
  • Column order matters slightly — alignment padding means bigint, bigint, bool packs tighter than bigint, bool, bigint.
Storage
An 8KB heap page:
┌──────────────────────────────────────────┐
│ header                                   │
│ item pointers →→→                        │  grows down
│                                          │
│              (free space)                │
│                                          │
│                        ←←← row data      │  grows up
│ row3 │ row2 │ row1                       │
└──────────────────────────────────────────┘

A row identifier is (page number, item number) — Postgres calls it a
ctid, InnoDB uses the primary key. That pair is what an index stores.

-- Values too large for a page go to overflow storage (TOAST in Postgres),
-- compressed and stored out of line. A big text column you rarely SELECT
-- costs almost nothing to skip - but SELECT * pays for it every time.

Two engine designs matter here. A heap table (Postgres) stores rows in no particular order and every index points into it. A clustered index (InnoDB) stores the rows inside the primary key's B-tree, in key order. That difference explains most Postgres/MySQL behavioural gaps you will hit later.

Chapter 23

Why an index is a B+tree

A B+tree keeps everything sorted with a very high branching factor, so any row is three or four page reads away — and unlike a hash, it also answers range queries and ORDER BY.

An index exists to avoid reading every page. A binary tree would be the obvious structure and is the wrong one: each node is a separate random read, so a million rows means twenty random reads. A B+tree stores hundreds of keys per page, so the tree is wide and shallow — typically three or four levels for hundreds of millions of rows.

Structure
A B+tree (order simplified for the diagram):

                    [ 50 | 100 ]              root  (cached, always)
                   /     |      \
         [10|30]     [60|80]      [150|300]    internal (usually cached)
        /   |   \    ...
    →[1..9]→[10..29]→[30..49]→ ...             leaves, LINKED left to right
       │       │        │
     row ptrs (page, item)

Why it wins:
  fanout ~250 keys/page → 250^4 = 3.9 billion rows in 4 levels
  levels 1-3 are almost always in memory
  so a lookup is ~1 actual disk read

And because leaves are sorted AND linked:
  point lookup   WHERE id = 42          → descend, done
  range scan     WHERE id BETWEEN a,b   → descend once, walk the leaves
  sorted output  ORDER BY id            → walk the leaves, NO SORT NEEDED
  min / max      → leftmost / rightmost leaf
  prefix match   LIKE 'Jat%'            → a range scan in disguise
Interview angle

“Why B+tree and not a hash index?” — a hash gives O(1) equality and nothing else. No ranges, no sorting, no prefix matching, no min/max. The B+tree gives up a little on point lookups to serve every other access pattern, which is why it is the default everywhere.

Chapter 24

Clustered and secondary indexes

A clustered index IS the table, stored in key order. A secondary index is a separate structure pointing back — and in InnoDB it points back via the primary key, which is why a fat primary key is expensive.

In InnoDB the primary key is a clustered index: the leaf pages of that B+tree contain the actual rows. There is exactly one, because the rows can only be in one order. Every other index is secondary, and its leaves store the indexed columns plus the primary key value — not a physical address.

Engines
InnoDB: secondary lookup is TWO tree descents

  SELECT * FROM users WHERE email = 'a@b.com';

  1. descend the email index  → finds primary key value 4271
  2. descend the PK index     → finds the actual row
     (this second step is the "bookmark lookup")

  → so every secondary index stores a full copy of the PK.
    A 16-byte UUID PK inflates EVERY index on the table.

Postgres: heap table, indexes hold a physical pointer (ctid)

  1. descend the email index  → ctid (page 91, item 3)
  2. read that heap page directly

  → PK width does not propagate into other indexes
  → but the ctid moves when a row is updated, which is why Postgres
    has HOT updates and why VACUUM exists

ORDER OF ROWS ON DISK
  InnoDB   : always primary key order (it IS the clustered index)
  Postgres : insertion order-ish; CLUSTER reorders once, but it does
             not stay clustered as rows are updated
Principle

In InnoDB, choose the primary key knowing it is copied into every secondary index and determines physical row order. A sequential bigint keeps inserts appending and indexes narrow; a random UUID does the opposite twice over.

Chapter 25

Composite indexes and the leftmost rule

A multi-column index is sorted by the first column, then the second within that. You can use any leftmost prefix — and the first range condition ends the useful part of the index.

Think of a phone book sorted by (last name, first name). You can look up everyone named Sharma, and you can look up Sharma, Priya. You cannot look up everyone named Priya — the book is not sorted that way. That is the leftmost-prefix rule, complete.

SQL
CREATE INDEX ON orders (customer_id, status, placed_at);

-- USES the index
WHERE customer_id = 5
WHERE customer_id = 5 AND status = 'paid'
WHERE customer_id = 5 AND status = 'paid' AND placed_at > '2026-01-01'
WHERE customer_id = 5 ORDER BY status, placed_at      -- sort for free

-- CANNOT use it (no leftmost prefix)
WHERE status = 'paid'
WHERE placed_at > '2026-01-01'
WHERE status = 'paid' AND placed_at > '2026-01-01'

-- PARTIALLY uses it: equality on col1, range on col2, then STOPS.
WHERE customer_id = 5 AND status > 'a' AND placed_at = '...'
--    ^ index seek     ^ range        ^ placed_at is now a filter,
--                                      not a seek. Column order matters.

How to order the columns

The E-R-S rule
  • Equality columns first, then the range column, then the columns you only sort by. This is the single most useful indexing heuristic.
  • Among equality columns, ordering by selectivity barely matters for lookups — but putting the low-cardinality column first can compress better and helps ORDER BY.
  • One well-ordered composite index usually replaces three single-column ones. Fewer indexes means faster writes.
Interview angle

“Why isn't my index being used?” is most often a leftmost-prefix miss, followed by a non-sargable predicate, followed by the planner deciding a scan is genuinely cheaper. Check them in that order.

Chapter 26

Covering indexes and index-only scans

If the index contains every column the query needs, the database never touches the table at all. This is often the difference between 40ms and 0.4ms.

Normally an index lookup finds a row pointer and then reads the table page — the bookmark lookup. If every column in the SELECT, WHERE and ORDER BY is present in the index itself, that second read is skipped entirely. The index covers the query.

SQL
-- Query
SELECT status, placed_at FROM orders
WHERE customer_id = 5 ORDER BY placed_at DESC;

-- Plain index: seek, then read N table pages
CREATE INDEX ON orders (customer_id);

-- Covering: everything needed is in the index → index-only scan
CREATE INDEX ON orders (customer_id, placed_at DESC, status);

-- Postgres INCLUDE: payload columns in the leaf, NOT in the sort key.
-- Smaller internal pages, and the extra columns cannot be searched on.
CREATE INDEX ON orders (customer_id, placed_at DESC) INCLUDE (status);

EXPLAIN output to look for:
  Index Only Scan using orders_cust_idx      <- covered
  Index Scan  ... + Heap Fetches: 41000      <- not covered

-- Postgres caveat: an index-only scan still checks the visibility map,
-- so a table with many dead rows falls back to heap fetches until
-- VACUUM runs. "Heap Fetches: 0" is the signal it truly worked.
Principle

Covering is the highest-leverage index optimisation and also the easiest to overdo. Every added column widens the index, slows every write to the table, and consumes cache that other indexes wanted. Cover the query that matters, not every query.

Chapter 27

Partial, expression, and unique indexes

A partial index covers only the rows matching a condition — often 2% of the table for 100% of the queries. It is the most underused feature in Postgres.

Most tables have a hot minority. Ten million orders, of which four hundred are pending — and every polling query asks only about those. A full index on status indexes ten million rows to serve four hundred. A partial index indexes four hundred, fits in cache, and updates only when a row enters or leaves the condition.

SQL
-- PARTIAL: tiny index, huge win
CREATE INDEX ON jobs (created_at) WHERE status = 'pending';
-- Used only when the query's WHERE implies the index's WHERE:
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 10;

-- Uniqueness that applies conditionally - a soft-delete essential
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;
-- one live account per email, unlimited deleted ones

-- Only one default per user
CREATE UNIQUE INDEX ON addresses (user_id) WHERE is_default;

-- EXPRESSION: index the computed value
CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com';   -- now a seek
-- The expression in the query must match the index EXACTLY.

CREATE INDEX ON orders (date_trunc('month', placed_at));
CREATE INDEX ON events ((payload->>'user_id'));       -- into JSONB

-- UNIQUE across multiple columns
CREATE UNIQUE INDEX ON memberships (org_id, user_id);
Interview angle

“One default address per user” or “one active subscription per customer” asked in an interview usually gets an application-level answer with a race condition. A partial unique index is the correct answer and is one line.

Chapter 28

Beyond B-trees: GIN, GiST, hash, and full text

B-tree indexes one value per row. When a row contains many searchable things — words, array elements, JSON keys — you need an inverted index, which is what GIN is.

A B-tree assumes each row has one key. That breaks for a document with 200 words, an array of tags, or a JSONB blob with arbitrary keys. GIN inverts the relationship: it maps each term to the list of rows containing it, which is exactly how a search engine works.

SQL
-- GIN: many keys per row (arrays, JSONB, full text, trigrams)
CREATE INDEX ON posts USING gin (tags);            -- text[]
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

CREATE INDEX ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type":"signup"}';

-- Full text search
ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX ON posts USING gin (tsv);
SELECT * FROM posts WHERE tsv @@ websearch_to_tsquery('english', 'index bloat');

-- Trigrams: makes LIKE '%infix%' and fuzzy matching indexable
CREATE EXTENSION pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%phone%';
SELECT * FROM products ORDER BY name <-> 'ifone' LIMIT 5;   -- fuzzy

-- GiST: ranges, geometry, nearest-neighbour, EXCLUDE constraints
CREATE INDEX ON bookings USING gist (period);

-- BRIN: tiny index for naturally-ordered huge tables (append-only logs)
CREATE INDEX ON events USING brin (created_at);
-- gigabytes of table, kilobytes of index - but only works if physical
-- order matches the column's order.

-- HASH: equality only. Rarely worth it over a B-tree.
Principle

GIN is fast to read and slow to write - it may update many entries per row. On a write-heavy table, tune fastupdate and the pending-list size, or accept that inserts cost several times more than they did.

Chapter 29

What an index costs

Every index must be updated on every insert, update to its columns, and delete. Indexes are a read optimisation paid for with write throughput, memory, and disk.

Indexes feel free because the cost lands somewhere you are not looking. A table with eight indexes turns one row insert into nine write operations, each potentially splitting a page. The reason a bulk load is dramatically faster after dropping indexes is that you stopped paying this on every row.

SQL
-- Find indexes nobody uses (Postgres)
SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0 AND indexrelid NOT IN (
         SELECT conindid FROM pg_constraint WHERE contype IN ('p','u'))
ORDER  BY pg_relation_size(indexrelid) DESC;

-- Find duplicate/redundant indexes
-- (a,b) makes a lone (a) redundant - the prefix rule covers it

-- Table vs index size
SELECT pg_size_pretty(pg_table_size('orders'))   AS heap,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes;
-- Indexes larger than the table is normal-ish; 3x is worth investigating.

-- Rebuild a bloated index without blocking
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
A working policy
  • Index the columns you filter, join and sort on — not every column.
  • A composite (a, b) makes a separate index on (a) redundant. Drop the narrower one.
  • Foreign key columns need an index for the reverse lookup, and for ON DELETE CASCADE not to full-scan the child table. Postgres does not create it for you.
  • Drop indexes with zero scans after a full business cycle of observation — a month, not a Tuesday.
Interview angle

A useful rule of thumb for interviews: on an OLTP table, more than about five or six indexes is a smell worth explaining. On an analytics table nobody writes to, index freely.

V. Query execution
Chapter 30

What the planner actually does

SQL says what you want, not how. The planner enumerates ways to get it, estimates the cost of each from statistics, and picks the cheapest guess. Every planner mistake traces back to a bad estimate.

For a three-table join there are already a dozen plausible plans: which table to read first, which join algorithm, which index, whether to sort or hash. The planner assigns each a cost — an abstract number combining estimated page reads and CPU — and picks the minimum. It is doing arithmetic on estimates, not measuring anything.

The four stages
  • Parse — syntax to a tree.
  • Rewrite — expand views, inline CTEs and simple subqueries, apply rules.
  • Plan — enumerate access paths and join orders, cost each using table statistics, pick the cheapest.
  • Execute — run the chosen tree, pulling rows through the operators.
SQL
-- The planner's cost model, roughly:
seq_page_cost      = 1.0    -- sequential page read
random_page_cost   = 4.0    -- random page read (1.1 on SSD - tune this!)
cpu_tuple_cost     = 0.01   -- process one row
cpu_index_tuple_cost = 0.005

-- Everything hinges on the row-count estimate:
--   "I think this filter matches 30 rows"   → index scan
--   "I think this filter matches 3,000,000" → sequential scan
-- Both can be right. Only one is right for YOUR data.

SET random_page_cost = 1.1;   -- the single most common useful tweak;
                              -- the default 4.0 assumes spinning disks
Interview angle

“The database is ignoring my index” is nearly always the planner correctly deciding a scan is cheaper — or incorrectly deciding it because the statistics are stale. Compare estimated vs actual rows in EXPLAIN ANALYZE before blaming the optimiser.

Chapter 31

Reading EXPLAIN

EXPLAIN shows the plan and its estimates. EXPLAIN ANALYZE runs it and shows reality. The gap between estimated and actual rows is where the answer almost always is.

Read a plan from the inside out and the bottom up: the most indented nodes run first and feed their parents. The number you care about most is not the cost — it is rows= estimated versus rows= actual. A 1000× discrepancy means the planner chose based on fiction.

EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name, count(*) FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at > now() - interval '7 days'
GROUP BY c.name;

 HashAggregate  (cost=18231..18456 rows=225) (actual time=412..414 rows=228)
   ->  Hash Join  (cost=1120..17982 rows=49812)
                  (actual time=18..380 rows=51204 loops=1)
         Hash Cond: (o.customer_id = c.id)
         ->  Seq Scan on orders o
               (cost=0..15320 rows=49812) (actual rows=51204 loops=1)
               Filter: (placed_at > ...)
               Rows Removed by Filter: 948796      <-- read 1M to keep 51k
               Buffers: shared read=12043          <-- 94MB off disk
         ->  Hash  (actual rows=2140 loops=1)
               ->  Seq Scan on customers c (actual rows=2140)

 Planning Time: 0.4 ms
 Execution Time: 415.2 ms
What to look at, in order
  • estimated vs actual rows — a big gap means stale or missing statistics. Fix this first.
  • Rows Removed by Filter — the work thrown away. Large numbers mean a missing index.
  • loops=N — actual time is per loop. Multiply by loops for the real total. This is where N+1 hides.
  • Buffers: shared read vs hit — read came from disk, hit from cache. Always ask for BUFFERS.
  • external merge Disk: 48MB — a sort spilled; raise work_mem for that query.

Paste the text output into explain.dalibo.com or explain.depesz.com. Both highlight the node where the time actually went, which is faster than reading indentation by eye.

Chapter 32

Scan types

Sequential scans are not a bug. Reading the whole table sequentially beats thousands of random index lookups — the planner switches around 5-10% selectivity for a reason.

The instinct that Seq Scan means something is wrong is one of the most common misreadings of a plan. A sequential scan reads pages in order, which the disk and the read-ahead cache love. An index scan on 30% of a table performs random reads for each row, plus a heap fetch each, and is genuinely slower.

Plans
Seq Scan            read every page in order
                    → right for small tables and low-selectivity filters

Index Scan          walk the index, fetch each matching row from the heap
                    → right when few rows match; random I/O per row

Index Only Scan     everything needed is in the index; heap never touched
                    → the fastest option. Check "Heap Fetches: 0"

Bitmap Heap Scan    build a bitmap of matching pages from the index,
 + Bitmap Index Scan then read those pages IN PHYSICAL ORDER
                    → the middle ground: too many rows for an index scan,
                      too few for a seq scan. Also combines two indexes
                      with BitmapAnd / BitmapOr.

Rough crossover:
  < 1%  of rows      Index Scan
  1-10%              Bitmap Heap Scan
  > 10-20%           Seq Scan

-- Prove it to yourself (NEVER in production config):
SET enable_seqscan = off;    -- forces the alternative, shows its real cost
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;
Interview angle

If forcing enable_seqscan=off makes the query faster, the planner mis-estimated. Fix the cause — run ANALYZE, add an extended statistics object, or lower random_page_cost — rather than shipping the flag.

Chapter 33

Join algorithms

Three algorithms. Nested loop for few rows with an index, hash join for big unsorted equality joins, merge join when both sides are already sorted.

Knowing the three lets you read any join plan and know immediately whether it is reasonable. Nearly every catastrophic join plan is a nested loop chosen because the planner thought the outer side had 10 rows when it had 500,000.

Plans
NESTED LOOP
  for each row in outer:  look up matches in inner
  cost: O(outer × inner_lookup)
  GREAT when outer is tiny and inner has an index on the join key
  CATASTROPHIC when the outer estimate is wrong - this is the plan
  that turns 20ms into 20 minutes

HASH JOIN
  build a hash table of the SMALLER side, then stream the larger past it
  cost: O(n + m), needs memory for the build side
  The workhorse for big equality joins. Spills to disk if work_mem is
  too small - watch for "Batches: 8" in the plan.
  Equality only.

MERGE JOIN
  sort both sides, then walk them together like a zipper
  cost: O(n log n + m log m), or O(n + m) if both are already sorted
  Wins when both inputs arrive sorted (from index scans) or the result
  needs to be sorted anyway. Handles range conditions.

How to read it:
  Nested Loop with loops=847291   → the outer estimate was wrong
  Hash Join with Batches: 1       → healthy, fit in memory
  Hash Join with Batches: 16      → spilled; raise work_mem
Principle

A nested loop over a large outer side is the single most common cause of a query that used to be fast becoming pathologically slow. The trigger is usually a stale estimate after a data-volume change - run ANALYZE before anything else.

Chapter 34

Statistics, and when the planner is wrong

The planner assumes columns are independent. When they are correlated — city and pincode, brand and model — it multiplies selectivities and underestimates wildly.

Postgres keeps a histogram, a list of most-common values, and a distinct count per column. For a single filter this is usually accurate. For WHERE city = 'Pune' AND state = 'Maharashtra' it multiplies the two selectivities as if they were unrelated — but every Pune row is a Maharashtra row, so the real count is far higher than the estimate. The planner then picks a nested loop for what it thinks is three rows.

SQL
-- 1. Are stats stale?
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

ANALYZE orders;              -- refresh now

-- 2. Correlated columns: teach the planner they are dependent
CREATE STATISTICS city_state (dependencies, ndistinct)
  ON city, state FROM addresses;
ANALYZE addresses;

-- 3. Skewed column: more histogram detail
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;  -- default 100
ANALYZE orders;

-- 4. Autovacuum too lazy on a big hot table
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.02,   -- default 0.1 = 10% of rows
  autovacuum_vacuum_scale_factor  = 0.05
);
-- On a 100M-row table the default means waiting for 10M changes
-- before stats refresh. That is far too late.
Four causes of a bad estimate
  • A bulk load or a big DELETE without a following ANALYZE — the classic fast in staging, slow in production.
  • Correlated columns, fixed with a CREATE STATISTICS object.
  • A skewed column where one value is 90% of rows and the histogram is too coarse.
  • Parameterised queries where one plan is cached and reused for a wildly different parameter.
Chapter 35

Finding the slow query

Find the query costing the most total time, not the slowest single execution. A 20ms query run two million times an hour outranks a 4-second report run twice a day.

Optimisation without measurement is guessing. pg_stat_statements aggregates every query by its normalised shape and tells you total time, calls, and mean. Sort by total_exec_time — that column is your priority list, and it is frequently not what anyone expected.

SQL
CREATE EXTENSION pg_stat_statements;   -- needs shared_preload_libraries

-- The single most useful query in database operations
SELECT substr(query, 1, 90)          AS q,
       calls,
       round(total_exec_time::numeric)      AS total_ms,
       round(mean_exec_time::numeric, 2)    AS mean_ms,
       rows,
       round(100.0 * shared_blks_hit /
             nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_pct
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

SELECT pg_stat_statements_reset();   -- baseline before a change

-- What is running RIGHT NOW, and what is blocked
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS runtime, left(query, 80)
FROM   pg_stat_activity
WHERE  state <> 'idle' AND backend_type = 'client backend'
ORDER  BY runtime DESC;

-- Log anything slow, permanently
ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET auto_explain.log_min_duration = '1s';
Principle

Optimise by total time, then by variance. A query with a 5ms mean and a 9-second p99 is a worse problem than a steady 200ms one - the p99 is where timeouts, retries and cascading failures come from.

Chapter 36

Query anti-patterns

A short list of shapes that are slow every time. Learning to spot them in code review is cheaper than finding them in a flame graph.

The list
  • N+1 — one query for the list, then one per row. The most expensive mistake in application code, and invisible in the database's per-query timings because each one is fast.
  • SELECT * — pulls TOASTed blobs you did not want, prevents index-only scans, and breaks when a column is added.
  • Function on an indexed column — WHERE date(created_at) = ... disables the index.
  • Leading wildcard — LIKE '%foo%' cannot use a B-tree. Use trigrams or full text.
  • OFFSET deep paging — produces and discards everything before the offset.
  • NOT IN with NULLs — silently returns zero rows.
  • Implicit type cast — comparing a bigint column to a string parameter can disable the index.
  • count(*) on a huge table — an exact count means reading everything. Use an estimate when approximate is fine.
Mixed
-- N+1 (in the app, invisible in slow query logs)
posts = db.query("SELECT * FROM posts LIMIT 50")
for p in posts:
    p.author = db.query("SELECT * FROM users WHERE id = ?", p.author_id)
# 51 round trips. At 2ms network latency that is 100ms of pure waiting.

# Fix 1: join
SELECT p.*, u.name FROM posts p JOIN users u ON u.id = p.author_id LIMIT 50;

# Fix 2: batch (what a good ORM's "eager loading" does)
SELECT * FROM users WHERE id = ANY(ARRAY[1,2,3,...]);

-- Approximate count, instantly
SELECT reltuples::bigint FROM pg_class WHERE relname = 'events';

-- Implicit cast killing an index
WHERE user_id = '4271'    -- string vs bigint column: check the plan
WHERE user_id = 4271      -- match the type
Interview angle

N+1 is the highest-yield thing to name in an interview because it is an application bug that presents as a database problem. Each query is 1ms and blameless; the sum is the outage.

VI. Transactions
Chapter 37

ACID, precisely

Atomicity and durability are nearly free and rarely tuned. Isolation is the expensive one, it is configurable, and almost every concurrency bug you will meet is an isolation misunderstanding.

The acronym flattens four properties of very different cost and interest. Atomicity and durability are what the write-ahead log gives you and you seldom think about them. Consistency is mostly your constraints doing their job. Isolation is the one with a dial on it, and turning that dial trades correctness for throughput.

The four, weighted by how much they will cost you
  • Atomicity — all statements in the transaction, or none. Delivered by the WAL and rollback.
  • Consistency — the database moves from one valid state to another, where valid means your constraints hold. Mostly your job, not the engine's.
  • Isolation — concurrent transactions do not see each other's partial work. Configurable, and the subject of the next three chapters.
  • Durability — once COMMIT returns, the data survives a crash. Delivered by fsync of the WAL.
SQL
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                      -- atomic: both, or neither

-- Savepoints: partial rollback inside a transaction
BEGIN;
  INSERT INTO orders ...;
  SAVEPOINT before_optional;
  INSERT INTO analytics ...;         -- allowed to fail
  ROLLBACK TO before_optional;       -- undo only that
COMMIT;

-- The most important operational fact about transactions:
-- an open transaction holds locks and blocks vacuum for its whole life.
-- BEGIN → call a payment API for 8 seconds → COMMIT
-- is a bug, not a design. Do external I/O outside the transaction.
Principle

Keep transactions short and free of network calls. A transaction that waits on an HTTP request holds its locks for the duration of someone else's outage, and one slow dependency becomes database-wide contention.

Chapter 38

The anomalies

Five things that go wrong when transactions overlap. Isolation levels are defined by exactly which of these they prevent, so the anomalies come first.

Learn these as concrete scenarios rather than names. Every isolation level in the next chapter is nothing more than a list of which of these it forbids, and an interview answer that describes the scenario always beats one that recites the term.

Scenarios
DIRTY READ  - reading uncommitted data
  T1: UPDATE balance = 0    (not committed)
  T2: SELECT balance        → 0
  T1: ROLLBACK              → T2 acted on a value that never existed

NON-REPEATABLE READ - same row, different value, same transaction
  T1: SELECT balance → 500
  T2: UPDATE balance = 300; COMMIT
  T1: SELECT balance → 300  → T1's two reads disagree

PHANTOM READ - same query, different SET of rows
  T1: SELECT count(*) WHERE status='open' → 5
  T2: INSERT a new open row; COMMIT
  T1: SELECT count(*) WHERE status='open' → 6

LOST UPDATE - two read-modify-writes, one silently discarded
  T1: read count=10                T2: read count=10
  T1: write count=11               T2: write count=11
  → two increments, one result. THE most common real bug.

WRITE SKEW - each transaction is individually valid, together they break
  Rule: at least one doctor must stay on call.
  T1: sees Alice+Bob on call, takes Alice off duty
  T2: sees Alice+Bob on call, takes Bob off duty
  → both commit. Nobody is on call. No row was written twice.
     Only SERIALIZABLE prevents this.
Interview angle

Write skew is the one that separates a memorised answer from an understood one. It cannot be fixed by locking the rows you wrote, because the conflict is on rows you only read — which is precisely why SERIALIZABLE has to track read sets.

Chapter 39

Isolation levels

Four levels, each forbidding more anomalies at more cost. Read Committed is the common default and permits lost updates — which is why you still need explicit locking or a retry loop.

SQL
LEVEL              DIRTY  NON-REPEAT  PHANTOM  WRITE SKEW
READ UNCOMMITTED     yes      yes        yes       yes
READ COMMITTED        -       yes        yes       yes    ← PG/Oracle default
REPEATABLE READ       -        -       (see below)  yes    ← MySQL default
SERIALIZABLE          -        -         -          -

-- Postgres REPEATABLE READ is a true snapshot: it also prevents
--   phantoms, exceeding the SQL standard's requirement.
-- MySQL REPEATABLE READ prevents phantoms for plain SELECT via MVCC,
--   but locking reads (SELECT ... FOR UPDATE) see the latest committed
--   rows. Same words, materially different behaviour.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

At READ COMMITTED — where most production systems run — each statement sees a fresh snapshot of committed data. That means two statements in one transaction can see different data, and a read-modify-write across two statements can lose an update. The database will not stop you; it is not an error at that level.

SQL
-- LOST UPDATE at READ COMMITTED
BEGIN;
  SELECT count FROM counters WHERE id = 1;   -- 10
  -- another transaction commits 11 here
  UPDATE counters SET count = 11 WHERE id = 1;  -- overwrites their work
COMMIT;

-- Fix 1: do it in one atomic statement (best when possible)
UPDATE counters SET count = count + 1 WHERE id = 1;

-- Fix 2: pessimistic lock
SELECT count FROM counters WHERE id = 1 FOR UPDATE;   -- others wait

-- Fix 3: optimistic - version check, retry on failure
UPDATE counters SET count = 11, version = 8
WHERE id = 1 AND version = 7;      -- 0 rows affected → someone beat you
Principle

SERIALIZABLE in Postgres does not block - it detects conflicts and aborts one transaction with a serialization_failure. Choosing it means you MUST have a retry loop; without one you have swapped a data bug for an error-rate bug.

Chapter 40

MVCC: how readers avoid blocking writers

Instead of locking on read, the database keeps multiple versions of each row and shows each transaction the version that was current when it started. Readers never block writers, and writers never block readers.

This is the central design decision of Postgres, InnoDB and Oracle, and it is why a long analytical query does not freeze your write traffic. An UPDATE does not overwrite — it writes a new version and marks the old one as ending. Every transaction has a snapshot that determines which versions it can see.

MVCC
Every row carries hidden columns (Postgres):
  xmin — the transaction that created this version
  xmax — the transaction that deleted/superseded it

UPDATE users SET name='B' WHERE id=1;

  version 1: name='A'  xmin=100  xmax=205    ← still visible to older snapshots
  version 2: name='B'  xmin=205  xmax=null   ← visible to newer ones

A transaction sees a version if:
  xmin committed before my snapshot   AND
  xmax is null, or did not commit before my snapshot

-- The cost: dead versions accumulate. That is what VACUUM reclaims.
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),1) AS dead_pct
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

VACUUM (VERBOSE, ANALYZE) orders;   -- reclaim space, refresh stats
-- VACUUM FULL rewrites the table and takes an ACCESS EXCLUSIVE lock.
-- It is not a routine operation. Use pg_repack instead.
Principle

A transaction left open blocks vacuum from reclaiming ANY version newer than its snapshot - across the whole database, not just its own tables. One forgotten idle-in-transaction connection can bloat a database by hundreds of gigabytes. Set idle_in_transaction_session_timeout.

Interview angle

InnoDB does the same thing differently: old versions go to a separate undo log rather than staying in the table. So MySQL does not need VACUUM, but a long transaction grows the undo log instead — the same problem wearing a different hat.

Chapter 41

Locks

Row locks are taken by writes and by explicit FOR UPDATE. Table locks are taken by DDL. Most production lock incidents are a migration queueing behind one long-running read.

SQL
ROW LOCKS
  FOR UPDATE         exclusive; others block. For read-modify-write.
  FOR NO KEY UPDATE  weaker; allows concurrent FK checks
  FOR SHARE          others may read, not write
  FOR UPDATE NOWAIT  fail immediately instead of waiting
  FOR UPDATE SKIP LOCKED   skip locked rows - the queue-worker idiom

-- Safe job queue: N workers, no double processing, no waiting
UPDATE jobs SET status='running', worker=$1
WHERE id IN (
  SELECT id FROM jobs WHERE status='pending'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED       -- each worker grabs a different row
  LIMIT 10
) RETURNING *;

TABLE LOCKS (Postgres, weakest to strongest)
  ACCESS SHARE        SELECT
  ROW EXCLUSIVE       INSERT/UPDATE/DELETE
  SHARE               CREATE INDEX (non-concurrent) - blocks writes
  ACCESS EXCLUSIVE    ALTER TABLE, DROP, VACUUM FULL - blocks EVERYTHING

-- The lock queue is FIFO and this is what bites people:
  1. a reporting SELECT runs for 10 minutes  (ACCESS SHARE)
  2. ALTER TABLE waits behind it             (wants ACCESS EXCLUSIVE)
  3. every new SELECT now waits behind the ALTER
  → a 5ms migration takes the site down for 10 minutes

SET lock_timeout = '3s';    -- ALWAYS, before any migration
SQL
-- Who is blocking whom, right now
SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query,
       now() - blocking.query_start AS blocking_duration
FROM   pg_stat_activity blocked
JOIN   pg_stat_activity blocking
         ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE  cardinality(pg_blocking_pids(blocked.pid)) > 0;

SELECT pg_cancel_backend(pid);     -- polite
SELECT pg_terminate_backend(pid);  -- forceful
Interview angle

FOR UPDATE SKIP LOCKED is the answer to “how would you build a job queue on Postgres without workers stepping on each other?” — a question that comes up constantly and that most people answer with an application-level lock.

Chapter 42

Deadlocks

Two transactions each hold what the other needs. The database detects the cycle and kills one. The fix is ordering, not retrying — though you need the retry too.

A deadlock is not a database failure; it is the database correctly resolving an impossible situation. It is also almost always caused by the same root: two code paths that acquire the same set of locks in different orders.

Mixed
T1: UPDATE accounts WHERE id=1;   -- holds lock on 1
T2: UPDATE accounts WHERE id=2;   -- holds lock on 2
T1: UPDATE accounts WHERE id=2;   -- waits for T2
T2: UPDATE accounts WHERE id=1;   -- waits for T1  → deadlock

-- FIX: always acquire in a consistent order
BEGIN;
  SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
  -- every transaction now locks the lower id first. Cycle impossible.
  UPDATE ...; UPDATE ...;
COMMIT;

-- Retry loop for the ones you cannot prevent
for attempt in range(3):
    try:
        with db.transaction():
            do_work()
        break
    except (DeadlockDetected, SerializationFailure):
        sleep(0.05 * 2**attempt + random.random()*0.05)   # jittered backoff
else:
    raise

-- Diagnose: Postgres logs both queries and the cycle
log_lock_waits = on
deadlock_timeout = '1s'
SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();
Preventing them
  • Lock rows in a deterministic order — sort by primary key before updating a set.
  • Keep transactions short; a shorter hold time is a smaller collision window.
  • Touch tables in the same order across every code path.
  • Use a single atomic statement where you can — it takes its locks in one step.
  • Retry with jittered backoff, because you cannot prevent all of them.
Chapter 43

Optimistic vs pessimistic concurrency

Pessimistic locks first and assumes conflict. Optimistic checks at write time and assumes conflict is rare. Contention rate decides which is right, and long user-facing edits force optimistic.

If two users edit the same record twice a day, locking a row for the duration of a form being filled in is absurd — the lock would be held for minutes. If forty workers fight over the same counter, optimistic retries turn into a livelock. The choice is set by how often conflicts actually happen.

SQL
-- PESSIMISTIC: take the lock, do the work, release
BEGIN;
  SELECT stock FROM inventory WHERE id=7 FOR UPDATE;  -- others wait here
  -- ... check and decide ...
  UPDATE inventory SET stock = stock - 1 WHERE id=7;
COMMIT;
+ no wasted work, no retries, simple to reason about
- holds a lock; unusable across a user's think-time; deadlock risk

-- OPTIMISTIC: version column, check on write
SELECT id, stock, version FROM inventory WHERE id=7;   -- version 12
-- ... user thinks for two minutes, no lock held ...
UPDATE inventory SET stock = 4, version = 13
WHERE  id = 7 AND version = 12;
-- 0 rows affected → someone else changed it. Reload and merge.
+ no locks, works across HTTP requests, scales with low contention
- wasted work on conflict; needs a retry or merge path in the UI

-- HTTP's version of exactly this:
--   ETag / If-Match → 412 Precondition Failed
Principle

Prefer a single atomic statement over both. UPDATE inventory SET stock = stock - 1 WHERE id=7 AND stock > 0 needs no lock and no version column - the check and the write are one operation, and zero rows affected means it was out of stock.

Chapter 44

Long transactions and idle connections

An open transaction holds locks, pins a snapshot, and blocks vacuum for the whole database. Idle-in-transaction is one of the most damaging states a connection can be in.

The damage is disproportionate. A connection sitting in idle in transaction for an hour prevents vacuum from cleaning any row version newer than its snapshot — everywhere, not just in tables it touched. Tables it has never read grow bloated, plans degrade, and disk fills.

SQL
-- Find them
SELECT pid, state, now() - state_change AS idle_for, left(query, 60)
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
ORDER  BY idle_for DESC;

-- Prevent them, at the server
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET statement_timeout = '30s';           -- per statement
ALTER SYSTEM SET lock_timeout = '5s';

-- Per-role is often better than global
ALTER ROLE reporting SET statement_timeout = '10min';
ALTER ROLE web       SET statement_timeout = '15s';

-- The transaction age that matters for bloat
SELECT max(age(backend_xmin)) FROM pg_stat_activity;
Where long transactions come from
  • Opening a transaction before doing HTTP work, then committing after.
  • An ORM that begins a transaction on first query and holds it for the whole web request.
  • A worker that BEGINs, then blocks on a queue read.
  • A psql session someone left open after a BEGIN, over lunch.
Interview angle

“Our database keeps growing but the row count is flat” is a bloat question, and the answer is nearly always a long-running or idle-in-transaction session preventing vacuum. It is worth being able to name the diagnostic query.

VII. Durability and recovery
Chapter 45

The write-ahead log

Write the intention to a sequential log and fsync that, before touching the data pages. One sequential write buys durability for many scattered page changes.

A commit that had to flush every modified data page would perform random writes across the disk and be unusably slow. Instead the database appends a compact description of the change to a log, fsyncs that one file, and returns. The data pages are updated in memory and written out later, lazily. If the machine dies, the log is replayed.

WAL
COMMIT does this:
  1. append change records to the WAL buffer
  2. fsync the WAL to durable storage        ← the only synchronous wait
  3. return success to the client
     (data pages are still dirty in memory - that is fine)

CRASH RECOVERY on restart:
  find the last checkpoint
  replay every WAL record after it   (REDO)
  roll back transactions with no COMMIT record  (UNDO)
  → back to a consistent state, with every committed transaction present

Why this is fast:
  one sequential append + one fsync
  vs
  dozens of random 8KB page writes scattered across the disk

WAL also powers, for free:
  streaming replication  (ship the log to a replica and replay it)
  point-in-time recovery (replay the log to a chosen moment)
  logical decoding / CDC (turn the log into a change stream)
Interview angle

“Why is a database faster than writing to files if it writes MORE data?” — because it converts many random writes into one sequential write plus one fsync. Sequential I/O is orders of magnitude faster, and only the log has to be durable at commit time.

Chapter 46

Checkpoints, fsync, and the durability dial

Durability is a spectrum with a real throughput price. Knowing exactly what each setting gives up is more useful than knowing the safe default.

The WAL cannot grow forever, so periodically a checkpoint flushes all dirty pages to disk and records that everything before this point is safely applied. Checkpoints are I/O spikes; spreading them out is most of what checkpoint tuning is.

Config
-- The durability dial (Postgres)
synchronous_commit = on        -- default. fsync WAL before ack. Safe.
synchronous_commit = off       -- ack before fsync.
                               -- Risk: lose the last ~0.2s of COMMITTED
                               -- transactions on a crash. NOT corruption.
                               -- Often a legitimate choice for logs/analytics.
fsync = off                    -- NEVER in production. Corruption on crash.

-- Per-transaction, which is the useful form:
BEGIN;
  SET LOCAL synchronous_commit = off;   -- this analytics write can be lossy
  INSERT INTO page_views ...;
COMMIT;

-- Checkpoint tuning: smooth the spikes
max_wal_size = '8GB'              -- checkpoint less often
checkpoint_timeout = '15min'
checkpoint_completion_target = 0.9  -- spread writes over 90% of the interval

-- Are checkpoints being forced by WAL volume rather than time?
SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;
-- checkpoints_req climbing → raise max_wal_size
Principle

synchronous_commit = off loses recent transactions on a crash but never corrupts the database. fsync = off can corrupt it beyond repair. These are frequently confused, and the difference is the whole point.

Chapter 47

Backups: logical and physical

Logical backups are portable SQL and restore slowly. Physical backups are byte copies that restore fast and enable point-in-time recovery. Production needs physical; logical is for moving data around.

Backups
LOGICAL  (pg_dump / mysqldump)
  + portable across versions and platforms; selective (one table)
  + human-readable, diffable
  - slow to take and much slower to restore (replays every INSERT,
    rebuilds every index) - hours for a large database
  - snapshot only; no point-in-time recovery

pg_dump -Fc -j4 mydb > db.dump        # custom format, parallel
pg_restore -j8 -d mydb db.dump        # parallel restore

PHYSICAL  (pg_basebackup, WAL archiving, pgBackRest)
  + fast restore - it is a file copy
  + enables PITR when combined with archived WAL
  - same major version and architecture only
  - all or nothing; you cannot restore one table

pg_basebackup -D /backup/base -Ft -z -P --wal-method=stream

-- Continuous archiving: the basis of PITR
archive_mode = on
archive_command = 'pgbackrest archive-push %p'

What production actually runs:
  nightly physical base backup
  + continuous WAL archiving to object storage
  + a periodic logical dump for portability and selective restore
Principle

A backup you have never restored is a hypothesis, not a backup. Restore drills belong on a schedule, and the number you must actually know is how long a full restore takes - because that is your real recovery time, not the number in the policy document.

Chapter 48

Point-in-time recovery

A base backup plus every WAL segment since means you can restore to any second in between — including the second before someone ran DELETE without a WHERE.

This is the capability that turns backups from we lost a day into we lost four minutes. Because the WAL is a complete ordered record of every change, replaying it from a base backup and stopping at a chosen moment reconstructs the database exactly as it was then.

Recovery
-- Setup (before you need it)
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

-- Recovery: restore the base backup, then tell it where to stop
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-30 14:22:59+05:30'
recovery_target_action = 'promote'

-- Stop just before a known bad transaction instead:
recovery_target_xid  = '918273645'
recovery_target_lsn  = '0/9A3F2C10'
recovery_target_name = 'before_the_migration'   -- from pg_create_restore_point

-- Mark a safe point before anything risky
SELECT pg_create_restore_point('before_the_migration');

RPO = how much data you can lose  → set by WAL archive frequency
RTO = how long recovery takes     → set by base backup size + WAL volume

-- Faster real-world recovery for the DELETE-without-WHERE case:
-- restore to a SEPARATE instance at the target time, export the
-- affected rows, import them into the live database. No downtime,
-- no losing everything that happened since the mistake.
Interview angle

The last block is the answer worth giving. “Restore the whole database to 10 minutes ago” also discards ten minutes of legitimate traffic. Restoring aside and copying back only the damaged rows is what an experienced operator actually does.

Chapter 49

Testing that you can actually restore

The failure mode is not a missing backup — it is a backup that has been silently failing for six weeks, or one that restores into a database missing an extension nobody documented.

The drill
  • Automate a restore into a scratch instance on a schedule — weekly if you can, monthly at worst.
  • Verify the restored data, not just that the process exited zero. Check row counts on key tables and run a real query.
  • Time it. The measured duration is your true RTO; the number in the runbook is aspirational.
  • Alert on backup age, not on backup failure. A job that stops running fires no failure alert at all.
  • Rehearse the human part — who has credentials, where the archive lives, what the runbook says. Discover this before the incident.
bash
#!/bin/bash
set -euo pipefail

START=$(date +%s)
pgbackrest --stanza=main --delta restore --pg1-path=/tmp/verify
pg_ctl -D /tmp/verify -o "-p 5433" start

# does it actually contain data?
for t in users orders payments; do
  n=$(psql -p 5433 -tAc "SELECT count(*) FROM $t")
  echo "$t: $n rows"
  [ "$n" -gt 0 ] || { echo "FAIL: $t is empty"; exit 1; }
done

# does a real query work?
psql -p 5433 -c "SELECT * FROM orders ORDER BY placed_at DESC LIMIT 1" >/dev/null

echo "restore verified in $(( $(date +%s) - START ))s"
pg_ctl -D /tmp/verify stop && rm -rf /tmp/verify

# Alert on AGE, so silence is not mistaken for success:
#   backup_age_hours > 26  → page
Principle

Alert on the age of the newest successful backup, never on the failure of the backup job. A job that has crashed on startup for a month emits no failures at all - and that is the exact scenario in which you discover you have no backups.

VIII. Scaling
Chapter 50

Connection pooling

Each Postgres connection is an OS process costing several megabytes. Five hundred app threads opening five hundred connections is how a database falls over while the CPU sits idle.

Connections are expensive and, past a point, actively harmful — more concurrent queries than cores means context switching and lock contention, and throughput falls as you add connections. The pool exists to keep the number of active database connections near the sweet spot regardless of how many application threads exist.

Config
-- Rough sizing: start here, then measure
pool_size ≈ (cpu_cores × 2) + effective_spindles
-- A 16-core box: 30-40 connections, not 500.

-- PgBouncer pooling modes
session      one client = one server connection for its whole session
             (safe, least pooling benefit)
transaction  server connection returned after each COMMIT   ← the useful one
             1000 clients → 25 server connections
statement    returned after each statement; breaks transactions

# pgbouncer.ini
[databases]
app = host=10.0.0.5 dbname=app
[pgbouncer]
pool_mode = transaction
max_client_conn = 5000     # what the app sees
default_pool_size = 25     # what Postgres sees

-- Transaction mode breaks anything tied to a session:
--   session-level SET, LISTEN/NOTIFY, WITH HOLD cursors,
--   server-side prepared statements (unless the driver handles it)

-- Check where you are
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;
Principle

A smaller pool is usually faster than a larger one. Queueing 200 requests for 30 fast connections beats running 200 concurrent queries that thrash cache and fight over locks - the queue is visible and bounded, the thrash is neither.

Chapter 51

Replication and replica lag

Ship the WAL to another server and replay it. Asynchronous is fast and can lose recent commits on failover; synchronous is durable and makes every write wait for a second machine.

SQL
ASYNCHRONOUS (default)
  primary commits → acks client → ships WAL when it can
  + no write latency penalty
  - replica lags (ms to minutes under load)
  - failover can lose the last N transactions

SYNCHRONOUS
  primary commits → waits for replica ack → acks client
  + no committed data lost on failover
  - every write pays a network round trip
  - if the sync replica dies, WRITES STALL until it returns or is
    reconfigured. This surprises people during an incident.

synchronous_standby_names = 'ANY 1 (rep1, rep2)'  -- any one of two: safer
synchronous_commit = remote_apply   -- strongest: replayed, not just received

-- Measure lag - in bytes AND seconds; they mean different things
SELECT client_addr, state, sent_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- On the replica
SELECT now() - pg_last_xact_replay_timestamp() AS lag_seconds;

Physical replication ships WAL bytes: an exact copy, whole cluster, same version. Logical replication ships decoded row changes: selective tables, cross-version, and the basis for CDC into Kafka or a warehouse. Physical for high availability, logical for integration.

Interview angle

“What happens if the synchronous replica goes down?” — writes block. A candidate who knows that, and knows ANY 1 (a, b) quorum is the mitigation, has run this in production.

Chapter 52

Read replicas and read-your-writes

Read replicas scale reads and introduce a new bug class: a user writes, immediately reads from a lagging replica, and their own change is missing.

This is the most common production surprise when replicas are introduced, and it looks like a caching bug or a phantom data-loss report. The user updates their profile, the write goes to the primary, the redirect reads from a replica that is 200ms behind, and the page shows the old name. Nothing is broken; the architecture simply does not promise what the UI assumes.

Patterns
-- Strategy 1: read from the primary after a write (per user, per window)
after a write, pin this user's reads to the primary for ~5s
+ simple, correct   - loses some replica benefit for active users

-- Strategy 2: route by operation, accept staleness where it is fine
primary : checkout, profile edit, anything the user just changed
replica : search, browse, analytics, recommendations, reports

-- Strategy 3: LSN token (correct and precise)
-- after writing on the primary:
SELECT pg_current_wal_lsn();          -- '0/9A3F2C10' → store in session
-- before reading on a replica:
SELECT pg_last_wal_replay_lsn() >= '0/9A3F2C10';   -- caught up?
-- if not, fall back to the primary

-- Strategy 4: let the replica wait (MySQL)
SELECT MASTER_POS_WAIT('binlog.000042', 8891, 1);

-- ALWAYS: alert on lag before users notice
lag > 10s  → warn        lag > 60s → page and stop routing reads there
Principle

Decide per query, not per application, which reads may be stale and by how much. "Everything reads from replicas" produces mysterious bugs; "everything reads from the primary" wastes the replicas entirely.

Chapter 53

Failover and high availability

Automatic failover trades one failure mode for another: you stop losing time to manual promotion and start risking split-brain, where two nodes both believe they are primary.

HA
THE COMPONENTS
  leader election   Patroni + etcd/consul, or a managed service
  fencing / STONITH ensure the old primary CANNOT accept writes
  connection routing a VIP, HAProxy, or DNS the clients follow

SPLIT-BRAIN
  the network partitions; the old primary is alive and reachable by
  some clients; a new one is promoted. Both take writes. The two
  histories diverge and cannot be merged automatically.
  → prevented by quorum + fencing, never by hoping

FAILOVER CHECKLIST
  [ ] quorum-based election (an odd number of nodes)
  [ ] the old primary is fenced before the new one is promoted
  [ ] clients follow the new primary without a redeploy
  [ ] the connection pooler is reconfigured or restarted
  [ ] replication slots survive, so replicas do not need rebuilding
  [ ] you have practised it deliberately, in daylight

-- After async failover, the old primary may have transactions the new
-- one never received. Rejoining it needs pg_rewind, and those
-- transactions are gone. That is the cost of async, made concrete.
Principle

Failover you have not rehearsed is not high availability, it is an untested script that runs for the first time during your worst hour. Schedule a deliberate failover in business hours, at least quarterly.

Chapter 54

Partitioning

One logical table split into physical pieces by a key. The two real wins are pruning — scanning only relevant partitions — and dropping a month of data instantly instead of deleting it.

Partitioning is often sold as a performance feature and its most reliable benefit is operational. DROP TABLE events_2025_01 is instant and reclaims all its space; DELETE FROM events WHERE created_at < ... on the same rows generates enormous WAL, bloats the table, and leaves the space to vacuum.

SQL
-- Range partitioning by time - by far the most common
CREATE TABLE events (
  id bigserial, created_at timestamptz NOT NULL, payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- Pruning: only one partition is touched
EXPLAIN SELECT * FROM events WHERE created_at >= '2026-08-15';
--  → Seq Scan on events_2026_08   (the others are never opened)

-- Retention becomes instant instead of catastrophic
DROP TABLE events_2025_08;                    -- milliseconds
ALTER TABLE events DETACH PARTITION events_2025_08;  -- keep it, unlink it

-- LIST and HASH also exist
PARTITION BY LIST (region)     -- 'in', 'eu', 'us'
PARTITION BY HASH (tenant_id)  -- even spread, no natural ranges

CONSTRAINTS
  the partition key MUST be in every PRIMARY KEY and UNIQUE constraint
  → you cannot have a globally unique email on a table partitioned by date
  cross-partition queries touch every partition; plan for pruning
  hundreds of partitions slow planning; keep it in the tens, not thousands
Interview angle

Partitioning does not make a point lookup on an indexed column faster — a B-tree was already three or four levels deep. It helps range scans that prune, and it transforms bulk deletion. Claiming it speeds up everything is a common overreach.

Chapter 55

Sharding

Splitting data across independent databases. The last resort, because it costs you cross-shard joins, distributed transactions, and global uniqueness — permanently.

Partitioning splits a table within one database; sharding splits it across many, each with its own primary. That single difference removes almost everything SQL was good at. Before sharding, exhaust vertical scaling, read replicas, caching, archiving cold data, and query optimisation — a well-tuned single Postgres instance handles far more than most teams assume.

Sharding
CHOOSING THE SHARD KEY - the decision you cannot undo
  tenant_id / customer_id   most queries stay in one shard  ← usually right
  user_id                   good if data is user-scoped
  hash(id)                  even spread, but every range query fans out
  geography                 good for latency and data residency

WHAT YOU GIVE UP
  cross-shard JOIN          → fan out and join in the application
  global UNIQUE             → UUIDs, or a central allocator
  cross-shard transaction   → sagas, or 2PC and its blocking failure mode
  AUTO_INCREMENT            → per-shard ranges, snowflake ids, or UUIDv7
  rebalancing               → the genuinely hard part

ROUTING
  directory : a lookup table of key → shard. Flexible, one more hop,
              and a component that must not go down.
  hash mod N: simple, and resharding moves nearly everything.
  consistent hashing / vnodes: resharding moves ~1/N. Worth the setup.

HOT SHARD: one tenant is 40% of traffic. Plan for it - a shard per
  large tenant, or a split key - because it WILL happen.
Principle

Pick a shard key so that the overwhelming majority of queries touch exactly one shard. If common queries must fan out to all shards, you have built a slower distributed database, not a faster one.

Chapter 56

Caching in front of the database

A cache is a second copy of your data with no consistency guarantee. Every caching design is really an invalidation design, and invalidation is where they fail.

Caching
CACHE-ASIDE (lazy) - the default
  read : check cache → miss → read DB → write cache → return
  write: write DB → DELETE the cache key   (delete, do not update)
  + simple, only caches what is used
  - first read is slow; a stale window exists between the two steps

WRITE-THROUGH   write DB and cache together. Consistent, slower writes.
WRITE-BEHIND    write cache, flush to DB later. Fast, can lose data.

-- Delete rather than update on write: two concurrent writers updating
-- the cache can interleave and leave the older value behind.

THE THREE FAILURE MODES
  stampede   key expires under load → 5000 requests hit the DB at once
             → single-flight lock, or probabilistic early expiry
  penetration a key that does not exist is requested repeatedly
             → cache the negative result, briefly
  avalanche  many keys expire simultaneously
             → jitter every TTL: ttl = base + random(0, base*0.1)

-- Single-flight: only one request rebuilds
if not (val := cache.get(k)):
    if cache.set(f"lock:{k}", 1, nx=True, ex=5):
        val = db.query(...); cache.set(k, val, ex=300 + random()*30)
    else:
        sleep(0.05); val = cache.get(k)      # someone else is rebuilding

WHAT NOT TO CACHE
  anything where staleness is a correctness bug - balances, stock counts,
  permissions. Cache the expensive read-mostly things instead.
Principle

Always set a TTL, even when you invalidate explicitly. The TTL is your backstop for every invalidation you will forget to write - and you will forget one. A cache entry with no expiry is a permanent bug waiting for a deploy.

IX. Beyond relational
Chapter 57

What NoSQL actually gave up

NoSQL databases bought horizontal scale and flexible schemas by giving up joins, multi-key transactions, and ad-hoc queries. Knowing which trade you are making is the whole skill.

The name is unhelpful — these systems differ from each other more than they differ from SQL. What unites them is a set of deliberate sacrifices made to scale writes across many machines. A join is hard to distribute. A transaction across arbitrary keys requires coordination. An ad-hoc query needs a planner and statistics. Drop all three and horizontal scaling becomes far easier.

The families
  • Key-value — Redis, DynamoDB. Get and put by key. Fastest, least queryable.
  • Document — MongoDB, Couchbase. JSON documents, queryable inside, no joins across collections.
  • Wide-column — Cassandra, HBase, ScyllaDB. Huge write throughput, query patterns fixed at design time.
  • Graph — Neo4j. Relationships as first-class; multi-hop traversal without exponential join cost.
  • Time-series — TimescaleDB, InfluxDB. Append-heavy, time-ordered, aggressive compression.
  • Search — Elasticsearch, OpenSearch. Inverted index, relevance ranking, not a system of record.
Interview angle

The honest modern answer to “SQL or NoSQL?” is that Postgres absorbed most of the reasons to leave. JSONB with GIN covers document use cases, arrays and ranges cover many others, and logical replication feeds a search index. Start relational; move a specific workload out when it earns it.

Chapter 58

Key-value stores

A hash map with durability and a network. Astonishingly fast for get-by-key, and useless for any question you did not build a key for.

KV
REDIS - in-memory, single-threaded for commands (so operations are atomic)
  strings, hashes, lists, sets, sorted sets, streams, HyperLogLog
  used for: cache, session store, rate limiter, leaderboard, queue,
            distributed lock, pub/sub

  persistence: RDB snapshots (periodic, can lose minutes)
               AOF append-only log (fsync per second or per write)
  → Redis IS durable if configured; the default is weaker than a database

-- Rate limiting: atomic because Redis is single-threaded
INCR ratelimit:user:42
EXPIRE ratelimit:user:42 60 NX

-- Leaderboard: a sorted set does this natively
ZADD scores 4820 'jatin'
ZREVRANGE scores 0 9 WITHSCORES
ZREVRANK scores 'jatin'

DYNAMODB - managed, partitioned by key, single-digit ms at any scale
  partition key (+ optional sort key) determines placement
  query by key or key-prefix ONLY; a scan reads the whole table
  single-table design: model access patterns first, then the schema
  → the exact inverse of relational design, and it is a real inversion,
    not a stylistic one

HOT KEY is the failure mode: one key on one partition takes all the
traffic. No amount of nodes helps. Shard the key itself.
Principle

In a key-value store you design the keys around the queries, because there is no planner to rescue a query you did not plan for. Adding a new access pattern later usually means a backfill, not an index.

Chapter 59

Document databases

Store a whole object as one document. Great when the document is the natural unit of read and write; painful the moment you need to relate documents to each other.

The pitch is that an aggregate — an order with its line items, addresses and status history — is fetched and written as one thing, so no join is needed and the shape matches the object in your code. That is genuinely valuable when it holds. It stops holding when two documents need to reference each other and stay consistent.

Document
// One document, no join needed
{ _id: ObjectId("..."),
  customer: { id: 42, name: "Jatin", city: "Pune" },   // embedded copy
  items: [ { sku: "A1", qty: 2, price_cents: 49900 } ],
  total_cents: 99800,
  placed_at: ISODate("2026-08-30T09:00:00Z") }

db.orders.find({ "customer.city": "Pune", total_cents: { $gt: 50000 } })
db.orders.createIndex({ "customer.city": 1, total_cents: -1 })

EMBED when: the child is owned by the parent, read together, bounded
            in size, and rarely changes independently
REFERENCE when: many-to-many, unbounded growth, or shared between parents

THE COSTS
  denormalised copies must be updated everywhere (customer.name above)
  no joins across collections ($lookup exists and does not scale like one)
  16MB document limit; an unbounded embedded array will hit it
  multi-document transactions exist in modern MongoDB but cost more
    than a relational one and are not the intended shape

-- Postgres does this too, with constraints and joins still available:
CREATE TABLE orders (id bigserial PRIMARY KEY, doc jsonb NOT NULL);
CREATE INDEX ON orders USING gin (doc jsonb_path_ops);
SELECT * FROM orders WHERE doc @> '{"customer":{"city":"Pune"}}';
Interview angle

“Schemaless” means the schema moved from the database into every piece of code that reads the data — including the code written two years ago by someone who left. It is a real trade, not a free win.

Chapter 60

Wide-column stores

Cassandra and its relatives take enormous write volume across many nodes, with no single point of failure — in exchange for fixing every query pattern at table-design time.

CQL
THE MODEL
  partition key   → which node holds the data (and it is ALL on that node)
  clustering keys → sort order WITHIN the partition
  query: you must supply the full partition key. Always.

CREATE TABLE messages_by_room (
  room_id  uuid,
  sent_at  timestamp,
  msg_id   uuid,
  body     text,
  PRIMARY KEY ((room_id), sent_at, msg_id)
) WITH CLUSTERING ORDER BY (sent_at DESC);

SELECT * FROM messages_by_room WHERE room_id = ? LIMIT 50;   -- fast
SELECT * FROM messages_by_room WHERE sent_at > ?;            -- REFUSED

-- Want a second access pattern? Write a second table.
CREATE TABLE messages_by_user (...);   -- same data, different key
-- Denormalisation is the DESIGN here, not a compromise.

WHY WRITES ARE SO FAST
  LSM tree: append to a commit log + an in-memory memtable, done.
  Flush to immutable SSTables later; compact them in the background.
  No read-modify-write on the write path at all.
  Cost: reads may check several SSTables (bloom filters help), and
        compaction consumes I/O continuously.

TUNABLE CONSISTENCY, per query
  R + W > N  → strong consistency
  N=3, W=QUORUM(2), R=QUORUM(2) → 2+2 > 3 ✓
  N=3, W=ONE, R=ONE → fast, may read stale

AVOID: large partitions (>100MB), unbounded partition growth,
       and tombstone accumulation from heavy deletes.
Principle

In Cassandra you design one table per query. If you find yourself wanting an ad-hoc filter or a join, the workload is relational and you are using the wrong tool - not using this one badly.

Chapter 61

Graph, time-series, and search

Three specialists. Each wins decisively on its shape and loses badly outside it, which makes them easy to place correctly.

Specialists
GRAPH  (Neo4j, and Postgres recursive CTEs for modest depth)
  Wins when relationships are the data and traversal is deep:
  fraud rings, social degrees, dependency chains, recommendations.
  "Friends of friends of friends" is 3 self-joins in SQL - each
  multiplying rows - and one traversal in a graph database.

  MATCH (a:User {id:42})-[:FOLLOWS*2..3]->(b:User)
  WHERE NOT (a)-[:FOLLOWS]->(b)
  RETURN b.name, count(*) AS mutuals ORDER BY mutuals DESC LIMIT 10

  Below ~3 hops on moderate data, a recursive CTE in Postgres is fine.

TIME-SERIES  (TimescaleDB, InfluxDB, Prometheus)
  Append-only, time-ordered, queried by range and aggregate,
  and old data is compressed or downsampled rather than deleted.

  SELECT time_bucket('5 minutes', ts) AS bucket, avg(cpu)
  FROM metrics WHERE ts > now() - interval '6 hours' GROUP BY bucket;

  10-20x compression is normal, because adjacent values are similar.
  TimescaleDB is a Postgres extension - you keep SQL, joins and
  constraints, which is often the deciding factor.

SEARCH  (Elasticsearch, OpenSearch)
  Inverted index + relevance ranking + analyzers (stemming, synonyms,
  fuzzy matching, facets). Postgres full-text handles a lot; ES wins
  on relevance tuning, aggregations over text, and scale.

  NEVER the system of record. Feed it from your database via CDC and
  be able to rebuild the whole index from scratch - because one day
  you will have to.
Principle

Every specialist store you add is another thing to back up, monitor, secure, upgrade and keep in sync. Add one when a workload is genuinely failing on the primary database, not because it is the theoretically correct tool.

Chapter 62

CAP, PACELC, and choosing

CAP says that during a network partition you must choose availability or consistency. It is narrower than usually quoted — PACELC adds the case that actually applies most of the time.

CAP is routinely misused to claim a system is AP or CP as a permanent identity. The theorem is only about behaviour during a partition. Partitions are rare; the latency-versus-consistency trade is continuous, which is why PACELC is the more useful framing in practice.

Theory
CAP - only during a network partition, you pick one:
  CP  refuse requests rather than serve possibly-stale data
      → Postgres with sync replication, HBase, etcd, Zookeeper
  AP  keep serving, reconcile later
      → Cassandra, DynamoDB (default), Riak
  CA  not achievable in a distributed system - networks partition.

PACELC - the complete statement:
  if Partition:  choose Availability or Consistency
  Else:          choose Latency or Consistency

  Postgres (sync)  PC/EC   consistent always, pays latency
  DynamoDB         PA/EL   available and fast, eventually consistent
                           (strongly-consistent reads cost 2x and are slower)
  Cassandra        PA/EL   tunable per query
  MongoDB          PA/EC   configurable via write/read concern

The Else branch is the one you live in every day. Partitions are rare;
the latency-vs-consistency decision is made on every single request.

A decision procedure

Ask in this order
  • Does a stale read cause a real problem? Money, stock, permissions → strong consistency. Feeds, counts, recommendations → eventual is fine.
  • Do you know the queries in advance? No → relational. Yes and unchanging → key-value or wide-column is viable.
  • Are relationships the point? Yes and deep → graph. Yes and shallow → SQL joins.
  • Is write volume beyond one machine? Genuinely, after tuning → wide-column or sharding. Assumed → almost certainly not.
  • Default — Postgres, plus a cache, plus a search index fed by CDC. This is the right answer far more often than the interesting one.
X. Operating and interviews
Chapter 63

What to monitor

Four signals catch most incidents: replication lag, connection saturation, the longest-running transaction, and cache hit ratio. Everything else is diagnosis after the page.

SQL
-- Cache hit ratio (want > 99% on OLTP; below 95% means undersized memory)
SELECT round(100.0*sum(blks_hit)/nullif(sum(blks_hit+blks_read),0), 2)
FROM pg_stat_database;

-- Connection saturation - the fastest route to a hard outage
SELECT count(*)::float / current_setting('max_connections')::float
FROM pg_stat_activity;

-- Longest transaction - predicts bloat and lock storms
SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state <> 'idle';

-- Replication lag
SELECT client_addr, pg_wal_lsn_diff(sent_lsn, replay_lsn) FROM pg_stat_replication;

-- Bloat / dead tuples
SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- Transaction ID wraparound - rare, and catastrophic if ignored
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
-- above ~1.5 billion: act now. At 2 billion the database SHUTS DOWN.

-- Unused and duplicate indexes, sequence exhaustion, disk free
-- ...all worth a dashboard, none worth a page at 3am.
Alert tiers
  • Page — disk above 85%, connections above 90%, replication lag above 60s, wraparound age above 1.5B, primary unreachable.
  • Warn — cache hit below 95%, longest transaction above 5 min, dead tuples above 20%, p99 query latency doubling.
  • Dashboard only — QPS, index sizes, table growth, autovacuum activity.
Chapter 64

Postgres and MySQL, where they actually differ

Both are excellent and the choice rarely decides a project. But a handful of real differences will surprise you when you move between them.

Comparison
                    POSTGRES                MYSQL (InnoDB)
row storage         heap + separate indexes clustered on primary key
old row versions    in the table (VACUUM)   undo log (no VACUUM)
default isolation   READ COMMITTED          REPEATABLE READ
REPEATABLE READ     no phantoms (snapshot)  gap locks; locking reads differ
DDL transactional   YES - roll back a       NO - implicit commit;
                    migration mid-way       a failed migration leaves
                                            the schema half-changed
index types         B-tree, GIN, GiST,      B-tree, full-text, spatial
                    BRIN, SP-GiST, hash
JSON                jsonb, indexable, rich  JSON type, functional indexes
arrays / ranges     native                  no
partial indexes     yes                     no  (a real daily difference)
CTEs / windows      full                    8.0+
replication         WAL (physical/logical)  binlog (statement/row/mixed)
connections         process per conn        thread per conn
                    (needs a pooler sooner)  (lighter)
case sensitivity    strings case-sensitive  collation-dependent, often not

PICK POSTGRES for: correctness-critical data, complex queries, JSON +
  relational together, extensions (PostGIS, TimescaleDB, pgvector),
  and transactional DDL.
PICK MYSQL for: existing expertise, very read-heavy simple workloads,
  ecosystem lock-in, or when a managed offering you trust is MySQL-only.
Interview angle

Transactional DDL is the difference operators feel most. In Postgres a migration that fails halfway rolls back to a known state. In MySQL you are left with a schema that is neither the old one nor the new one, at 2am.

Chapter 65

The anti-pattern list

Everything in this book, compressed into things not to do. Most production database pain is on this page.

Do not
  • No index on a foreign key. Reverse lookups and cascading deletes full-scan the child table. Postgres does not create it for you.
  • SELECT * in application code. Breaks on schema change, drags TOASTed columns, prevents index-only scans.
  • Storing money in a float. Use numeric or integer minor units.
  • timestamp instead of timestamptz. A wall-clock reading with no meaning attached.
  • Deep OFFSET pagination. Use keyset pagination.
  • N+1 queries. Invisible per query, fatal in aggregate.
  • Transactions that make network calls. Someone else's outage becomes your lock contention.
  • EAV tables. (entity, attribute, value) re-implements a database inside a database, badly. Use JSONB.
  • Unbounded IN lists. Ten thousand ids in one query blows up planning time. Batch, or use a temp table.
  • Migrations without lock_timeout. One slow read turns a 5ms change into an outage.
  • Soft deletes everywhere by default. Viral, and one forgotten filter leaks deleted data.
  • No ON DELETE policy on foreign keys. Decide explicitly; the default is a surprise later.
  • VACUUM FULL in production. Takes an exclusive lock on the whole table. Use pg_repack.
  • Indexes added speculatively. Every one taxes every write. Add on evidence, drop on evidence.
  • Trusting application validation alone. The migration script and the manual fix do not run it.
Chapter 66

The database interview drill

Database questions cluster into six shapes. If you can answer these six well, you can answer nearly anything asked in a Software Engineer loop.

The six shapes
  • “This query is slow. Debug it.” — EXPLAIN ANALYZE, compare estimated vs actual rows, look for Rows Removed by Filter and high loops, check for a non-sargable predicate or a missing leftmost prefix, then run ANALYZE. Say measure first out loud.
  • “Design the schema for X.” — entities, relationships, keys, then the indexes justified by the access patterns you name. Mention constraints. Mention what you would denormalise and what keeps the copy correct.
  • “Two users do this at the same time.” — name the anomaly (lost update, write skew), state the isolation level, then give the three fixes: one atomic statement, FOR UPDATE, or a version column with a retry.
  • “How would you scale this?” — in order: indexes and query fixes, connection pooling, caching, read replicas, partitioning, and only then sharding. Jumping to sharding is the classic misstep.
  • “SQL or NoSQL?” — ask about access patterns, consistency requirements, and write volume before answering. Then default to Postgres and justify any deviation.
  • “Migrate this table with no downtime.” — expand, backfill in batches, switch, contract. Name lock_timeout and CREATE INDEX CONCURRENTLY.
Drill
THINGS TO SAY THAT SIGNAL EXPERIENCE
  "Let me look at the plan before I guess."
  "What is the read/write ratio, and what is the p99 target?"
  "Is a stale read a correctness problem here, or just cosmetic?"
  "That copy needs something keeping it correct - here is what."
  "I would measure total time in pg_stat_statements, not the slowest query."
  "How much data, and how fast is it growing?"

THINGS THAT COST YOU
  reaching for sharding before indexing
  "we'll just cache it" with no invalidation story
  reciting ACID without being able to describe write skew
  claiming an index makes everything faster
  ignoring the write cost of the six indexes you just proposed

THE ONE-LINE ANSWERS WORTH HAVING READY
  index         → B+tree, high fanout, sorted leaves, ~4 levels
  ACID          → isolation is the expensive, configurable one
  MVCC          → versions not locks; readers never block writers
  WAL           → sequential write + one fsync buys durability
  normalise     → store every fact once
  when to shard → after indexes, pooling, cache, replicas, partitioning
Interview angle

The strongest single habit in a database interview is asking about access patterns and data volume before proposing anything. Every real answer depends on them, and volunteering the question is what separates someone who has operated a database from someone who has read about one.

66 chapters · Databases Ground Up

Back to the ledger →

Made By Jatin Bhargava · 2026