checkpoint_completion_target
Controls how smoothly checkpoints spread their I/O. Too low (0.1): periodic I/O bursts. Default 0.9: smooth flush over 90% of the checkpoint interval.
Tier 1 — Lab-Proven — Measured behavior and safety boundaries are available below.
What We Observed
box
cl_cct_low/cl_cct_hi (--memory=1g, PG 17.10), pgbench TPC-B scale 5, -c 4 -j 2 -T 60
cct 01
- tps
1019.5
- latency avg ms
3.916
- latency stddev ms
7.769
- note
high stddev = latency spikes during flush
cct 09
- tps
1184.8
- latency avg ms
3.37
- latency stddev ms
2.849
- note
smoother I/O
historical latency
- cct 01
avg=2.78ms, max=11ms, max/avg=3.96×
- cct 09
avg=3.79ms, max=9.13ms, max/avg=2.41×
- source
version note
Default changed from 0.5 to 0.9 in PostgreSQL 17. Our containers show 0.9 for all PG14-18 (configured).
formula curve
# FIXED WORK -t 40000 -c 4 = 160000 txns; checkpoint_timeout=30s FIXED; max_wal_size=16GB; img=postgres:17
completion_target avg_write_s num_timed buffers_written write_time_ms flush_buf_per_s wal_MB 0.1 1.5 3 30379 5333 4754 1210.4 0.3 6.6 6 60577 52652 1151 1481.3 0.5 10.9 6 47832 87417 547 1497.4 0.7 13.6 5 31979 81732 392 1443.3 0.9 13.3 3 6375 53255 120 1258.8
Reproduced Danger
Sizing / Safe Range
Workload Guidance
Unlock Incident Pro
The free sections tell you what to do. Pro is the measured proof — the exact failure signature, safe-ceiling formula, and per-workload defaults, each captured on real PG 14–18 runs.
- Reproduced Danger
- Sizing / Safe Range
- Workload Guidance
checkpoint_completion_target controls HOW the checkpointer paces a checkpoint's buffer writes across time — it is the SHAPE knob, not the WHEN knob. When a checkpoint fires (because checkpoint_timeout elapsed or max_wal_size was hit), PostgreSQL does not dump every dirty shared buffer to disk as fast as possible; that would be a brutal I/O spike that starves foreground queries. Instead it spreads the writes over a target fraction of the estimated time until the NEXT checkpoint. completion_target is that fraction: 0.9 (the default) means 'aim to finish flushing this checkpoint's dirty buffers when 90% of the interval has elapsed', leaving the last 10% as slack for the final sync/fsync phase. A LOW value (e.g. 0.1) crams the same buffers into the first 10% of the interval — a short, sharp I/O burst. A value near 1.0 spreads the writes over almost the whole interval (gentlest per-second I/O) but leaves no slack, risking the checkpoint not finishing before the next one is due. Crucially, completion_target does NOT change how MUCH gets written (same buffers, same WAL) — only the RATE and SHAPE of the write burst. This lab measured both the rate curve and its foreground-latency consequence.
- name
checkpoint_completion_target
- setting default
0.9
- unit
(none in pg_settings; dimensionless fraction 0–1)
- vartype
real
- category
Write-Ahead Log / Checkpoints
- context
sighup
- min val
0
- max val
1
- boot val
0.9
- reset val
0.9
- pending restart
false
- short desc
Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
- ground truth note
Captured literally from pg_settings on PG 14-18 (-18) on 2026-06-19. setting=0.9, boot_val=0.9, reset_val=0.9 on EVERY version — default_changed=false, boot_fallback_changed=false. context=sighup (reload, no restart), vartype=real, min_val=0, max_val=1 identical on all five versions. (Historically the default was 0.5 before PostgreSQL 14; it was raised to 0.9 in PG 14, so within the 14-18 range tested here it is constant.)
checkpoint_completion_target is fully stable across the tested range: default 0.9, context=sighup, vartype=real, min_val=0, max_val=1 are IDENTICAL on PG 14, 15, 16, 17, 18 (boot_val==reset_val==0.9 every version; default_changed=false, boot_fallback_changed=false). The only version story is BEFORE the range: PostgreSQL 14 raised the default from 0.5 to 0.9 (codifying the long-standing tuning advice into the default), so on every modern version you already get the spread-out behavior out of the box. Its companions in the same category are equally stable 14→18: checkpoint_timeout=300 s (the TIME trigger that sets the interval this fraction applies to), max_wal_size=1024 MB (the VOLUME trigger), and full_page_writes=on. Per-version detail in _guc_matrix_machine.
context=sighup means a config reload, no restart: edit postgresql.conf (or ALTER SYSTEM SET checkpoint_completion_target=0.9;) then SELECT pg_reload_conf; and the checkpointer uses the new fraction on its next checkpoint. No connection drop, no downtime. It is a real in [0,1] (the lab booted containers with -c checkpoint_completion_target=0.1/0.3/0.5/0.7/0.9 and confirmed the in-effect value with SHOW). Because it is sighup (not user) it is cluster-wide — a session cannot change it for itself. Its effect is only visible against a real checkpoint interval, so every experiment here held checkpoint_timeout=30s and max_wal_size=16GB so checkpoints were TIME-driven and the fraction had a fixed window to spread across. BOUNDARY PROOF (captured literal): an out-of-range / invalid value is rejected at set time -- ALTER SYSTEM SET checkpoint_completion_target = 1e13; -> ERROR: 1e+13 is outside the valid range for parameter "checkpoint_completion_target" (0 .. 1). It is context=sighup, so a session-level set is refused -- SET checkpoint_completion_target = ... -> ERROR: parameter "checkpoint_completion_target" cannot be changed now; change it in postgresql.conf / ALTER SYSTEM and reload (pg_reload_conf), no restart.
On managed PostgreSQL (Amazon RDS, Google Cloud SQL/AlloyDB, Azure Database for PostgreSQL Flexible Server, Neon) checkpoint_completion_target is a normal parameter-group / server-parameter setting, sighup, so changing it does not reboot the instance. Providers ship the 0.9 default and there is almost never a reason to change it — the smoothing it provides is what you want on shared/managed storage, where a concentrated checkpoint burst (low target) is especially punishing because you share I/O capacity with other tenants and the storage layer is networked. Amazon Aurora is the exception to the underlying mechanism: its log-structured distributed storage does not perform traditional buffer-flush checkpoints the way community PostgreSQL does, so completion_target's classic 'spread the buffer flush' behavior does not map to Aurora's storage; on standard managed Postgres (RDS non-Aurora, Cloud SQL, Azure Flexible Server, Neon) it behaves exactly as measured here. Leave it at 0.9.
Common Pitfalls
- Lowering completion_target to 'finish checkpoints faster' — that CREATES the I/O burst and the foreground latency spikes (measured: ~40× flush rate and 3.96× jitter at 0.1 vs 0.9). It is the opposite of helpful.
- Inheriting an old 0.5 from a pre-PG-14 config and never updating it — you are running with sharper checkpoint spikes than the modern default; confirm SHOW checkpoint_completion_target = 0.9.
- Setting it to 1.0 — removes the sync-phase slack; the checkpoint write can run the whole interval and overlap the next checkpoint, making checkpoints fall behind. Keep ≤ 0.9.
- Expecting it to reduce WAL volume or checkpoint frequency — it changes NEITHER (proven: wal_MB flat across the sweep). It only reshapes the write burst.
- Tuning completion_target to cure checkpoint spikes that are really a FREQUENCY problem — if checkpoints are too frequent (volume-driven), raise max_wal_size / checkpoint_timeout; this knob can only smooth a given checkpoint, not make them rarer.
- Reasoning about it without checkpoint_timeout — it is a FRACTION; its effect is meaningless without the interval it multiplies. Always reason about the pair.
Evidence and References
- PostgreSQL 18 docs — Write Ahead Log / Checkpoints: checkpoint_completion_target, checkpoint_timeout, max_wal_size (runtime-config-wal.html)
- PostgreSQL 14 release notes — default checkpoint_completion_target raised from 0.5 to 0.9
- PostgreSQL docs — WAL Configuration: spreading checkpoint writes to avoid I/O spikes; write phase ≈ completion_target × interval
- PostgreSQL docs — The Cumulative Statistics System: pg_stat_checkpointer (num_timed, buffers_written, write_time) and log_checkpoints 'checkpoint complete: . write=X s' — the signals this lab measured
- Captured live on PG 14-18 (-18) ground truth + postgres:17 effect/latency labs (cl_cct, cl_cctlat), 2026-06-19, sev1db-config-eval
Technical Reference
- Category
- Write-Ahead Log / Checkpoints
- Type
- real ((dimensionless fraction 0–1 of the checkpoint interval; no unit string in pg_settings))
- Blast radius
- cluster
- Severity
- Moderate
- Context
- sighup
- Versions
- 14, 15, 16, 17, 18
- Reviewed
- 2026-06-19
Severity scale: 1 Minimal · 2 Low · 3 Moderate · 4 High · 5 Critical.