Inside a Database II — Queries & Transactions

A query's life: from text to result

When you send SELECT … FROM … WHERE … to Postgres or DuckDB, it doesn't just "run." Your query passes through a small assembly line of stages, each handing its work to the next:

Read that left to right. Parse checks your SQL is well-formed and turns the text into a tree the engine can reason about. Plan enumerates the ways the result could be produced — scan the whole table, or use an index? join table A to B, or B to A? Optimize estimates the cost of each candidate and picks the cheapest. Execute runs that winning plan, pulling bytes from disk (or cache) and applying the algorithms from chapter 02. Return streams the rows back to you.

The query planner: a cost estimator that guesses

The planner (also called the optimizer) is the brain of the database. Its job is to look at all the plausible plans and pick the one it believes is cheapest. But "cheapest" is a guess — the planner hasn't run anything yet. So how does it guess?

It keeps statistics about your data: roughly how many rows are in each table, how many distinct values a column has, how those values are spread out, what fraction is the most common value. From those numbers it estimates things like "WHERE age > 30 will match about 12% of rows." That estimate drives the decision:

• If a filter matches most of the table, reading the whole table top to bottom (a sequential scan) is actually cheapest — an index would just add overhead.
• If a filter matches a tiny slice, jumping straight to those rows through an index (an index scan) wins big.

This is why the same query can get a different plan as your data grows. A table with 100 rows is faster to scan whole; the planner ignores the index. The same table at 10 million rows tips the math, and the planner switches to the index — automatically. You didn't change the query; the data changed, so the best how changed.

Reading EXPLAIN: seeing the plan

You don't have to guess what the planner decided — you can ask it. Put EXPLAIN in front of any query and the database prints the plan it would run, without running it. Add ANALYZE and it actually runs the query and reports real timings and row counts beside its estimates. Let's do it. (Postgres syntax shown; DuckDB is nearly identical — use EXPLAIN there too.)

-- a throwaway table with a million rows
CREATE TABLE events AS
  SELECT g AS id,
         (random() * 1000)::int AS user_id,
         (random() * 100)::int  AS amount
  FROM generate_series(1, 1000000) AS g;

-- no index yet: how does the engine find one user's events?
EXPLAIN ANALYZE
SELECT * FROM events WHERE user_id = 42;

A plan is a tree, read from the most-indented node outward (leaves run first, feeding their parents). For the query above, with no index, you get a single leaf:

Now add an index and re-run the exact same query:

CREATE INDEX idx_events_user ON events (user_id);
ANALYZE events;            -- refresh stats so the planner knows about the index

EXPLAIN ANALYZE
SELECT * FROM events WHERE user_id = 42;

When a query joins two tables, the plan grows a join node with two children — each child a scan, the parent combining them:

That last check is gold. EXPLAIN ANALYZE prints both the planner's estimate and the real count for every node. When they diverge wildly — "estimated 50 rows, got 4 million" — you've found why the plan is bad: the planner was guessing from stale or skewed statistics, exactly the stale-stats trap from the section above.

Transactions: all-or-nothing units of work

So far we've read data. Changing data raises a harder question: what if a change is really several changes that must succeed together? The classic example is a bank transfer. Moving $100 from Alice to Bob is two operations:

BEGIN;                                              -- start a transaction
  UPDATE accounts SET balance = balance - 100 WHERE name = 'alice';
  UPDATE accounts SET balance = balance + 100 WHERE name = 'bob';
COMMIT;                                              -- make both permanent

Imagine the server crashes between those two updates. Alice has been debited; Bob was never credited. $100 has vanished. That must never be allowed to happen. A transaction is the database's promise that the operations inside it are a single all-or-nothing unit: either every statement takes effect, or none does. The three commands that bound it:

• BEGIN — open a transaction; nothing inside is visible to others yet.
• COMMIT — finalize; all changes become permanent and visible at once.
• ROLLBACK — abandon everything since BEGIN, as if it never happened.

ACID, plainly

The transaction guarantee has four parts, remembered by the acronym ACID. Each one maps to a worry a data engineer who handles money, ledgers, or billing actually loses sleep over:

Isolation levels: how much do transactions hide from each other?

Isolation is the trickiest letter, because perfect isolation is expensive. If every transaction had to run completely alone, your database could only do one thing at a time — useless under load. So databases offer a dial: isolation levels, trading concurrency for protection. The higher the level, the more anomalies it prevents, but the less work can happen in parallel.

First, the three classic anomalies — bad things that can happen when transactions overlap:

• Dirty read — you read a row another transaction changed but hasn't committed; it might still ROLLBACK, so you read a value that never officially existed.
• Non-repeatable read — you read a row twice in one transaction and get different values because someone else committed a change in between.
• Phantom read — you run the same WHERE twice and a new row appears (or vanishes) because someone else inserted/deleted matching rows.

* The SQL standard allows phantoms at Repeatable Read; Postgres's snapshot-based Repeatable Read actually prevents them too, and reserves true serial-equivalent behavior for Serializable.

A taste of how: MVCC

You might assume isolation means locking every row a reader touches so writers can't change it — but that would stall the whole system. Instead, Postgres uses MVCC (multi-version concurrency control): every update writes a new version of the row rather than overwriting the old one, and each transaction sees the snapshot of versions that existed when it began. The payoff is the famous rule — readers never block writers and writers never block readers — because they're simply looking at different versions of the same row.

✓ Check yourself

Answer from memory before opening each one.

1. You create an index and a query that took 40 ms now takes under 1 ms, although not one character of its SQL changed. Which stage of the query's life made a different decision, and what property of SQL makes that possible?

   Show answer

   The plan/optimize stage: it enumerates the ways to produce the result, estimates each one's cost, and now has a cheaper candidate — an index scan — to pick. That works because SQL is declarative: you state what rows you want, never how to fetch them, so the engine is free to change the how while the what stays the same. See A query's life.

2. A table has an index on status, yet EXPLAIN for WHERE status = 'active' shows a Seq Scan. About 95% of rows are active. A teammate concludes the planner is broken. What is actually going on?

   Show answer

   The planner is doing its job. From its statistics it estimates the filter matches most of the table, and when that is true reading the table top to bottom is cheapest — going through the index would only add overhead on the way to nearly every row anyway. An index scan wins when the filter selects a tiny slice; the same goes for a very small table, which is quicker to scan whole. See The query planner.

3. The morning after a bulk load, a report crawls. EXPLAIN ANALYZE shows a node where the planner estimated 50 rows and the actual count was 4,000,000. What does that gap tell you, why does it produce a slow plan, and what do you run?

   Show answer

   The statistics are stale: the planner is still costing plans from the pre-load row counts, so it chose a plan tuned for a tiny result and got crushed by a huge one. Comparing the estimated and actual rows on each node is the check that exposes it. Refresh the stats with ANALYZE your_table; — autovacuum normally does this, but a big bulk load can outrun it. See the "100× slower overnight" mystery and Reading EXPLAIN.

4. The server loses power after Alice's debit has run but before Bob's credit, inside a single BEGIN … COMMIT. What do the two balances look like when the database comes back up, and which ACID property is that? Which different property covers a crash one second after COMMIT?

   Show answer

   Both balances are as they were before BEGIN: the uncommitted debit is rolled back, as if the transfer never started. That is atomicity — all statements take effect or none do. A crash after COMMIT is durability: once committed, the transfer survives the crash. See Transactions and ACID, plainly.

5. Inside one transaction at Postgres's default isolation level, a reconciliation job runs SELECT SUM(balance) FROM accounts twice and gets two different totals. Nothing rolled back anywhere. Which anomalies could explain it, and which isolation level rules each one out?

   Show answer

   The default is Read Committed, which only prevents dirty reads. If another transaction committed an update to a balance between the two reads, that is a non-repeatable read, prevented from Repeatable Read upward. If it committed an insert or delete of an account, that is a phantom read, which the SQL standard only rules out at Serializable (Postgres's snapshot-based Repeatable Read happens to prevent it too). It cannot be a dirty read — nothing uncommitted was visible. See Isolation levels.

6. After that incident, someone proposes setting every transaction in the system to Serializable "to be safe". What does that cost, and how do you decide which transactions deserve it?

   Show answer

   Serializable behaves as if transactions ran one at a time, so the engine must hold more locks or do more conflict-checking and fewer transactions can run concurrently — throughput under load is the price. Decide by what an anomaly would cost: where a phantom or changed row would corrupt a total, as in financial settlement, pay for Serializable; for everyday work, Read Committed is the sweet spot, which is why most databases default to it. See the tradeoff, and a sane default.

-- BEFORE the index:
EXPLAIN ANALYZE SELECT * FROM events WHERE user_id = 42;
--   -> Seq Scan on events, Filter: (user_id = 42),
--      ~1,000,000 rows read, ~999,000 Rows Removed by Filter,
--      Execution Time on the order of tens of ms.

CREATE INDEX idx_events_user ON events (user_id);
ANALYZE events;

-- AFTER the index:
EXPLAIN ANALYZE SELECT * FROM events WHERE user_id = 42;
--   -> Index Scan using idx_events_user on events,
--      no "Rows Removed by Filter", the B-tree jumps straight
--      to matches, Execution Time drops 10-100x.

Takeaway

• SQL is declarative: you state the rows you want and the planner chooses the scan, join order and algorithm by estimated cost.
• The planner works from statistics, so a table that grew after the last ANALYZE can flip a fast plan to a slow one with no code change.
• Read EXPLAIN ANALYZE for a sequential scan on a large table with a selective filter, the node with the most time, and a large gap between estimated and actual rows.
• A transaction is all-or-nothing: if anything fails between BEGIN and COMMIT, ROLLBACK undoes every statement, including after a crash.
• ACID is atomicity, consistency, isolation and durability, and a reconciled ledger depends on all four.
• Isolation levels trade concurrency for protection from dirty reads, non-repeatable reads and phantoms. Postgres defaults to Read Committed and uses MVCC so readers do not block writers.

You know how a database plans and protects a query, so next we get fluent in the language itself. → SQL in Depth

Further reading

• Using EXPLAIN — PostgreSQL documentation. How to read plan nodes, costs, estimated versus actual rows and buffer counts.
• Routine Vacuuming — PostgreSQL documentation. Includes how ANALYZE keeps the planner's statistics current.
• Concurrency Control: Introduction — PostgreSQL documentation. How MVCC lets readers and writers work without blocking each other.
• Transaction Isolation — PostgreSQL documentation. What each isolation level allows in Postgres, which differs from the SQL standard's minimums.
• A Critique of ANSI SQL Isolation Levels — Berenson et al., SIGMOD 1995. Why the standard's three anomalies are not enough to define isolation.