Module F-13·20 min read

Schema modelling, B-tree indexes, what the query planner is actually deciding, and how one slow query turns into an upstream outage.

JJS

Written by Jatin Jain Saraf · Senior Software Engineer

Module F-13 — Database Fundamentals for Architects

Q: A team enforces "one pending cart per customer" in application code with a `SELECT` to check for an existing cart followed by an `INSERT`. Duplicates appear in production. What does the module say?

Check-then-insert is a race: two concurrent requests can both observe no pending cart and both insert. A `UNIQUE` or `EXCLUDE` constraint is atomic where a `SELECT` followed by an `INSERT` is not — and application validation additionally fails for other writers, bulk imports, and future code paths that forget the check. — The module lists concurrency first among the four ways application validation fails, and names this exact pattern: two simultaneous requests both see no pending order and both create one, because the constraint is atomic and the check-then-act sequence isn't. It also gives the `EXCLUDE (customer_id WITH =) WHERE (status = 'pending')` formulation as the enforcement. Option C is the most tempting wrong answer: a transaction gives atomicity and isolation, but under the common `READ COMMITTED` level two concurrent transactions can still both read "no cart" and both insert — this is the lost-update/write-skew family covered in Module P-1, and it needs either a constraint, `SERIALIZABLE`, or explicit locking. Option A proposes a heavier mechanism where a declarative constraint suffices, and triggers face the same concurrency question unless they rely on a constraint anyway. Option B invents a replica that the described flow doesn't involve.

Q: A query that ran in 20ms now takes 40 seconds with no code change. `EXPLAIN ANALYZE` shows a nested loop with `rows=1` estimated and `rows=48213` actual. What's the correct diagnosis and first action?

The planner's estimate is wrong by four orders of magnitude, so every decision built on it is wrong — a nested loop is reasonable for one row and catastrophic for 48,213. Fix the estimate (`ANALYZE` after bulk changes, extended statistics for correlated columns) and the plan generally corrects itself. — The module identifies the estimated-vs-actual row gap as the first thing to look at in a plan, precisely because the planner is guessing from statistics and a wrong guess invalidates every downstream choice. It lists the usual causes — stale statistics after a bulk load, correlated columns estimated as independent, parameter-sensitive queries, opaque functions — and states that fixing the estimate usually fixes the plan, whereas chasing the plan treats the symptom. Option B forces a plan without addressing why the planner chose wrongly, which leaves the same landmine for every other query on the table. Option C may or may not be warranted, but the row count alone doesn't indicate a missing index; the loop is slow because it repeats, not because the join lacks an index. Option D names a genuine alternative cause, but the evidence in the plan points squarely at the misestimate.

Q: During an incident, one query slows to 5 seconds and *every* endpoint becomes slow — including ones that don't touch the affected table. What's the mechanism, and which mitigation does the module put first?

Connection hold time rises, so by Little's Law concurrency rises until the pool is exhausted; unrelated endpoints then queue for connections, and the wait is invisible as a "slow query" because no query is running yet. A per-query `statement_timeout` is the cheap first defence, since it makes a slow query fail rather than occupy a connection. — This is the module's seven-step cascade, and the key insight is that the shared resource is the connection pool: 200 req/s × 5s is 1,000 concurrent requests competing for 20 connections per instance, so endpoints with no relationship to the slow table are queued behind it. The module notes the diagnostic trap — the time accumulates in pool-wait, not query execution — and lists `statement_timeout` first among the remedies, alongside bulkheads, shallow health checks, circuit breakers, and monitoring pool wait time. Option A misdescribes Postgres, which executes queries concurrently; replicas also wouldn't help writes or pool exhaustion. Option B misattributes it to bloat, which is a gradual degradation rather than an acute cascade, and `VACUUM FULL` takes an exclusive lock that would make the incident worse. Option D describes a real load-balancing concern from Module F-11 that isn't what's happening here.

What this module covers: The database is where most system design decisions become permanent, and where most outages originate. This module covers schema modelling and when denormalisation is actually justified, constraints as correctness guarantees that application code cannot replicate, B-tree indexes — including composite column order, covering indexes, partial indexes, and selectivity — the costs an index imposes on every write, what the query planner is really deciding and why stale statistics produce bad plans, MVCC and the bloat it creates, and finally the mechanism by which one slow query takes down an entire service.

The Schema Is the Most Expensive Decision You'll Make

Application code gets rewritten. Schemas accumulate data, and data is what makes change hard: a column type you regret is a migration on 400 GB while traffic continues (Module O-4), and a primary key you regret may be effectively permanent (Module P-3).

So schema decisions deserve the DesignDecision treatment from Module F-1 — with the accepted cost written down.

Analogy: the schema is the foundation and the plumbing. You can repaint any room whenever you like. Moving a load-bearing wall or re-routing the drains means occupying the whole building and hoping nothing else was resting on it.

Normalise First, Denormalise With a Reason

Normalisation stores each fact once. One row per customer; orders reference the customer by ID. Updating an address touches one row, and there's no way for two copies to disagree.

Denormalisation duplicates data to avoid a join — storing customer_name on the order row so rendering an order list needs no join at all.

Start normalised. It's the default because correctness is the default: if a fact exists in one place, it cannot be inconsistent with itself. Then denormalise when you can name all three of these:

The join is genuinely expensive at your data volume — verified with EXPLAIN ANALYZE, not assumed.
The read pattern is frequent enough that the saving matters.
Staleness is acceptable, or you have a reliable mechanism to keep the copy correct.

That third condition is the one that gets skipped, and it's what turns denormalisation into a data-integrity bug rather than an optimisation. A copied customer_name is wrong the moment the customer renames themselves, and now you need a trigger, an application-level fan-out update, or a CDC pipeline (Module P-23) to fix every copy. Which is fine — as long as it was a decision.

There's a legitimate special case worth naming: immutable historical facts. An invoice should store the customer's name and address as they were at the time of billing, not join to the current values. That isn't denormalisation, it's correct modelling — the invoice's copy is a different fact from the customer's current address, and the accounting requirement is that it never changes.

Types and constraints, briefly, because they're cheap to get right and expensive to fix:

Money is never a float. numeric/decimal, or integer minor units (cents). 0.1 + 0.2 !== 0.3 in binary floating point, and a payment ledger that drifts by fractions of a cent is a reconciliation nightmare (Module P-24).
Timestamps are timestamptz, always. A timestamp without time zone records an instant with no way to know which instant, and the bug appears at a DST boundary, months later, in another team's report.
JSONB for genuinely schemaless data, not as an escape from schema design. It's excellent for third-party payloads and sparse attributes, and it costs you type checking, foreign keys, and efficient indexing of the fields inside it.

Constraints are the part most worth defending:

sql

CREATE TABLE orders (
  id            bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  customer_id   bigint NOT NULL REFERENCES customers(id),
  status        text   NOT NULL CHECK (status IN ('pending','paid','cancelled')),
  total_cents   bigint NOT NULL CHECK (total_cents >= 0),
  created_at    timestamptz NOT NULL DEFAULT now(),
  -- One active cart per customer, enforced by the database rather than by hope.
  CONSTRAINT one_pending_per_customer
    EXCLUDE (customer_id WITH =) WHERE (status = 'pending')
);

The objection is always "we validate that in application code." Application validation fails in four ways the database doesn't:

Concurrency. Check-then-insert is a race. Two simultaneous requests both see no pending order and both create one. A UNIQUE or EXCLUDE constraint is atomic; a SELECT followed by an INSERT is not (Module P-1).
Multiple writers. A background job, a data migration, an admin script, and a second service in another language all bypass your validation layer.
Bulk operations. Imports and backfills routinely skip application logic entirely.
Bugs. A new code path forgets the check. The constraint doesn't forget.

Why this matters in production: a constraint converts a class of data corruption into a failed transaction. Failed transactions are visible, loud, and fixable. Corrupt data is silent, spreads into reports and downstream systems, and by the time someone notices you can't reconstruct what was true.

B-Tree Indexes: What They Actually Do

Postgres's default index is a B-tree: a balanced tree with all values in sorted order at the leaves, which are linked so a range scan can walk sideways. Lookup is O(log n) — for 100 million rows that's roughly 4–5 page reads instead of scanning 100 million.

What matters for design:

Sorted order means a B-tree serves more than equality. It handles =, <, >, BETWEEN, IN, ORDER BY, and prefix LIKE 'abc%' — all from the same structure, because the leaves are already in order. It cannot help LIKE '%abc' (no known prefix) or a function applied to the column (WHERE lower(email) = ...) unless you index that expression.

Composite index column order is not arbitrary. An index on (a, b, c) is usable for queries filtering on a, on (a, b), or on (a, b, c) — the leftmost prefix rule — and is essentially useless for a query filtering only on b.

sql

CREATE INDEX ON orders (customer_id, created_at DESC);

-- Uses the index fully: equality on the leading column, then ordered range.
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

-- Cannot use it: no predicate on the leading column.
SELECT * FROM orders WHERE created_at > now() - interval '1 day';

The practical rule: equality columns first, then the range or sort column. That ordering is exactly what Module F-8's cursor pagination needs, and it's why the cursor query specified (created_at DESC, id DESC) as a matched composite.

A covering index avoids touching the table. If every column a query needs is in the index, Postgres can answer from the index alone — an index-only scan, skipping the heap fetch entirely:

sql

-- Postgres answers this without reading the table at all.
CREATE INDEX ON orders (customer_id) INCLUDE (status, total_cents);

For a hot query this is a large win, because the heap fetch is a random page read per row (Module F-2's random-access penalty, in its most common production form).

A partial index indexes only the rows you query. If 99% of orders are completed and you only ever query the pending ones:

sql

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

Smaller index, cheaper writes for rows outside the predicate, and it fits in memory more easily. Under-used and often exactly right.

Selectivity decides whether an index helps at all. An index earns its keep by eliminating most rows. An index on a boolean column with a 50/50 split doesn't — the planner will correctly ignore it and scan, because reading half the table via random index lookups is slower than reading all of it sequentially. Which brings us to the planner.

The Query Planner: Estimates, Not Facts

The planner chooses how to execute a query: which index (if any), which join algorithm, in what order. It does this by estimating the cost of alternatives using table statistics — approximate row counts, value distributions, most-common values, and correlation.

This is the key insight: the planner is guessing, informed by statistics. When the statistics are wrong, the guess is wrong, and a query that ran in 20ms yesterday takes 40 seconds today with no code change.

Common causes of a bad plan:

Stale statistics after a bulk load. You insert 5 million rows; the planner still believes the table has 1,000 and picks a nested loop that's catastrophic at the real size. Fix: ANALYZE after bulk changes.
Correlated columns. The planner assumes independence by default, so WHERE city = 'Mumbai' AND country = 'India' gets its selectivity estimated as the product of two probabilities and comes out far too low. Fix: extended statistics (CREATE STATISTICS).
Parameter-sensitive queries. One customer has 3 orders, another has 400,000. The best plan differs, and a single cached plan can't be right for both.
Functions the planner can't see into. A user-defined function in a predicate gets a default selectivity guess, which may be nowhere near reality.

Reading EXPLAIN (ANALYZE, BUFFERS) is the skill that makes this actionable. The thing to look at first is not the total time — it's the gap between estimated and actual row counts:

text

Nested Loop  (cost=0.29..8.32 rows=1 width=64) (actual time=0.02..3021.55 rows=48213 loops=1)
                                    ^^^^^^                                  ^^^^^^^^^^
                              planner expected 1                      reality was 48,213

An estimate off by four orders of magnitude means every decision built on it is wrong. The nested loop was a reasonable choice for one row and a disaster for 48,213. Fix the estimate and the plan usually fixes itself — chasing the plan directly (with hints or query rewrites) treats the symptom.

Sequential scans are not automatically bad. For a large fraction of a table, one sequential read at multi-GB/s genuinely beats hundreds of thousands of random page fetches — the planner is doing Module F-2's arithmetic, and it's usually right. A Seq Scan in a plan is only a problem when it's scanning a lot to return a little.

MVCC, and Why Deleted Rows Still Cost You

Postgres uses multi-version concurrency control: an UPDATE doesn't overwrite a row, it writes a new version and marks the old one dead. A DELETE just marks. Readers see the version that was current when their transaction began.

The good part: readers never block writers, and writers never block readers. This is why Postgres handles mixed read/write workloads well, and it's the foundation of the isolation levels in Module P-1.

The cost: dead rows accumulate as bloat. Consequences that show up as mysterious slowdowns:

Tables and indexes grow beyond their live data. A table with 1M live rows can occupy the space of 5M, and a sequential scan reads all of it — dead rows included.
VACUUM must reclaim the space, and autovacuum can fall behind on write-heavy tables. When it does, performance degrades gradually until someone notices the table is four times larger than it should be.
A long-running transaction blocks cleanup of anything newer than it. An idle-in-transaction connection — often a forgotten BEGIN in application code, or an analyst's open session — prevents vacuum from removing dead rows across the whole database. This is a genuinely common and genuinely surprising cause of runaway bloat.
Frequently-updated rows are the worst case. A counter row updated 1,000 times/second generates 1,000 dead versions per second. This is a design smell that points at Redis or an append-and-aggregate pattern instead.

How One Slow Query Takes Down a Service

Here's the cascade, and it's worth being able to draw from memory because it's the most common outage shape in a database-backed system:

text

1. A query that normally takes 20ms starts taking 5s.
   (A bad plan after a bulk load, a missing index on a table that grew,
    a lock wait, or bloat.)

2. Each request now holds its database connection 250× longer.
   Little's Law (F-4): concurrency = arrival rate × latency.
   200 req/s × 5s = 1,000 concurrent requests needing connections.

3. The connection pool (say 20 per instance) is exhausted. Requests queue
   for a connection — and this wait is invisible as a "slow query" because
   no query is running yet.

4. Every endpoint slows down, including ones that don't touch the slow table,
   because they're queued behind it for pool capacity.

5. Health checks that query the database start timing out. If the health check
   is deep (F-11), instances are marked unhealthy and removed — now the
   surviving instances get more load.

6. Clients time out and retry, adding load (A-6). Users refresh. Load doubles.

7. Total outage, from one query.

Every step is a different module's subject, which is the point: this cascade is the through-line connecting them. What breaks the chain:

A per-query timeout (statement_timeout) — a slow query fails instead of occupying a connection indefinitely. Cheap, effective, and routinely absent.
Bulkheads — separate pools for critical and non-critical paths, so a slow report can't consume checkout's connections (Module A-7).
Shallow health checks — so a database problem doesn't become an infrastructure problem (Module F-11).
Circuit breakers — fail fast rather than queueing behind a dependency that's already gone (Module A-7).
Monitoring the pool wait time, not just query duration. The queue is where the latency actually accumulated.

Where This Shows Up in Your Stack

PostgreSQL: pg_stat_statements for which queries consume total time (often a fast query called constantly, not the slowest one), pg_stat_user_tables for sequential scans on large tables and dead-tuple counts, and pg_locks when things are stuck. Set statement_timeout and idle_in_transaction_session_timeout on every application role.
ORMs: lazy-loaded relations are the standard cause of server-side N+1 (Module F-8), and ORM-generated queries can be hard for the planner to handle well. Log the generated SQL in development — the gap between what you wrote and what ran is where the surprises live.
Node.js: a connection is held for the duration of the surrounding await chain. An HTTP call made while holding a transaction open ties a database connection to a third party's latency, which is how one slow vendor exhausts your pool.
Redis: the right home for the high-frequency counter that would otherwise generate a thousand dead row versions per second. Recognising that pattern is a schema decision, not a caching decision.

Summary

The schema is the most expensive decision — data makes change hard, so schema choices deserve explicit cost accounting.
Normalise first; denormalise only when the join is measurably expensive, the read is frequent, and staleness is acceptable or actively maintained. Immutable historical facts are a separate, legitimate case.
Money as numeric or integer minor units; timestamps as timestamptz; JSONB for genuinely schemaless data, not as an escape from modelling.
Constraints beat application validation because they survive concurrency, multiple writers, bulk operations, and bugs — and they convert silent corruption into a loud failed transaction.
B-trees serve equality, ranges, ordering, and prefix matching. Composite indexes obey the leftmost-prefix rule: equality columns first, then range/sort. Covering indexes enable index-only scans; partial indexes are under-used.
Selectivity decides whether an index helps. A low-cardinality column rarely benefits, and the planner is right to ignore it.
The planner estimates from statistics. Bad plans usually mean bad estimates — from stale stats, correlated columns, or parameter-sensitive queries. Compare estimated vs actual rows in EXPLAIN ANALYZE first, and fix the estimate rather than the plan.
MVCC gives non-blocking reads and costs you bloat. Watch autovacuum, and know that one long-running transaction blocks cleanup database-wide.
One slow query cascades: longer connection hold → pool exhaustion → every endpoint slows → deep health checks fail → retries pile on → outage. Break it with statement_timeout, bulkheads, shallow health checks, and pool-wait monitoring.

The next module, Choosing a Data Model: SQL vs NoSQL, steps back from Postgres specifically to the prior question — given your access patterns, which shape of datastore should be holding this data at all?

Knowledge Check

A team enforces "one pending cart per customer" in application code with a SELECT to check for an existing cart followed by an INSERT. Duplicates appear in production. What does the module say?

A query that ran in 20ms now takes 40 seconds with no code change. EXPLAIN ANALYZE shows a nested loop with rows=1 estimated and rows=48213 actual. What's the correct diagnosis and first action?

During an incident, one query slows to 5 seconds and *every* endpoint becomes slow — including ones that don't touch the affected table. What's the mechanism, and which mitigation does the module put first?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule F-12: Caching Fundamentals Next Module F-14: Choosing a Data Model: SQL vs NoSQL

Discussion

Join the discussion

Loading comments...