In PostgreSQL, what do wal_level minimal, replica and logical each write to the WAL, and what must you plan before changing it?
answer
- replica is the default
- minimal skips WAL for new tables
- logical feeds logical decoding
- postmaster context: restart
basics
~10 sreplica, the default, logs enough for archiving and standbys; minimal logs only crash-recovery data and breaks both; logical adds what logical decoding needs. Changing wal_level requires a server restart.
solid answer
~40 s`wal_level` sets how much the WAL records. **`replica`** (default) supports WAL archiving, streaming standbys and read-only queries on them. **`minimal`** keeps only what crash recovery needs and skips WAL entirely for data written into a table created or truncated in the same transaction, which speeds bulk loads; but it rules out archiving and streaming, the server refuses to start with it while `max_wal_senders` is above 0 (default 10), and earlier base backups become useless for recovery. **`logical`** logs everything `replica` does plus what logical decoding needs, so it is required for logical slots and publications, at the cost of more WAL. It is a restart-only setting, so plan the restart and check `SHOW wal_level` afterwards.
go deeper
Remember the three values - minimal, replica, logical - that replica is the default, and that changing the level means restarting the server.
Explain what minimal skips and why that rules out archiving and standbys, what logical adds, and that the setting needs a restart.
Plan the change as an operation: restart window, max_wal_senders for minimal, fresh base backups afterwards, measured WAL growth for logical.
Decide whether primaries should run at logical by default so future change-data consumers do not force a restart, against the permanent WAL cost.
## The three levels `wal_level` decides how much information PostgreSQL writes into the write-ahead log (WAL). Each level includes everything the lower one logs. | Level | What it adds | What it enables | |---|---|---| | `minimal` | only what crash recovery needs | crash recovery, nothing else | | `replica` (default) | row data even for tables created or truncated in the same transaction, plus what a standby needs to run queries (running transactions, locks) | WAL archiving, point-in-time recovery, streaming standbys, read-only queries on standbys | | `logical` | `replica` plus information to extract logical change sets | logical decoding: logical replication slots, publications and subscriptions | The older names `archive` and `hot_standby` are still accepted and are mapped to `replica`. ## `minimal`: faster bulk work, no copies At `minimal`, PostgreSQL does not write row data to WAL for a permanent table or index that the current transaction created or truncated; at commit it simply fsyncs the files. Commands that benefit include: - `CREATE TABLE` followed by loading in the same transaction, and `COPY` into a table truncated earlier in that transaction - `TRUNCATE`, `CLUSTER`, `REINDEX` - `ALTER ... SET TABLESPACE` - `REFRESH MATERIALIZED VIEW` without `CONCURRENTLY` The price is that the WAL is no longer a complete history: - WAL archiving cannot be enabled (`archive_mode` refuses), so no point-in-time recovery. - Streaming replication is impossible; the server **will not start** while `max_wal_senders` is above zero, and its default is 10, so you must set it to 0 as well. - Switching to `minimal` makes every earlier base backup unusable for archive recovery and for building standbys. That makes `minimal` a tool for a one-off load into a cluster you will back up afresh afterwards, not a production default. ## `logical`: what it adds and costs Logical decoding reconstructs row changes from WAL, so it needs extra information that `replica` does not record. Creating a logical replication slot on a server at `replica` fails with `logical decoding requires "wal_level" >= "logical"`. Setting `logical` increases WAL volume, especially for tables set to `REPLICA IDENTITY FULL` under heavy `UPDATE` and `DELETE` traffic, because the old row is logged in full. Physical standbys do not need it; `replica` is enough for streaming. ## Changing it safely `wal_level` has postmaster context: a reload does nothing. 1. Decide what needs the change: a new logical consumer of the payments ledger needs `logical`; a one-off load might justify `minimal`. 2. Set it in `postgresql.conf` or with `ALTER SYSTEM SET wal_level = logical;`. 3. Plan and perform a restart; `pg_settings.pending_restart` shows the change is waiting. 4. Confirm with `SHOW wal_level;`. 5. If you lowered it to `minimal` and later raise it back, take a **new base backup** and rebuild standbys: nothing from before the switch can be replayed across it. ## Lowering it: check what depends on it first Lowering `wal_level` is where restarts go wrong. If a logical replication slot still exists when the server starts at `replica`, startup fails with `logical replication slot "..." exists, but "wal_level" < "logical"`; a physical slot does the same below `replica`. Before lowering the level: - list slots in `pg_replication_slots` and drop the ones whose consumers are retired; - check that no publication or change-data consumer still expects decoded changes; - for `minimal`, also set `max_wal_senders = 0` and `archive_mode = off`, or the server will not start. A quick way to judge the cost of `logical` before committing to it is to measure WAL per hour with LSN arithmetic or `pg_stat_wal.wal_bytes` on a test copy at each level under the same workload. ```sql ALTER SYSTEM SET wal_level = logical; SELECT name, setting, pending_restart FROM pg_settings WHERE name = 'wal_level'; ``` ## Choosing in practice - Keep **`replica`** unless something needs more; it is what archiving and standbys need. - Choose **`logical`** ahead of time on a primary that may later feed logical replication or change-data capture, so that the day it is needed does not also require an unplanned restart; measure the extra WAL first. - Use **`minimal`** only for clusters you can rebuild or will back up immediately after a bulk job.
- Does a physical streaming standby need wal_level = logical on the primary?No. `replica` already logs everything a physical standby needs to replay WAL and run read-only queries. `logical` is needed only for logical decoding: logical replication slots, publications and change-data capture.
- You set wal_level = minimal for a bulk load and the server will not start. Why?`max_wal_senders` defaults to 10, and the server refuses to start with WAL senders enabled at `minimal`, logging that WAL streaming requires `wal_level` replica or logical. Set `max_wal_senders = 0` too (and `archive_mode = off`), then restart.
- wal_level can be changed with a reload.
- Physical streaming standbys need wal_level = logical.
- wal_level = minimal only shrinks WAL; standbys and old base backups keep working.
- archive and hot_standby are separate levels with their own WAL content.