Torn pages and double-write buffers

Why does a write larger than the device's atomic unit break recovery, and which three ways close the hole?

CaseConstructed case · Nadia at Kestrel Pay

A balance that matches neither value

Four puzzles in one month:

After a power cut, the database restarts, runs recovery with no error, and then one customer's account page shows a balance that matches neither the old nor the new value. A page checksum fails on that page.
Omar compares two machines running the same test with the same software. A developer's laptop (consumer SSD) shows fsync taking 1–2 ms; a candidate server with an enterprise SSD shows 20 µs.
Omar replaces the old RAID controller with a new one. Everything works. Three months later, commit throughput falls by 50× overnight. Every health check is green and every query still returns correct results.
A background audit finds a corrupted block in a table that nobody reads. The newest backup that still contains a good copy was deleted 9 days ago. Backups are kept for 14 days. The audit runs every 30 days.

The medium is an indifferent but dishonest witness

Logs and undo, Group commit and fsync and Bounding the log assumed that the medium is indifferent but honest: a write lands whole, in the order issued, and stays as written. Every mechanism so far depends on that assumption. It is false in three independent ways:

A write lands partially. Atomicity (all-or-nothing) is guaranteed only at the device's unit, not at yours.
A write is acknowledged before it is safe. The fix is to move the durability point, not to wait longer.
A write changes after landing, or was never where it claimed to be. Durability is not an event but a claim that needs checking again and again.

What joins the first two: the failure-atomicity unit of the device is not the unit that your software thinks in, and nothing in the interface reliably tells you what it is. What makes the third different: it is the only mechanism here that checks instead of assumes.

Every layer below you is a witness you cannot cross-examine. This article is about the three ways witnesses lie and the one mechanism that catches them.

Double-write buffers and torn-page protection

Let A be the largest write that the device applies atomically across a power loss. Let P be the page size that your storage engine's recovery assumes.

Typical A: 512 B (older drives), 4 KiB (4Kn drives, or the atomic write unit that NVMe reports), sometimes more.
Typical P: 8 KiB (Postgres, SQL Server), 16 KiB (InnoDB).

So P/A is commonly 2 to 32. When P > A, a crash in the middle of a page write leaves a torn page: part old, part new. That is a state that never existed and that no log record describes.

Why this specifically breaks the WAL

A torn page does not just damage one page. Physiological redo decides whether to apply a record by reading the pageLSN from the page itself. On a torn page, the LSN field may come from one half and the rest of the page from the other. Recovery then either skips a record it needed, or applies a change to garbage. And it does so confidently, because the page describes itself and the page lied.

A page that never existedP = 16 KiB · A = 4 KiB

That is why it is a correctness blocker, not just a data-quality nuisance. It is also the first appearance here of the old rule of granularity mismatch: here the mismatch first creates a correctness hole, and then creates amplification when you close the hole.

Three ways to close the hole

1. Double-write buffer (InnoDB). Write the page first to a small contiguous scratch area and flush it. Then write it to its real location. On recovery, any page whose checksum fails is restored from the scratch copy. The cost is 2× page writes in bytes, but much less than 2× in time, because the scratch area is sequential and batched. This is the WAL trick applied again: a random durable write becomes a sequential one. The mechanism that protects the log's assumptions is itself a small log.

2. Full-page writes into the WAL (Postgres). Log the whole page image on its first modification after each checkpoint. It is the same purchase in a different place, and the place has a side effect: it couples torn-page protection to the checkpoint interval, which is the source of the ~80× WAL amplification from Bounding the log. InnoDB's placement has no such coupling. Two engines, the same mechanism, different location, and the location decides which other knob gets contaminated. That is a general lesson about where to put a mechanism that cuts across the design.

3. Buy a device that promises A ≥ P. NVMe reports an atomic-write field (NAWUPF), and MySQL 8.0.30 and later can disable the doublewrite buffer on hardware that claims it. The cost: you have replaced a mechanism you control with a firmware field you cannot verify. This is the "no layer verifies the one below" problem, bought on purpose.

The economics: insurance

Torn-page protection is insurance. You pay a continuous premium of about 2× on data-page write bandwidth to remove a loss that can happen at most once per crash, with a probability of roughly

crash_rate × (page writes in flight × write duration).

Enterprises buy it. An atomic-write device changes the decision not because it makes the protection cheaper, but because it makes the premium zero.

Where the granularity sits elsewhere

Object stores: a PUT (upload) is atomic. There is no torn object. So torn-write protection disappears from data-lake table formats, and the entire problem moves to a single compare-and-swap (a write that succeeds only if the value is still what you expect) on the metadata pointer. Principle: when the storage's atomic unit is at least as large as your object, torn-write protection disappears and the whole problem becomes one pointer swap.
Persistent memory: the atomic unit shrinks to 8 bytes. The primitives become CLWB (write a cache line back to memory) plus sfence (wait until it is done) instead of fsync. "Torn write" at that size is a different problem with different solutions, taken up in Battery-backed memory and NVRAM.