Configuring Write-Ahead Log
1. Understanding WAL
| Property | Detail |
|---|
| Purpose | Durability + crash recovery |
| Write order | WAL before data pages (write-ahead) |
| Segment size | 16 MB default (configurable at initdb) |
2. Setting wal_level
wal_level = replica # minimal | replica | logical
3. Setting fsync
fsync = on # NEVER disable on production data
Warning: fsync = off + crash = silent corruption.
4. Setting synchronous_commit
| Value | Trade-off |
|---|
| on | Durable; commit waits for fsync |
| off | ~3-5× faster small writes, <3× wal_writer_delay data at risk |
| local | Skip wait for remote standbys |
| remote_write/apply | See section 61 |
5. Setting wal_sync_method
wal_sync_method = fdatasync # Linux default; or fsync, open_sync, ...
6. Setting wal_buffers (WAL buffer size)
wal_buffers = 16MB # -1 → auto (1/32 shared_buffers, max 16MB)
7. Setting checkpoint_timeout
checkpoint_timeout = 15min # default 5min; longer = less I/O burst, more WAL replay
8. Setting checkpoint_completion_target
checkpoint_completion_target = 0.9 # spread writes across 90% of interval
9. Setting max_wal_size
max_wal_size = 8GB # soft cap before forced checkpoint
10. Setting min_wal_size
min_wal_size = 1GB # keep at least this many recycled segments
11. Setting wal_compression
wal_compression = lz4 # PG 15+: pglz | lz4 | zstd | on | off
12. Setting full_page_writes
full_page_writes = on # required for crash safety on typical FS
13. Monitoring WAL Activity
SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());
SELECT * FROM pg_stat_wal; -- PG 14+
SELECT * FROM pg_stat_bgwriter;