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

• Can you explain why SQL being declarative is what lets a query speed up after you add an index, with no change to the SQL?
• What does the planner use to decide between a sequential scan and an index scan — and what makes a query go "100× slower overnight"?
• In an EXPLAIN ANALYZE plan, which two numbers tell you the stats are stale?
• Why must a bank transfer be wrapped in a single transaction? Which ACID letter is that?
• Which isolation level prevents a dirty read, and what is a dirty read?

-- 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.

Next

You now know how a database plans, executes, and protects your queries — time to get fluent in the language itself, from joins to window functions. → SQL in Depth