Checkpoint Amplification

PostgreSQL's checkpoint process periodically flushes all dirty shared buffer pages to disk, causing a predictable I/O storm at each checkpoint interval that spikes disk utilisation and elevates write transaction latency for the duration of the flush.

A PostgreSQL checkpoint is a consistency point: all dirty pages in shared_buffers are written to their data files, ensuring that a crash can recover from that checkpoint point without replaying WAL from the very beginning. Checkpoints occur either at checkpoint_timeout (default 5 minutes) or when WAL has grown by max_wal_size (default 1GB) since the last checkpoint, whichever comes first.

The checkpoint process flushes dirty pages using the bgwriter and checkpointer background processes. The total I/O volume is bounded by the number of dirty pages in shared_buffers at checkpoint time. With shared_buffers = 8GB and a write-heavy workload, the entire 8GB may be dirty by checkpoint time, requiring 8GB of disk writes in a short window.

checkpoint_completion_target controls how to spread this I/O over time: it is the fraction of the checkpoint interval the checkpointer has to finish writing all dirty pages. This default is version-scoped and the value matters: PostgreSQL 13 and earlier default to 0.5; PostgreSQL 14 (released September 2021) and every version since default to 0.9. An instance running the current default is not the aggressive case; the aggressive case is a pre-14 instance, or any instance where an operator has manually set completion_target back down toward 0.5.

At completion_target = 0.5 (pre-14 default, or a manually lowered value), the checkpoint must complete within 50% of the interval. For checkpoint_timeout = 5min, that is 2.5 minutes: all 8GB of dirty pages must be flushed in 2.5 minutes, 8GB / 150s = 53MB/s of checkpoint I/O, added to the baseline write workload.

At completion_target = 0.9 (the PostgreSQL 14+ default), the same 8GB is spread over 4.5 minutes: 8GB / 270s = 30MB/s, a lower, more sustained I/O rate instead of a concentrated burst. This is the current sane default. The failure mode's periodic I/O-spike signature is most pronounced on a pre-14 instance still running 0.5, or on any 14+ instance where completion_target has been manually lowered.

full_page_writes = on (default, safety-critical) causes the first write to any page after a checkpoint to write the full 8KB page image to WAL, not just the changed bytes. This is necessary for partial write protection during crashes but doubles or triples WAL size for write-heavy workloads. After a checkpoint, the first modification of each page in shared_buffers generates a 8KB WAL record instead of a few hundred bytes. For a working set of 100,000 pages all modified after a checkpoint, this generates 800MB of WAL records before the normal MVCC overhead.

The I/O pattern is periodic: quiet between checkpoints, then a burst at checkpoint time. This is visible in disk I/O metrics as a regular spike every checkpoint_timeout seconds, correlating with elevated write query latency.

MySQL's InnoDB storage engine has its own checkpoint mechanism, and it is a mechanically different design from PostgreSQL's, not the same knob under a different name. InnoDB uses fuzzy checkpointing: instead of flushing all dirty pages in a bounded window tied to a wall-clock checkpoint interval, InnoDB's page cleaner threads continuously flush dirty pages from the buffer pool in the background, paced against redo log capacity rather than a timer. innodb_log_file_size (or, from MySQL 8.0.30 onward, the combined innodb_redo_log_capacity that replaces the old file-size-times-file-count sizing) bounds how much redo log can exist before InnoDB is forced into a synchronous checkpoint to reclaim log space; a larger redo log gives fuzzy checkpointing more room to spread flushes over time and lowers the odds of a throughput-halting forced checkpoint. innodb_max_dirty_pages_pct (default 90) is the second lever: the page cleaner flushes more aggressively as the buffer pool's dirty page fraction approaches this ceiling, aiming to keep dirty pages bounded on an ongoing basis rather than to complete a batch within a fixed fraction of an interval. There is no InnoDB equivalent of checkpoint_completion_target, because InnoDB is not targeting "finish this flush within X% of an interval"; it is targeting "keep dirty pages and redo log utilisation under a continuous ceiling." An operator who tunes PostgreSQL checkpoint behaviour and assumes the same knob, or the same mental model, carries over to InnoDB will end up tuning the wrong parameter.