PostgreSQL From First Principles

A Causal Reasoning Guide for Engineers Who Know Just Enough to Be Dangerous

Who this is for: You know how to call getAll(), findOne(), create(), update(). You have a working app. But when something is slow, you guess. When something breaks under load, you are surprised. This book ends that. After reading it you will be able to look at any query or schema and predict what it will do — before running it.

How to read this book: Every chapter is built around causal chains. The format is always: "If X → Y happens → because Z." Learn the chains, not the facts. Facts you can Google. Causal reasoning is what makes you dangerous at a whiteboard, in a code review, and at 2am when production is down.

The one rule: Never accept "it's slow" as a diagnosis. Always ask: what is the machine physically doing? That question, asked honestly every time, is the entire skill this book teaches.

TABLE OF CONTENTS

• Part 1 — How PostgreSQL Actually Works (the mental model)
• Part 2 — Schema Design From First Principles
• Part 3 — Indexes From First Principles
• Part 4 — Queries That Destroy Performance
• Part 5 — Transactions, Locks, and Concurrency
• Part 6 — Patterns Every SDE1/SDE2 Must Know
• Part 7 — The Decision Checklists

PART 1: HOW POSTGRESQL ACTUALLY WORKS

Before you write a single query, you need a mental picture of what PostgreSQL is physically doing. Without this picture, everything you learn about indexes, queries, and schema is just trivia you cannot apply.

Chapter 1: Where Your Data Actually Lives

The Physical Reality

Your data is not in "a database." It is in files on disk.

Specifically, PostgreSQL stores every table as a file (called a heap file) made up of fixed-size chunks called pages. Each page is 8 kilobytes. Every row you insert gets packed into one of those 8KB pages. Every row you read requires PostgreSQL to load the page that row lives on.

This is the single most important mechanical fact about PostgreSQL:

You never read a single row. You always read the entire 8KB page that row lives on.

Even if you do SELECT id FROM users WHERE id = 1 — one column, one row — PostgreSQL still loads the entire 8KB page containing that row. The page is the atomic unit of I/O.

Now here is the chain that follows from that:

The Causal Chains

The Naive Mistake

An engineer builds a users table with: id, name, email, password_hash, bio, avatar_url, settings_json, metadata_json, onboarding_state, created_at, updated_at.

The bio averages 500 bytes. The settings_json averages 2KB. The metadata_json averages 1KB.

Each row is now ~4KB on average. That means only 2 rows fit per 8KB page.

A query like SELECT id, name, email FROM users WHERE last_active > now() - interval '7 days' must scan through most of the table. At 100,000 users, that is 50,000 pages. At 500,000 users, that is 250,000 pages.

The exact same query on a lean users table with 80-byte rows would need 1,000 pages at 100,000 users. The wide table is 50x more expensive — not because the query is different, but because of how the rows were packed.

The Right Pattern

Split your table by access frequency, not by concept.

-- Hot table: read on every request, keep it lean
CREATE TABLE users (
  id          bigserial PRIMARY KEY,
  name        text NOT NULL,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Cold table: read rarely, can be wide
CREATE TABLE user_profiles (
  user_id     bigint PRIMARY KEY REFERENCES users(id),
  bio         text,
  settings    jsonb,
  metadata    jsonb
);

Now your users table has ~50 bytes per row. 160 rows per page. A scan of 100,000 users reads 625 pages instead of 50,000. The user_profiles table is wide, but you only join it when you actually need that data.

The Tradeoff Map

• This optimizes for: read performance on the hot table, cache efficiency
• This makes worse: queries that need both tables require a JOIN
• Breaks at scale when: your join volume is so high that the JOIN cost exceeds the I/O savings
• Choose denormalized wide table when: you almost always need all columns together and your table is small enough to fit in cache

The Rule You Keep Forever

Row size determines page count. Page count determines I/O cost. I/O cost determines query speed. Design your tables around row size, not just columns.

Chapter 2: The Buffer Cache — Why "Fast" Queries Are Fast

The Physical Reality

Reading from disk is slow. A spinning disk takes ~5-10ms to seek to a page. Even an NVMe SSD takes ~50-100 microseconds. Reading from RAM takes ~100 nanoseconds. That is a 50,000x to 100,000x difference.

PostgreSQL is not stupid. It does not read the same page from disk every time you query it. It keeps frequently-used pages in RAM in a memory region called the shared buffer cache (configured by shared_buffers, default 128MB, should be ~25% of your RAM in production).

When you run a query:

1. PostgreSQL checks the buffer cache for the needed page
2. Cache hit: page is in RAM → read it in ~100 nanoseconds → fast
3. Cache miss: page is not in RAM → read from disk → load into cache → read → slow

This is why the same query can take 2ms one time and 200ms another time. The first run warmed the cache. The second run after a restart hits cold disk.

The Causal Chains

The Naive Mistake

A developer adds a nightly analytics job that does:

SELECT * FROM events WHERE created_at > now() - interval '90 days';

This table has 50 million rows. The query loads hundreds of thousands of pages into the buffer cache to compute its result. By morning, the buffer cache is full of analytics pages. The first 10 minutes of normal user traffic are slow because every query is a cache miss — the hot pages were evicted by the analytics job.

Nobody notices the cause. They just see "the app is slow in the morning."

The Right Pattern

For analytics queries that touch large amounts of data, use a read replica or run them during off-hours with SET enable_seqscan = off combined with pg_prewarm on your hot tables afterward.

For your main server, the principle is:

-- Instead of SELECT * for analytics, be surgical
SELECT date_trunc('day', created_at), count(*)
FROM events
WHERE created_at > now() - interval '90 days'
GROUP BY 1;

The aggregated query reads the same pages but discards them after counting — less useful than preventing cache pollution, but combined with a read replica is the real solution.

The Tradeoff Map

• Cache size optimizes for: read latency on frequently-accessed data
• Makes worse: nothing, until you run out of RAM
• Breaks at scale when: your dataset grows beyond what RAM can hold
• At that point: you need smarter data access patterns (indexes, partitioning) not just more RAM

The Rule You Keep Forever

Every query either helps or hurts your cache. A query that reads data nobody else needs is not just slow itself — it slows down every query that comes after it.

Chapter 3: How a Query Actually Executes — The Query Planner

The Physical Reality

When you send a query to PostgreSQL, it does not just run it. It first plans it.

The query planner is a piece of software that looks at your query and asks: what is the cheapest way to answer this? It considers:

• Should it read the whole table (sequential scan) or use an index?
• If joining two tables, which table should it scan first?
• Should it sort in memory or on disk?

To make these decisions, the planner uses statistics — data about your tables that PostgreSQL collects automatically. Specifically, it knows the approximate row count and the distribution of values in each column.

The planner does not know the right answer. It estimates. And if its statistics are stale or wrong, it makes a bad plan, and your query is slow.

Reading EXPLAIN

This is the most important diagnostic tool in PostgreSQL. Always run it before asking "why is my query slow?"

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Output:

Seq Scan on orders  (cost=0.00..2540.00 rows=1 width=120)
  Filter: (user_id = 42)

What this tells you:

• Seq Scan → PostgreSQL plans to read every row in the table (bad if table is large)
• cost=0.00..2540.00 → estimated startup cost .. total cost in arbitrary units
• rows=1 → it estimates 1 row will match
• width=120 → each output row is about 120 bytes

Now run EXPLAIN ANALYZE to see what actually happened:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Output:

Seq Scan on orders  (cost=0.00..2540.00 rows=1 width=120)
                    (actual time=0.042..98.3 rows=847 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 127153

Now you see: the planner estimated 1 row but actually got 847. It removed 127,153 rows by filtering during the scan. This is a bad plan — it scanned the entire table to find rows that a simple index could have located directly.

The gap between estimated rows (1) and actual rows (847) tells you: statistics are stale. Run ANALYZE orders; to update them.

The Causal Chains

The Scan Types You Will See

Sequential Scan (Seq Scan): Reads every page of the table, top to bottom. Good when you need most of the rows. Bad when you need few rows from a large table.

Index Scan: Walks the index B-tree to find matching row locations, then fetches each row from the heap. Good for fetching a small number of rows. Each heap fetch is a random I/O.

Index Only Scan: Walks the index and returns data from the index itself without touching the heap at all. Possible when all columns you need are in the index. Fastest possible read.

Bitmap Heap Scan: Collects all matching row locations from the index first, sorts them by page number, then fetches pages in order. Good middle ground when you need a moderate number of rows — turns random I/O into sequential I/O.

The Rule You Keep Forever

EXPLAIN ANALYZE is not optional when debugging performance. Read it before you add an index. Read it after. Never guess what PostgreSQL is doing.

Chapter 4: MVCC — Why Updates Do Not Overwrite Data

The Physical Reality

Here is something surprising: when you UPDATE a row in PostgreSQL, it does not overwrite the old row. It writes a brand new version of the row and marks the old one as deleted.

This is called MVCC — Multi-Version Concurrency Control. It exists because of a fundamental problem: what should a reader see when a writer is in the middle of changing data?

Without MVCC, you would need to lock rows every time you read them to prevent seeing half-written data. Under high concurrency, this causes enormous bottlenecks. Every read would block on every write to the same row.

MVCC solves this by never modifying data in place. Instead:

• Every row has a xmin (transaction ID that created it) and xmax (transaction ID that deleted/updated it)
• A reader sees the version of the row that was committed before their transaction started
• A writer creates a new version without touching the version the reader is using
• Readers and writers do not block each other

Readers never block writers. Writers never block readers. This is the entire value of MVCC.

The Problem MVCC Creates: Dead Tuples

Every UPDATE leaves behind the old row version. Every DELETE marks a row as deleted but does not remove its bytes from the page. These orphaned row versions are called dead tuples.

Dead tuples waste space. They also make queries slower because PostgreSQL has to skip past them when scanning pages.

This is what VACUUM is for. VACUUM is a background process that scans tables, identifies dead tuples, and marks that space as reusable. It does not shrink the file — it just makes that space available for future inserts.

The Causal Chains

Transaction ID Wraparound — The One That Takes You Offline

Every transaction in PostgreSQL gets a 32-bit transaction ID (XID). There are about 2 billion possible values. When you reach 2 billion transactions, the counter wraps around.

If PostgreSQL did nothing about this, rows committed 2 billion transactions ago would look like they were committed in the "future" — and would become invisible. You would lose data.

PostgreSQL prevents this by requiring VACUUM to mark old rows as "frozen" before the XID counter gets too close to wrapping. If you somehow get to ~2 billion transactions without vacuuming, PostgreSQL will refuse to accept new transactions and your database goes read-only until you run VACUUM manually.

This sounds extreme. It has happened to real production databases. The lesson: never disable autovacuum, and monitor your oldest XID.

The Naive Mistake

A developer sees that VACUUM is consuming I/O and disables autovacuum on a high-traffic table:

ALTER TABLE events SET (autovacuum_enabled = false);

For a week, queries are fast. Then over the following month, the table bloats. Sequential scans that took 100ms now take 800ms. The developer adds indexes. The indexes help. Three months later, the XID counter approaches danger zone, PostgreSQL throws a warning, and the team spends a weekend running manual VACUUM in an emergency.

The Right Pattern

Let autovacuum run. Tune it for your table if needed:

-- For a high-churn table, make autovacuum more aggressive
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- vacuum when 1% of rows are dead (default 20%)
  autovacuum_analyze_scale_factor = 0.005
);

Monitor bloat with:

SELECT schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

The Rule You Keep Forever

Every UPDATE creates a dead tuple. Dead tuples are space and I/O waste. VACUUM is not optional housekeeping — it is the mechanism that keeps MVCC from destroying your performance.

PART 2: SCHEMA DESIGN FROM FIRST PRINCIPLES

Chapter 5: Data Types — The Decision That Cannot Be Undone

The Physical Reality

Every column in every row takes up space on disk. That space determines how many rows fit per page. That determines how many pages must be read per query. Choosing the wrong data type is a tax you pay on every single query, forever.

The choice of data type is one of the hardest decisions to undo in a live system. Changing a column type on a table with 10 million rows requires rewriting every row, holds locks, and causes downtime. Get it right the first time.

Numbers: int vs bigint vs uuid

int is 4 bytes. Maximum value: ~2.1 billion. bigint is 8 bytes. Maximum value: ~9.2 quintillion.

• If you use int for a primary key and insert more than 2.1 billion rows → the sequence overflows → inserts start failing → your service is down
• If you use bigint for a primary key → 8 bytes per row instead of 4 → for 100 million rows → 400MB extra storage just for that column → and every index on that column is larger too

At typical SaaS scale (under 100M rows), bigint is almost always the right call. The storage cost is real but small. The overflow risk with int is catastrophic.

uuid is 16 bytes — 4x an int, 2x a bigint. It has a critical performance problem: random UUIDs (v4) are not sequential. Index insertions on random values cause page splits constantly, which fragments your index and makes writes progressively slower.

The causal chain:

• Sequential IDs (bigserial) → new rows always append to the end of the index → no splits → fast inserts
• Random UUIDs → new rows insert at random positions in the index → page splits → index fragmentation → write amplification → inserts slow down as table grows

If you need UUIDs for external reasons (API exposure, distributed ID generation), use ULID or UUIDv7 — they are time-ordered, so they insert like sequential IDs but look like UUIDs.

Text: text vs varchar(n)

In PostgreSQL, text and varchar are stored identically. varchar(255) does not store data more efficiently than text. The only difference is that varchar(n) adds a length check.

• If you use varchar(255) → PostgreSQL checks length on every insert → no storage benefit → only a constraint
• If you use text → no length limit → less surprising behavior

Use varchar(n) only when you actually want to enforce a maximum length as a business rule (e.g., a username with a maximum of 30 characters). Otherwise, use text.

Timestamps: timestamp vs timestamptz

timestamp stores a date and time with no timezone information. timestamptz stores a date and time as UTC internally, displaying in the session timezone.

This is where most bugs come from:

• If you use timestamp and your server changes timezone → your "created_at" values now appear to be in a different time → your date-range queries return wrong results
• If you use timestamptz → always stored as UTC internally → always correct regardless of server timezone → this is always the right choice for any timestamp representing a real moment in time

Default to timestamptz. Always.

JSON: jsonb vs json vs relational columns

json stores the raw JSON text and re-parses it on every read. jsonb stores JSON in a parsed binary format and can be indexed.

• json vs jsonb: always use jsonb. json is almost never the right choice.
• jsonb vs relational columns: this is the real decision.

Use jsonb when:

• The structure is genuinely variable across rows (different users have different settings keys)
• You are storing data you will retrieve as a whole blob, not filter by individual keys

Use relational columns when:

• You need to filter, sort, or aggregate by those values (SQL can do this with jsonb but it is awkward and slower)
• The structure is fixed across all rows

The causal chain:

• If you store user preferences as jsonb and rarely query individual keys → fine, retrieves fast as a blob
• If you then need WHERE settings->>'theme' = 'dark' on 1 million users → you need an index, and which one depends on how the query is written: ->> equality needs an expression index CREATE INDEX ON users ((settings->>'theme')); the containment form WHERE settings @> '{"theme": "dark"}' can use a GIN index on the whole column. A plain GIN index does not help ->> comparisons — a very common mistake (Chapter 10B) → either way you maintain an extra index → you are paying a tax for flexibility you maybe did not need

The Rule You Keep Forever

Choose data types by thinking about how the column will be used in queries, not just what it stores. The wrong type is a permanent performance tax.

Chapter 6: Normalization — The Tradeoff at the Heart of Schema Design

The Physical Reality

Normalization is the practice of organizing data to eliminate redundancy. Instead of storing user_name in both the orders table and the users table, you store it once in users and reference it via a foreign key.

This sounds purely good. It is not. Normalization trades write simplicity for read complexity. Denormalization trades write complexity for read simplicity. There is no free lunch.

The Causal Chains

Normalization:

Denormalization:

• If you denormalize → store redundant data (e.g., user_name in the orders table) → reads are fast, no JOINs needed
• If you denormalize → when user changes name → must update orders table too → risk of inconsistency
• If denormalized update fails halfway → some orders have old name, some have new name → data corruption

The Naive Mistake

An engineer stores user_email in the notifications table because "we always need it when fetching notifications."

CREATE TABLE notifications (
  id         bigserial PRIMARY KEY,
  user_id    bigint,
  user_email text,  -- denormalized
  message    text,
  created_at timestamptz
);

Six months later, a user changes their email. The engineer updates users.email but forgets notifications.user_email. Now some notification records have an old email. The email system sends to the wrong address. The bug is invisible until a user complains.

The Right Pattern

Normalize first. Denormalize with evidence and explicit decisions.

-- Normalized: notifications does not store email
CREATE TABLE notifications (
  id         bigserial PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id),
  message    text,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- When you need email, JOIN it
SELECT n.message, u.email
FROM notifications n
JOIN users u ON u.id = n.user_id
WHERE n.user_id = $1;

If this JOIN is provably slow at your scale and you have benchmarks showing it, then denormalize — but with a documented decision and an explicit sync mechanism.

The Tradeoff Map

• Normalized: optimizes for write consistency and storage efficiency
• Normalized: makes worse: read performance when many JOINs are needed
• Denormalized: optimizes for read performance
• Denormalized: makes worse: write correctness (must keep copies in sync)
• Breaks at scale when: denormalized copies drift from source of truth

The Rule You Keep Forever

Normalize first, denormalize with evidence. Every denormalization must be paired with a plan for keeping the copies consistent. If you do not have that plan, you have a future bug, not a performance optimization.

Chapter 7: Primary Keys and Foreign Keys — The Hidden Costs

The Physical Reality

A primary key is not just a constraint. When you declare a primary key, PostgreSQL automatically creates a unique index on that column. That index is an 8KB-page B-tree structure on disk, separate from the heap file.

Every time you insert a row, PostgreSQL must update the heap file AND the primary key index. Every time you update the primary key value (rare but it happens), PostgreSQL must update the index. The index is not free storage — it is a parallel data structure maintained in sync with your table.

A foreign key is a constraint that says: "the value in this column must exist in that other table's primary key." It sounds simple. The physical implication is not: on every INSERT into the child table, PostgreSQL must verify the referenced row exists in the parent table. On every DELETE from the parent table, PostgreSQL must verify no child row references it. Foreign keys add a query to every write operation.

The Causal Chains

Primary keys:

Foreign keys:

• If you add a foreign key orders.user_id → users.id → every INSERT into orders runs SELECT 1 FROM users WHERE id = $1 internally → one extra query per insert
• If you delete a user and have ON DELETE CASCADE → PostgreSQL must find and delete all their orders → if there are 10,000 orders → 10,000 deletes → can be slow and lock-heavy
• If you remove foreign keys entirely → inserts are faster → data integrity now depends entirely on your application code → one bug means orphaned rows → invisible corruption

The NOT NULL Question

NOT NULL is a constraint enforced at the page level. PostgreSQL stores a null bitmap per row, with one bit per nullable column. If a column is NOT NULL, that bit is not needed.

More importantly: nullable columns in indexes behave unexpectedly. A B-tree index includes NULL values by default, but WHERE column = value will never match NULL. A query like WHERE status IS NULL can use an index, but it is a different operation than equality.

The real cost of NOT NULL is schema migrations. Adding NOT NULL to an existing column with data requires either a table rewrite (lock, slow) or a multi-step migration (add as nullable → backfill → set NOT NULL). Plan for this when designing.

The Rule You Keep Forever

Primary keys determine index structure. Index structure determines write performance. Foreign keys enforce integrity at the cost of write overhead. Know what you are buying and what you are paying before you declare either.

Chapter 8: NULL — The Most Misunderstood Thing in SQL

The Physical Reality

NULL does not mean zero. NULL does not mean empty string. NULL means the absence of a value — unknown, not applicable, not provided.

The problem is that SQL's three-valued logic (TRUE, FALSE, NULL) violates the intuitions most engineers bring from application code, where null == null is true.

In SQL: NULL = NULL evaluates to NULL (not TRUE).

This one fact causes more subtle bugs than almost anything else in database programming.

The Causal Chains

The Naive Mistake

-- Find all users who are NOT in a premium plan
SELECT * FROM users
WHERE plan_id NOT IN (SELECT id FROM plans WHERE tier = 'premium');

This looks correct. But if any row in plans has id IS NULL, the subquery returns a NULL in the list. NOT IN with a NULL in the list always returns nothing. Every user is silently excluded.

The correct version:

SELECT * FROM users
WHERE plan_id NOT IN (
  SELECT id FROM plans WHERE tier = 'premium' AND id IS NOT NULL
);

-- Or better, use NOT EXISTS:
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM plans p
  WHERE p.id = u.plan_id AND p.tier = 'premium'
);

The Right Pattern

Three rules:

1. Default columns to NOT NULL unless you have a specific reason for NULL. If it should be empty, use an empty string or zero.
2. Never use NOT IN with a subquery. Use NOT EXISTS instead.
3. When filtering nullable columns, explicitly account for NULLs with IS NULL or IS NOT NULL checks.

The Rule You Keep Forever

NULL is not a value. It is the absence of a value. Comparing NULL to anything — including another NULL — gives you NULL. Every query that touches nullable columns must explicitly handle NULL or it is silently wrong.

PART 3: INDEXES FROM FIRST PRINCIPLES

Chapter 9: How B-tree Indexes Actually Work

The Physical Reality

An index is a separate data structure stored on disk, alongside your table's heap file. The most common index type in PostgreSQL is the B-tree (balanced tree).

Imagine a sorted list of the values in your indexed column, with a pointer from each value to the actual row's location in the heap. Now imagine that list is organized as a tree so you can do binary search in O(log n) time instead of linear scan in O(n) time.

That is essentially what a B-tree index is.

The tree has three kinds of nodes. (The number of levels is not fixed: it grows with the logarithm of the row count, and because each 8KB page holds hundreds of entries, even a table with hundreds of millions of rows is usually only 3–4 levels deep.)

• Root node: the top, one per index
• Internal nodes: intermediate levels for navigation
• Leaf nodes: the bottom, contain actual index values and heap pointers (called TIDs — tuple identifiers, which are (page_number, row_offset) pairs)

All leaf nodes are linked together in sorted order, so range queries (WHERE x BETWEEN 1 AND 100) can find the starting point and then walk the leaf nodes linearly.

The Lookup Process

When you run SELECT * FROM users WHERE email = 'foo@bar.com':

1. PostgreSQL checks if there is an index on email
2. If yes: traverse the B-tree → 3-4 node reads → find the leaf entry → get the TID (page 1782, row 4)
3. Fetch page 1782 from the heap (buffer cache or disk) → find row 4 → return it
4. Total: 3-4 index page reads + 1 heap page read = ~5 page reads

Without an index: read all heap pages in sequence → for a table with 100,000 rows at 100 bytes each → ~1,300 pages → 1,300 page reads to find one row.

The index reduces 1,300 page reads to 5. That is why it is faster.

The Hidden Cost: Write Amplification

Every index is a data structure that must be kept in sync with the table. Every time you INSERT, UPDATE, or DELETE a row, every index on that table must be updated.

• If you have 1 index → each insert does 1 heap write + 1 index write = 2 writes
• If you have 5 indexes → each insert does 1 heap write + 5 index writes = 6 writes
• If you have 10 indexes → 11 writes per insert

The important exception: HOT updates. An UPDATE writes a new row version (Chapter 4). If the UPDATE changes no indexed column and the new version fits on the same heap page, PostgreSQL makes a Heap-Only Tuple (HOT) update: the indexes keep pointing at the old version, which links to the new one — zero index writes. This is why "don't index a column that changes constantly" (e.g. last_seen_at, a counter) matters so much: one index on it turns every cheap HOT update into a write to every index on the table. Leaving free space on pages (ALTER TABLE ... SET (fillfactor = 90)) makes HOT updates more likely on update-heavy tables. Check n_tup_hot_upd vs n_tup_upd in pg_stat_user_tables.

And index writes are not free. B-tree insertions can cause page splits when a leaf node is full. A page split:

1. Allocates a new page
2. Moves half the entries to the new page
3. Updates the parent node to point to both
4. This can cascade up the tree (splits can split)

Over time, page splits cause index fragmentation. A fragmented index has partially-full pages, which means the index file is larger than needed, cache efficiency drops, and range scans (walking leaf nodes) become slower.

The Causal Chains

The Rule You Keep Forever

An index is a bet: you are betting reads will be common enough to justify the write overhead. For every index you add, you are making writes more expensive. On a write-heavy table, fewer indexes are often better.

Chapter 10: When to Index and When NOT To

The Selectivity Principle

An index is worth using when it dramatically reduces the number of rows PostgreSQL must examine. This is called selectivity — how specifically does the filter narrow the result set?

• WHERE user_id = 42 on a 1M-row table that has 10 rows per user → selects 0.001% of rows → highly selective → index is extremely valuable
• WHERE status = 'active' on a table where 90% of rows are active → selects 90% of rows → low selectivity → PostgreSQL will likely ignore the index and do a seq scan anyway

Why would PostgreSQL ignore a perfectly good index? Because following index pointers to fetch individual rows from the heap is random I/O. Reading the entire table sequentially is sequential I/O. Sequential I/O is faster per byte than random I/O (especially on spinning disks). If you need 90% of the rows, sequential scan is often faster than following 900,000 random heap pointers.

PostgreSQL knows this. Its cost model explicitly compares sequential vs random I/O costs and chooses accordingly.

The Causal Chains

Composite Indexes and Column Order

A composite index on (a, b) can be used by queries that filter on:

• WHERE a = 1 ✓ (leading column rule)
• WHERE a = 1 AND b = 2 ✓ (full composite)
• WHERE b = 2 ✗ usually (b is not the leading column → no efficient way into the index)

The leading column rule: a composite index is efficient for queries that constrain a leftmost prefix of its columns. Entries are sorted by a, then by b within each a — so all the b = 2 entries are scattered across every value of a.

Two precise caveats, because interviewers like to test them:

• PostgreSQL 18+ can "skip scan" a B-tree: if a has only a handful of distinct values, it jumps to each value of a and searches for b = 2 inside it. Useful, but only cheap when the leading column has few distinct values — don't design around it.
• The planner may still do a full index scan (reading the whole index) when that is cheaper than the table, e.g. when the index is much narrower than the rows.

Order your composite index columns by:

1. Equality filters first (WHERE a = ?)
2. Range or sort columns last (WHERE b > ?, ORDER BY b) — after a range condition, later columns can't be used to narrow the search
3. Among equality columns, choose the order that lets the most queries use a leftmost prefix. "Most selective first" is a popular myth: for a query with a = ? AND b = ? both orders find the same entries equally fast. What matters is which other queries need a alone or b alone.

Covering Indexes

An index only scan happens when the index contains all the columns the query needs — no heap fetch required. This is the fastest possible read.

-- This index covers the query below
CREATE INDEX ON orders (user_id, status, created_at);

-- This query needs user_id (filter), status (filter), created_at (return value)
-- All in the index → index only scan → no heap fetch
SELECT created_at FROM orders WHERE user_id = 42 AND status = 'shipped';

Adding INCLUDE columns lets you add non-filterable columns to the index without affecting the sort order:

CREATE INDEX ON orders (user_id) INCLUDE (total_amount);
-- Now SELECT total_amount FROM orders WHERE user_id = 42 is index-only

The catch: the visibility map. An index entry doesn't record whether its row version is visible to your transaction (that lives in the heap, because of MVCC). An index-only scan can skip the heap only for pages that VACUUM has marked all-visible in the visibility map. On a table with lots of recent writes and lagging autovacuum, EXPLAIN shows Index Only Scan with a high Heap Fetches: count — it is quietly visiting the heap anyway. Keep autovacuum healthy on tables you rely on for index-only scans.

The Rule You Keep Forever

Index columns you filter on with high selectivity. Skip low-selectivity columns (booleans, status with few values). The most valuable index is one that makes a slow query fast without degrading a fast write path.

Chapter 10A: Partial Indexes — Indexing Only What You Query

After Chapter 10, you understand why low-selectivity columns make bad indexes. A status column with values 'active' and 'inactive' is a classic example — if 80% of rows are active, an index on status is nearly useless because PostgreSQL would rather do a sequential scan than follow 800,000 random heap pointers. You probably left Chapter 10 thinking: "fine, I just can't index low-selectivity columns." That instinct is right for full indexes. But it leaves a real problem unsolved.

This chapter breaks that constraint. You will learn to build indexes that only cover the rows your queries actually need — making those indexes small, fast, and cheap to maintain in ways a full index cannot be. By the end, you will look at two of the most common patterns in SaaS code — soft deletes and multi-tenant data — and immediately see exactly what index to build and why.

Part 1: The Problem a Full Index Cannot Solve

Consider AttemptIQ. You have an attempts table. Every question a user answers produces a row. After a year of operation, this table has 2 million rows. Your application queries it constantly — loading a user's active session, showing recent attempts, calculating analytics.

Here is what that table might look like:

CREATE TABLE attempts (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users(id),
  question_id bigint NOT NULL REFERENCES questions(id),
  answer      text,
  is_correct  boolean,
  status      text NOT NULL DEFAULT 'completed', -- 'completed', 'in_progress', 'abandoned'
  attempted_at timestamptz NOT NULL DEFAULT now()
);

Most attempts are 'completed'. Maybe 99% of your 2 million rows have status = 'completed'. Only the current session has 'in_progress' — at most a few hundred rows at any given moment.

Your application has one hot query: load the current in-progress session for a user.

SELECT * FROM attempts
WHERE user_id = $1 AND status = 'in_progress'
ORDER BY attempted_at DESC;

You run EXPLAIN ANALYZE and see a sequential scan. You already have an index on user_id. PostgreSQL uses it, finds all attempts for that user (say, 500 rows across their history), then filters for status = 'in_progress' among those 500. This is fine at small scale. But as your table grows and users accumulate thousands of attempts, that per-user filter gets more expensive.

You think: add an index on status. Chapter 10 already told you this is a bad idea. With only three distinct values and 'completed' dominating, the index has near-zero selectivity and PostgreSQL will not use it.

You think: composite index on (user_id, status). Better — highly selective on the left column. But you are now indexing every row's status. Your 2 million completed rows each get an entry in this index. The index is large. Every insert into the attempts table updates it. And all of that index size and write overhead is for 1,980,000 rows you will never query via status = 'in_progress'.

This is the constraint. You want an index that helps you find 'in_progress' rows, but you do not want to pay for indexing the 99% of rows that will never match that filter. A full index cannot give you this. A partial index can.

Part 2: What a Partial Index Actually Is

A partial index is a B-tree (or other index type) that is built only over rows that satisfy a WHERE condition. Physically, the index file on disk contains entries only for the matching rows — not for the full table.

CREATE INDEX ON attempts (user_id)
WHERE status = 'in_progress';

What PostgreSQL builds here is a B-tree on user_id — but only populated with entries for rows where status = 'in_progress'. If you have 2 million total rows and 200 are in-progress, this index has 200 entries. Not 2 million. Two hundred.

The consequences cascade:

The index file is tiny. 200 B-tree entries fit in a handful of pages. The whole index likely fits in the buffer cache permanently. Every lookup is a cache hit.

Index maintenance is nearly free. When a new attempt is inserted with status = 'completed', PostgreSQL does not touch this index at all — the row does not satisfy the WHERE clause. Only inserts of in_progress rows update it. And those are rare. Write amplification from this index is close to zero.

The query that uses it becomes extremely fast. PostgreSQL filters the index to find user_id = $1 among only the in-progress rows, reads a handful of heap pages, returns the result. No full table scan. No per-user filtering of hundreds of completed rows.

Here is what PostgreSQL needs to see to use a partial index: the query's WHERE clause must be compatible with the index's WHERE clause. If your query is WHERE user_id = $1 AND status = 'in_progress', and your index is WHERE status = 'in_progress', PostgreSQL knows every row in the index already satisfies status = 'in_progress' — it just needs to find the right user_id among them. It will use the index.

If your query is WHERE user_id = $1 with no status filter, PostgreSQL cannot use this index — it only covers a subset of rows, so it cannot answer a question about all rows for that user.

Part 3: The Soft Delete Pattern — The Most Common SaaS Bug

Almost every SaaS product at some point adds soft deletes. Instead of DELETE FROM users WHERE id = $1, you do:

UPDATE users SET deleted_at = now() WHERE id = $1;

The row stays in the table. You filter it out of all queries with WHERE deleted_at IS NULL. This gives you an audit trail, the ability to recover deleted accounts, and analytics on churned users.

The problem: you have now turned a highly selective column into a permanent performance tax.

Suppose AttemptIQ has 50,000 registered users. After a year, 2,000 have deleted their accounts. The other 48,000 are active. Every query in your application now includes WHERE deleted_at IS NULL — because every query should only return active users.

SELECT * FROM users WHERE email = $1 AND deleted_at IS NULL;
SELECT * FROM users WHERE id = $1 AND deleted_at IS NULL;
SELECT * FROM users WHERE plan_id = $2 AND deleted_at IS NULL;

You have an index on email. PostgreSQL uses it to find the row with that email, then checks deleted_at IS NULL. Fine — that is a single row lookup. But consider a query that has lower selectivity on its primary filter:

SELECT * FROM users WHERE plan_id = 3 AND deleted_at IS NULL;

If plan_id = 3 matches 10,000 users and 200 of them are deleted, PostgreSQL must examine 10,000 index entries, fetch 10,000 heap rows, and filter out 200. You are doing 10,000 heap fetches to return 9,800 rows.

A partial index changes this completely:

CREATE INDEX ON users (plan_id)
WHERE deleted_at IS NULL;

This index contains only the 48,000 active users. Deleted users are not in it. The query WHERE plan_id = 3 AND deleted_at IS NULL now scans an index with 48,000 entries instead of 50,000 — but more importantly, every entry it finds is guaranteed to be active. No heap fetch will be wasted on a deleted row. The index is also 4% smaller (48,000 vs 50,000 entries), which means better cache efficiency.

For your primary lookup patterns — WHERE email = $1 AND deleted_at IS NULL — a covering partial index is the correct architecture:

CREATE UNIQUE INDEX ON users (email)
WHERE deleted_at IS NULL;

This does two things: it enforces that no two active users can have the same email (deleted users can share emails with active ones — useful when users delete and re-register), and it lets email lookups skip all deleted rows in the index entirely. The index is also a uniqueness constraint, which means it runs on every insert to verify no duplicate active email exists — and because it only covers active rows, that check is fast.

The Causal Chain for Soft Deletes

Every query in a soft-delete system filters WHERE deleted_at IS NULL. This filter is applied after the primary index lookup. If deleted rows are present in your primary indexes, every index lookup potentially includes them, and the heap fetch confirms or discards each one. As your delete rate grows, the cost of this "filter after fetch" pattern grows with it. A partial index on WHERE deleted_at IS NULL pushes the filter into the index itself — deleted rows never enter the lookup, so they cost nothing.

The counterintuitive part: the partial index is also faster for PostgreSQL's cost estimator to reason about. The planner knows the index only contains active rows. It can accurately estimate selectivity against just that subset. A full index on (email) on a table with soft deletes has statistics that mix active and deleted rows together, which can cause the planner to over- or under-estimate result set sizes.

Part 4: Multi-Tenancy — One Table, Many Customers

AttemptIQ is a single-tenant product right now — one set of users, one question bank, one analytics system. But ScoreResume, your other product, has a structure that is closer to multi-tenant: different users get different resume analyses, and you want their data isolated.

Imagine a table like this:

CREATE TABLE resume_analyses (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users(id),
  resume_data jsonb,
  score       int,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Every query in the system filters by user_id. User A never sees User B's analyses. The queries look like:

SELECT * FROM resume_analyses WHERE user_id = $1 ORDER BY created_at DESC LIMIT 10;
SELECT * FROM resume_analyses WHERE user_id = $1 AND score > 70;

A regular index on user_id handles this fine. But now consider a true multi-tenant system — say, if ScoreResume offered team plans where a company could have 500 employees all using the product under one account. The user_id is now a tenant_id, and the table grows to 50 million rows across thousands of tenants.

For most tenants, queries are fast — their slice of the data is small. But for your largest tenants, queries still scan the full tenant partition inside the index. Partial indexes per active tenant are not practical (you cannot maintain thousands of them dynamically). But a partial index on your most critical operational subset is:

-- Index only analyses created in the last 90 days -- the "active" window
CREATE INDEX ON resume_analyses (user_id, created_at)
WHERE created_at > now() - interval '90 days';

Wait — this will not work. now() is evaluated at index creation time, not at query time. The index condition would never update. This is an important constraint: partial index WHERE clauses must be immutable. A condition like WHERE status = 'active' is immutable — the status of a row can change, but the condition itself does not change its meaning. A condition involving now() is not.

The correct approach for time-windowed data is table partitioning (a different topic), not partial indexes. Partial indexes work for conditions based on the row's own data values, not on time relative to now.

Where partial indexes do help in multi-tenancy is for operational status columns. If you have a jobs table where most rows are 'done' and a small fraction are 'pending' or 'running', and every worker query asks for pending jobs:

SELECT * FROM jobs WHERE status IN ('pending', 'running') ORDER BY created_at LIMIT 1;

A partial index is exactly right:

CREATE INDEX ON jobs (created_at)
WHERE status IN ('pending', 'running');

This index stays small as long as your job queue does not grow unboundedly — completed jobs are not in it, and completed jobs are always the majority.

Part 5: When Partial Indexes Go Wrong

A partial index on a column that frequently changes its condition value can cause silent performance degradation. Consider:

CREATE INDEX ON orders (user_id)
WHERE status = 'pending';

If orders frequently move from 'pending' to 'processing' to 'shipped' — each update that changes status removes the row from the index (for the pending condition) and the index entry is deleted. High-churn rows create a lot of index maintenance: insertions when the row enters 'pending' state, deletions when it leaves. For a high-volume order system, this churn can be expensive. Measure before assuming a partial index is free.

Also: partial indexes only help queries that include the index condition. If you build WHERE deleted_at IS NULL and you later need to query deleted users (for an admin dashboard, say), that query gets no benefit. It needs a different index — either a full index, or a second partial index WHERE deleted_at IS NOT NULL.

This is the tradeoff map: partial indexes are smaller and cheaper to maintain than full indexes, but they are narrower. They can only answer queries that align with their condition. A full index answers any query on that column. The right choice depends on your query distribution — if 99% of queries need the condition, partial index wins. If queries need both sides equally, full index wins.

Part 6: Putting It Together — The Decision

The rule for deciding whether to use a partial index:

First, identify the condition that appears in the vast majority of queries on this table. If there is a dominant filter — deleted_at IS NULL, status = 'active', status IN ('pending', 'running') — and that filter excludes a significant fraction of the table, a partial index is almost always better than a full index.

Second, check that the condition is immutable (no now(), no values that depend on external state). The condition is baked into the index at creation time and does not change.

Third, check that the excluded rows are truly excluded from your hot query paths. If you regularly query deleted users or completed jobs from the same table using the same index column, you need a full index or two separate partial indexes.

Fourth, remember that partial indexes work with any index type. A GIN partial index for full-text search, limited to active documents only, is valid. A partial unique constraint (WHERE deleted_at IS NULL) is one of the most useful patterns in SaaS schema design.

The Rule You Keep Forever

A full index taxes every write to pay for reads you may never make. A partial index taxes only the writes that matter. When your query always includes the same condition, that condition belongs in the index definition, not just in the query.

Vocabulary Map

Practice Problems

1. Failure analysis. Your subscriptions table has 500,000 rows. 480,000 have status = 'expired'. 20,000 have status = 'active'. You add a full B-tree index on status to speed up WHERE status = 'active' queries. EXPLAIN ANALYZE shows PostgreSQL is ignoring your index and doing a sequential scan. Why? What is the planner calculating? What is the correct fix?

2. Design from scratch. AttemptIQ adds a feature: users can flag questions as "bookmarked" for later review. The question_bookmarks table has columns (user_id, question_id, bookmarked_at, removed_at). When a user removes a bookmark, you set removed_at = now() rather than deleting the row (for analytics). The hot query is: SELECT question_id FROM question_bookmarks WHERE user_id = $1 AND removed_at IS NULL ORDER BY bookmarked_at DESC. Design the index. Justify every decision — column order, partial condition, whether to include any columns.

3. Synthesis. You have a jobs table with columns (id, status, priority, created_at, worker_id). Status values: 'queued', 'running', 'done', 'failed'. 95% of rows are 'done'. Workers poll for jobs with: SELECT id FROM jobs WHERE status = 'queued' ORDER BY priority DESC, created_at ASC LIMIT 1 FOR UPDATE SKIP LOCKED. Connect the concepts of partial indexes, selectivity (Chapter 10), and locking (Part 5) to design the optimal index for this query. What does SKIP LOCKED do and why does it matter for a job queue?

4. Go deeper. A partial index with condition WHERE deleted_at IS NULL is created on a users table. A user deletes their account: UPDATE users SET deleted_at = now() WHERE id = $1. What does PostgreSQL physically do to the index during this update? Where does the dead tuple end up? Who cleans it up and when? Connect this answer to Chapter 4 (MVCC and VACUUM).

5. Connect to previous chapters. You have a notifications table (from Chapter 6's normalization discussion). It has 10 million rows. 9.9 million have read_at IS NOT NULL (already read). 100,000 have read_at IS NULL (unread). The hot query is SELECT * FROM notifications WHERE user_id = $1 AND read_at IS NULL ORDER BY created_at DESC LIMIT 20. You create a partial index CREATE INDEX ON notifications (user_id, created_at) WHERE read_at IS NULL. Six months later the index is no longer helping — EXPLAIN shows a sequential scan. Walk through every possible reason this could happen, ordered from most to least likely.

Chapter 10B: Index Types Beyond B-tree — Matching the Index to the Operator

The Physical Reality

An index is useful only if it can answer the operator in your WHERE clause. A B-tree stores values in sorted order, so it answers questions about order: =, <, >, BETWEEN, ORDER BY, IN, and prefix LIKE 'abc%'. It cannot answer "does this JSON document contain this key?", "does this array overlap that one?", "do these two time ranges overlap?", or "does this text contain the word refund?" — sorting doesn't help with those questions.

PostgreSQL ships different index structures for different questions. Picking one is not about speed in general; it is about which operators it supports.

The Index Types You Should Know

B-tree (default): equality, ranges, sorting, uniqueness. If you don't know which type you need, you need this one. Note: LIKE 'abc%' only uses a B-tree under the C collation or with the text_pattern_ops operator class; LIKE '%abc%' never does.

Expression index (a B-tree on a computed value): the query must use the exact same expression.

CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);      -- uses it
SELECT * FROM users WHERE email ILIKE $1;                -- does NOT

CREATE INDEX ON users ((settings->>'theme'));            -- note the double parentheses
SELECT * FROM users WHERE settings->>'theme' = 'dark';   -- uses it

GIN (Generalized Inverted Index): for values that contain many elements — jsonb, arrays, full-text documents. Like the index at the back of a book: for every element (key, array item, word), a list of the rows that contain it.

CREATE INDEX ON events USING gin (payload);              -- jsonb_ops
SELECT * FROM events WHERE payload @> '{"type": "refund"}';   -- containment: uses GIN
SELECT * FROM events WHERE payload ? 'coupon_code';           -- key exists: uses GIN
SELECT * FROM events WHERE payload->>'type' = 'refund';       -- does NOT use GIN

CREATE INDEX ON posts USING gin (tags);                   -- text[] column
SELECT * FROM posts WHERE tags && ARRAY['postgres','sql']; -- overlap: uses GIN

-- Full-text search
CREATE INDEX ON articles USING gin (to_tsvector('english', body));
SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('english', 'refund & policy');

-- Substring search: LIKE '%abc%' with the pg_trgm extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON users USING gin (name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%anna%';           -- uses the trigram index

The cost: one row can produce dozens of index entries, so GIN indexes are large and slow to update. PostgreSQL buffers new entries in a "pending list" (fastupdate) and merges them later, which makes writes cheaper but some reads slower until the merge.

GiST (Generalized Search Tree): for things that overlap or are near each other — ranges, geometry (PostGIS), nearest-neighbour search. Its killer feature for application developers is the exclusion constraint, which the database enforces even under concurrency:

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_bookings (
  room_id  int       NOT NULL,
  during   tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)   -- no two bookings of a room may overlap
);

Without it, "check for an overlapping booking, then insert" is a check-then-act race (Chapter 16) — two requests can both see a free slot. With it, the second insert fails. This is the cleanest answer to "design a booking system so a room is never double-booked."

BRIN (Block Range Index): for huge, append-only tables whose rows are physically stored in order — logs, events, metrics with created_at always increasing. Instead of one entry per row, BRIN stores the min/max value per block range (128 pages by default). A BRIN index on a 1-billion-row table can be a few megabytes instead of tens of gigabytes. It only works if the column is correlated with physical order; on a randomly ordered column it skips nothing.

Hash: equality only, no ranges, no sorting, no uniqueness. Rarely worth choosing over a B-tree.

The Causal Chains

The Naive Mistake

-- "Search is slow, add an index"
CREATE INDEX ON products (name);
SELECT * FROM products WHERE name ILIKE '%phone%';   -- still a sequential scan

A B-tree on name can't help a leading-wildcard, case-insensitive search. The fix is a trigram GIN index (gin_trgm_ops) — or, for real search (ranking, typos, synonyms), full-text search or a search engine.

The Decision

Query shape                                       → Index
=, <, >, BETWEEN, ORDER BY, unique                → B-tree
lower(x) = ?, (json->>'k') = ?, date(ts) = ?      → expression index (B-tree)
jsonb @> / ? , array && / @>                      → GIN
full-text  @@                                     → GIN on to_tsvector(...)
LIKE '%abc%' / ILIKE                              → GIN with pg_trgm (gin_trgm_ops)
ranges overlap, "no double booking"               → GiST (+ EXCLUDE constraint)
geometry, nearest neighbour                       → GiST (PostGIS)
billions of rows, append-only, time-ordered       → BRIN
only a subset of rows is ever queried             → partial index (Chapter 10A), any type

Practice Problems

Problem 1. A products table has attributes jsonb. The app runs WHERE attributes->>'brand' = 'Acme' and WHERE attributes @> '{"color": "red"}'. A teammate adds CREATE INDEX ON products USING gin (attributes). Which query gets faster, which doesn't, and what would you add?

Answer sketch: the @> query can use the GIN index; the ->> query can't. Add an expression index on ((attributes->>'brand')), or rewrite it as attributes @> '{"brand": "Acme"}'. If brand is used everywhere, promote it to a real column.

Problem 2. Design the schema for a meeting-room booking service so that two people can never book the same room for overlapping times, even if both requests arrive in the same millisecond.

Answer sketch: a tstzrange column plus EXCLUDE USING gist (room_id WITH =, during WITH &&) (needs btree_gist). The second conflicting insert fails with an exclusion violation, which you turn into "that slot was just taken."

Problem 3. An events table receives 50 million rows a day, is never updated, and is queried by time range. A B-tree on created_at is 40 GB. What would you consider, and when would your suggestion fail?

Answer sketch: BRIN on created_at (tiny, good for time-range scans), likely combined with time-based partitioning. It fails if rows aren't inserted roughly in time order (e.g. late-arriving backfills) or if you need fast single-row lookups.

The Rule You Keep Forever

Pick the index by the operator, not by the column. B-tree for order, GIN for "contains", GiST for "overlaps / near", BRIN for "huge and in order". And always check the query uses the exact operator or expression the index was built for — EXPLAIN tells you.

What's Coming Next

You now have the full indexing toolkit: B-tree mechanics, selectivity, composite indexes, covering indexes, partial indexes, and the index types beyond B-tree. Part 4 shifts from how to build indexes to how queries destroy performance despite them — the N+1 problem, offset pagination that collapses under load, and JOIN patterns that seem fine at 10,000 rows and become emergencies at 10 million. Every query anti-pattern in Part 4 has a specific physical cause. You will diagnose each one the same way you have been diagnosing everything else: find the physical bottleneck, trace the causal chain, fix the root.

PART 4: QUERIES THAT DESTROY PERFORMANCE

Chapter 11: The N+1 Problem — The Silent Killer

The Physical Reality

N+1 is when you make 1 query to fetch N records, then make N additional queries — one for each record — to fetch related data.

The application code looks fine. The problem is invisible until you log your queries.

The Causal Chains

The Naive Mistake (with ORM)

// Sequelize / typical ORM code
const posts = await Post.findAll({ limit: 100 });

// This looks innocent
for (const post of posts) {
  post.author = await User.findOne({ where: { id: post.user_id } });
}
// Result: 101 queries

The app code looks clean. The database is being hammered.

How to Find It

Enable query logging and look for the pattern:

LOG: SELECT * FROM users WHERE id = 1   (0.3ms)
LOG: SELECT * FROM users WHERE id = 2   (0.3ms)
LOG: SELECT * FROM users WHERE id = 3   (0.3ms)
... 97 more identical queries ...

Or in pg_stat_statements, look for queries with very high calls count that run in a tight loop.

The Fix

Option 1: JOIN — fetch everything in one query

SELECT p.*, u.name as author_name, u.avatar_url
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at > now() - interval '7 days'
ORDER BY p.created_at DESC
LIMIT 100;

1 query. Done.

Option 2: Eager loading in ORM

// Sequelize: include the association
const posts = await Post.findAll({
  limit: 100,
  include: [{ model: User, as: 'author' }]
});
// Still 1 query (or 2 at most, with a batched IN query)

Option 3: Batch fetch (DataLoader pattern)

// Collect all user IDs first
const posts = await Post.findAll({ limit: 100 });
const userIds = [...new Set(posts.map(p => p.user_id))];

// Fetch all users in one query
const users = await User.findAll({ where: { id: userIds } });
const userMap = Object.fromEntries(users.map(u => [u.id, u]));

// Map them in memory
posts.forEach(p => p.author = userMap[p.user_id]);

2 queries total instead of 101.

The Rule You Keep Forever

If you are looping over records and querying inside the loop, you have N+1. Always fetch related data in bulk — through JOINs, IN clauses, or ORM eager loading. Never query in a loop.

Chapter 12: JOINs — What Actually Happens

The Physical Reality

A JOIN combines rows from two tables based on a condition. This sounds simple. The physical execution is not.

PostgreSQL has three JOIN algorithms it can choose from:

Nested Loop Join: For each row in table A, scan table B for matching rows. Time complexity: O(A × B) in the worst case. Fast when table A is small and table B has an index on the join column.

Hash Join: Build a hash table of all rows in the smaller table (keyed by the join column). Then scan the larger table and probe the hash table for each row. Time complexity: O(A + B). Requires enough memory to hold the hash table.

Merge Join: Requires both inputs to be sorted on the join column. Scans both in order, merging matching rows. Time complexity: O(A log A + B log B) including the sort. Fast when both tables have indexes providing sorted access.

The Causal Chains

The Naive Mistake

-- Finding orders with their shipping address
SELECT o.*, a.street, a.city
FROM orders o
JOIN addresses a ON a.user_id = o.user_id;
-- No index on addresses.user_id
-- For 100,000 orders and 100,000 addresses:
-- Nested loop: 100,000 × 100,000 = 10 BILLION row comparisons
-- This query runs for minutes

Add the index:

CREATE INDEX ON addresses (user_id);
-- Now: index lookup per order → 100,000 index lookups → fast

The Rule You Keep Forever

Every JOIN column on the larger table must have an index. An unindexed JOIN is a nested loop waiting to happen. Run EXPLAIN on every JOIN before shipping code.

Chapter 12A: SQL Interview Patterns — Writing the Query, Not Just Tuning It

The Physical Reality

Most of this book is about what the database does with your query. This chapter is about writing the query in the first place — the part of a backend or full-stack interview where you are handed a schema and asked "write the SQL." Interviewers at the 2–5 year level expect you to be fluent with aggregation, joins, CTEs, and window functions, and to know the traps.

Everything starts from one fact: SQL is not evaluated in the order you write it.

Written order:   SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT
Logical order:   1. FROM / JOIN        build the rows
                 2. WHERE              filter individual rows
                 3. GROUP BY           collapse rows into groups
                 4. HAVING             filter groups
                 5. SELECT             compute output columns (window functions run here)
                 6. DISTINCT
                 7. ORDER BY           (can use SELECT aliases)
                 8. LIMIT / OFFSET

This explains most "why doesn't this work?" questions:

• You can't use a SELECT alias in WHERE — WHERE runs before SELECT. (ORDER BY can, because it runs after.)
• You can't filter on COUNT(*) in WHERE — aggregates don't exist until GROUP BY. That is what HAVING is for.
• You can't filter on a window function in WHERE either — wrap it in a CTE or subquery and filter outside.

Aggregation: GROUP BY and HAVING

-- Customers with more than 5 orders in 2026, biggest spenders first
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
WHERE created_at >= '2026-01-01'          -- row filter: before grouping
GROUP BY customer_id
HAVING COUNT(*) > 5                       -- group filter: after grouping
ORDER BY spent DESC;

Know the difference between COUNT(*) (rows), COUNT(col) (rows where col is not NULL), and COUNT(DISTINCT col). And SUM over zero rows is NULL, not 0 — use COALESCE(SUM(x), 0).

Joins: The LEFT JOIN Trap

-- Goal: every user, with their PAID orders (users with none should still appear)
SELECT u.id, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';                  -- BUG: silently becomes an INNER JOIN

Users with no orders get o.status = NULL, and NULL = 'paid' is not true (Chapter 8), so WHERE drops them. Put conditions on the right-hand table in the ON clause:

LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'

Anti-join — "users who never ordered": use NOT EXISTS, never NOT IN with a subquery. If the subquery returns a single NULL, NOT IN returns no rows at all.

SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

CTEs: Naming the Steps

A CTE (WITH) names an intermediate result so a complex query reads top to bottom. Since PostgreSQL 12, a CTE that is referenced once is inlined into the main query and optimised with it, so it is not a performance barrier (use AS MATERIALIZED if you want it computed once).

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue
FROM monthly
WHERE revenue > 100000;

Recursive CTEs walk hierarchies — org charts, comment threads, category trees:

-- Everyone who reports (directly or indirectly) to employee 7
WITH RECURSIVE reports AS (
  SELECT id, manager_id, name FROM employees WHERE manager_id = 7
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports;

Window Functions: The Interview Favourite

A window function computes a value across a set of related rows without collapsing them — unlike GROUP BY, every input row stays in the output.

function() OVER (PARTITION BY <group> ORDER BY <order>)

-- Rank employees by salary within each department
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

For salaries 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4 (ties broken arbitrarily), RANK gives 1, 2, 2, 4 (gap after a tie), DENSE_RANK gives 1, 2, 2, 3 (no gap). Interviewers ask which one to use: "top 3 people" → ROW_NUMBER; "everyone in the top 3 salary levels" → DENSE_RANK.

-- Running total and day-over-day change
SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day)                      AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY day)            AS change_vs_yesterday,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_revenue;

The Questions You Will Actually Get

"Top N per group" — the 3 highest-paid employees in each department:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

"Second-highest salary" (return NULL if there isn't one):

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- or: SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 LIMIT 1;
--     (returns no row instead of NULL — say which behaviour you want)

"Find duplicates" and "delete duplicates, keep the newest":

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

DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
  ) t WHERE rn > 1
);
-- then add the UNIQUE constraint so it can't happen again

"Latest row per user" (latest order, latest status) — the same ROW_NUMBER pattern, or Postgres's DISTINCT ON:

SELECT DISTINCT ON (user_id) user_id, id, created_at
FROM orders
ORDER BY user_id, created_at DESC;

"Users who did X but never Y" — e.g. signed up but never ordered: the NOT EXISTS anti-join above.

"Insert or update" (upsert) — safe under concurrency, no check-then-act:

INSERT INTO daily_stats (day, page, views)
VALUES (CURRENT_DATE, '/pricing', 1)
ON CONFLICT (day, page)
DO UPDATE SET views = daily_stats.views + EXCLUDED.views;   -- EXCLUDED = the row you tried to insert

The Naive Mistake

Doing in application code what SQL does better: fetching every order into Node.js to group, rank, or find the latest per user. It moves millions of rows over the network (Chapter 11's N+1 problem in a different costume) and re-implements, with bugs, what the database does in one pass with an index. The reverse mistake also exists — a 200-line query nobody can maintain. The skill is knowing which work belongs where.

Practice Problems

Problem 1. Tables users(id, created_at) and orders(id, user_id, total, created_at). Write a query for each month of 2026: number of new users, and how many of those users placed at least one order within 30 days of signing up.

Answer sketch: a CTE first_orders AS (SELECT user_id, MIN(created_at) AS first_order_at FROM orders GROUP BY user_id); then FROM users u LEFT JOIN first_orders f ON f.user_id = u.id WHERE u.created_at >= '2026-01-01' AND u.created_at < '2027-01-01', GROUP BY date_trunc('month', u.created_at), and select COUNT(*) plus COUNT(*) FILTER (WHERE f.first_order_at < u.created_at + interval '30 days'). The LEFT JOIN keeps users who never ordered in the first count.

Problem 2. Why does this return fewer rows than expected, and how do you fix it? SELECT p.name, COUNT(r.id) FROM products p LEFT JOIN reviews r ON r.product_id = p.id WHERE r.rating >= 4 GROUP BY p.name;

Answer sketch: the WHERE on r.rating removes products with no reviews (NULL rating), turning the LEFT JOIN into an inner join. Move r.rating >= 4 into the ON clause. Also group by p.id, not p.name — two products can share a name.

Problem 3. Given logins(user_id, day), find users who logged in on at least 3 consecutive days.

Answer sketch: the "gaps and islands" trick — day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) is constant within a run of consecutive days. Group by (user_id, that value) and keep groups with COUNT(*) >= 3. (Deduplicate days first.)

The Rule You Keep Forever

Know the logical order — FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — and most SQL puzzles solve themselves. Filter rows in WHERE, groups in HAVING, window results outside the query that computes them. Right-table conditions of a LEFT JOIN go in ON. NOT EXISTS, never NOT IN.

Chapter 13: OFFSET Pagination — The Trap

The Physical Reality

Most apps paginate results. The naive implementation:

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;   -- page 1
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 20;  -- page 2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 500; -- page 26

This looks correct. Here is what PostgreSQL actually does for page 26:

1. Scan and sort (or index scan) the created_at index
2. Walk through 520 rows
3. Discard the first 500
4. Return rows 501-520

OFFSET does not skip work. It does the work and throws away the result.

The Causal Chains

The Fix: Cursor-Based Pagination

Instead of using OFFSET, remember where you left off:

-- Page 1
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;
-- Returns rows. Save the last row's (created_at, id) as your cursor.

-- Page 2: use the cursor
SELECT * FROM posts
WHERE (created_at, id) < ('2024-01-15 10:23:00', 12847)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This uses the index to jump directly to the right position — it does not scan and discard. Page 1000 costs exactly the same as page 1.

The tradeoff: cursor-based pagination cannot jump to arbitrary pages ("go to page 500"). It can only go forward (or backward with a reverse cursor).

For most product UIs (infinite scroll, "load more"), this is fine. For spreadsheet-style navigation with page numbers, you need OFFSET — but cap the maximum page to something reasonable.

The Rule You Keep Forever

OFFSET pagination gets slower with every page. Cursor-based pagination is O(1) regardless of page number. Use cursor pagination for any dataset that can grow.

PART 5: TRANSACTIONS, LOCKS, AND CONCURRENCY

Chapter 14: Transactions and ACID — What the Letters Actually Mean

The Physical Reality

A transaction is a group of operations that succeed or fail together. This is not just a convenience feature. It is a fundamental guarantee against data corruption when processes crash.

Here is why you need it: your program does not crash at random points in theory. It crashes at the worst possible time — after the first write but before the second. Without transactions, you end up with partial writes: money debited but not credited, order created but inventory not decremented, user created but profile not initialized.

ACID is the set of guarantees a transaction provides:

A — Atomicity: All operations in the transaction happen, or none of them do. Physically, this means: if your process crashes mid-transaction, PostgreSQL rolls back to the state before the transaction started. It can do this because it uses a Write-Ahead Log (WAL) — before changing any data, it writes the change to a log. On crash recovery, it replays committed transactions and discards uncommitted ones.

C — Consistency: The database moves from one valid state to another valid state. Constraints (NOT NULL, UNIQUE, FOREIGN KEY, CHECK) are checked as each statement runs, not at commit: the violating statement raises an error and the whole transaction is aborted (every later statement fails until you ROLLBACK). Only constraints declared DEFERRABLE INITIALLY DEFERRED (possible for UNIQUE, PRIMARY KEY and FOREIGN KEY) wait until COMMIT — useful when two rows must reference each other or when swapping unique values.

I — Isolation: Concurrent transactions do not see each other's in-progress work. This is implemented via MVCC (Chapter 4). How much isolation you get is configurable — and the default does not protect you from every anomaly (Chapter 14A).

D — Durability: Once a transaction is committed, it is permanent. Even if the server crashes immediately after. Physically, this means PostgreSQL calls fsync() on the WAL file before returning a commit response. fsync() forces the OS to flush data from kernel buffers to actual disk. Until that happens, the data could be lost in a power outage.

The Causal Chains

The Naive Mistake

// Transfer money between accounts
await db.query(`UPDATE accounts SET balance = balance - 100 WHERE id = 1`);
// Server crashes here
await db.query(`UPDATE accounts SET balance = balance + 100 WHERE id = 2`);
// Account 1 is debited. Account 2 is never credited. Money vanishes.

The fix:

await db.transaction(async (trx) => {
  await trx.query(`UPDATE accounts SET balance = balance - 100 WHERE id = 1`);
  await trx.query(`UPDATE accounts SET balance = balance + 100 WHERE id = 2`);
  // Both succeed or both are rolled back. No partial state possible.
});

The Rule You Keep Forever

If two or more writes must be consistent with each other — wrap them in a transaction. If you crash between two writes that are not in a transaction, you have corrupted data. It is not a question of if, it is a question of when.

Chapter 14A: Isolation Levels — What Your Transaction Can Still See Go Wrong

The Physical Reality

A transaction (Chapter 14) guarantees all-or-nothing. It does not, by default, guarantee that concurrent transactions can't interfere with each other's decisions. How much interference is allowed is the isolation level, and it comes down to one MVCC question (Chapter 4): when does my transaction take its snapshot of the data?

• READ COMMITTED (PostgreSQL's default): a new snapshot for every statement. Each statement sees everything committed before it started — including commits that happened in the middle of your transaction.
• REPEATABLE READ: one snapshot for the whole transaction, taken at its first statement. You see a frozen picture of the database; other transactions' commits are invisible to you. This is called snapshot isolation.
• SERIALIZABLE: the same snapshot as REPEATABLE READ, plus PostgreSQL tracks what each transaction read and wrote (Serializable Snapshot Isolation, SSI). If concurrent transactions could produce a result that no one-at-a-time order would produce, it aborts one of them.
• READ UNCOMMITTED exists in the SQL standard, but PostgreSQL treats it as READ COMMITTED. You can never read uncommitted data in Postgres.

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

The Anomalies — What Each Level Allows in PostgreSQL

Anomaly                         READ COMMITTED     REPEATABLE READ        SERIALIZABLE
Dirty read                      no                 no                     no
Non-repeatable read             YES                no                     no
Phantom read                    YES                no (stronger than      no
                                                   the SQL standard)
Lost update (read-modify-write) YES — silently     no — 2nd writer gets   no
                                                   error 40001
Write skew                      YES                YES                    no — one txn gets 40001

• Non-repeatable read: you read a row twice in one transaction and get different values, because someone committed in between.
• Phantom read: you run the same WHERE query twice and new rows appear.
• Lost update: two transactions read stock = 5, both compute 4 in application code, both write 4. One sale vanished. (Under READ COMMITTED, UPDATE ... SET stock = stock - 1 is safe — the second UPDATE waits for the row lock and re-reads the committed value. The bug is computing the new value in your code.)
• Write skew: two transactions read the same data, each updates a different row based on what it read, and together they break a rule that each one checked. No row is written twice, so row locks never conflict.

Write Skew, Step by Step

The rule: at least one doctor must be on call. Alice and Bob are both on call, and both click "go off call" at the same moment. Each transaction checks the rule first.

Write skew is behind real bugs: double-booking a meeting room ("no overlapping booking exists → insert"), two users claiming the same username when uniqueness is checked in code, and an account balance rule spread across several rows ("the sum of all accounts must stay ≥ 0").

The Causal Chains

The Naive Mistake

// "We use SERIALIZABLE, so we're safe"
await db.query('BEGIN ISOLATION LEVEL SERIALIZABLE')
await db.query('...')
await db.query('COMMIT')     // throws 40001 under contention → request fails with a 500

SERIALIZABLE does not prevent conflicts; it detects them and makes you retry. Without a retry loop, it turns a data bug into an error page.

The Right Pattern

async function withSerializableRetry(fn, maxAttempts = 5) {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect()
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE')
      const result = await fn(client)
      await client.query('COMMIT')
      return result
    } catch (err) {
      await client.query('ROLLBACK').catch(() => {})
      // 40001 = serialization_failure, 40P01 = deadlock_detected: both are safe to retry
      const retryable = err.code === '40001' || err.code === '40P01'
      if (!retryable || attempt === maxAttempts) throw err
      await new Promise(r => setTimeout(r, 2 ** attempt * 10 + Math.random() * 20))  // backoff + jitter
    } finally {
      client.release()
    }
  }
}

The whole transaction — reads included — must be inside fn, because the retry needs a fresh snapshot. Keep side effects (emails, payments, API calls) outside it, or the retry repeats them.

Which level when:

• Default web request: READ COMMITTED + atomic updates or FOR UPDATE for read-modify-write.
• Reports, exports, anything that reads several times and must be internally consistent: REPEATABLE READ (read-only, so no retries needed in practice).
• A business rule that spans rows and can't be expressed as a constraint: SERIALIZABLE with a retry loop.

Practice Problems

Problem 1. A ticketing service checks SELECT COUNT(*) FROM tickets WHERE event_id = $1 against the venue capacity, then inserts a ticket. It runs under READ COMMITTED. Under load, events oversell. Which anomaly is this, would REPEATABLE READ fix it, and what would you do?

Answer sketch: phantom / write skew — each transaction counts, both see room, both insert new rows. REPEATABLE READ does not fix it (different new rows, no conflict). Fixes: SERIALIZABLE with retries; lock a parent row (SELECT ... FROM events WHERE id = $1 FOR UPDATE) so bookings for one event serialise; or keep a seats_left counter and use UPDATE events SET seats_left = seats_left - 1 WHERE id = $1 AND seats_left > 0.

Problem 2. A monthly finance report runs five queries (revenue, refunds, net, per-region totals) and the numbers sometimes don't reconcile. Nothing is wrong with the SQL. What's happening?

Answer sketch: under READ COMMITTED each query sees a different snapshot, so orders committed between queries appear in some totals but not others. Run the report in one REPEATABLE READ (read-only) transaction.

Problem 3. Your teammate says "REPEATABLE READ prevents phantoms in Postgres, so it's equivalent to SERIALIZABLE." Give a concrete counterexample.

Answer sketch: the doctors-on-call write skew above — no phantoms, no non-repeatable reads, and the rule is still broken. Only SERIALIZABLE (or explicit locking) prevents it.

The Rule You Keep Forever

Isolation is about when your snapshot is taken. READ COMMITTED: per statement. REPEATABLE READ: per transaction. SERIALIZABLE: per transaction, plus conflict detection. Neither of the first two stops write skew, and anything above READ COMMITTED needs retry logic for error 40001.

Chapter 15: Locks — Why Queries Block Each Other

The Physical Reality

When two transactions want to modify the same row, one must wait. PostgreSQL uses locks to coordinate this.

There are two types of locks you must understand:

Row-level locks: When you UPDATE or DELETE a row, PostgreSQL places a lock on that specific row. Other transactions that want to UPDATE or DELETE the same row must wait. Readers (SELECT) are not blocked — MVCC handles that.

Table-level locks: Certain operations lock the entire table. DDL statements like ALTER TABLE, CREATE INDEX (without CONCURRENTLY), VACUUM FULL — these acquire an exclusive lock on the table. No reads or writes to that table can proceed while the lock is held.

The Causal Chains

Deadlocks — The Concrete Example

A sends ₹100 from account 1 to account 2. At the same moment, B sends ₹50 from account 2 to account 1.

The fix: Always acquire locks in a consistent order. If every transaction locks row 1 before row 2, deadlocks on this pair are impossible.

Advisory Locks

PostgreSQL provides application-level locks that you can take and release manually:

SELECT pg_try_advisory_lock(12345);  -- try to acquire lock for key 12345
-- returns true if acquired, false if already held by another session

These are useful for preventing concurrent execution of a critical section without locking any actual rows or tables:

-- Ensure only one process runs the billing job at a time
SELECT pg_try_advisory_lock(hashtext('billing_job'));
-- if true: run the job
-- if false: another process is already running it, skip

The Rule You Keep Forever

Keep transactions short. The longer a transaction lives, the more it blocks other queries. Long transactions are the most common cause of unexplained latency spikes in production PostgreSQL systems.

Chapter 16: The Race Condition You Will Definitely Hit

The Physical Reality

The pattern: read a value, make a decision, write based on that decision. This is called Check-Then-Act, and it is broken under concurrent access unless you explicitly lock.

-- User tries to claim a coupon (only 1 use allowed)
SELECT uses FROM coupons WHERE code = 'SAVE20';  -- returns 0
-- application decides: 0 < 1, so use is allowed
UPDATE coupons SET uses = uses + 1 WHERE code = 'SAVE20';

Under concurrent access, two requests can both read uses = 0, both decide it is allowed, and both update. The coupon is used twice.

Step through it — and then through each fix. Predict before you reveal.

The Fix: SELECT FOR UPDATE

BEGIN;
SELECT uses FROM coupons WHERE code = 'SAVE20' FOR UPDATE;
-- This locks the row. Any other transaction that tries to SELECT FOR UPDATE
-- on the same row will wait until this transaction commits.
-- No two transactions can read and then update this row concurrently.

-- Now check and update safely
UPDATE coupons SET uses = uses + 1 WHERE code = 'SAVE20' AND uses < 1;
-- Check return count: if 0 rows updated, coupon was already used
COMMIT;

Or use an atomic update and check the result:

-- Atomic: increment only if under limit
UPDATE coupons
SET uses = uses + 1
WHERE code = 'SAVE20' AND uses < 1
RETURNING uses;
-- If no rows returned: limit already reached. Reject.
-- If rows returned: successfully claimed.

The atomic update approach is better — no explicit transaction needed, no lock held between queries.

The Causal Chains

The Rule You Keep Forever

Whenever your logic is "read a value, decide, write based on that decision" — that is a race condition waiting to happen. Either lock with SELECT FOR UPDATE, or write an atomic UPDATE that checks the condition itself.

PART 6: PATTERNS EVERY SDE1/SDE2 MUST KNOW

Chapter 17: Connection Pooling — Why Your App Breaks at 200 Users

The Physical Reality

PostgreSQL is not like most server software. Most servers use threads or async I/O to handle many clients efficiently. PostgreSQL spawns a separate OS process for every connection.

What does that mean?

• A new connection → PostgreSQL forks a new OS process → allocates ~5-10MB of RAM for its working memory → creates a file descriptor table → registers with the OS scheduler
• 100 connections → 100 OS processes → ~500MB to 1GB of RAM just for connection overhead → the OS scheduler context-switches between 100 processes constantly → CPU overhead grows

At 200 connections → the database spends a meaningful fraction of its CPU just managing connections rather than executing queries.

At 500+ connections → performance degrades noticeably. At 1000+ → PostgreSQL may become unstable.

The Causal Chains

The Fix: pgBouncer

pgBouncer is a connection pooler that sits between your app and PostgreSQL. Your app connects to pgBouncer (which accepts thousands of connections cheaply). pgBouncer maintains a small pool of real PostgreSQL connections and multiplexes app connections onto them.

100 app connections → pgBouncer → 10 real PostgreSQL connections

pgBouncer has two relevant modes:

Transaction mode: A real connection is assigned to an app connection only for the duration of a transaction. Between transactions, the real connection is returned to the pool. This allows maximum multiplexing.

Session mode: A real connection is held by an app connection for its entire lifetime. Less multiplexing, but supports session-level features (prepared statements, SET variables, advisory locks).

The causal chain with pgBouncer:

• If you use transaction mode → 1000 app connections can share 20 real connections → PostgreSQL sees 20 processes → low overhead → works
• If you use transaction mode with prepared statements → on PgBouncer older than 1.21 they break (a statement prepared on one real connection doesn't exist on the next) → upgrade and set max_prepared_statements (1.21+ tracks protocol-level prepared statements for you), or disable prepared statements in the driver
• If you use transaction mode with session state → SET (e.g. SET search_path, SET app.tenant_id), session-level advisory locks, LISTEN, and temporary tables leak to whichever app connection gets that real connection next, or vanish → use SET LOCAL inside a transaction, transaction-level advisory locks (pg_advisory_xact_lock), or session mode for those connections

The Rule You Keep Forever

Each database connection is an OS process consuming RAM and CPU. Connection pools are not an optimization — they are mandatory above trivial traffic. Without them, you will hit PostgreSQL's connection limit and every user gets an error.

Chapter 17A: Read Replicas — Scaling Reads Without Scaling Your Primary

Chapter 17 showed you that each database connection is an OS process consuming RAM and CPU, and that connection pooling is mandatory above trivial traffic. A connection pool solves the problem of too many connections to one PostgreSQL server. This chapter addresses a different problem: what happens when one PostgreSQL server cannot handle the volume of reads your application generates, no matter how well you pool connections?

The answer is to add more servers. But not in the way you might think. You do not add a second primary and split your writes. You add servers whose only job is to answer read queries — servers that are always in sync with your primary, that require no application-level data synchronisation logic, and that PostgreSQL handles entirely through a mechanism called streaming replication. These are read replicas.

After this chapter, you will understand the physical mechanism of replication, what replication lag means and why it exists, how to route queries correctly between primary and replica, and — most importantly — the specific class of bug that destroys data integrity if you route naively. That bug has hit production systems at real companies. It will not hit yours.

Part 1: The Physical Mechanism — How a Replica Stays In Sync

To understand replicas, you need to understand one physical fact about PostgreSQL that was touched on briefly in Chapter 4 (MVCC): every change PostgreSQL makes to data is first written to a log.

That log is called the Write-Ahead Log, or WAL. It sits on disk, ahead of the actual data files. When you run INSERT INTO users ..., PostgreSQL does not immediately write to the users heap file. It first appends a record to the WAL describing the change: "at LSN 0/1A3F2D8, insert row with these values into page 47 of the users table." Then it writes to the heap file. The WAL is the source of truth for everything that has happened to the database.

The WAL exists primarily for crash recovery — if PostgreSQL crashes mid-write, it can replay the WAL on restart to restore the database to a consistent state. But it turns out the WAL is also the perfect mechanism for replication, because it is a complete, ordered log of every change.

Here is what streaming replication does physically:

Primary server:
  Write → WAL record appended → Data page updated → WAL shipped to replica

Replica server:
  Receive WAL record → Apply it to local data pages → Replica now matches primary

The replica runs a background process called the WAL receiver. It connects to the primary's WAL sender process and requests WAL records starting from a given position (called an LSN — Log Sequence Number). The primary streams new WAL records to the replica as they are generated. The replica applies them to its local data files in the same order they were applied on the primary.

The result: the replica's data files are a continuously updated copy of the primary's data files. Any SELECT query you run on the replica will see the same data as the primary — with one critical caveat we will get to shortly.

The replica is read-only by design. PostgreSQL enforces this. Attempting to write to a replica returns an error. This is intentional — allowing writes to a replica would create conflicts with the primary's WAL stream, and there is no conflict resolution mechanism in streaming replication. Writes go to the primary. Reads can go anywhere.

Part 2: Replication Lag — The Physics You Cannot Avoid

The replica applies WAL records continuously, but it cannot apply them instantaneously. There is a physical delay between when a write occurs on the primary and when that write is visible on the replica. This delay is called replication lag.

In a healthy system on the same data centre, lag is typically 10–100 milliseconds. Under heavy write load, or when the replica's disk is slower than the primary's, lag can grow to seconds. In pathological cases — a replica that was offline and is catching up, or a primary under an extreme write burst — lag can be minutes.

Replication lag is not a bug. It is a physical consequence of the fact that WAL records take time to travel over the network, and the replica takes time to apply them to disk. You cannot eliminate lag. You can only measure it and design your application around it.

Measure lag from the primary:

-- Run this on the primary to see lag for each connected replica
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes,
  replay_lag
FROM pg_stat_replication;

The replay_lag column shows the time delay between when the primary wrote a WAL record and when the replica confirmed it applied that record. This is your lag. If it says 00:00:00.042, your replica is 42 milliseconds behind. If it says 00:00:08.310, your replica is 8 seconds behind — a warning sign.

You can also query the replica directly:

-- Run on the replica
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

This tells you how old the last replicated transaction is. If it says 5 seconds, every read from this replica is reading data that may be up to 5 seconds stale.

Part 3: What You Can and Cannot Route to a Replica

The rule sounds simple: send reads to the replica, writes to the primary. In practice, applying this rule naively produces one of the most insidious bugs in distributed systems.

Reads You Can Safely Route to a Replica

Analytics queries. Generating a report of AttemptIQ's attempt counts by subject over the past 30 days does not need real-time data. A few seconds of lag is irrelevant.

Background jobs. A nightly job that emails users their weekly performance summary can read from the replica. If the data is 50 milliseconds stale, nobody will notice.

Read-heavy pages where data changes infrequently. The question bank at AttemptIQ — the actual question content and explanations — changes rarely. Reading it from a replica is safe.

Dashboard widgets showing aggregate statistics. Total users, total attempts, average score — these numbers move slowly enough that replica lag is invisible.

The Write-Then-Read Bug

Here is the scenario that breaks applications. A user signs up for AttemptIQ. Your application:

1. Inserts the new user into the users table on the primary.
2. Redirects the user to their dashboard.
3. The dashboard page loads and queries SELECT * FROM users WHERE id = $1 — but this query is routed to the replica.
4. The replica has not yet replayed the INSERT. It returns zero rows.
5. Your application throws a 404 or crashes trying to render a non-existent user.

The user created their account successfully. The data is on the primary. But the replica has not caught up yet — even 50 milliseconds of lag is enough for this to happen on every signup if your code routes the immediate post-write read to the replica.

This is called the write-then-read problem, or the read-your-own-writes consistency violation. It is not a bug in PostgreSQL — it is an application-level routing error. PostgreSQL streaming replication is explicitly asynchronous by default. The primary does not wait for the replica to confirm before acknowledging the write to your application. This is the correct behaviour for performance. But it means you cannot assume a replica is immediately consistent after a write.

The bug is subtle because it is intermittent. In testing, your replica is usually caught up. In production under load, lag grows and the bug appears. Engineers often spend hours convinced the bug is in the application logic before realising it is a routing problem.

Part 4: Solving Write-Then-Read — The Routing Strategies

There are four approaches to solving this. They are ordered from simplest to most complex.

Strategy 1: Route All Reads After Writes to the Primary

The simplest fix: any query that follows a write in the same user session goes to the primary. This requires your application to track "has this user written anything in this session?"

In practice, the simplest implementation is a session-level flag. After any write, set a flag in the user's session or cookie indicating they should read from the primary. Clear the flag after a timeout (say, 5 seconds — long enough for the replica to catch up under normal conditions). This is sometimes called sticky primary reads or read-your-writes routing.

User writes → set session flag: "use primary for reads"
User reads  → flag set? → route to primary
             flag not set? → route to replica

This is the approach used by most SaaS products that deploy replicas. It is pragmatic: the vast majority of reads are not immediately post-write, so most reads still hit the replica. The flag catches the dangerous window.

Strategy 2: Route Writes and Their Dependent Reads Together

Some frameworks and ORMs support this at the connection level. Your database connection wrapper checks: "is there an active transaction? Did this request already perform a write?" If yes, all queries in that request go to the primary. If no writes have happened, queries go to the replica.

This is cleaner than the session-flag approach because it is request-scoped rather than session-scoped. It does require your connection wrapper to understand which pool (primary or replica) to use per query.

Strategy 3: Synchronous Replication (Nuclear Option)

PostgreSQL supports synchronous replication, where the primary waits for the replica to confirm it has applied a WAL record before acknowledging the write to the application. This eliminates lag — by definition, the replica is always up-to-date.

The cost: every write is now as slow as the round trip to the replica and its disk flush. If your replica is 5ms of network latency away, every write takes at least 5ms longer. Under high write load, this compounds. Synchronous replication is only appropriate when data loss is truly unacceptable (financial systems, medical records) and you have accepted the write latency cost explicitly.

For most SaaS products, synchronous replication is not the right answer. The write-then-read bug is better solved with routing logic than by making all writes slower.

Strategy 4: Application-Level Lag Awareness

Some sophisticated setups track the LSN of the primary after each write and then check whether the replica has replicated at least to that LSN before routing a read to it:

-- After a write on primary, capture its current LSN
SELECT pg_current_wal_lsn();  -- returns something like '0/1A3F2D8'

-- Before routing a read to the replica, check if replica has caught up
SELECT pg_last_wal_replay_lsn() >= '0/1A3F2D8';  -- true = safe to read

If the replica has not caught up to the required LSN, the read goes to the primary instead. This is precise — it routes to the replica the moment it is safe, rather than using a timeout. It is also complex to implement correctly and adds latency to post-write reads (you are making an extra query to check LSN). This approach is used by very high-scale systems that need to maximise replica utilisation. For AttemptIQ at current scale, Strategy 1 or 2 is the right starting point.

Part 5: When to Add a Read Replica

The right time to add a replica is not when you are in crisis. A replica takes time to set up and time to catch up from the primary's current state. Adding a replica during a traffic emergency is too late.

The right time is when you see warning signs. Specifically:

Your primary's CPU is consistently above 60–70% during normal traffic, not spikes. Sustained high CPU on a database server means queries are competing for execution time. Offloading reads to a replica gives the primary cycles back.

Your primary's pg_stat_activity shows many queries in state active or idle in transaction simultaneously. This means the connection pool is saturated and queries are queuing. A replica gives you a second pool to direct reads to.

Your EXPLAIN ANALYZE output shows queries that were previously fast now taking longer without any schema changes. This usually means the buffer cache is under pressure — more traffic is flooding the cache with more diverse pages, reducing hit rates. A replica effectively doubles your cache — each server has its own shared_buffers, and between them they can cache more of your working set.

The wrong reason to add a replica: "we want to be safe." A replica is not a backup. Streaming replication replicates data changes — including DROP TABLE, DELETE FROM table, and corruption events — to the replica in real time. If you drop a table on the primary, it disappears from the replica within milliseconds. A replica gives you read scale and high availability (if the primary goes down, a replica can be promoted). It does not protect against data loss from accidental mutations. For that, you need backups (pg_dump or continuous WAL archiving).

Part 6: Connection Pooling With Replicas

Chapter 17 introduced pgBouncer for pooling connections to a single server. With replicas, you need two pools: one pointing to the primary, one pointing to each replica.

Application
    │
    ├── pgBouncer (primary pool) → PostgreSQL primary
    │
    └── pgBouncer (replica pool) → PostgreSQL replica

Your application maintains two database connection strings. Write operations use the primary pool. Read operations use the replica pool (subject to the routing strategies in Part 4).

Most ORMs (Prisma, ActiveRecord, SQLAlchemy) have native support for read/write splitting — you configure a primary URL and a list of replica URLs, and the ORM handles the routing automatically for most queries. The write-then-read problem still exists with ORM-managed splitting — you need to configure the ORM's sticky-primary behaviour for post-write reads, or implement it explicitly.

In Prisma, for example:

const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.PRIMARY_DATABASE_URL },
  },
})

// For replica reads, use a separate client
const prismaReplica = new PrismaClient({
  datasources: {
    db: { url: process.env.REPLICA_DATABASE_URL },
  },
})

// Write → primary
await prisma.user.create({ data: { ... } })

// Immediately after: read from primary (not replica) to avoid write-then-read bug
const user = await prisma.user.findUnique({ where: { id: newUser.id } })

// Later: safe to use replica for general queries
const stats = await prismaReplica.attempt.groupBy({ ... })

Part 7: Monitoring What Matters

A read replica you are not monitoring is a liability. The two things to watch:

Replication lag. Set an alert if replay_lag exceeds your tolerance. For most SaaS applications, a lag alert threshold of 30 seconds is reasonable — at that point, reads from the replica are meaningfully stale and you should investigate why the replica is falling behind.

Replica availability. If the replica goes down, your application must fail over to the primary for all reads. If it does not, every read query fails. Your connection pool health checks should detect a dead replica and reroute automatically. Test this in a non-production environment before relying on it.

The query to run on the primary to see replica status:

SELECT
  application_name,
  state,            -- 'streaming' is healthy; 'catchup' means it's behind
  sync_state,       -- 'async' or 'sync'
  replay_lag
FROM pg_stat_replication;

If the query returns zero rows, no replica is connected to the primary. If state is not 'streaming', the replica is not keeping up.

The Rule You Keep Forever

A replica gives you more read capacity and a failover option. It does not give you consistency guarantees for post-write reads. Every team that adds replicas eventually hits the write-then-read bug. Build the routing strategy before you hit it, not after.

Vocabulary Map

Practice Problems

1. Failure analysis. An AttemptIQ user completes their Polity mock test and the results are saved (written to the primary). They are immediately redirected to their analytics dashboard. The dashboard shows their old analytics — the just-completed test is not reflected. The engineering team is confused because the data exists in the database (they can query it on the primary). What is the root cause? What is the minimal code change to fix it? What would you check in pg_stat_replication to confirm your diagnosis?

2. Design from scratch. You are adding a read replica to AttemptIQ. You have three types of queries: (A) fetching a user's current attempt session (written to within the last few seconds), (B) loading the question bank for a given subject (updated once a week when new questions are added), (C) generating a user's monthly performance report (reads from the last 30 days, no associated write in the same session). Design the routing strategy for A, B, and C. For query A, describe exactly when it is safe to route to the replica.

3. Synthesis. Your replica's replay_lag is normally under 100ms. One evening it grows to 45 seconds. Your monitoring alerts fire. Walk through the diagnostic process: what do you check first, second, third? Connect your answer to Chapter 3 (query planner), Chapter 4 (MVCC and VACUUM), and Chapter 17 (connection pooling) — each of these can be a root cause of a replica falling behind.

4. Go deeper. Synchronous replication eliminates lag by making the primary wait for the replica before acknowledging writes. But what happens if the replica goes offline during synchronous replication? Does the primary continue accepting writes? Research and reason about this: what is synchronous_standby_names and what happens if the named replica disappears? What does this mean for availability vs consistency tradeoffs in a SaaS product?

5. Connect to previous chapters. Chapter 2 introduced the buffer cache — a region of RAM where PostgreSQL keeps hot pages to avoid disk I/O. A read replica has its own separate buffer cache. How does this change your analysis of a cache-pollution problem from Chapter 2 (the analytics query that floods the cache)? Could you intentionally route heavy analytics queries to the replica specifically to protect the primary's buffer cache? What are the conditions under which this strategy works, and when does it backfire?

What's Coming Next

You now have the pattern toolkit for production PostgreSQL: connection pooling to manage concurrent access, read replicas to scale read throughput, and the routing discipline to avoid consistency bugs. Chapter 18 covers the last major operational risk — schema migrations on live tables. Every ALTER TABLE is either instant or it acquires a lock that stops your application. Knowing which is which, and how to migrate without downtime, is the difference between a routine Tuesday deployment and an hour of production outage.

Chapter 18: Schema Migrations Without Downtime

The Physical Reality

Most ALTER TABLE operations on large tables require rewriting every row. This takes time — minutes for large tables — and during that time, PostgreSQL holds an AccessExclusiveLock that blocks all reads and writes to the table.

An ALTER TABLE orders ALTER COLUMN amount TYPE bigint on a 10-million-row table → PostgreSQL locks the table → rewrites all 10 million rows → during this time, every query to orders is blocked → your app is down.

In PostgreSQL 11+, adding a nullable column with no default is instant (just a catalog update). Adding a column with a DEFAULT requires a rewrite in older PostgreSQL. This behavior changes across versions — know your version.

Common Operations and Their Lock Cost

ADD COLUMN (no default, nullable): AccessExclusiveLock, but held only for a catalog update (milliseconds), with no rows rewritten. The danger is the wait: the ALTER queues behind any open transaction on the table, and every new query queues behind the ALTER. Set lock_timeout (e.g. SET lock_timeout = '2s') and retry, so a long-running transaction can't turn a millisecond change into an outage.

ADD COLUMN DEFAULT value (PostgreSQL 11+): Instant for most cases. The default is stored in the catalog, not written to every row.

ADD COLUMN DEFAULT value (PostgreSQL 10 and below): Full table rewrite under AccessExclusiveLock. Production downtime.

CREATE INDEX: ShareLock for the whole build. Reads continue, but every INSERT, UPDATE and DELETE on the table blocks until the index is built. Use CREATE INDEX CONCURRENTLY instead — it builds the index without blocking writes, but takes longer, can't run inside a transaction, and leaves an INVALID index behind if it fails (drop it and retry).

DROP COLUMN: Does not physically remove data (just marks the column invisible). Fast. The space is only reclaimed when rows are later updated/vacuumed.

ADD CONSTRAINT CHECK: Full table scan to validate existing rows under AccessExclusiveLock. Can be slow. Add it as NOT VALID first (instant; new rows are checked from then on), then run ALTER TABLE … VALIDATE CONSTRAINT, which scans under a weaker lock that doesn't block reads or writes.

ALTER COLUMN TYPE: Usually a full table rewrite (plus rebuilding every index on the column) under AccessExclusiveLock — e.g. int → bigint. On a large table that is downtime. A few changes are catalog-only and instant: increasing a varchar(n) limit, varchar(n) → text, removing a length limit. For the rewriting cases, use expand-contract: add a new column, backfill in batches, keep it in sync with a trigger, then swap.

The Safe Migration Pattern

For any migration that would block on a large table, use the expand-contract pattern:

Adding a NOT NULL column:

-- Step 1: Add as nullable (instant catalog change; still takes a brief
-- AccessExclusiveLock, so set a lock timeout and retry if it can't get it)
SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN phone text;

-- Step 2: Backfill in batches (does not hold a long lock)
UPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN 1 AND 10000;
UPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN 10001 AND 20000;
-- ... repeat in batches of 10,000

-- Step 3: Prove the data is valid without blocking writes, then set NOT NULL.
-- SET NOT NULL alone scans the whole table under AccessExclusiveLock.
-- With a validated CHECK already in place, PostgreSQL 12+ skips that scan.
ALTER TABLE users ADD CONSTRAINT users_phone_not_null CHECK (phone IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_phone_not_null;  -- scans, but reads and writes continue
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;            -- instant: uses the constraint
ALTER TABLE users DROP CONSTRAINT users_phone_not_null;

Adding an index:

-- Never do this on a live table:
CREATE INDEX ON orders (user_id);  -- blocks all writes during build

-- Always do this instead:
CREATE INDEX CONCURRENTLY ON orders (user_id);
-- Takes longer but does not block reads or writes

The Rule You Keep Forever

Every schema migration is either instant, or it acquires a lock that blocks your table. Know which category your migration falls into before running it in production. When in doubt, use CONCURRENTLY for indexes and expand-contract for column changes.

Chapter 19: The Slow Query Workflow — Never Guess

The Physical Reality

When a query is slow, there are only a small number of root causes:

1. Missing index causing a seq scan on a large table
2. Wrong join order or join type
3. Stale statistics causing a bad plan
4. Lock contention (query is waiting, not executing)
5. The query is genuinely doing a lot of work (no fix except redesign)

The job of diagnosis is to figure out which one it is. You cannot figure that out without data. Never guess and add an index hoping it helps.

The Diagnostic Workflow

Step 1: Find the slow queries

-- Enable pg_stat_statements extension first:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 10 slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Step 2: Run EXPLAIN ANALYZE on the slow query

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

Step 3: Read the output — find the expensive node

Start from the innermost (most indented) nodes and work outward. Look for:

• Seq Scan on a large table → consider index
• rows=X vs actual rows=Y with large discrepancy → stale statistics, run ANALYZE
• Buffers: hit=10 read=50000 → 50,000 disk page reads → I/O bound → consider index or caching
• Sort Method: external merge Disk → sort spilled to disk → increase work_mem for this query

Step 4: Measure after the fix

Never declare victory without measuring. Run the query before and after. The EXPLAIN output should show different plan and lower actual time.

The Rule You Keep Forever

EXPLAIN ANALYZE → find expensive node → understand why → fix the root cause → measure again. Never add an index without running EXPLAIN first. Never call a query "fixed" without running EXPLAIN ANALYZE after.

PART 7: THE DECISION CHECKLISTS

These are rules you can apply immediately, without deep thinking, to make correct decisions 80% of the time. The other 20% requires the reasoning from the previous chapters.

The Indexing Checklist

IF query filters on this column AND < 5% of rows match      → add index
IF query filters on multiple columns together               → composite index (equality cols first)
IF you need sort AND filter on same column                  → single index covers both
IF column has < 10 distinct values (boolean, status enum)   → index probably useless
IF table gets heavy INSERT/UPDATE/DELETE                     → fewer indexes = better write throughput
IF you query a subset of columns always together            → covering index with INCLUDE
IF you need an index but cannot afford downtime             → CREATE INDEX CONCURRENTLY
IF index exists but query is still doing seq scan           → check selectivity, check statistics (ANALYZE)
IF query ALWAYS includes the same condition (deleted_at IS NULL, status = 'active')
                                                            → partial index with that condition
IF you need unique constraint only among active rows        → partial unique index WHERE deleted_at IS NULL
IF partial index WHERE clause involves now() or runtime val → use table partitioning instead
IF query uses lower(x), json->>'k', date(ts)                → expression index on that exact expression
IF query uses jsonb @> / ?, array &&, full-text @@          → GIN
IF query uses LIKE '%abc%' / ILIKE                         → GIN with pg_trgm
IF rows must not overlap (bookings, schedules)              → GiST exclusion constraint
IF table is huge, append-only, time-ordered                 → BRIN
IF a column changes on almost every update                  → avoid indexing it (keeps updates HOT)

The Schema Design Checklist

IF primary key needs to be globally unique but you want fast writes  → UUIDv7 or ULID (time-ordered)
IF primary key is internal only                                      → bigserial (simplest, fastest)
IF column is always required                                         → NOT NULL at DB level, not app level
IF you need to enforce referential integrity                         → foreign key (know the write cost)
IF data structure varies per row                                     → jsonb, but index keys you filter on
IF data structure is fixed across all rows                           → relational columns
IF column stores a moment in time                                    → timestamptz (never plain timestamp)
IF two tables are always read together                               → consider if they should be one table
IF one table is read frequently with narrow columns                  → separate wide rarely-read columns

The Transaction Checklist

IF two or more writes must both succeed or both fail        → wrap in transaction
IF you read then write based on what you read               → SELECT FOR UPDATE or atomic UPDATE
IF a rule spans several rows (capacity, on-call, balances)  → SERIALIZABLE + retry, or lock a parent row
IF a report runs several queries that must agree           → one REPEATABLE READ transaction
IF isolation is above READ COMMITTED                        → retry on 40001 (and 40P01), no side effects inside
IF you need idempotency                                     → INSERT ON CONFLICT DO NOTHING / DO UPDATE
IF your transaction is longer than 100ms                    → redesign to make it shorter
IF you need to lock without locking rows                    → pg_try_advisory_lock
IF you are retrying a failed write                          → ensure the operation is idempotent first

The Query Checklist

IF query is slow                                            → EXPLAIN ANALYZE first, guess nothing
IF EXPLAIN shows seq scan on large table                    → missing index or low selectivity
IF EXPLAIN estimated rows != actual rows (large gap)        → run ANALYZE tablename
IF you are looping and querying inside the loop             → N+1 problem → use JOIN or batch fetch
IF you are paginating with OFFSET on large datasets         → switch to cursor-based pagination
IF you JOIN on a column without an index on the larger side → add the index
IF you use NOT IN with a subquery                           → use NOT EXISTS instead (NULL safety)
IF you need top-N per group or latest row per user          → ROW_NUMBER() OVER (PARTITION BY …) or DISTINCT ON
IF you filter the right table of a LEFT JOIN                → put the condition in ON, not WHERE
IF you insert-or-update                                     → INSERT … ON CONFLICT DO UPDATE
IF you use SELECT *                                         → select only columns you need

The Read Replica Checklist

IF primary CPU > 60-70% sustained under normal traffic      → consider adding a read replica
IF query is analytics / reporting / background job          → safe to route to replica
IF query immediately follows a write in the same session    → route to primary (write-then-read bug)
IF query is a user reading their own just-created data      → route to primary
IF query reads infrequently-changing data (question bank)   → safe to route to replica
IF replica replay_lag > 30 seconds                          → alert, investigate, do not route reads there
IF replica goes down                                        → fail over all reads to primary automatically
NEVER assume a replica is a backup                          → replication copies deletes and drops too

The Migration Checklist

IF adding a nullable column (no default)                    → safe, instant, no lock
IF adding a column with DEFAULT (PostgreSQL 11+)            → safe, instant
IF adding a column with DEFAULT (PostgreSQL 10-)            → full rewrite, plan for downtime
IF creating an index on a live table                        → CREATE INDEX CONCURRENTLY
IF adding NOT NULL to existing column                       → expand-contract pattern
IF altering column type                                     → full rewrite, plan for downtime
IF adding a foreign key to large table                      → ADD CONSTRAINT ... NOT VALID, then VALIDATE separately
ALWAYS                                                      → test migrations on a copy of production first

FINAL THOUGHT

You started this book knowing how to call getAll() and findOne(). You finish it knowing what those functions are physically doing — which pages are read, which indexes are walked, which locks are held, which dead tuples are skipped.

That knowledge has one use: making confident predictions. Not memorizing rules. Not copying patterns. Predicting what a query will do before you run it, and knowing why.

The next time a query is slow, you will not reach for an index and hope. You will run EXPLAIN ANALYZE, read the plan, find the expensive node, understand the root cause, and fix it precisely.

That is the difference between an engineer who uses a database and an engineer who understands one.

End of PostgreSQL From First Principles