PostgreSQL: Reliability (docs ch. 28.1)

The chapter that says what the hardware is actually doing underneath a COMMIT, and therefore why write-ahead-logging needs full-page images at all. First-party engine documentation of its own durability mechanism — T1. Graded 30d as a living reference.

The torn page

The failure mode has nothing to do with the database and everything to do with sector granularity:

"Disk platters are divided into sectors, commonly 512 bytes each. Every physical read or write operation processes a whole sector. When a write request arrives at the drive, it might be for some multiple of 512 bytes (PostgreSQL typically writes 8192 bytes, or 16 sectors, at a time), and the process of writing could fail due to power loss at any time, meaning some of the 512-byte sectors were written while others were not."

Sixteen sectors, and a power cut can land between any two of them. The page left on disk is then neither the old page nor the new one — it is a hybrid that no version of the database ever held, and a redo log full of deltas cannot repair it, because a delta assumes a known starting state.

Hence the defence:

"To guard against such failures, PostgreSQL periodically writes full page images to permanent WAL storage before modifying the actual page on disk. By doing this, during crash recovery PostgreSQL can restore partially-written pages from WAL."

That is the whole justification for the write amplification measured in postgres-wal-configuration: the first write of each page after a checkpoint carries the entire 8 kB, because after a torn write there is nothing trustworthy to apply a delta to. Where the filesystem already rules the failure out — the docs name ZFS — full_page_writes can be turned off and the amplification disappears with it.

Write caches, and why fsync exists

Durability also depends on the write actually reaching the platter rather than a volatile cache:

"Some of these caches are write-through, meaning writes are sent to the drive as soon as they arrive. Others are write-back, meaning data is sent to the drive at some later time. Such caches can be a reliability hazard because the memory in the disk controller cache is volatile, and will lose its contents in a power failure."

The engine's answer is the operating system's flush primitive — "all operating systems give applications a way to force writes from the buffer cache to disk, and PostgreSQL uses those features" — tuned through wal_sync_method.

The point worth carrying up to the spoke's thesis: a WAL is a protocol against a specific hardware failure model, not an abstract logging idea. Change the model — a filesystem that cannot tear a page, a device with non-volatile cache — and specific parts of the protocol become unnecessary. The engine exposes that as a parameter rather than assuming either way.

Related

write-ahead-logging · postgres-wal-configuration · checkpoint · postgresql · pgbackrest