PostgreSQL: checkpoints are occurring too frequently – what to tune

Your PostgreSQL logs show LOG: checkpoints are occurring too frequently (9 seconds apart) with the hint Consider increasing the configuration parameter 'max_wal_size'. Sustained I/O latency spikes correlate with WAL segment rotation. On write-heavy primaries, this almost always means max_wal_size is too small for the workload.

A checkpoint flushes all dirty shared buffers to disk. By default, a checkpoint fires every 5 minutes (checkpoint_timeout) or every 1 GB of WAL (max_wal_size), whichever comes first. On a busy OLTP primary, 1 GB of WAL can accumulate in minutes. When max_wal_size triggers the checkpoint, you get a forced (requested) checkpoint. These are unpredictable, collide with existing write load, and cause visible latency spikes.

Do not suppress the warning or restart the database. Give the checkpointer enough headroom so time-based checkpoints dominate, and spread the I/O so the flush does not saturate disk.

What this means

PostgreSQL maintains two checkpoint triggers. checkpoint_timeout is the time-based ceiling. max_wal_size is the WAL-volume ceiling. The warning fires when checkpoints land closer together than checkpoint_warning seconds (default 30). In practice, this means max_wal_size is being reached long before checkpoint_timeout.

When a checkpoint fires, the checkpointer process flushes dirty pages to data files. If checkpoint_completion_target is set correctly, this write load is spread across most of the checkpoint interval. If checkpoints are firing too often because max_wal_size is small, two things happen: the I/O system never gets a clean interval between flushes, and the checkpointer may not finish spreading one checkpoint before the next begins. The result is sustained disk saturation and query latency jitter.

The default 1 GB max_wal_size is calibrated for small, low-write databases. Production OLTP workloads typically need significantly more.

flowchart TD
    A[Warning: checkpoints too frequent] --> B[Query pg_stat_bgwriter]
    B --> C{checkpoints_req dominates?}
    C -->|Yes| D[max_wal_size is the bottleneck]
    C -->|No| E[checkpoint_timeout is the bottleneck]
    D --> F[Raise max_wal_size
e.g. 4 GB or higher]
    E --> G[Increase checkpoint_timeout
e.g. 15-30 min]
    F --> H[Check checkpoint_completion_target]
    G --> H
    H --> I[Set to 0.9 if lower]
    I --> J[Monitor buffers_checkpoint
and I/O latency]

Common causes

CauseWhat it looks likeFirst thing to check
max_wal_size too small for WAL rateLog warning; checkpoints_req rising; I/O spikes correlate with write throughputSELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;
checkpoint_timeout too short for the workloadTime-driven spikes on a fixed schedule (e.g., every 5 minutes)SHOW checkpoint_timeout;
checkpoint_completion_target too lowCheckpoint I/O is bursty rather than smoothSHOW checkpoint_completion_target;
Bulk load or batch jobSudden spike during ETL; ratio normal outside the batch windowJob schedule vs. pg_stat_bgwriter timestamps

Quick checks

# Ratio of forced to timed checkpoints
psql -c "SELECT checkpoints_timed, checkpoints_req,
  ROUND(100.0 * checkpoints_req / NULLIF(checkpoints_timed + checkpoints_req, 0), 2) AS req_pct
  FROM pg_stat_bgwriter;"

# Current WAL and checkpoint configuration
psql -c "SHOW max_wal_size; SHOW checkpoint_timeout; SHOW checkpoint_completion_target;"

# Average checkpoint write time in milliseconds
psql -c "SELECT ROUND(checkpoint_write_time::numeric /
  NULLIF(checkpoints_timed + checkpoints_req, 0), 2) AS avg_write_ms
  FROM pg_stat_bgwriter;"

# WAL on disk (can exceed max_wal_size due to replication slots or archiving)
psql -c "SELECT pg_size_pretty(sum(size)) AS wal_disk_usage FROM pg_ls_waldir();"

How to diagnose it

  1. Confirm the symptom. Look for LOG: checkpoints are occurring too frequently in PostgreSQL logs, or correlate I/O latency spikes with checkpoint timing. Check pg_stat_bgwriter since server start or last reset.
  2. Determine the trigger. Compare checkpoints_timed to checkpoints_req. If checkpoints_req is more than 10% of the total, PostgreSQL is forcing checkpoints because max_wal_size was reached before the timeout. This is the smoking gun.
  3. Measure your WAL rate. If max_wal_size divided by your hourly WAL generation gives an interval shorter than checkpoint_timeout, max_wal_size is the bottleneck. Estimate WAL generation by sampling pg_current_wal_lsn() over a known time window, or by matching log timestamps to WAL segment rotations.
  4. Check I/O spreading. Divide checkpoint_write_time by (checkpoints_timed + checkpoints_req) to get the average write duration per checkpoint. If the average exceeds 30000 ms (30 seconds) and checkpoint_completion_target is below 0.9, the checkpointer is not smoothing writes.
  5. Review recent changes. If the ratio was healthy until a specific time, look for a new batch job, a bulk load, an unbatched schema migration, or cascading replication that amplifies writes.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
checkpoints_req / (checkpoints_timed + checkpoints_req)Forced checkpoints mean size is driving I/O, not time> 0.10 sustained over 24 hours
Checkpoint write time per checkpointLong writes indicate insufficient spreading or too many dirty pages> 30000 ms average
WAL generation rate vs max_wal_sizeTells you how long it takes to fill the WAL budgetInterval < checkpoint_timeout
buffers_checkpoint vs buffers_backendBackends doing direct writes indicate extreme checkpoint pressure or a starved bgwriterbuffers_backend > 20% of (buffers_checkpoint + buffers_backend) sustained

Fixes

Increase max_wal_size

This is the primary lever. The default 1 GB is calibrated for light workloads. For OLTP primaries, 4 GB is a common floor, and many write-heavy systems run 8-16 GB or higher. The right value depends on your WAL generation rate: you want max_wal_size large enough that checkpoints_timed accounts for nearly all checkpoints.

Tradeoff: Crash recovery time grows with the amount of WAL between checkpoints. More WAL means more replay on startup. Keep checkpoint_timeout reasonable (15-30 minutes) to cap the maximum replay window even if max_wal_size is large.

Remember that max_wal_size is a soft target. The server may retain more WAL on disk if replication slots, archiving, or wal_keep_size require it.

You do not need to restart PostgreSQL. From PostgreSQL 9.5 onward, max_wal_size can be changed with ALTER SYSTEM and a reload:

ALTER SYSTEM SET max_wal_size = '4GB';
SELECT pg_reload_conf();

Adjust checkpoint_timeout

If your WAL generation is low but you still see frequent time-based checkpoints, the timeout itself may be too aggressive. The default is 5 minutes. Raising it to 15 or 30 minutes reduces checkpoint frequency and gives the bgwriter more time to pre-flush dirty pages.

Tradeoff: Longer timeouts allow more dirty pages to accumulate, which can make each individual checkpoint larger. Pair a longer timeout with a larger max_wal_size and a high checkpoint_completion_target.

Spread writes with checkpoint_completion_target

This parameter controls what fraction of the checkpoint interval is used to spread writes. The default is 0.5. If it is set lower, raise it to 0.9:

ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

With 0.9, PostgreSQL tries to spread checkpoint writes across 90% of the interval, preventing the “thundering herd” flush at the end.

Handle batch loads and bulk writes

If frequent checkpoints only appear during a nightly ETL job or bulk load, the fix may be operational rather than configuration-based. Schedule large batch writes during low-traffic windows. If you must run them during peak hours, consider temporarily raising max_wal_size for the duration, then lowering it.

Do not simply increase max_wal_size to an extreme value to absorb all writes without considering recovery time. If you raise it to 64 GB on a slow disk, a crash could require an hour of WAL replay.

Prevention

  • Set max_wal_size = 4GB as a production floor for write-heavy workloads, tuning upward based on pg_stat_bgwriter ratios.
  • Monitor the ratio of checkpoints_req to checkpoints_timed. Alert if requested checkpoints exceed 10% of the total.
  • Keep checkpoint_completion_target = 0.9 on clusters where bursty I/O is unacceptable.
  • Review checkpoint metrics after any deployment that increases write throughput.

How Netdata helps

  • Track pg_stat_bgwriter metrics including checkpoints_timed, checkpoints_req, and buffers_checkpoint.
  • Correlate checkpoint timing with disk latency percentiles to spot I/O saturation from flush spikes.
  • Alert when requested checkpoints exceed your operational threshold.
  • Trend WAL generation against max_wal_size to spot pressure before the warning fires.