In PostgreSQL, what triggers an automatic checkpoint, what are the defaults of checkpoint_timeout, max_wal_size and checkpoint_completion_target, and what does raising them trade?
answer
- time or WAL volume, whichever first
- 5 minutes and 1 GB
- spread over 90% of the interval
- fewer full-page images, longer recovery
basics
~20 sA checkpoint starts after checkpoint_timeout (5 min) or when WAL growth would exceed max_wal_size (1 GB), and its writes are spread over checkpoint_completion_target (0.9) of the interval. Raising them smooths I/O and cuts WAL but lengthens crash recovery.
solid answer
~40 sThe checkpointer starts a checkpoint when `checkpoint_timeout` (default **5min**, range 30s to 1 day) has passed or when WAL since the last checkpoint would otherwise push `pg_wal` past `max_wal_size` (default **1GB**), whichever comes first; a timed checkpoint is skipped if no WAL was written. Its writes are spread so it finishes after `checkpoint_completion_target` (default **0.9**) of the interval. Because WAL keeps coming while that spread checkpoint runs, the WAL-driven trigger fires at about `max_wal_size / (1 + checkpoint_completion_target)`, roughly 540 MB at defaults. Raising the timeout and `max_wal_size` means fewer checkpoints, fewer **full-page images** and smoother I/O; the price is longer crash recovery and more disk for `pg_wal`. All three settings take effect on a reload.
code
ini · 4 linescheckpoint_timeout = 15min
max_wal_size = 20GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9go deeper
Remember the two triggers - time and WAL volume - and the defaults: 5 minutes, 1 GB, completion target 0.9.
Explain how the WAL trigger is computed, why spreading the writes matters, and why fewer checkpoints means fewer full-page images and less WAL.
Size max_wal_size from measured WAL per interval so checkpoints are timed, and state the recovery-time and disk cost you accept for it.
Tie checkpoint spacing to a stated recovery-time objective and storage budget, and decide who owns those numbers when workloads change.
## What a checkpoint does here A PostgreSQL **checkpoint** is run by the checkpointer process: it writes every dirty page in shared buffers to the data files, asks the OS to flush them, writes a checkpoint record into the write-ahead log (WAL) and updates `pg_control`. The checkpoint's **redo point** is where crash recovery starts replaying, so how often checkpoints happen decides both how much I/O they cause and how long recovery takes. ## What triggers one 1. **Time.** `checkpoint_timeout` has elapsed since the last checkpoint started (default `5min`, allowed 30 s to 1 day). If no WAL has been written since the previous checkpoint, the timed checkpoint is skipped. 2. **WAL volume.** Enough WAL has been written that continuing would exceed `max_wal_size` (default `1GB`). Because WAL keeps arriving while a spread checkpoint runs, the checkpointer requests one after about `max_wal_size / (1 + checkpoint_completion_target)` of WAL - roughly 540 MB with the defaults - so the WAL written during the checkpoint still fits. 3. **Explicit requests.** The `CHECKPOINT` command, a clean shutdown, and the start of a base backup. `pg_stat_checkpointer` counts the first kind as `num_timed` and the WAL-driven and explicit kinds as `num_requested`. ## The settings | Setting | Default | Change with | What it does | |---|---|---|---| | `checkpoint_timeout` | `5min` (30 s to 1 d) | reload | longest time between automatic checkpoints | | `max_wal_size` | `1GB` | reload | soft ceiling on WAL between checkpoints; drives WAL-triggered checkpoints | | `min_wal_size` | `80MB` | reload | WAL always kept for recycling, even when idle | | `checkpoint_completion_target` | `0.9` | reload | fraction of the interval over which writes are spread | | `checkpoint_warning` | `30s` | reload | log a warning when WAL-triggered checkpoints come closer than this | | `checkpoint_flush_after` | `256kB` on Linux | reload | ask the OS to write back checkpoint writes in smaller chunks | `max_wal_size` is a **soft** limit: heavy load, failing archiving or a large `wal_keep_size` can push `pg_wal` beyond it. ## What raising them buys - **Fewer full-page images.** With `full_page_writes = on` (the default), the first change to each page after a checkpoint logs the whole 8 kB page. Fewer checkpoints means fewer such images, so less WAL. - **Less repeated writing.** A hot page dirtied many times between checkpoints is written once per checkpoint, not once per change. - **Smoother I/O.** Time-driven checkpoints spread their writes over 90% of a longer interval, instead of being compressed by a WAL-driven deadline. ## What it costs - **Longer crash recovery**, because more WAL lies between the redo point and the crash. The manual says so explicitly for `max_wal_size`. - **More disk in `pg_wal`**, sized by `max_wal_size` plus whatever other holders keep. - **A standby follows the primary.** Restartpoints on a standby can only happen at checkpoint records the primary wrote, so its recovery cadence follows the primary's. Leave `checkpoint_completion_target` at 0.9. ## On a standby: restartpoints A standby in recovery cannot write checkpoint records of its own. Instead it performs **restartpoints**: it flushes its state to disk, updates `pg_control` so that already-replayed WAL need not be scanned again, and recycles old segments. The same two settings drive them: - A restartpoint is scheduled when a replayed checkpoint record is reached and at least `checkpoint_timeout` has passed since the last one. - One is requested when WAL is about to exceed `max_wal_size`, but it can only run at a checkpoint record already replayed, so during recovery `pg_wal` can overshoot `max_wal_size` by up to one checkpoint cycle's worth of WAL. `pg_stat_checkpointer` counts them as `restartpoints_timed`, `restartpoints_req` and `restartpoints_done`; only `restartpoints_done` means real work was done. A burst of `restartpoints_req` during a mass insert on the primary is normal: the standby has asked for restartpoints it cannot perform yet. Lowering it concentrates the writes into bursts; raising it to 1.0 makes it likely that checkpoints do not finish on time. ## Applying a change All of these are reload-level settings, so no restart is needed: ```ini # postgresql.conf for a write-heavy payments ledger checkpoint_timeout = 15min max_wal_size = 20GB min_wal_size = 2GB checkpoint_completion_target = 0.9 ``` Then run `SELECT pg_reload_conf();` (or `pg_ctl reload`) and confirm with `SHOW max_wal_size;`. The values above are an example: size `max_wal_size` from the WAL your workload writes per `checkpoint_timeout`, so that most checkpoints become timed rather than requested, and check that recovery time remains acceptable.
- Do these checkpoint settings need a server restart?No. `checkpoint_timeout`, `max_wal_size`, `min_wal_size` and `checkpoint_completion_target` are all reload-level: edit `postgresql.conf` or use `ALTER SYSTEM`, then `SELECT pg_reload_conf();`. The checkpointer picks the new values up without dropping connections.
- Why can lowering checkpoint_timeout to shorten recovery increase WAL volume?With `full_page_writes = on`, the first change to each page after every checkpoint writes a full page image into WAL. More frequent checkpoints mean more pages are 'first changed' again, so the WAL grows, partly undoing the gain and adding I/O.
- Dirty pages only reach the data files at checkpoints.
- max_wal_size is a hard cap that pg_wal can never exceed.
- checkpoint_completion_target = 1.0 is best because it spreads I/O the most.
- Changing max_wal_size requires a server restart.
- A larger max_wal_size makes crash recovery faster.