Articles

Postgres Checkpoint Tuning: Reduce I/O Spikes (2026)

April 18, 2026 6 min read

Postgres checkpoint tuning is less about preventing I/O spikes and more about orchestrating them to happen when you can afford them.

Let’s watch a checkpoint in action. Imagine a busy PostgreSQL database. Transactions are pouring in, data is being written to memory buffers.

-- While transactions are active, observe the WAL write-ahead log.
-- wal_writer_delay is typically 200ms.
SELECT pg_current_wal_lsn();

Eventually, these dirty buffers in memory need to be flushed to disk. This is the checkpoint.

-- Trigger a manual checkpoint for observation.
CHECKPOINT;

During a checkpoint, PostgreSQL writes all "dirty" data pages from shared memory to disk. This ensures that all committed transactions up to a certain point are durably stored. Without checkpoints, the system would have to scan the entire Write-Ahead Log (WAL) to recover after a crash, which is prohibitively slow for large databases. The problem arises when this write activity becomes a massive, sudden I/O spike that saturates your storage, impacting concurrent queries.

The core of checkpoint tuning lies in controlling two primary parameters: max_wal_size (formerly checkpoint_segments in older versions) and checkpoint_timeout.

1. max_wal_size (or checkpoint_segments): The "Amount" of WAL

  • What it is: This parameter determines the maximum amount of WAL that can be generated between checkpoints. PostgreSQL will try to trigger a checkpoint when the WAL volume reaches this size, even if checkpoint_timeout hasn’t elapsed.
  • Diagnosis: Check your current WAL size and the configured max_wal_size.
    # On the server, check the current WAL directory size
du -sh /var/lib/postgresql/14/main/pg_wal/
# Check the current configuration value
SHOW max_wal_size;
  • Common Cause & Fix: If your WAL files are growing very large and checkpoints are infrequent, it means you’re accumulating a lot of dirty data, leading to a massive flush when the checkpoint finally does happen. You need to make checkpoints happen more often by lowering max_wal_size.
    • Diagnosis: Look at your postgresql.conf or use SHOW max_wal_size;. If it’s set to a very high value (e.g., 4GB or 16GB) and you see long periods of high I/O, it’s likely too high.
    • Fix: Lower max_wal_size. For instance, if you’re seeing 10GB of WAL generated before a checkpoint, reduce max_wal_size to 8GB.
      # postgresql.conf
max_wal_size = 8GB
    • Why it works: A smaller max_wal_size means PostgreSQL will initiate a checkpoint sooner, when less WAL has been generated. This breaks down the large flush into smaller, more frequent flushes, smoothing out I/O.

2. checkpoint_timeout (or checkpoint_completion_target): The "Time" Between Checkpoints

  • What it is: This parameter sets the maximum time allowed between automatic checkpoints. PostgreSQL will trigger a checkpoint if this time elapses, regardless of WAL size.
  • Diagnosis: Check the current checkpoint_timeout and observe the actual time between checkpoints.
    SHOW checkpoint_timeout;
-- You can monitor checkpoint activity in the logs.
-- Look for lines like: "CHECKPOINT starting: pid=XXXX" and "CHECKPOINT completed"
  • Common Cause & Fix: If your database is very active but checkpoint_timeout is set too high (e.g., 30min), WAL can accumulate significantly between the timed checkpoints, leading to large I/O spikes. You need to reduce the time between checkpoints.
    • Diagnosis: If your checkpoint_timeout is 30min and you have periods of heavy write activity, consider reducing it.
    • Fix: Lower checkpoint_timeout. A common starting point is 5min or 10min.
      # postgresql.conf
checkpoint_timeout = 5min
    • Why it works: By reducing the maximum time between checkpoints, you force PostgreSQL to perform smaller, more frequent flushes of dirty buffers, thereby reducing the peak I/O load associated with each checkpoint.

3. checkpoint_completion_target (The "Smoothness" of the Flush)

  • What it is: This parameter controls how gradually the data is written during a checkpoint. It’s a fraction (0.0 to 1.0) of the time between checkpoints (checkpoint_timeout) during which the checkpoint writes must be completed.
  • Diagnosis: Check the current checkpoint_completion_target.
    SHOW checkpoint_completion_target;
  • Common Cause & Fix: The default checkpoint_completion_target is 0.5 (50%). If your storage system can handle higher bursts, or if you want to make checkpoints even less disruptive, you can increase this value. This spreads the I/O over a longer period within the checkpoint interval.
    • Diagnosis: If you’ve tuned max_wal_size and checkpoint_timeout but still see significant I/O spikes, you can try to smooth out the remaining writes.
    • Fix: Increase checkpoint_completion_target towards 0.9 or 0.95.
      # postgresql.conf
checkpoint_completion_target = 0.9
    • Why it works: A higher checkpoint_completion_target means PostgreSQL will spread the checkpoint writes over a longer portion of the interval between checkpoints. This reduces the peak I/O rate during the checkpoint, making it less likely to saturate your storage.

4. WAL Writer Activity

  • What it is: The wal_writer_delay parameter controls how often the WAL writer process flushes WAL buffers to disk. While not directly a checkpoint parameter, it affects how quickly WAL accumulates and can indirectly influence checkpoint frequency and size.
  • Diagnosis: Check the current wal_writer_delay.
    SHOW wal_writer_delay;
  • Common Cause & Fix: The default 200ms is usually fine. However, on very high-throughput systems, if WAL buffers are frequently full, the WAL writer might be a bottleneck. This can lead to more data accumulating before a checkpoint is forced by max_wal_size.
    • Diagnosis: If your system is extremely write-heavy, and you notice WAL buffers are frequently flushed by the WAL writer, you might consider a slight reduction.
    • Fix: Lower wal_writer_delay. For instance, reducing it to 100ms can make the WAL writer more aggressive.
      # postgresql.conf
wal_writer_delay = 100ms
    • Why it works: A lower wal_writer_delay means WAL buffers are flushed to disk more frequently by the WAL writer process itself. This can help keep the total amount of WAL data accumulated down, potentially allowing max_wal_size to be reached less often or checkpoint_timeout to be hit with less accumulated data.

5. bgwriter_delay and bgwriter_lru_maxpages

  • What it is: These parameters control the background writer process, which aims to clean dirty buffers before a checkpoint is forced. bgwriter_delay is how often it wakes up, and bgwriter_lru_maxpages is the maximum number of dirty buffers it will write per round.
  • Diagnosis: Check current settings.
    SHOW bgwriter_delay;
SHOW bgwriter_lru_maxpages;
  • Common Cause & Fix: If the background writer isn’t aggressive enough, many buffers become dirty, and the checkpoint has to do a lot of work. Increasing the background writer’s effectiveness can reduce the load on checkpoints.
    • Diagnosis: If you see checkpoints consistently writing a very large number of pages, and your bgwriter_delay is high (e.g., 200ms), you might benefit from increasing bgwriter_lru_maxpages.
    • Fix: Decrease bgwriter_delay and/or increase bgwriter_lru_maxpages.
      # postgresql.conf
bgwriter_delay = 50ms       # Wake up more often
bgwriter_lru_maxpages = 1000 # Write more pages per round
    • Why it works: A more active background writer cleans dirty buffers proactively. This means fewer dirty buffers need to be flushed during the checkpoint itself, reducing the I/O spike.

6. Storage Performance

  • What it is: Ultimately, the ability of your storage subsystem to handle write I/O dictates how much impact a checkpoint has.
  • Diagnosis: Monitor your storage I/O metrics (IOPS, throughput, latency) during checkpoints.
    # Example using iostat on Linux
iostat -xd 5
  • Common Cause & Fix: If your storage is already saturated with regular application I/O, any additional write activity from a checkpoint will cause noticeable degradation.
    • Diagnosis: High %util, high await, and low iops or throughput during writes indicate a storage bottleneck.
    • Fix: Upgrade your storage (e.g., from HDDs to SSDs, or faster SSDs), configure RAID for better performance, or use a SAN with higher capabilities.
    • Why it works: Faster storage can absorb the write load of a checkpoint more quickly, reducing the duration and impact of the I/O spike.

The next error you’ll hit after optimizing your checkpoints is likely related to WAL archiving, if you’re using it, or potentially very high CPU utilization if the database is struggling to keep up with write demands even after smoothing I/O.

Want structured learning?

Take the full Postgres course →