skip to content

In PostgreSQL, what do wal_level minimal, replica and logical each write to the WAL, and what must you plan before changing it?

level: middleimportance: should knowfreq 42%

answer

  1. replica is the default
  2. minimal skips WAL for new tables
  3. logical feeds logical decoding
  4. postmaster context: restart

basics

~10 s

replica, 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

for a junior

Remember the three values - minimal, replica, logical - that replica is the default, and that changing the level means restarting the server.

for a middle

Explain what minimal skips and why that rules out archiving and standbys, what logical adds, and that the setting needs a restart.

for a senior

Plan the change as an operation: restart window, max_wal_senders for minimal, fresh base backups afterwards, measured WAL growth for logical.

for a principal

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.