The graph was so regular it looked synthetic. p99 latency on a PostgreSQL 15 cluster spiked from 40 milliseconds to over 300 every five minutes, held for about thirty seconds, and recovered. Five minutes is checkpoint_timeout, and once you have seen a checkpoint-bound system you cannot unsee it: the application was healthy, the queries were unchanged, and the storage system was being asked to flush a mountain of dirty pages on a schedule, all at once. Three months earlier, on a MySQL 8.0 system, I had chased the same disease with different symptoms: no periodic spikes, just one hard stall every night around 02:20, when the batch import pushed InnoDB's redo log to its capacity and the engine slammed on the brakes until flushing caught up.
Both engines are write-ahead-logged: changes are appended to a log first, and the data pages are written later, in the background, at checkpoint time. That design is why both are fast and why both have the same failure mode. If dirty pages accumulate faster than the background machinery flushes them, the log fills, and the database must stop accepting changes until the flush catches up. The knobs that control this are completely different between MySQL and PostgreSQL, the symptoms look different, and the monitoring is different, but the physics are identical. This article is both sides of it.
What does a checkpoint actually owe you?
A checkpoint is a promise that everything in the log up to some point has been flushed to the data files, which lets the database reuse that portion of the log. Between checkpoints, modified pages sit dirty in memory, and the log grows by every change. The checkpoint horizon, the distance between the oldest dirty page's log position and the current one, is your debt ceiling. On PostgreSQL, the log is WAL in pg_wal, and max_wal_size (1GB by default) is the soft ceiling: when WAL since the last checkpoint approaches it, the checkpointer starts an aggressive checkpoint regardless of the schedule. On InnoDB, the redo log's total capacity is the hard ceiling, innodb_log_file_size times innodb_log_files_in_group on older releases, innodb_redo_log_capacity (default 100MB) on 8.0.30 and later, and the ceiling is not soft at all. Approaching it triggers first asynchronous, then synchronous flushing, and at the limit, user threads are conscripted into flushing before they may write anything else. That conscription was our 02:20 stall.
The default sizes deserve special scorn because both are sized for a laptop, not a server. A hundred megabytes of redo capacity or a gigabyte of WAL is gone in seconds on a busy write workload, which means the database runs its entire flushing behavior at the panic end of the curve. Every production system I have inherited on either engine needed this raised as one of the first three changes.
How do you tune PostgreSQL checkpoint behavior?
Start from the evidence. PostgreSQL logs a warning when checkpoints trigger too close together, and the statistics views quantify what is happening:
-- postgresql.conf
log_checkpoints = on
-- then read the log: "checkpoints are occurring too frequently
-- (8 seconds apart); consider increasing max_wal_size"
-- PostgreSQL 16 and earlier:
SELECT checkpoints_timed, checkpoints_req,
checkpoint_write_time, checkpoint_sync_time,
buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;
-- PostgreSQL 17+: the checkpointer got its own view
SELECT num_timed, num_requested, write_time, sync_time,
buffers_written
FROM pg_stat_checkpointer;Read checkpoints_req (or num_requested) carefully: those are checkpoints forced by WAL volume, not by the schedule, and a high ratio of requested to timed checkpoints means max_wal_size is the binding constraint. The tuning sequence that fixed our five-minute sawtooth had three steps. First, raise max_wal_size until requested checkpoints are rare; for that workload the working number was 16GB, and the correct ceiling is disk budget for WAL plus crash-recovery time tolerance, since more WAL between checkpoints means more to replay after a crash. Second, set checkpoint_completion_target to 0.9, so each checkpoint's writes spread across ninety percent of the interval instead of arriving as a burst at the end. Third, accept that full_page_writes, which must stay on for crash safety, makes the first touch of each page after a checkpoint log the entire page, so checkpoint frequency and WAL volume are coupled; less frequent checkpoints also shrink WAL. The full parameter tour is in the PostgreSQL checkpoint tuning guide, and the WAL side in the WAL and checkpoint piece.
Two honest caveats. Spreading a checkpoint does not remove the writes, it reschedules them, so if the steady-state dirty rate exceeds what the storage can flush continuously, no completion target saves you; you need faster storage or fewer dirty pages per second. And checkpoint spikes have accomplices: autovacuum and the background writer share the I/O budget, so a sawtooth can be a checkpoint sitting on top of a vacuum wave on top of your traffic, and pg_stat_bgwriter's buffers_backend counter, pages written by backends themselves rather than the background machinery, is the tell that the flushing pipeline is losing.
How do you tune InnoDB redo and flushing?
InnoDB's equivalent evidence lives in the engine status and the metrics tables. The numbers to watch are checkpoint age, how much of the redo capacity is currently unreclaimed, and whether InnoDB is flushing ahead of demand or being chased:
-- MySQL 8.0: checkpoint age vs capacity
SHOW ENGINE INNODB STATUS\G
-- LOG section:
-- Log sequence number 39488203411
-- ...
-- Last checkpoint at 39488203398
-- the difference is your checkpoint age; compare it to
SELECT @@innodb_redo_log_capacity; -- 8.0.30+
-- (or @@innodb_log_file_size * @@innodb_log_files_in_group before that)The rule of thumb: keep peak checkpoint age comfortably under about three quarters of capacity, because InnoDB's flushing behavior escalates as the age grows, from relaxed adaptive flushing to synchronous flushing, and past roughly seven eighths it stops user writes. Our 02:20 stall was the batch import driving the age from a daytime 60MB to nearly the full 100MB in minutes; the engine responded exactly as designed, and the design is "everyone waits." The fix was raising innodb_redo_log_capacity to 4GB, which moved the panic threshold far enough out that the adaptive flusher could do its job, and the nightly stall never recurred. The capacity mechanics and the stall signatures are covered in the redo log capacity and checkpoint stalls field notes.
Beyond capacity, two settings shape the flush itself. innodb_io_capacity and innodb_io_capacity_max tell InnoDB how much write I/O your storage can sustain for background work; defaults assume spinning disks, and on modern NVMe they are low by an order of magnitude, which means the flusher paces itself for hardware you do not own. Set them to honest measured numbers, not vendor marketing. And keep innodb_flush_log_at_trx_commit separate in your head: that is the durability knob for commit-time log flushing, a different question from redo capacity, and conflating the two is how people "fix" checkpoint stalls by weakening durability, which fixes nothing and costs plenty.
How do the failure modes differ in practice?
PostgreSQL's failure is periodic and democratic: every checkpoint interval, everyone's latency rises together, because the flush burst contends with foreground reads. It shows up as a sawtooth in p99 and a spike in checkpoint write time, and the fix is mostly about frequency and spreading. InnoDB's failure is episodic and brutal: nothing looks wrong for hours, then write throughput collapses to the speed of emergency flushing, and the period is determined by whenever write volume hits capacity rather than by any schedule. It shows up as threads piling into a flush wait state and a checkpoint age pinned near the ceiling, and the fix is mostly about capacity and I/O pacing.
The operational consequence: on PostgreSQL, alert on requested-versus-timed checkpoint ratio and checkpoint write time; on MySQL, alert on checkpoint age as a fraction of capacity. Both are cheap to compute, both predict the pain hours before users feel it, and both are more honest than inferring checkpoint health from latency alone. Also note the shared accomplice on both engines: a long-running transaction does not cause checkpoint problems directly, but the vacuum or purge backlog it creates means more work for the same flush budget later, so checkpoint tuning and transaction hygiene are coupled in practice even though the knobs are separate.
How MonPG watches the flush pipeline
Checkpoint debt is exactly the kind of signal that is obvious in hindsight and invisible in real time without history. MonPG's PostgreSQL monitoring tracks the signals this article is built from: checkpoint timing and frequency, buffer write sources, WAL generation rate, and the latency percentiles that form the sawtooth, so "checkpoints are occurring too frequently" arrives as an alert with context instead of a line you grep for after the incident review.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the counters from the 02:20 stall are the ones it will surface: checkpoint age against redo capacity as a first-class metric, flush pacing against configured I/O capacity, and the thread-state buildup that marks a sync-flush stall in progress. Until then, the MySQL monitoring page tracks that work. On either engine, the lesson is the same: the write-ahead log is a finite buffer between your write rate and your storage's patience, and the database will always choose correctness over latency when it fills. Your job is to make sure it never has to choose.