Follow a Write Through the Engine

The planner treats WHERE id = 42 exactly as it would for a SELECT: an index scan on accounts_pkey — root, internal, leaf — yields tuple id (2093, 7). The heap page is requested from the buffer pool and pinned. So far nothing has been modified, and the cost so far is the same four page requests as the read path in Follow a Read Through the Engine. An UPDATE with no usable index scans the table to find its rows, and it holds locks while it does so; the slowest writes are usually slow reads.

The transaction supplies the identity for everything that follows: transaction id 7412 will be written into the log records, into the tuple header (PostgreSQL xmax on the old version, xmin on the new), and into the lock manager. A concurrent UPDATE of the same row sees the row locked and waits — the beginning of everything in The Lock Manager.

Before a byte of the page changes, the backend appends a record to the write-ahead log: transaction id, relation, page number, slot, the old balance 500, the new balance 400 — around 80 bytes. The record receives an LSN, its byte offset in the log stream, which will be stamped on the page. This ordering is the entire meaning of "write-ahead": the description of a change reaches the log before the change can reach storage. The record is in the log *buffer* in memory for now; nothing has been fsynced. What matters is the order, and the order is enforced by the page LSN: no page may be written to storage while the log is not durable up to its LSN.

Then the page is modified. PostgreSQL places a new tuple version in the page's free space (new xmin = 7412, balance 400) and sets xmax = 7412 on the old version; the old version stays for readers whose snapshot predates the transaction — this is the mechanism of MVCC Internals: Version Chains and Snapshots. InnoDB overwrites the row in place and copies the old image into an undo log page, which is itself logged. Either way the change is a few bytes moved inside an 8 KB frame: the cheapest step in the whole write.

The frame's dirty bit is set and the page header's LSN field becomes 5 812 331. From here the page has two futures. The background writer or the next checkpoint will write it — after first ensuring the log is durable through 5 812 331 — and clear the dirty bit. Or the frame will be chosen as a victim by some other backend's miss, which then has to perform that write on its own critical path. Until one of those happens, storage holds balance 500 and the page in RAM holds 400, and the only durable evidence of 400 — once COMMIT has done its fsync — is the log record.

The page LSN does double duty. It enforces the write-ahead rule going forward, and during recovery it tells redo whether a page already contains a change (page LSN ≥ record LSN → skip) — which is what makes recovery safe to repeat. Both are covered in Crash Recovery.

The primary key did not change, so accounts_pkey still maps 42 → the row. But in PostgreSQL the row is now a *new tuple* at a new slot, and an index entry points at a slot. Updating every index on every UPDATE would make writes cost one page per index. The escape is the HOT update (heap-only tuple): if no indexed column changed and the new version fits on the same page, the old slot is turned into a redirect pointer to the new version, and every index continues to point at the old slot. The lookup follows the chain on the heap page; no index page is read or written. If balance were indexed, the index would need a new leaf entry (400 → new tid) and the old entry (500 → old tid) would remain until VACUUM removes it — a second B+ tree descent, a second dirty page, a second WAL record, and possibly a leaf split.

The practical rule: an UPDATE costs one modified page plus one per index whose key changed. Indexes on columns that change on every write (updated_at, last_seen, counters, status) multiply write cost and defeat HOT; a fillfactor of 80–90 leaves room for the new version on the same page and makes HOT possible at all.

InnoDB updates the clustered-index row in place, so the row's identity (its primary key) does not move and secondary indexes are unaffected unless their column changed. The old row image goes to the undo log — needed for rollback and for MVCC readers — and both the row change and the undo write are described in the redo log. A changed secondary-index column produces a delete-mark of the old entry and an insert of the new one; if the secondary leaf page is not in the buffer pool, the change is recorded in the change buffer and merged when the page is next read, turning a random read-modify-write into a deferred one.

The redo record InnoDB writes is physiological — "on page P, apply this logical change to record R" — and small; the redo log is a fixed-size ring of files (innodb_log_file_size) that must be checkpointed before it wraps, which is why a burst of writes can stall on "log wait" when the checkpointer falls behind.

COMMIT appends a commit record for 7412, writes the log buffer through it, and calls fsync. When fsync returns the client is told OK. Count what is on disk at that moment: the log records (update, commit) — and nothing else. Heap page 2093 on storage still says 500. The dirty frame will be written later, perhaps in seconds, perhaps at the next checkpoint in five minutes.

So: the machine loses power one second after the client saw OK. The frame is gone. Storage says 500. Does the transaction survive? If the engine kept the log, yes — recovery reads the commit record, finds the update record, sees that page 2093's LSN on storage is older than 5 812 331, and rewrites balance 400 into the page. If the engine did not keep a log, the answer is no: the change existed only in RAM, the client holds a receipt for a transfer that never happened, and there is no evidence anywhere. That gap — committed but not flushed — is not a bug to close; it is the design, and closing it correctly is the subject of Write-Ahead Logging.