PostgreSQL WAL, Checkpoints, and Durability Cheat Sheet

SELECT name, setting, source
FROM pg_settings
WHERE name IN ('fsync', 'full_page_writes', 'wal_sync_method', 'synchronous_commit')
ORDER BY name;

BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO app.telemetry(event_at, payload) VALUES (clock_timestamp(), '{"source":"edge"}');
COMMIT;

Note: Use only where bounded loss is acceptable; replicas and crash recovery can temporarily lag acknowledged commits.

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('checkpoint_timeout', 'checkpoint_completion_target', 'max_wal_size', 'min_wal_size')
ORDER BY name;

SELECT num_timed, num_requested, num_done, write_time, sync_time, buffers_written, stats_reset
FROM pg_stat_checkpointer;

SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
WHERE slot_type = 'physical';

Note: Access may require pg_monitor or elevated privileges; NULL restart_lsn needs separate interpretation.

SELECT pg_has_role(current_user, 'pg_checkpoint', 'MEMBER') AS may_checkpoint;
-- Run CHECKPOINT separately only when an approved runbook requires it.

SELECT pg_current_wal_lsn() AS primary_lsn, pg_current_wal_insert_lsn() AS insert_lsn;

Note: These functions are meaningful on a primary and may require monitoring privileges depending on the deployment.