PostgreSQL WAL and Checkpoint Monitoring

Purpose

Provides visibility into WAL generation and checkpoint behaviour:

1. Checkpoint frequency and timing (requested vs. timed)
2. bgwriter statistics — buffers clean, maxwritten stops
3. WAL generation rate estimate
4. Checkpoint configuration parameters

Required Permissions

pg_monitor role or SELECT on pg_stat_bgwriter, pg_stat_wal (PostgreSQL 14+).

Script

-- ============================================================
-- PostgreSQL WAL and Checkpoint Monitoring
-- Permissions: pg_monitor
-- Risk: Read-only
-- ============================================================

-- 1. Checkpoint statistics
--    checkpoints_req: checkpoints triggered by WAL fullness (bad — indicates checkpoint too slow)
--    checkpoints_timed: checkpoints at checkpoint_timeout (normal)
SELECT
    checkpoints_timed,
    checkpoints_req,
    CASE WHEN checkpoints_timed + checkpoints_req > 0
        THEN ROUND(
            100.0 * checkpoints_req / (checkpoints_timed + checkpoints_req), 1
        )
    END                              AS pct_requested,
    checkpoint_write_time / 1000.0   AS write_time_seconds,
    checkpoint_sync_time / 1000.0    AS sync_time_seconds,
    buffers_checkpoint,
    buffers_clean,
    maxwritten_clean,                -- bgwriter stopped due to bgwriter_lru_maxpages
    buffers_backend,                 -- buffers written directly by backends (bad for I/O)
    buffers_backend_fsync,           -- backend forced fsync (very bad)
    buffers_alloc,
    stats_reset
FROM pg_stat_bgwriter;

-- 2. WAL statistics (PostgreSQL 14+)
SELECT
    wal_records,
    wal_fpi,                         -- full page images (increases after checkpoint)
    wal_bytes,
    pg_size_pretty(wal_bytes)        AS wal_size_human,
    wal_buffers_full,                -- WAL buffer full flush count (increase wal_buffers if high)
    wal_write,
    wal_sync,
    wal_write_time,
    wal_sync_time,
    stats_reset
FROM pg_stat_wal;

-- 3. Current WAL position and LSN
SELECT
    pg_current_wal_lsn()                                         AS current_wal_lsn,
    pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        pg_current_wal_flush_lsn()
    )                                                             AS unflushed_bytes,
    pg_size_pretty(
        pg_wal_lsn_diff(
            pg_current_wal_lsn(),
            pg_current_wal_flush_lsn()
        )
    )                                                             AS unflushed_human;

-- 4. Checkpoint and WAL configuration
SELECT
    name,
    setting,
    unit,
    short_desc
FROM pg_settings
WHERE name IN (
    'checkpoint_timeout',
    'max_wal_size',
    'min_wal_size',
    'checkpoint_completion_target',
    'wal_level',
    'wal_compression',
    'wal_buffers',
    'bgwriter_delay',
    'bgwriter_lru_maxpages',
    'bgwriter_lru_multiplier',
    'synchronous_commit',
    'full_page_writes'
)
ORDER BY name;

Interpreting Results

checkpoints_req vs checkpoints_timed: If pct_requested is above 10-20%, checkpoints are being triggered by WAL accumulation rather than the timeout. This means max_wal_size may be too small, or the checkpoint is taking longer than checkpoint_timeout. Increase max_wal_size first.

buffers_backend: Backends writing dirty buffers directly indicates the bgwriter and checkpointer cannot keep up with write load. Increase bgwriter_lru_maxpages or shared_buffers.

buffers_backend_fsync: Any non-zero value is a concern — backends are being forced to fsync, which blocks them. This typically indicates I/O subsystem saturation.

maxwritten_clean: The bgwriter hit its bgwriter_lru_maxpages limit and stopped cleaning. Increase bgwriter_lru_maxpages if this is consistently non-zero.

wal_buffers_full: If consistently high, increase wal_buffers (default is often too low for write-heavy workloads).

checkpoint_sync_time high: Checkpoint sync phase takes long — indicates storage I/O latency. Investigate storage performance.

Safe Execution Guidance

Run in psql or any PostgreSQL client. Safe at any time. Useful to run as a scheduled check alongside table statistics snapshots.

The pg_stat_wal view is only available from PostgreSQL 14. Remove that query on PostgreSQL 13.

Rollback Steps

Not applicable — read-only script.