Inside a Database: The Journey of a Write

At 09:47 your marketplace takes order #501 — one bowl of pho, ₹240. The app runs INSERT INTO orders … and COMMIT, the database answers done in well under a millisecond, and the customer gets their confirmation screen.

Now the uncomfortable part. If you could freeze time at 09:47:00.001 and read the orders table file on disk — the actual bytes — order #501 would not be there. Not corrupted, not half-written. Simply absent. And yet nothing is wrong: you can kill the machine's power at this exact instant and the order still survives.

A database that says done without doing the thing you assumed it did is not lying — it's doing something smarter. This lesson follows that one INSERT through the engine, room by room, and names every piece it meets: parser, planner, executor, buffer pool, write-ahead log, and the background crew that cleans up after everyone. By the end, "the database" stops being a black box that eats SQL and becomes a floor plan you can point at.

Scope: this lesson names the pieces and walks the write past them. How the index finds the right page is B-Trees & B+ Trees; why log-first replay is guaranteed correct is WAL & Durability: Why Committed Data Survives; how the planner chooses between plans is How a Query Executes. Each gets its own lesson in this section.

Your INSERT arrives at the engine as a string of bytes on a socket. Three offices process it before anything touches data.

The parser checks the syntax and builds a query tree — pure grammar, no opinions. Then analysis resolves every name against the system catalogs: does a table called orders exist? Is total a numeric column? The catalogs, pleasingly, are themselves just tables the engine queries.

The rewriter applies stored transformation rules. Its headline act is views: when you query a view, the rewriter swaps your query for one against the underlying base tables. You never notice.

The planner gets the rewritten tree and asks: of all the ways to execute this, which is cheapest? For our single-row INSERT there's little to debate. For a SELECT joining three tables there might be thousands of candidate paths — it estimates the cost of each and hands the winner, a plan tree, to the executor. The executor is the only one of the four that actually touches data.

Notice what the executor received: not SQL. A compiled plan. That separation — declarative question in the front, imperative plan in the back — is the oldest and best idea in relational databases, and How a Query Executes is where we'll watch the planner earn its salary.

Here is the fact that reorganizes everything else: the executor never edits a file.

Tables and indexes are stored as fixed-size pages — 8,192 bytes each in Postgres. Run SHOW block_size; and it answers 8192. Our whole four-row orders table fits in exactly one page; a big table is just millions of them. To touch any row, the engine must first have that row's page in the buffer pool — a slab of RAM (Postgres calls it shared_buffers, default 128 MB) holding copies of recently used pages.

Page already in the pool? The executor edits the in-memory copy and moves on. Not there? Read it from disk into the pool first, then edit. You can watch the difference. The same one-row lookup, cold and then warm, on a real Postgres 16:

read means "went to disk", hit means "already in RAM" — and the warm run is 7× faster. Even the planner shows up in these counters (Planning: Buffers: shared hit=57 read=17): those are catalog pages. Planning a query reads tables too.

Now the write. The executor puts order #501 into the in-memory page, and from that moment memory and disk disagree: the pool holds the new truth, the file holds the old one. Such a page is called dirty. This is deliberate. On a live Postgres you can count dirty pages with the pg_buffercache extension — one INSERT took ours from 6 dirty pages to 17 (the heap page, index pages, catalog bookkeeping). Nobody is in a hurry to write them out.

Which should bother you. The customer was told done — and their order currently exists in RAM, the most mortal storage there is. One power cut and the pool is gone. Why is that safe?

Because before the executor touched that page, it wrote a confession.

Every change to every page also produces a WAL record — write-ahead log: which relation, which page, what changed — appended to a small in-memory staging area (wal_buffers, 4 MB by default). The records accumulate while your transaction runs.

Then you say COMMIT. The engine flushes your transaction's WAL records to the log file on disk and calls fsync — now the bytes are on durable storage. That flush is the commit. Nothing else happens: the dirty page with your row stays in RAM; the table file still doesn't contain order #501. What the disk holds is a description of the change, and a description is enough to redo it after any crash.

This is also why commits are fast. A commit that had to write table pages would scatter random writes across the disk — heap here, index there. The log is one file, appended sequentially, the access pattern disks love most. Our measured INSERT with auto-commit: 0.568 ms, end to end.

You can watch the log move. pg_current_wal_lsn() reports the WAL position — take it before and after an INSERT and subtract:

That first-insert anomaly is worth naming, because it's a clue about crash safety hiding in plain sight. The first time any page is modified after a checkpoint, Postgres logs the entire 8 kB page into the WAL, not just the delta — a full-page image. Reason: if a crash interrupts a page write halfway, the file is left with half-new, half-old bytes — a torn page — and you can't apply a delta to garbage. A full image gives recovery a clean starting point. Insert #1 touched several fresh-after-checkpoint pages, so it paid the full-image tax; inserts #2 and #3 rode on deltas. (The full torn-page story, and why replay is provably correct, is WAL & Durability: Why Committed Data Survives.)

And the proof that the log alone is enough — we ran it. Commit a row, then docker kill the container mid-flight (a SIGKILL: no shutdown, no flush, dirty pages vaporized), then start it again:

The engine replayed the log over the stale files and rebuilt exactly the state it had promised. Done never meant "the table file has your row." It means "the log can always put it back."

Your transaction finished at 09:47. The dirty page with order #501 might reach the table file at 09:52 — and nobody who ordered pho will ever know.

Two background processes do it. The background writer trickles dirty pages out continuously, keeping the pool from silting up. The checkpointer does the periodic deep clean: every checkpoint_timeout (5 minutes by default) or whenever max_wal_size (1 GB) of log has accumulated, it writes every page dirtied up to that moment and then records a redo point in the log — "everything before here is safely in the files; recovery may start at this position." You saw one in the crash log above: redo starts at 0/1596640 — that was the redo point our last checkpoint left behind, doing exactly its job. On a live box, CHECKPOINT; took our dirty-page count from 17 back to 0.

Step back and the design's shape appears. A hot page — a restaurant's order count on a Friday night — might absorb five hundred updates between checkpoints. Cost: five hundred ~200-byte log deltas plus one eventual 8 kB page write. The memory-first engine is a batching machine: it converts a stream of scattered random writes into sequential log appends now, and consolidated page writes later.

Reading about the durability line is one thing; holding it is another. Below is the engine as a console. INSERT an order (type any amount) and watch its page go dirty in the buffer pool. COMMIT and watch the WAL records cross the durable line — the table files stay stale. Push the background writer and you'll see something the prose only claimed: an uncommitted row can reach disk. Then pull the plug — but first predict, row by row, what survives. Recovery replays the durable log and grades you.

Try this first: INSERT twice, COMMIT, INSERT once more without committing, flush one page with the background writer, then cut the power. The committed rows come back from the log; the flushed-but-uncommitted one sits on disk yet stays invisible. That's the whole lesson in one crash.

Swap Postgres for MySQL's InnoDB and the floor plan barely changes — only the name plates. The buffer pool is called… the buffer pool. WAL buffers become the log buffer; the WAL itself is the redo log; the checkpointer's role is played by InnoDB's page-flushing machinery. SQL Server tells the same story with a transaction log and a lazy writer.

InnoDB adds two pieces worth recognizing on sight. A change buffer defers modifications to secondary-index pages that aren't in memory — batching random index I/O the way the buffer pool batches everything else. And a doublewrite buffer answers the torn-page problem differently than Postgres: instead of logging full page images after checkpoints, InnoDB writes each page to a scratch area first, then to its real home — crash mid-write, and a clean copy always exists somewhere. Same danger, two engineering answers. (A third piece, undo logs — old row versions kept for rollback and concurrent readers — is the subject of MVCC: Readers Don't Block Writers.)

This is the lesson under the lesson: you are learning the model, not the engine. Parse → plan → execute → modify in memory → log first → flush later. Learn it once and every serious database you ever open — Oracle, SQLite in WAL mode, even the storage engines inside distributed systems — is a dialect, not a new language.

What the design buys is everything this lesson measured: sub-millisecond commits (one sequential log append instead of scattered page writes), reads served from RAM at 0.009 ms, and write batching that turns five hundred updates into one page flush. Now the bill.

The checkpoint dial. Crash recovery replays the WAL from the last redo point — so a checkpoint 2 minutes ago means ~2 minutes of log to replay, and a checkpoint 30 minutes ago can mean an outage-extending replay. Checkpoint often, then? Each checkpoint resets the full-page-image tax (that 162× first-touch cost, again on every hot page) and adds an I/O burst. Postgres spreads the flush across ~90% of the interval (checkpoint_completion_target = 0.9) precisely to soften those bursts — the famous "wave pattern" in long Postgres benchmarks is this tension made visible. Recovery time versus steady-state I/O: a dial, not a default you inherit blindly.

RAM as a load-bearing wall. A buffer pool that's too small doesn't just slow reads — it forces your query's backend to evict and write dirty pages itself before it can load the page it wanted. The flush cost stops being a background chore and lands inside your p99.

And the honest counter-argument. For some workloads the full journey is too expensive. synchronous_commit = off tells Postgres to return done before the WAL flush: commits at memory speed, in exchange for a small window (typically a few hundred milliseconds of recent commits) that a crash can erase. For a metrics firehose or a clickstream, losing 200 ms of data in a rare crash is a fine trade. For order #501 — someone's dinner, someone's money — it never is. Durability is a per-workload dial, and knowing that it's a dial is the difference between reciting the write path and engineering with it.

Everything this lesson claimed, you can watch in fifteen minutes with Docker. These are the exact commands we ran; the outputs shown are pasted from the real session (postgres:16.14), so you know what you should see.

1. Start a disposable Postgres and meet the numbers.

2. Watch a page get dirty — and get cleaned.

3. Price your inserts in WAL bytes. Grab the log position before and after:

4. The plug-pull. Commit a row, kill the container with no warning, and check whether the database kept its promise:

Predict before you run step 4: the row was only in a dirty page, and you killed the process. Write down why it should survive anyway — then check yourself against the redo lines. Clean up with docker rm -f journey when you're done.

• "COMMIT writes my row into the table." COMMIT writes the log and fsyncs it. The table file catches up minutes later, via the background writer or a checkpoint. You proved it with the plug-pull: the table file was stale, the row survived anyway.
• The reverse is also false — and almost nobody says it: uncommitted changes CAN reach disk. Dirty-page flushing ignores transaction boundaries; the pool evicts what it needs to, committed or not. The log (and row versioning, coming in MVCC: Readers Don't Block Writers) is what makes that safe — durability lives in the WAL, never in which pages happened to flush.
• "Reads go to disk." Reads go to the buffer pool: 0.009 ms warm vs 0.067 ms cold in our run. A healthy OLTP database serves the vast majority of reads from RAM — even query planning reads its catalogs through the pool.
• "The WAL slows writes down — it's extra work." Backwards. The WAL is why commits are fast: one sequential append (0.568 ms measured) instead of random page writes to heap and index files on every commit.
• "Checkpoints commit my data." Unrelated clocks. Commit = your transaction's log flush, at COMMIT time. Checkpoint = periodic housekeeping that flushes dirty pages and advances the redo point. Your data was durable long before the checkpoint ran.

• A database engine is a pipeline, not a box: parser → rewriter → planner → executor, and only the executor touches data.
• Data lives in fixed-size pages (8 kB in Postgres); every read and write goes through the in-memory buffer pool. A modified page is dirty: memory and disk disagree on purpose.
• COMMIT = WAL flush. One sequential log append (~192 bytes per steady-state insert, 0.568 ms measured) makes the change durable — the table file is updated later, by the background writer and the checkpointer.
• Crash recovery = replay the log from the redo point. We killed the engine with a committed row only in RAM; redo starts at 0/1596640 brought it back.
• Every serious engine walks this same journey — InnoDB just renames the rooms (redo log, log buffer, doublewrite). Learn the model once.
• The design is a bet with a bill: fast commits and batched writes, paid for with checkpoint tuning, RAM hunger, and replay-time recovery. And durability itself is a dial (synchronous_commit) you set per workload — not a law of nature.

One question was carefully left open. When the executor needed order #501's page, it found the right one among millions in microseconds. How? That's an index doing its quiet work — and it's next: B-Trees & B+ Trees, the structure that makes "find my row" cost three page reads instead of a million.