The durability chain from COMMIT to platter · PostgreSQL

When COMMIT returns without an error, PostgreSQL has promised that the transaction survives a crash. Understanding exactly what it did to earn that promise — and what it is relying on others to have done — is the foundation of every durability decision in this course.

The chain

flowchart TD
    A["COMMIT issued"] --> B["WAL record written\ninto the WAL buffers"]
    B --> C["write() to the WAL file\nnow in the OS page cache"]
    C --> D["fsync / fdatasync\nOS asked to make it durable"]
    D --> E["Device receives the flush"]
    E --> F["Data is on stable media"]
    F --> G["COMMIT returns to the client"]
    H["The dirty data PAGE\nis still only in shared_buffers"] -.-> I["Written later by the\ncheckpointer"]

The critical structural fact is on the right-hand branch. COMMIT does not write the data page. It makes the log record durable. The page is written later, and if the server crashes before that happens, recovery replays the log to reconstruct it. Part XII covers the replay; this lesson is about the flush.

The three parameters that govern it

Read-only / Safethe durability settings and their contexts— Captured from a live 18.6 cluster.

$ psql -U postgres -c "SELECT name, setting, context FROM pg_settings WHERE name IN ('fsync','synchronous_commit','full_page_writes','wal_sync_method') ORDER BY name"

        name        |  setting  | context
--------------------+-----------+---------
fsync              | on        | sighup
full_page_writes   | on        | sighup
synchronous_commit | on        | user
wal_sync_method    | fdatasync | sighup
(4 rows)

fsync decides whether PostgreSQL asks the operating system to make writes durable at all. With it off, PostgreSQL never issues a flush, and a crash of the host — not the database — can lose or corrupt arbitrary data.

synchronous_commit decides whether COMMIT waits for that flush. With it off, PostgreSQL still flushes, but the client is told the transaction committed before the flush completes.

full_page_writes decides whether the first modification of a page after a checkpoint writes the whole page into WAL, protecting against torn pages.

The difference between the first two matters enormously and is routinely conflated.

	`fsync = off`	`synchronous_commit = off`
Flush issued?	No	Yes, just later
Lose recent commits on OS crash?	Yes	Yes, a bounded window
Database left corrupt after OS crash?	Yes, possibly	No
Reasonable in production?	Never	Sometimes, deliberately

fsync = off is not a performance setting, it is a decision to have no durability

The parameter is sometimes suggested for speeding up bulk loads or test environments, and the reasoning stops one step short.

With fsync = off, PostgreSQL never asks the operating system to make anything durable. The write-ahead log ordering guarantee — that a log record reaches stable storage before the data page it describes — is what makes crash recovery possible. Without the flush, that ordering is whatever the operating system happened to do, so after a host crash the log and the data files can be inconsistent with each other in ways recovery cannot repair.

The result is not “the last few transactions are missing”. It is a cluster that may not start, or may start and serve data that is silently wrong. There is no repair procedure, because there is no information from which to repair.

synchronous_commit = off is the setting people usually mean. It keeps every ordering guarantee and every flush, and only stops the client waiting for them. A crash loses at most the last three wal_writer_delay intervals of commits — the bound the documentation gives, since the WAL writer favours writing whole pages at a time when busy — the database is consistent, and recovery works normally. That is a real, bounded, defensible trade.

For a disposable test cluster that is destroyed and rebuilt on failure, fsync = off is defensible because nothing is being promised. That is the only case.

What each layer can lie about

PostgreSQL issues fsync() and trusts the answer. Every layer below can return success before the data is on stable media.

Layer	How it can acknowledge early	Detectable by PostgreSQL?
OS page cache	Only if `fsync` is off, or the call is ignored	No
Filesystem	Journal modes, barrier settings	No
Volume manager / RAID	Write-back cache without a battery	No
Virtualisation	Host cache mode set to `writeback`/`unsafe`	No
Network storage	Acknowledging before the remote commit	No
The disk itself	Volatile write cache, no power-loss protection	No

Every row in that “detectable” column says no, and that is the whole problem. PostgreSQL’s durability is exactly as good as the weakest honest layer beneath it, and it has no way to measure that.

The questions to ask of a storage platform, in order

These are the questions that establish whether the chain is honest. They are worth asking of a hypervisor team, a SAN vendor or a cloud provider, and the answers belong in the same document as the RPO.

Does a flush from the guest reach stable media before it is acknowledged? For a hypervisor this is the disk cache mode: a writeback or unsafe cache mode acknowledges from host memory.
Do the devices have volatile write caches, and are they protected? Enterprise drives have power-loss protection; consumer drives generally do not, and their cache is lost on power failure.
For a battery- or capacitor-backed controller, when was the backup power last verified? A degraded battery typically causes the controller to disable write-back silently, which is the safe behaviour and a performance change nobody attributes to it.
For network storage, is a flush acknowledged after the remote write is durable, or after it is queued?
For a cloud block device, what does the provider document about flush semantics? This is documentation-verified rather than testable from inside the guest.

A “yes” to all of these is the precondition for treating a COMMIT as a durability promise. A “we think so” is a finding.

Verifying what you can from inside

Some of the chain is visible from the host, and it is worth checking the parts that are.

# What PostgreSQL believes it is doing
psql -U postgres -c \
  "SELECT name, setting FROM pg_settings
    WHERE name IN ('fsync','synchronous_commit','full_page_writes','wal_sync_method')"

# Which flush method is actually available and chosen
psql -U postgres -c 'SHOW wal_sync_method'

# Filesystem mount options: barriers, journal mode
findmnt -no SOURCE,TARGET,FSTYPE,OPTIONS "$(psql -U postgres -tAc 'SHOW data_directory')"

# Device write cache, where the device exposes it
lsblk -o NAME,ROTA,SCHED,MODEL 2>/dev/null || true

wal_sync_method is worth a look. PostgreSQL selects a default appropriate to the platform — fdatasync on Linux — and the alternatives differ in how many system calls they make and whether they flush metadata as well as data. Changing it is a measurement exercise rather than a default worth adjusting.

Torn pages, and why full_page_writes exists

PostgreSQL writes 8 KiB pages. Storage devices have their own atomic write unit, historically 512 bytes and now commonly 4 KiB. When those differ, a power failure part-way through writing a page can leave it half old and half new — a torn page.

The consequence is worse than losing the write. Recovery replays WAL records that describe changes to a page: set this byte, insert this tuple at this offset. Applying such a change to a half-written page produces something that is neither the old page nor the new one, and the corruption is silent.

full_page_writes prevents it. The first time a page is modified after each checkpoint, the entire page image is written into WAL rather than just the change. During recovery that full image replaces whatever is on disk before any incremental change is applied, so a torn page is overwritten wholesale rather than patched.

The cost is WAL volume, and it is not small: immediately after a checkpoint, every page touched generates 8 KiB of WAL instead of a few dozen bytes. This is why WAL generation is spiky rather than smooth, why checkpoint frequency and WAL volume are coupled, and why the checkpoint tuning in Part XII has to consider both.

The setting may be turned off only if the storage guarantees atomic 8 KiB writes, which very few platforms document and fewer guarantee. The correct posture is to leave it on, and to account for its WAL cost in capacity planning rather than trying to remove it.

Note the interaction with fsync: full_page_writes protects against a torn page, and fsync is what ensures the full-page image reached disk before the data page it protects. Turning off the second makes the first ineffective.

Production discipline

Never set fsync = off on anything whose data matters. It is not a performance setting; it removes the guarantee that makes recovery possible.
Use synchronous_commit = off when you want that trade, and record the bounded loss window it accepts.
Leave full_page_writes on unless the platform documents atomic 8 KiB writes, and budget its WAL cost.
Ask the storage questions explicitly and record the answers. PostgreSQL cannot detect a layer that acknowledges early.
Re-ask after any infrastructure change. A hypervisor migration, a SAN firmware update or a move to different instance storage changes the answers.
Treat a controller battery as a durability dependency, and check that its health is monitored.

Cross-course references

Linux for Production Sysadmins — Part XIII (Disks), Part XV (Fstab) and Part XLI (Disk performance) cover mount options, write barriers and device caches directly.
Ceph & Distributed Storage — Part II (Storage performance) and Part XII (BlueStore) cover what a distributed storage layer does with a flush, which is the same question asked of a different platform.
Proxmox — Part V (Storage fundamentals) covers hypervisor disk cache modes, which is where the guest’s flush is most often acknowledged early.