PostgreSQL: WAL Configuration (docs ch. 28.5)

The chapter that explains the dials, read for the four things write-ahead-logging listed as undefined in this corpus: checkpoints, full-page writes, the durability levels between "committed" and "safely elsewhere", and WAL write amplification. First-party engineering documentation for the engine's own mechanism, so T1 by the artifact-facts branch of the tier rule. Graded 30d: this is a living reference edited in place, versioned with the engine.

Checkpoints, and why they are a tuning problem at all

A checkpoint is the moment the engine stops owing the log a debt:

"A checkpoint is begun every checkpoint_timeout seconds, or if max_wal_size is about to be exceeded, whichever comes first."

Defaults are 5 minutes and 1 GB. A CHECKPOINT command forces one, and if no WAL has been written since the last one, the next is skipped rather than performed on an idle system.

The trade-off is stated plainly, and it is the reason this is not a set-and-forget parameter:

"Reducing checkpoint_timeout and/or max_wal_size causes checkpoints to occur more often. This allows faster after-crash recovery, since less work will need to be redone."

Against which: checkpoints "are fairly expensive, first because they require writing out all currently dirty buffers, and second because they result in extra subsequent WAL traffic." So the dial trades recovery time against steady-state I/O, and both ends are real costs.

max_wal_size is a target and not a bound — "max_wal_size is never a hard limit anyway, so you should always leave plenty of headroom to avoid running out of disk space." Segment files that are no longer needed are removed or recycled, renamed into place as future segments in the sequence.

Spreading the write. checkpoint_completion_target (default 0.9) is a fraction of the checkpoint interval over which the dirty-buffer writes are paced: "the I/O rate is adjusted so that the checkpoint finishes when the given fraction of checkpoint_timeout seconds have elapsed, or before max_wal_size is exceeded, whichever is sooner." The docs warn against 1.0 — checkpoints do other work besides writing buffers, so a target of 1.0 "is quite likely to result in checkpoints not being completed on time."

Full-page writes are the write amplification

This is the mechanism, and it is a direct answer to the amplification question the spoke had open:

"If full_page_writes is set (as is the default), there is another factor to consider. To ensure data page consistency, the first modification of a data page after each checkpoint results in logging the entire page content."

The whole 8 kB page goes into the log the first time it is touched in a checkpoint cycle, however small the change. Which means checkpoint frequency and log volume are coupled in the direction nobody expects:

"In that case, a smaller checkpoint interval increases the volume of output to the WAL, partially negating the goal of using a smaller interval, and in any case causing more disk I/O."

Checkpointing more often to shorten recovery makes the log bigger, because every page pays its full-image toll again on the other side of each checkpoint. The two obvious tuning goals — quick recovery, small log — are in direct opposition, and the coupling runs through full-page writes. Why the full images are needed at all is the torn-page problem, described in postgres-wal-reliability.

The durability levels

synchronous_commit "specifies how much WAL processing must complete before the database server returns a 'success' indication to the client," with values remote_apply, on (the default), remote_write, local and off. The docs' own recommendation is the interesting part:

"In many situations, turning off synchronous_commit for noncritical transactions can provide much of the potential performance benefit of turning off fsync, without the attendant risks of data corruption."

The distinction matters and is easy to lose: fsync=off risks a corrupt database, while synchronous_commit=off risks losing recent transactions that were reported committed. One breaks the file; the other breaks the promise. And because the setting is per-transaction, durability is not a property of the server — a single database can honour it strictly for payments and loosely for telemetry.

Group commit, and the number to start from

The amortisation write-ahead-logging already describes — one fsync committing many transactions — has a dial. commit_delay "defines for how many microseconds a group commit leader process will sleep after acquiring a lock within XLogFlush, while group commit followers queue up behind the leader." No sleep happens if fsync is off or if fewer than commit_siblings sessions are in active transactions.

Two details that make this usable rather than decorative:

• A starting value is given, derived from measurement rather than taste. "A value of half of the average time the program reports it takes to flush after a single 8kB write operation is often the most effective setting for commit_delay."
• Platform resolution can swallow the setting entirely — "on some platforms, the resolution of a sleep request is ten milliseconds, so that any nonzero commit_delay setting between 1 and 10000 microseconds would have the same effect."

At the default of zero, group commit still happens opportunistically: a group forms from whatever sessions need to flush during the window in which the previous flush is already running.

wal_buffers sits underneath all of it. XLogInsertRecord runs "on every database low level modification… at a time when an exclusive lock is held on affected data pages," so if it has to evict filled buffers itself, that cost is paid under a lock. The docs specifically recommend raising wal_buffers when full_page_writes is on and the system is busy, "to help smooth response times during the period immediately following each checkpoint" — which is exactly when the full images arrive in a burst.

Related

write-ahead-logging · checkpoint · postgres-wal-reliability · postgresql · online-transaction-processing · patroni · pgbackrest