WAL and Checkpoint Tuning
A common reference says checkpoint stats live in pg_stat_bgwriter. Checked directly on this PostgreSQL 18 server, that view no longer has them at all.
A checkpoint flushes every dirty (modified) buffer in shared_buffers to disk and records a safe point WAL replay could restart from — necessary for crash recovery, but expensive: a checkpoint happening too frequently, or completing too quickly, can genuinely spike disk I/O and visibly hurt query latency for its duration. Recognizing whether checkpoints are the actual cause of an I/O spike, and whether they are firing on their normal timed schedule or being forced early ('requested') by too much WAL accumulating too fast, starts with checking the real statistics.\n\nPostgreSQL 17 moved those statistics out of pg_stat_bgwriter into a dedicated pg_stat_checkpointer view — a real, checkable fact on this PostgreSQL 18 server, not a detail to take on faith from an older reference. This lab checks that directly, triggers a real checkpoint to prove the counters genuinely move, and adjusts max_wal_size — a setting that, unlike shared_buffers from the previous lesson, takes effect with nothing more than a configuration reload.
Checking the Real, Current Location
SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;
ERROR: column "checkpoints_timed" does not exist
A genuine error, not a typo — PostgreSQL 17 moved checkpoint statistics out of pg_stat_bgwriter entirely.
SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;
num_timed | num_requested | num_done
-----------+---------------+----------
0 | 1 | 0
The real, current view: num_timed counts checkpoints that fired on their normal schedule (governed by checkpoint_timeout); num_requested counts ones forced early — typically because max_wal_size was reached before the timed interval elapsed.
A Real Checkpoint, Genuinely Triggered
CHECKPOINT;
SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;
num_timed | num_requested | num_done
-----------+---------------+----------
0 | 2 | 1
num_requested genuinely incremented from 1 to 2 — an explicit CHECKPOINT command counts as a requested checkpoint, the same category as one forced by WAL volume.
Where Checkpoint Log Messages Actually Go, Checked
SHOW log_checkpoints; -- on
SHOW logging_collector; -- off
log_checkpoints = on means every checkpoint logs a real message — but with logging_collector = off on this server, that message goes straight to the server's own stderr/console, not to a persistent log file anyone could grep later. On a real production server, logging_collector needs to be on (or logs routed to syslog/journald) for checkpoint messages to actually be reviewable after the fact.
max_wal_size: Reload, No Restart
-- edit postgresql.conf, then:
SELECT pg_reload_conf();
SHOW max_wal_size; -- 2GB
Unlike shared_buffers from the previous lesson, max_wal_size takes effect with a plain configuration reload — no server restart required.
pg_stat_checkpointer
The view holding checkpoint and restartpoint statistics as of PostgreSQL 17+ — num_timed and num_requested (this lab's equivalent of the older, now-removed pg_stat_bgwriter.checkpoints_timed/checkpoints_req), plus write_time, sync_time, and buffers_written. A high ratio of num_requested to num_timed is the direct signal that checkpoints are firing more often than the schedule intends, typically because max_wal_size is too small for the actual write volume.
logging_collector
A GUC controlling whether PostgreSQL captures its own stderr output into managed, persistent log files under log_directory. With it off, log messages (including checkpoint messages when log_checkpoints is on) still get written, but only to wherever the server process's stderr happens to be connected — not to any file that can be reviewed later, which matters a great deal for diagnosing an incident after the fact.
🔍 Find Checkpoint Statistics in Their Real, Current Location
Try the older pg_stat_bgwriter reference, see it fail, then find checkpoint statistics in their real current view.
psql -U postgres -d beer_db -c "SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;"psql -U postgres -d beer_db -c "SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;" SET ERROR: column "checkpoints_timed" does not exist LINE 1: SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwri... ^ student@lab:~$ psql -U postgres -d beer_db -c "SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;" SET num_timed | num_requested | num_done -----------+---------------+---------- 0 | 1 | 0 (1 row)
✅ Trigger a Real Checkpoint and Watch the Counter Move
Run an explicit CHECKPOINT command and confirm num_requested genuinely increments.
psql -U postgres -d beer_db -c "CHECKPOINT;"psql -U postgres -d beer_db -c "SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;"student@lab:~$ psql -U postgres -d beer_db -c "CHECKPOINT;" SET CHECKPOINT student@lab:~$ psql -U postgres -d beer_db -c "SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;" SET num_timed | num_requested | num_done -----------+---------------+---------- 0 | 2 | 1 (1 row)
📋 Check Where Checkpoint Log Messages Actually Go
Check log_checkpoints and logging_collector together to understand this server's real logging setup.
psql -U postgres -d beer_db -c "SHOW log_checkpoints;" -c "SHOW logging_collector;"student@lab:~$ psql -U postgres -d beer_db -c "SHOW log_checkpoints;" -c "SHOW logging_collector;" SET log_checkpoints ------------------ on (1 row) logging_collector -------------------- off (1 row)
🔄 Increase max_wal_size With Just a Reload
Edit max_wal_size and apply it with pg_reload_conf, confirming no restart is needed this time.
as-postgres sh -c "sed -i 's/max_wal_size = 64MB/max_wal_size = 2GB/' /var/lib/postgresql/18/data/postgresql.conf"psql -U postgres -d beer_db -c "SELECT pg_reload_conf();"psql -U postgres -d beer_db -c "SHOW max_wal_size;"student@lab:~$ as-postgres sh -c "sed -i 's/max_wal_size = 64MB/max_wal_size = 2GB/' /var/lib/postgresql/18/data/postgresql.conf" student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_reload_conf();" SET pg_reload_conf ----------------- t (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SHOW max_wal_size;" SET max_wal_size --------------- 2GB (1 row)
Lab 3.5.2 complete. WAL and checkpoint tuning, corrected against the real, current server:\n\n\n pg_stat_bgwriter checkpoint columns : ✅ confirmed removed (PG17+)\n pg_stat_checkpointer, real location : ✅ found and used directly\n Real CHECKPOINT, counter moved : ✅ num_requested 1 -> 2\n logging_collector reality checked : ✅ off — console, not a log file\n max_wal_size, reload only : ✅ 1GB -> 2GB, no restart\n
Enable JavaScript to run the live terminal and track your progress.