检查 checkpoint 行为（PG17+ 为 pg_stat_checkpointer，PG16 及更早为 pg_stat_bgwriter）

命令

-- PG17+
SELECT num_timed,
       num_requested,
       round(100.0 * num_requested / NULLIF(num_timed + num_requested, 0), 1) AS req_pct,
       restartpoints_timed,
       restartpoints_req,
       round((write_time / 1000)::numeric / NULLIF(num_timed + num_requested, 0), 1) AS avg_write_s,
       round((sync_time / 1000)::numeric / NULLIF(num_timed + num_requested, 0), 1) AS avg_sync_s,
       pg_size_pretty(buffers_written * current_setting('block_size')::bigint) AS written_by_checkpoint,
       stats_reset
FROM pg_stat_checkpointer;

-- PG16 and earlier
SELECT checkpoints_timed,
       checkpoints_req,
       round(100.0 * checkpoints_req / NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS req_pct,
       round((checkpoint_write_time / 1000)::numeric / NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS avg_write_s,
       round((checkpoint_sync_time / 1000)::numeric / NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS avg_sync_s,
       pg_size_pretty(buffers_checkpoint * current_setting('block_size')::bigint) AS written_by_checkpoint,
       pg_size_pretty(buffers_clean * current_setting('block_size')::bigint) AS written_by_bgwriter,
       pg_size_pretty(buffers_backend * current_setting('block_size')::bigint) AS written_by_backends,
       maxwritten_clean,
       stats_reset
FROM pg_stat_bgwriter;

-- Related settings
SELECT name, current_setting(name) AS value
FROM pg_settings
WHERE name IN ('checkpoint_timeout', 'max_wal_size', 'min_wal_size',
               'checkpoint_completion_target', 'log_checkpoints')
ORDER BY name;

作用

显示计划 checkpoint 与因 WAL 量而强制触发的 checkpoint 的比例、每次 checkpoint 的平均写入/同步时间以及写出的缓冲区量（在 PG16 及更早版本中，还包括 bgwriter 和后端进程的写入）。

适用场景

当写入密集时段出现 I/O 尖峰、WAL 生成量高，或日志警告 'checkpoints are occurring too frequently' 时。

注意

较高的 req_pct（通常高于 10-20%）表示由于 max_wal_size 被填满而强制触发 checkpoint；请调大 max_wal_size，并在 log_checkpoints = on 时关注 'checkpoint starting: wal' 行。较长的 checkpoint_timeout 会分散 I/O，但会延长崩溃恢复时间。在 PG16 及更早版本中，written_by_backends 较高表示后端进程必须自己写出脏页（shared_buffers/bgwriter 不足）；在 PG17 中此信息位于 pg_stat_io。平均值也计入了因无工作而跳过的计划 checkpoint，因此只能作为粗略数字。计数器是自 stats_reset 以来的累计值。