When wal_level is minimal and a transaction commits after creating or rewriting a permanent relation, this setting determines how to persist the new data.
At a glance
| Property | Value |
|---|---|
| Parameter | wal_skip_threshold |
| Category | Write Ahead Log |
| Default | two |
| Value type | integer |
| Change scope | Per-session (SET) |
| Available in | PostgreSQL 13, 14, 15, 16, 17, 18, 19 (added in 13) |
What it does
When wal_level is minimal and a transaction commits after creating or rewriting a permanent relation, this setting determines how to persist the new data. If the data is smaller than this setting, write it to the WAL log; otherwise, use an fsync of affected files. Depending on the properties of your storage, raising or lowering this value might help if such commits are slowing concurrent transactions. If this value is specified without units, it is taken as kilobytes. The default is two megabytes (2MB).
(Description quoted from the official PostgreSQL documentation.)
How to apply a change
Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.
Inspect the current value and source with SHOW wal_skip_threshold; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'wal_skip_threshold';.
Tuning guidance
Transactions creating relations smaller than this fsync the files at commit instead of writing WAL; raise it to favour fewer fsyncs for many small new tables, lower it to favour WAL. Rarely needs changing.