How to tune WAL settings in PostgreSQL
WAL tuning helps a write-heavy PostgreSQL server avoid checkpoint bursts without trading away crash safety. The common operator problem is a cluster that flushes dirty pages too often, keeps too little WAL for a standby or batch spike, or hides retention pressure until /pg_wal grows. Start from the active settings and the data-directory filesystem before changing values.
PostgreSQL begins a checkpoint when checkpoint_timeout passes or when max_wal_size is about to be exceeded. max_wal_size is a soft checkpoint limit, not a hard storage cap, because archiving failures, replication slots, wal_keep_size, and heavy write bursts can retain extra WAL. min_wal_size controls how much old WAL is recycled for future use, while checkpoint_completion_target spreads checkpoint writes across the checkpoint interval.
Keep fsync and full_page_writes enabled unless a disposable test system can tolerate corruption after a crash. wal_compression can reduce full-page image volume at the cost of CPU, and wal_level should follow replication or logical decoding requirements rather than be changed as a generic performance knob. Reloadable settings apply after a configuration reload, while postmaster settings such as wal_level stay pending until a full server restart disconnects clients.
Steps to tune WAL settings in PostgreSQL:
- Print the active PostgreSQL data directory.
$ sudo -u postgres psql -Atc "SHOW data_directory;" /var/lib/postgresql/18/main
ALTER SYSTEM writes to postgresql.auto.conf in this directory, and that file overrides matching entries in postgresql.conf.
- Check free space on the filesystem that stores the data directory and /pg_wal.
$ df -h /var/lib/postgresql/18/main Filesystem Size Used Avail Use% Mounted on overlay 118G 39G 74G 35% /
Raising max_wal_size, min_wal_size, or wal_keep_size increases the amount of WAL that may stay on disk.
- Record the current WAL and checkpoint settings with their reload or restart context.
$ sudo -u postgres psql -c "SELECT name, setting, unit, context, pending_restart FROM pg_settings WHERE name IN ('checkpoint_completion_target','checkpoint_timeout','max_wal_size','min_wal_size','wal_compression','wal_keep_size','wal_level') ORDER BY name;" name | setting | unit | context | pending_restart -----------------------------+---------+------+------------+----------------- checkpoint_completion_target | 0.9 | | sighup | f checkpoint_timeout | 300 | s | sighup | f max_wal_size | 1024 | MB | sighup | f min_wal_size | 80 | MB | sighup | f wal_compression | off | | superuser | f wal_keep_size | 0 | MB | sighup | f wal_level | replica | | postmaster | f (7 rows)sighup settings apply after reload. postmaster settings require a restart, so change wal_level only when replication or logical decoding requires it.
- Confirm durability-critical WAL safety settings are still enabled.
$ sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('fsync','full_page_writes') ORDER BY name;" name | setting ------------------+--------- fsync | on full_page_writes | on (2 rows)Turning off fsync or full_page_writes can cause unrecoverable data corruption after power loss, kernel crashes, or storage failures.
- Set a larger max_wal_size for the write workload.
$ sudo -u postgres psql -c "ALTER SYSTEM SET max_wal_size = '2GB';" ALTER SYSTEM
Higher max_wal_size usually reduces checkpoint frequency, but it can increase crash-recovery time and does not cap WAL retained by archiving or replication.
- Set min_wal_size to keep enough recycled WAL for repeated write bursts.
$ sudo -u postgres psql -c "ALTER SYSTEM SET min_wal_size = '512MB';" ALTER SYSTEM
min_wal_size reserves recycled WAL files for future use instead of removing them after every quiet checkpoint cycle.
- Set a checkpoint interval that matches the workload and recovery target.
$ sudo -u postgres psql -c "ALTER SYSTEM SET checkpoint_timeout = '10min';" ALTER SYSTEM
A longer interval can reduce checkpoint pressure but leaves more WAL to replay during crash recovery.
- Keep checkpoint writes spread across most of the interval.
$ sudo -u postgres psql -c "ALTER SYSTEM SET checkpoint_completion_target = 0.9;" ALTER SYSTEM
The default 0.9 setting gives the checkpointer most of the interval while leaving time for checkpoint work that is not dirty-buffer writes.
- Enable WAL compression with the built-in pglz method.
$ sudo -u postgres psql -c "ALTER SYSTEM SET wal_compression = 'pglz';" ALTER SYSTEM
Use lz4 or zstd only when the installed PostgreSQL build supports that compression method.
- Reserve WAL for standby catch-up when streaming replication needs retained segments without a slot.
$ sudo -u postgres psql -c "ALTER SYSTEM SET wal_keep_size = '512MB';" ALTER SYSTEM
Large wal_keep_size values retain WAL independently of max_wal_size and must fit within the /pg_wal filesystem budget.
- Reload PostgreSQL so it rereads postgresql.auto.conf.
$ sudo -u postgres psql -Atc "SELECT pg_reload_conf();" t
- Confirm the ALTER SYSTEM entries in postgresql.auto.conf were applied.
$ sudo -u postgres psql -c "SELECT name, applied, error FROM pg_file_settings WHERE sourcefile = current_setting('data_directory') || '/postgresql.auto.conf' AND name IN ('checkpoint_completion_target','checkpoint_timeout','max_wal_size','min_wal_size','wal_compression','wal_keep_size') ORDER BY name;" name | applied | error -----------------------------+---------+------- checkpoint_completion_target | t | checkpoint_timeout | t | max_wal_size | t | min_wal_size | t | wal_compression | t | wal_keep_size | t | (6 rows)If an entry shows applied = f with an error, fix or reset that setting before relying on the new WAL behavior.
- Check whether any setting still requires a restart.
$ sudo -u postgres psql -c "SELECT name, setting, context FROM pg_settings WHERE pending_restart IS true ORDER BY name;" name | setting | context ------+---------+--------- (0 rows)
A changed wal_level or other postmaster setting appears here until the server restarts.
- Restart PostgreSQL only when the previous check returns restart-pending rows.
$ sudo systemctl restart postgresql
A restart disconnects active sessions and can abort running transactions.
- Verify the active WAL settings after reload or restart.
$ sudo -u postgres psql -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name IN ('checkpoint_timeout','max_wal_size','min_wal_size','wal_compression','wal_keep_size') ORDER BY name;" name | setting | pending_restart --------------------+---------+----------------- checkpoint_timeout | 600 | f max_wal_size | 2048 | f min_wal_size | 512 | f wal_compression | pglz | f wal_keep_size | 512 | f (5 rows)Numeric output uses the units shown by pg_settings, so checkpoint_timeout = 600 means 600 seconds and max_wal_size = 2048 means 2048 MB.