A PostgreSQL application may perform well for hours and then suddenly experience slower inserts, updates, or bulk data imports. Queries that normally finish quickly begin taking longer, application requests pile up, and database latency increases even though the application code has not changed.
One possible cause is checkpoint activity.
PostgreSQL uses Write-Ahead Logging (WAL) to record database changes before the corresponding data pages are safely written to their final locations. Checkpoints help PostgreSQL maintain a recoverable database state by ensuring that changes up to a particular point have been written to the data files. During this process, PostgreSQL may write a large number of dirty pages to storage, creating additional I/O pressure.
If checkpoints happen too frequently or generate a large burst of disk activity, they can compete with normal database operations for storage bandwidth. The result may be higher write latency, slower queries, and inconsistent application response times.
However, a checkpoint is not automatically a performance problem. Checkpoints are a normal part of PostgreSQL operation, and write slowdowns can also result from slow storage, WAL pressure, autovacuum activity, locks, or other workloads.
The key is to measure checkpoint activity and correlate it with the period when the slowdown occurs.
This article explains how PostgreSQL checkpoints work, how to inspect checkpoint statistics, how to identify possible causes of sudden write slowdowns, and how to tune checkpoint settings without creating unnecessary recovery or storage risks.
Understand How PostgreSQL Checkpoints Work
When a transaction modifies a database page, PostgreSQL generally changes the page in shared memory before writing it back to the database files. The modified page is known as a dirty page.
At the same time, PostgreSQL records the required changes in WAL. WAL provides the information needed to recover database changes after a crash.
Writing every modified data page to its final location immediately would create unnecessary disk activity. Instead, PostgreSQL can accumulate changes in memory and write modified pages later.
A checkpoint establishes a recovery point by ensuring that the required WAL and data-page writes have reached the necessary persistence state. After a checkpoint completes, PostgreSQL can limit how far back it needs to process WAL during crash recovery.
Checkpoints are triggered according to configured time and WAL-generation thresholds, among other conditions.
Why checkpoints can affect write performance
Consider an application that continuously inserts and updates records. As the workload modifies pages, PostgreSQL accumulates dirty pages in memory and generates WAL.
When checkpoint activity causes many dirty pages to be written, storage devices must handle those writes alongside the application's normal database activity. If storage bandwidth or IOPS are limited, the extra work can increase latency.
For example, a database might normally complete an update quickly, but the update takes longer while the system is under heavy checkpoint-related I/O pressure.
The checkpoint may be part of the explanation, but timing alone does not prove causation. A high-write workload can both generate WAL rapidly and increase checkpoint activity. Storage saturation may be the underlying limitation affecting both.
This is why checkpoint statistics should be compared with query latency, WAL generation, and operating-system or cloud storage metrics.
Check Checkpoint Statistics in PostgreSQL
PostgreSQL exposes checkpoint statistics through the pg_stat_checkpointer view in supported versions, including PostgreSQL 17 and later. Older versions expose checkpoint-related counters through pg_stat_bgwriter, so the appropriate view depends on the database version.
For PostgreSQL 17 and later, start with:
SELECT
num_timed,
num_requested,
num_done,
restartpoints_done,
write_time,
sync_time,
buffers_written,
stats_reset
FROM pg_stat_checkpointer;
These statistics provide an overview of checkpoint activity since the statistics were last reset.
The main fields to examine are:
num_timed: The number of checkpoints initiated because of the configured checkpoint timing.
num_requested: The number of checkpoints requested before the next timed checkpoint, often because of WAL pressure.
num_done: The number of checkpoints completed.
write_time: The accumulated time spent writing checkpoint buffers, when checkpoint timing statistics are enabled.
sync_time: The accumulated time spent synchronizing files to durable storage, when timing statistics are enabled.
buffers_written: The number of buffers written during checkpoint processing.
stats_reset: The time when the statistics were last reset.
The exact fields available depend on the PostgreSQL version. Confirm the view definition in your target environment before using a monitoring query in production.
Understand timed and requested checkpoints
A high num_timed value is not automatically a warning. Timed checkpoints are expected in a normally operating database.
The num_requested counter is particularly useful when investigating frequent checkpoint activity. If requested checkpoints accumulate rapidly, WAL generation or the configured checkpoint thresholds may be causing checkpoints to occur more often than expected.
Compare the counters over time instead of examining only their lifetime totals.
For example, if num_requested increases by 100 during a short monitoring interval while write latency rises sharply, the pattern deserves investigation. The number alone is not enough to establish a problem, but it provides a useful lead.
Also remember that these statistics are cumulative. A large value may simply reflect a database that has been running for a long time.
Measure Checkpoint Activity Over Time
To identify a sudden slowdown, collect checkpoint statistics at regular intervals and calculate the difference between consecutive samples.
For example, capture the current values:
SELECT
clock_timestamp() AS sampled_at,
num_timed,
num_requested,
write_time,
sync_time,
buffers_written
FROM pg_stat_checkpointer;
Run the query again after a known interval and compare the results.
If requested checkpoints, buffers written, and checkpoint write time increase rapidly during the same period that application latency rises, checkpoint activity may be contributing to the slowdown.
The most useful measurements include:
Measurement | What it helps identify |
|---|
Increase in requested checkpoints | Checkpoints triggered by pressure before the normal schedule |
Increase in checkpoint write time | Time spent writing checkpoint buffers |
Increase in checkpoint sync time | Time spent synchronizing files |
Increase in buffers written | Amount of buffer-writing work |
WAL generation rate | How quickly the workload produces WAL |
Disk latency and utilization | Whether storage is becoming a bottleneck |
Application write latency | Whether users experience the slowdown |
A single measurement is rarely sufficient. Checkpoint write time may be high because a large workload generated many dirty pages, while slow storage may be responsible for the actual latency problem.
Account for timing statistics
PostgreSQL checkpoint timing statistics depend on the relevant configuration settings. If timing collection is disabled, timing counters may not provide useful values.
Check the configuration:
SHOW track_io_timing;
SHOW checkpoint_timeout;
SHOW max_wal_size;
SHOW checkpoint_completion_target;
The track_io_timing setting controls collection of certain I/O timing information. Its overhead depends on the operating system and workload, so evaluate it in the context of your environment.
The other settings help explain how checkpoint scheduling is configured. Do not change them immediately; first establish what the current workload is doing.
Find Out Whether WAL Pressure Is Triggering Checkpoints
PostgreSQL can request a checkpoint when WAL generation reaches a threshold before the next scheduled checkpoint.
The max_wal_size setting influences when PostgreSQL requests checkpoints because of WAL volume. It is not a strict maximum that guarantees the WAL directory can never exceed that size. Actual disk usage can vary because of workload behavior, archiving, replication slots, and other retention requirements.
If requested checkpoints increase rapidly, examine the rate at which WAL is generated.
On PostgreSQL versions that provide the relevant functions, you can capture the current WAL position:
SELECT
clock_timestamp() AS sampled_at,
pg_current_wal_lsn() AS current_wal_lsn;
Run the query at two different times and calculate the difference between the returned WAL locations using pg_wal_lsn_diff.
For example, if the WAL position advances by several gigabytes in a short interval, the application may be generating WAL rapidly. The cause could be a bulk import, large updates, index maintenance, or another write-intensive operation.
A high WAL generation rate is not inherently a problem. The important question is whether the database's checkpoint configuration, storage capacity, and WAL retention behavior can support the workload.
Also check whether replication slots or delayed archiving are retaining WAL. Those conditions can increase disk usage even when checkpoint frequency is not the main issue.
Understand Checkpoint Configuration
PostgreSQL provides several settings that influence checkpoint behavior. Changing them can help, but each setting has trade-offs.
Setting | Purpose | What to consider |
|---|
checkpoint_timeout | Sets the maximum time between scheduled checkpoints | Longer intervals can reduce timed checkpoint frequency but may increase recovery work |
max_wal_size | Influences when WAL volume triggers requested checkpoints | A larger value can reduce WAL-pressure checkpoints but requires sufficient storage |
min_wal_size | Influences how much WAL PostgreSQL retains for reuse | Can help reduce repeated WAL file creation and removal |
checkpoint_completion_target | Controls the portion of the checkpoint interval over which checkpoint work is spread | Spreading writes can reduce bursts, but the value must fit the workload and checkpoint interval |
These settings should be tuned together rather than changed independently without measurement.
Adjust max_wal_size carefully
If requested checkpoints occur frequently because the workload generates WAL rapidly, increasing max_wal_size may give PostgreSQL more room between WAL-triggered checkpoints.
However, a larger value does not eliminate the need for checkpoints. It changes the conditions under which some checkpoints are requested and can allow more WAL to accumulate.
Before increasing it, verify that the database has sufficient disk capacity and that WAL retention is understood. Replication slots or archive problems may already be causing WAL to occupy more space than expected.
Monitor the WAL directory, storage headroom, checkpoint frequency, and recovery requirements after changing the setting.
Review checkpoint_timeout
A longer checkpoint_timeout can reduce the frequency of time-triggered checkpoints. However, longer intervals can increase the amount of WAL that must be processed during crash recovery.
The appropriate value depends on write volume, storage performance, recovery objectives, and the rest of the checkpoint configuration.
Do not increase it simply because a database has slow writes. First determine whether timed checkpoints are actually the problem.
Understand checkpoint_completion_target
PostgreSQL can spread checkpoint writes over part of the interval between checkpoints. The checkpoint_completion_target setting controls this behavior.
Spreading writes can help avoid a sudden burst of I/O, but it cannot create additional storage capacity. If the storage system cannot keep up with the total write workload, smoothing the writes may not be enough.
After changing the setting, compare checkpoint duration, disk latency, and application write latency. The goal is to reduce disruptive bursts while allowing checkpoints to complete reliably.
Check Whether Storage Is the Real Bottleneck
A checkpoint-related slowdown may actually be a storage-performance problem.
When PostgreSQL writes many dirty pages, it competes with WAL writes, normal database activity, and other processes using the same storage. If the underlying disk or cloud volume has limited throughput or IOPS, latency can rise significantly.
Inspect the operating system's storage metrics or the monitoring metrics supplied by your cloud database provider.
Look for:
Increased disk read and write latency.
Sustained high IOPS or throughput utilization.
Queue depth or pending storage operations.
Storage throughput limits being reached.
Sudden changes in performance during bulk operations.
Contention from other workloads using the same storage volume.
If the database runs in a managed cloud service, check the provider's metrics for the database storage tier and any configured IOPS or throughput limits.
Do not assume that increasing PostgreSQL's memory settings will solve a storage bottleneck. More memory may reduce some reads, but it does not necessarily reduce the WAL and data-page writes required by the workload.
Investigate Other Causes of Sudden Write Slowdowns
Checkpoint activity is only one possible cause of slow writes. Check other sources of contention before deciding that checkpoint tuning is the right solution.
Check for lock contention
A transaction may be waiting for another transaction to release a lock. In that situation, the SQL statement appears slow even if storage and checkpoint activity are normal.
Use pg_stat_activity to inspect active sessions and wait events. For lock-related problems, examine pg_locks and identify blocking sessions.
A checkpoint configuration change will not solve a transaction that is waiting for a conflicting lock.
Check autovacuum activity
Autovacuum helps PostgreSQL reclaim dead tuples and maintain table statistics. Under some workloads, maintenance activity can compete with application queries for CPU and storage resources.
Inspect active processes and database statistics to determine whether maintenance is occurring during the slowdown.
Do not disable autovacuum as a quick fix. Doing so can allow table bloat to increase and may eventually cause more serious performance problems.
Check the workload
Bulk inserts, large updates, index creation, and maintenance operations can generate substantial WAL and data-page writes.
Compare the slowdown with deployment events, scheduled jobs, data imports, and reporting workloads. If a particular operation consistently triggers the problem, consider batching it, scheduling it during quieter periods, or optimizing its write pattern.
The goal is to identify the workload that creates the pressure, not merely to change the database configuration in response to the symptoms.
Common Mistakes When Diagnosing Checkpoint Problems
Treating every checkpoint as a failure
Checkpoints are a normal part of PostgreSQL operation. Investigate the frequency, duration, and effect on the workload rather than trying to eliminate checkpoints entirely.
Changing several settings at once
If you change max_wal_size, checkpoint_timeout, and checkpoint_completion_target together, it becomes difficult to know which change helped or harmed performance. Change one setting at a time when practical and compare the results.
Ignoring WAL retention
Increasing WAL-related thresholds without checking disk capacity can create storage pressure. Replication slots and archiving problems can also retain WAL independently of the checkpoint tuning you are investigating.
Relying on cumulative counters
Large counters may reflect months of normal activity. Compare values over known time intervals and correlate them with application latency.
Tuning PostgreSQL without checking storage metrics
A database cannot write faster than its storage system allows under the relevant workload. Investigate disk latency, throughput, and IOPS before assuming that configuration changes alone will solve the problem.
Best Practices for Production PostgreSQL Databases
Collect checkpoint statistics and storage metrics continuously so that you have a baseline before an incident occurs. Track requested and timed checkpoint activity, checkpoint write and sync times, WAL generation, and available disk space.
Correlate these measurements with application latency and database activity. When a slowdown occurs, capture statistics before restarting PostgreSQL or changing settings.
Tune checkpoint parameters using representative workloads and change one setting at a time. After each change, measure the effect on checkpoint frequency, write latency, storage utilization, and recovery behavior.
Also maintain enough storage capacity for normal WAL generation and retention requirements. Monitor replication slots and archive health, and ensure that recovery objectives remain achievable after any checkpoint configuration changes.
Advantages and Disadvantages of Checkpoint Tuning
Advantages
Appropriate tuning can reduce unnecessary checkpoint frequency caused by WAL pressure.
Spreading checkpoint writes can help reduce sudden I/O bursts.
Better monitoring makes it easier to connect database slowdowns with checkpoint activity.
Workload analysis can reveal bulk operations or storage limits that need attention.
A measured tuning process reduces the risk of making changes based on assumptions.
Disadvantages
Increasing checkpoint intervals or WAL thresholds can increase storage requirements or crash-recovery work.
Checkpoint tuning cannot fix every storage, locking, or workload problem.
Larger WAL retention requirements may put pressure on limited disk capacity.
Poorly tested changes can make recovery behavior or performance less predictable.
Accurate diagnosis requires measurements collected over time, not just a single query.
Summary
Sudden PostgreSQL write slowdowns can be related to checkpoint activity, but checkpoints are only one possible cause. Start by examining pg_stat_checkpointer on supported PostgreSQL versions, comparing timed and requested checkpoint activity, and measuring changes over time.
Next, compare checkpoint statistics with WAL generation, storage latency, and application write performance. If requested checkpoints are increasing rapidly, investigate whether WAL thresholds are too restrictive for the workload. If checkpoint writes coincide with storage saturation, address the underlying I/O limitation as well.
Review checkpoint_timeout, max_wal_size, min_wal_size, and checkpoint_completion_target only after establishing what is happening. Also investigate locks, autovacuum, bulk operations, and WAL retention before deciding on a fix.
The objective is not to prevent checkpoints. It is to ensure that PostgreSQL performs checkpoint work without creating avoidable disruption, while maintaining appropriate storage capacity and crash-recovery behavior.
Join the conversation! Your thoughts help the community grow.