Transactions Behind the Scenes: What Really Happens in a Database

What Actually Happens When You Hit COMMIT

I used to think of COMMIT as a light switch. You flip it, the data is saved, done. It wasn't until I actually dug into how Postgres handles a transaction internally that I realized how much engineering is packed into that one keyword — and how much of it exists purely to survive the worst possible moment: your server crashing mid-write.

This post is my attempt to explain that process the way I wish someone had explained it to me — with a real multi-table example, not a toy one.

Setting up a real scenario

Say we're running a small e-commerce backend with three tables:

users(id, name, balance)
orders(id, user_id, amount, status)
transactions(id, user_id, amount, type)

A user places an order. In your application code this probably happens inside a single DB transaction, something like:

BEGIN;

UPDATE users SET balance = balance - 500 WHERE id = 1;

INSERT INTO orders (user_id, amount, status)
VALUES (1, 500, 'placed');

INSERT INTO transactions (user_id, amount, type)
VALUES (1, 500, 'debit');

COMMIT;

Three separate writes, but they need to succeed or fail together. If the balance gets deducted but the order never gets recorded, you've got a very confused customer and a support ticket. This is exactly what a transaction is for — a group of operations that the database treats as one atomic unit, guaranteeing the classic ACID properties: atomicity, consistency, isolation, and durability.

But "guaranteeing durability" is doing a lot of work in that sentence. How does the database actually make sure committed data survives a crash a millisecond later? That's the interesting part.

Nothing happens directly on disk

The first thing that surprised me: none of those three statements touch your disk directly. Disk I/O is slow — genuinely, painfully slow compared to RAM — so Postgres (like basically every serious database) keeps a chunk of memory called the buffer cache (or buffer pool) where it pulls in the relevant data pages before touching them.

So when the UPDATE runs, Postgres loads the page containing user id = 1's row into the buffer cache, and modifies it there. Same for the new rows going into orders and transactions. At this point, the data on disk is completely unaware anything has changed. The only place these changes exist is in memory, and technically only in the database's memory — your terminal, your app, everything else has no idea either.

These modified-but-not-yet-saved pages have a name: dirty pages. It's a slightly dramatic term for "we changed this in RAM but haven't written it back yet," but it's the term you'll see in every Postgres internals doc, so it's worth knowing.

The part that actually protects you: WAL

Here's the problem this creates: if the server loses power right now, those dirty pages evaporate. RAM doesn't survive a crash. So how does a COMMIT ever mean anything?

The answer is Write-Ahead Logging, usually just called WAL. Before Postgres considers a transaction committed, it writes a compact record of exactly what changed to a log file — not the data pages themselves, just enough information to redo the change later. Something conceptually like:

TXN 101:
- users.balance: 1000 → 500
- orders: new row inserted
- transactions: new row inserted

This log entry first lands in a small WAL buffer in memory too. So at this point you might reasonably ask: if the log is also just sitting in memory, what have we actually gained?

This is the detail that took me a while to really internalize: when you run COMMIT, Postgres doesn't write your dirty data pages to disk. It writes the WAL entry to disk, and calls fsync to force the OS to physically flush it — not just hand it to a write buffer, but actually get it onto the storage device. Only once that fsync succeeds does Postgres tell your application "commit successful."

The dirty pages themselves — the actual users, orders, and transactions rows — can sit in memory for a while longer. That's the trick. Writing one small sequential log entry is much cheaper than writing several scattered data pages, so WAL lets Postgres give you durability guarantees fast without paying the cost of a full page flush on every single commit.

So the rule that actually matters is this: a commit is a promise that the WAL entry is safely on disk — not that your table's data pages are.

What crash recovery actually looks like

This log-first approach is also exactly what makes recovery possible. Two scenarios worth walking through:

The server crashes before COMMIT finishes. The WAL entry never made it to disk, and the dirty pages were only ever in memory. Both are gone. When Postgres restarts, it finds no trace of transaction 101 anywhere, so as far as the database is concerned, that transaction never happened. Your app would have seen the crash and (hopefully) never gotten a success response, so this is actually the "safe" failure — nothing inconsistent, nothing half-applied.

The server crashes after COMMIT succeeds, but before the dirty pages are flushed to disk. Now the WAL entry is safely on disk, but the actual table pages might not be yet. On restart, Postgres reads the WAL, finds transaction 101 marked complete, and replays those changes against the data files — a step called REDO. Because the log already told it exactly what changed, it doesn't need to guess or ask your application to resend anything. The database ends up in the same state it would have been in if it had flushed everything immediately, just via a slightly delayed path.

This is really the whole point of WAL: it turns "did my expensive multi-page write survive the crash" into "did my one small log entry survive the crash," which is a much easier problem to solve reliably.

Checkpoints: cleaning up so recovery stays fast

If Postgres kept every dirty page in memory forever and only ever relied on WAL replay, the log would grow without bound, and a crash after weeks of uptime could mean replaying an enormous amount of history on restart. That's where checkpoints come in.

Periodically (Postgres does this on a timer and also based on how much WAL has accumulated), it writes all the current dirty pages from memory out to disk for real, then marks a point in the WAL saying, essentially, "everything before this line is now safely reflected in the data files." Anything before that checkpoint no longer needs to be replayed during recovery — only WAL written after the last checkpoint matters.

This is the mechanism that keeps crash recovery from turning into a multi-hour ordeal on a busy production database. Without it, restart time would grow in proportion to how long the server had been running.

Putting the whole flow together

Strip away the terminology and the sequence for our order example looks like this:

1. The relevant rows for users, orders, and transactions are pulled into the buffer cache.
2. Each row gets modified in memory, becoming a dirty page.
3. Before commit, a WAL entry describing these changes is written.
4. On COMMIT, that WAL entry (not the data pages) is fsync'd to disk. The transaction is now durable.
5. At some point later — could be seconds, could be during the next checkpoint — the dirty pages themselves get flushed to disk.
6. If a crash happens in between, Postgres uses the WAL to redo whatever didn't make it to the data files yet.

A path most systems don't take: shadow paging

It's worth knowing there's an entirely different strategy some older or specialized systems use instead of WAL, called shadow paging. Rather than modifying pages in place and logging the change, the database writes an entirely new copy of the page with the changes applied, leaving the original untouched. Commit is then just a matter of atomically swapping a pointer from the old page to the new one.

It's conceptually simpler in some ways — no REDO or UNDO logic needed, since the old version is still sitting there intact until the swap happens. But it comes at a real cost: every update means duplicating a page rather than modifying it, which gets expensive fast in terms of both storage and I/O once you're dealing with high write volume. That's part of why WAL-based approaches won out in most mainstream relational databases, Postgres included.

Why this is worth actually understanding

You can build plenty of features without ever thinking about buffer pools or fsync calls. But the moment something goes sideways in production — a pod gets OOM-killed mid-transaction, a disk fills up, a deploy restarts the DB at the wrong second — knowing what's actually guaranteed at each stage is the difference between confidently saying "that data is safe" and just hoping it is.

That gap, more than anything else, is what separates someone who runs queries against a database from someone who actually understands what it's doing underneath them.

Comments

[Mads Hansen]

This is a useful reminder that “transaction” is not a single switch. It is a set of guarantees that only hold if the boundaries are clear.

The parts teams often underestimate:

• when locks are acquired
• what isolation level actually permits
• whether reads are repeatable
• what happens after a partial failure
• whether retries are safe
• where application code catches exceptions
• how long the transaction stays open

That last one matters a lot when higher-level automation or agent workflows touch databases. A natural-language request may sound small, but the generated operation still needs a clear transaction boundary, a timeout budget, and an audit trail.

The database can protect consistency, but only if the application does not blur the unit of work.