postgres://internals

One Query, End to End

// the problem

You've taken Postgres apart layer by layer. Now let's put it back together by following one ordinary query all the way down — from the moment it arrives on a connection to the 8 KB pages it reads — and watch every piece you've learned do its job. If you can narrate this path, you understand how Postgres works.

Here's the query. Nothing exotic: the ten most recent orders for one customer.

// one query, top to bottom

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10;

1
A backend receives it→ Connections & Pooling
One of the postmaster's per-connection processes parses the SQL and will run it. If this were a serverless app, a pooler handed it a backend to borrow.
2
The planner picks a plan→ Query Planner
It estimates costs and chooses: use the (customer_id, created_at) index — an index scan is far cheaper than scanning every order.
3
The executor pulls rows→ The Executor
Pull-based, one row at a time. Because the index already returns rows in created_at order (read backward for DESC), there's no blocking Sort — so LIMIT 10 stops the whole thing after ten rows.
4
Descend the B-tree→ B-tree Indexes
The index scan walks down the B-tree to customer 42's entries, reading (key, ctid) pairs in created_at order (newest first, scanned backward) — a handful of node reads, not a table scan.
5
Fetch through the buffer pool→ The Buffer Pool
Each ctid names a heap page; the executor asks the shared buffer pool for it — a cache hit if it's resident, a disk read (and maybe an eviction) if not.
6
Read the tuple off its page→ Storage Layout
On the 8KB page, the line pointer for that ctid points at the tuple; its columns are decoded.
7
Check visibility (MVCC)→ MVCC & Transactions
The query's snapshot decides whether this row version is visible — its xmin must be committed-in-snapshot and its xmax not. Concurrent writers are simply invisible; no locks were taken to read.

Run it for real and read the plan — you'll see exactly this: an Index Scan on the composite index, LIMIT cutting it off at ten rows (no Sort node, because the index supplies the order), and Buffers: shared hit for the pages it pulled from the pool.

sql · live postgresrun me first

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (id int, customer_id int, created_at date, amount int);
INSERT INTO orders
SELECT g, (g % 1000) + 1, date '2024-01-01' + (g % 400), g % 500
FROM generate_series(1, 60000) g;
CREATE INDEX ON orders (customer_id, created_at);
ANALYZE orders;

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (id int, customer_id int, created_at date, amount int);
INSERT INTO orders
SELECT g, (g % 1000) + 1, date '2024-01-01' + (g % 400), g % 500
FROM generate_series(1, 60000) g;
CREATE INDEX ON orders (customer_id, created_at);
ANALYZE orders;

⌘/ctrl + enter

explain · live plan treeindex scan, ordered, LIMIT-stopped

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10

Notice: actual rows on the index scan is 10, not the ~60 orders customer 42 has. The index gave the executor rows already sorted by created_at, so LIMIT stopped it after ten — the pipelining from the Executor lesson, made real.

What didn't happen (and why that's the point)

No row locks. Under MVCC, this read took no row locks — it never blocked, and it never blocked a writer.
No WAL. Nothing was modified, so nothing was logged. Had this been an UPDATE, the change would hit the WAL before the dirty page — durability from the WAL lesson — and leave a dead tuple for VACUUM.
No full scan, no Sort. The right index turned “find and sort 60,000 rows” into “read ten in order.” That single decision — made by the planner, verified with EXPLAIN — is the essence of the performance lesson.

Now change one thing

The whole system is coupled, so a small change ripples through every layer you just traced. Drop the index and re-run the plan — the same query now scans and sorts everything:

// challengeuses the orders table from the setup cell above

Prove the index is doing the work: drop it, then show the plan falls back to a full scan with a Sort. (The plan should now contain a Sort node.)

DROP INDEX IF EXISTS orders_customer_id_created_at_idx;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

DROP INDEX IF EXISTS orders_customer_id_created_at_idx;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

⌘/ctrl + enter

// why it matters · you can now read any query

Every query is some arrangement of these same pieces: a plan the planner chose, executed by pulling rows through access methods, served from the buffer pool over 8 KB pages, filtered by MVCC visibility, made durable by the WAL, and kept tidy by VACUUM — across connections, and out to replicas. That's the whole machine.

// what you now understand

01A query flows: connection/backend → planner → executor → index/heap access → buffer pool → 8KB pages → MVCC visibility → results.
02The planner's index choice is decisive: an ordered index turned a full scan + sort into reading ten rows in order.
03The executor is pull-based, so an ordered index lets LIMIT stop after ten rows (no blocking Sort).
04Reads take no row locks and write no WAL; writes would hit the WAL before the data page and leave dead tuples for VACUUM.
05Every stage you learned is a real component the query passes through — that's how Postgres works, end to end.

// self-test

With the (customer_id, created_at) index, why is there no Sort node even though the query says ORDER BY created_at DESC?

// go deeper

Query Path (docs) — the same journey, in the official overview
PostgreSQL internals (interactive book) — a deeper, chapter-by-chapter tour of everything here
Postgres source: backend README — where each stage actually lives in the code

you've reached the end — more lessons coming