How to tune WAL settings in PostgreSQL

Tuning the Write-Ahead Log (WAL) settings in PostgreSQL balances durability, replication requirements, and write performance. Correct values reduce checkpoint I/O spikes, keep standby servers from falling behind, and prevent WAL growth from exhausting storage.

PostgreSQL records changes in WAL before modifying data files, which enables crash recovery and powers physical and logical replication. WAL is written into the /pg_wal directory under the cluster data directory, and completed WAL segments are recycled when possible. Settings such as max_wal_size, min_wal_size, and checkpoint_timeout control how frequently checkpoints occur, while wal_level determines how much information is logged for replication and decoding.

Increasing WAL retention consumes disk space and can hide downstream problems (slow disks, replication lag) until storage is full. Disabling durability features like fsync or full_page_writes can cause unrecoverable corruption after power loss or kernel crashes. Some WAL parameters apply on reload, while postmaster parameters require a full server restart that disconnects clients.

Steps to tune PostgreSQL WAL settings:

Connect to the PostgreSQL server using psql as a superuser.

$ sudo -iu postgres psql
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1))
Type "help" for help.
 
postgres=#

Show the PostgreSQL cluster data directory hosting the /pg_wal directory.
```
postgres=# SHOW data_directory;
      data_directory
-------------------------
 /var/lib/postgresql/16/main
(1 row)
```
ALTER SYSTEM writes changes into /postgresql.auto.conf inside the data directory.
Check free space on the filesystem that stores the data directory.
```
$ df -h /var/lib/postgresql/16/main
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p2  200G   48G  143G  26% /
```
Raising max_wal_size, min_wal_size, or wal_keep_size increases worst-case WAL disk usage.

Record current WAL and checkpoint settings with reload/restart requirements.

postgres=# SELECT name, setting, unit, context, pending_restart
           FROM pg_settings
           WHERE name IN (
             'wal_level',
             'synchronous_commit',
             'fsync',
             'full_page_writes',
             'wal_compression',
             'checkpoint_timeout',
             'checkpoint_completion_target',
             'max_wal_size',
             'min_wal_size',
             'wal_keep_size'
           )
           ORDER BY name;
             name              | setting | unit |  context   | pending_restart
-------------------------------+---------+------+------------+-----------------
 checkpoint_completion_target  | 0.9     |      | sighup     | f
 checkpoint_timeout            | 5min    |      | sighup     | f
 fsync                         | on      |      | sighup     | f
 full_page_writes              | on      |      | sighup     | f
 max_wal_size                   | 1GB     |      | sighup     | f
 min_wal_size                   | 80MB    |      | sighup     | f
 synchronous_commit             | on      |      | user       | f
 wal_compression                | off     |      | sighup     | f
 wal_keep_size                  | 0       | MB   | sighup     | f
 wal_level                      | replica |      | postmaster | f
(10 rows)

context values of sighup apply on reload, and postmaster requires a restart.

Confirm durability-critical WAL safety settings remain enabled.
```
postgres=# SHOW fsync;
 fsync
-------
 on
(1 row)

postgres=# SHOW full_page_writes;
 full_page_writes
------------------
 on
(1 row)
```
Disabling fsync or full_page_writes can cause unrecoverable data corruption after power loss or system crashes.
Set wal_level based on replication and decoding requirements.
```
postgres=# ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM
```
Use replica for physical replication and base backups via the replication protocol, and use logical only when logical decoding or logical replication is required.

Tune WAL recycling and checkpoint cadence for write-heavy workloads.

postgres=# ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET min_wal_size = '2GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM

Higher max_wal_size reduces checkpoint frequency, and checkpoint_completion_target spreads checkpoint writes over more time.

Enable WAL compression to reduce WAL volume from full-page writes.
```
postgres=# ALTER SYSTEM SET wal_compression = 'on';
ALTER SYSTEM
```
wal_compression trades CPU for lower WAL write volume and can reduce replication bandwidth on write-heavy systems.
Set the default commit durability mode for latency-sensitive workloads.
```
postgres=# ALTER SYSTEM SET synchronous_commit = 'on';
ALTER SYSTEM
```
Values like local or off reduce commit latency but can lose recent transactions on a crash, while remote_write and remote_apply increase durability guarantees for synchronous replication.
Reserve WAL for standby servers when streaming replication is used without replication slots.
```
postgres=# ALTER SYSTEM SET wal_keep_size = '2GB';
ALTER SYSTEM
```
Large wal_keep_size values can retain significant WAL on disk and must fit within available storage.

Reload the PostgreSQL configuration to apply sighup settings.

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
--------------
 t
(1 row)

Check for settings requiring a PostgreSQL restart.

postgres=# SELECT name, setting, context, pending_restart
           FROM pg_settings
           WHERE pending_restart IS true
           ORDER BY name;
   name    | setting |  context   | pending_restart
-----------+---------+------------+-----------------
 wal_level | replica | postmaster | t
(1 row)

Restart requirements remain until the server is restarted, even after configuration reloads.

Exit the psql session.
```
postgres=# \q
```
Restart the PostgreSQL service to apply postmaster settings.
```
$ sudo systemctl restart postgresql
```
A restart disconnects active sessions and can interrupt applications during the service stop/start window.

Verify WAL-related settings and confirm no restart is pending.

$ sudo -iu postgres psql
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1))
Type "help" for help.
 
postgres=# SHOW max_wal_size;
 max_wal_size
--------------
 8GB
(1 row)
 
postgres=# SHOW checkpoint_timeout;
 checkpoint_timeout
--------------------
 15min
(1 row)
 
postgres=# SELECT count(*) AS pending_restart_settings
           FROM pg_settings
           WHERE pending_restart IS true;
 pending_restart_settings
-------------------------
 0
(1 row)

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.

Discuss the article:

Comment anonymously. Login not required.