Every map you keep must be redrawn on every write.

01 · The bill

Reads got cheaper. Something had to get more expensive.

Return to the vault. You keep 100 scrolls below, 10 slots on the desk, and now a small map that tells you where every scroll lives. Reading became almost free.

Now write something. Add a new scroll to the vault. The scroll itself must be stored — but the map is now wrong, and a wrong map is worse than no map at all. So the map must be fetched, corrected, and written back too.

One logical change, two physical pieces of work. Keep three maps instead of one and it becomes four. Every index you create is a tax collected on every write, forever.

That is the last time this lesson borrows the vault. To see exactly where the tax goes, we follow a write the way lesson 02 followed a read — in real terms: pages, the buffer pool, and the log. The path is stranger than most people expect.

02 · Where a write actually goes

A committed write has not touched your table file.

The intuitive model is that UPDATE writes to the table on disk. It does not. A write modifies a page in the buffer pool — the same pool, the same slots, the same clock sweep that serves your reads.

Which produces a consequence almost nobody predicts: to write a page, you must first read it. You cannot modify a page you do not have in memory. If it is not already in the buffer pool, the write path performs a disk read first, and only then changes it.

Writes generate read I/O. A write-heavy workload competes for buffer slots exactly like a read-heavy one — and evicts the pages your queries wanted.

A page that has been modified in memory but not yet written back to disk is called dirty. At the moment your transaction commits, the changed page is still sitting in the buffer pool, dirty, and the table file on disk is untouched.

Which raises the obvious question: if the data is only in volatile memory, what makes the commit durable? Pull the power cord and RAM forgets everything.

03 · One write, step by step

Follow a single change through all four places it can live.

A row on page 17 currently holds the value 100. We are going to change it to 90. To see what durability actually costs, watch four locations at once — two in memory, two on disk.

• WAL buffer (RAM) — a small staging area for journal lines not yet flushed.
• Buffer pool (RAM) — the page slots from lesson 02, where reads and writes both happen.
• WAL file (disk) — the durable journal, written by appending only.
• Table file (disk) — the table itself, the thing you probably think of as "the database".

Steps 1 to 3 never touch the disk

Everything so far happens in RAM. A crash at this point loses the change completely — and that is correct, because you have not committed yet. Nothing was promised, so nothing was broken.

Step 4 is the only disk write the commit waits for

Not the page. The journal line. One small append to a file that is only ever written sequentially, followed by an fsync to force it out of the operating system’s cache and onto the physical device.

This is the whole reason the design exists. The changed pages are scattered across a huge table file — page 17 here, page 904,113 there. Writing them at commit time would mean slow random writes. Appending one line to the end of the log is a fast sequential write, and it costs the same whether the transaction dirtied one page or fifty.

Step 5 is the part that surprises people

The user has been told the change is saved. The table file on disk still says 100. If the power failed one microsecond later, recovery would replay the journal line and reconstruct the change. The journal line is the only durable evidence that anything happened.

Step 6 quietly reconciles the two

At a checkpoint, background processes write the dirty pages out. Page 17 becomes clean, the table file finally says 90, and the journal lines describing it are no longer needed for recovery.

The page is what queries read. The WAL is what survives a crash. Neither can do the other’s job — a query cannot be answered from the log, and RAM cannot survive a power cut.

PostgreSQL documents this design in Write-Ahead Logging (WAL). The same log turns out to be what makes replication possible too, which is a debt lesson 07 settles.

04 · The same write, with indexes

Now count what each extra index adds to that trace.

The walkthrough above assumed a table with no indexes, and it dirtied exactly one page. Add indexes and step 3 stops being a single action.

PostgreSQL does not overwrite a row in place. It writes a new version of the row and marks the old one dead, so that concurrent readers still see a consistent snapshot. The new version sits at a new physical location — which means every index pointing at that row now points at the wrong place, and must be corrected.

Correcting an index means descending its tree to find the right leaf page, then modifying that leaf. Each one is another page pulled into the buffer pool, another page marked dirty, and another journal line in the WAL.

That gap between one logical change and four physical pages is called write amplification, and it grows in a straight line with the number of indexes you keep. The commit still waits for only one WAL flush — but the checkpoint now has four pages to write instead of one, and the buffer pool is holding four dirty pages instead of one.

05 · Closing the ledger

Writes are cheap to promise and expensive to keep.

Three things are worth carrying out of this lesson, and they all follow from the same trace.

A commit is a promise, not a filing

When the database says saved, your table file on disk has not been touched. What exists is one line appended to the write-ahead log. The page still sits in memory, dirty, waiting for a checkpoint that may be minutes away. That is not a shortcut or a risk — the log is what makes the promise keepable, and replaying it after a crash reconstructs everything.

The commit is fast because it defers almost everything

One small sequential append and one flush, no matter how many pages the transaction dirtied. That is why writes feel quick even on a busy table. But deferred is not cancelled. Every page you dirtied still has to reach disk eventually, and something has to carry it there.

Indexes multiply the deferred half, not the visible half

This is the part that makes over-indexing so easy to get wrong. Add three indexes and one row change dirties four pages instead of one — but the commit still waits for a single flush, so your write latency barely moves. Nothing feels slower at the moment you do it.

The cost lands somewhere you were not watching: four times the work at checkpoint time, and four buffer slots holding dirty pages instead of one. It shows up as everything being slightly slow, rather than as a slow query you could point at.

Which gives the discipline in one line: every index should be answerable to a real query. Not a hypothetical one, not a column that merely looks important. The ones that cannot answer for themselves are charging rent on both sides of the ledger — reads and writes — and returning nothing.

But notice what none of this fixes. Even with exactly the right indexes, the table keeps growing. Years of old orders sit in the same file as today’s, and every scan, every checkpoint, and every maintenance job still has to walk past them. The next lesson draws boundaries inside the table itself.

The Old Dragon “Ten maps, keeper, and nine lead nowhere you go. Your scribes redraw all ten, every single night.”