Parameter · Write-Ahead Log / Checkpoints
checkpoint_timeout
Sets the maximum time between automatic WAL checkpoints.
- Default:
5min - Type: integer (s)
- Range: 30s … 1d
- Applies: Reload
In plain words
A checkpoint writes all changed pages from memory to the data files so crash recovery can start from there. checkpoint_timeout is the longest gap between two automatic checkpoints.
Longer gaps mean less disk writing and less WAL (fewer full-page images) but a longer crash recovery. Many production systems use 15–30 minutes.
For DBAs and operators
A timed checkpoint starts when this interval has passed since the previous one started, unless a WAL-triggered one (from max_wal_size) came first. Idle servers skip checkpoints if nothing was written. Recovery time after a crash is roughly proportional to WAL since the last checkpoint, so the upper bound is about this interval of WAL replay (often faster than it took to generate).
Effect of raising it
- Fewer full-page images: after each checkpoint, the first change to each page logs the whole 8 kB page. Raising from 5 to 30 min can cut WAL volume dramatically on update-heavy systems.
- Pages updated repeatedly are written once per checkpoint, not many times.
- Increase
max_wal_sizeas well, or checkpoints will be triggered by WAL instead.
SELECT num_timed, num_requested, write_time, sync_time FROM pg_stat_checkpointer;Mostly num_timed means the timeout is in charge (good). The range is 30 s to 1 day. On a standby the same setting drives restartpoints.
How to change it
Change in postgresql.conf or ALTER SYSTEM, then run SELECT pg_reload_conf().
See it in context
Checkpoints Checkpointer Crash recovery The big picture Full-page writes
Related parameters
max_wal_size checkpoint_completion_target checkpoint_warning full_page_writes log_checkpoints