PostgreSQL WAL Configuration: fsync, synchronous_commit, and Checkpoint Tuning

# postgresql.conf
wal_level = replica          # minimal, replica, or logical
fsync = on                   # NEVER disable in production
synchronous_commit = on      # on, remote_write, remote_apply, local, off
wal_buffers = 64MB           # default -1 (auto ~3% of shared_buffers)
wal_writer_delay = 200ms     # how often wal writer flushes
checkpoint_timeout = 15min   # max time between checkpoints
max_wal_size = 2GB           # trigger checkpoint when WAL exceeds this
min_wal_size = 80MB

Setting         Data Loss Risk    Write Latency
----------------|-----------------|-------------
on (default)    None              Higher
remote_apply    None (with HA)    Highest
local           On standby only  Medium
off             Up to 3×wal_writer_delay  Lowest

-- Per-transaction override (e.g., for non-critical writes)
SET LOCAL synchronous_commit = off;
INSERT INTO audit_log (event) VALUES ('page_view');
COMMIT;

SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean
FROM pg_stat_bgwriter;

-- checkpoints_req >> checkpoints_timed means max_wal_size is too small
-- Increase max_wal_size to reduce I/O spikes from forced checkpoints

archive_mode = on
archive_command = 'wal-g wal-push %p'
archive_timeout = 300   # force archive at least every 5 minutes

full_page_writes = on  # NEVER disable — prevents partial page write corruption
# After a checkpoint, first write to any page includes full page image in WAL
# Required for crash safety