Burak SenBurak Sen

WAL Levels in Postgres and Effective WAL Level in PG19

6 min read
postgresqldatabases
On this page▾

Postgres 19 is to be released in a few months. In June first beta version pg19beta1 and in July second pg19beta2 were released. Postgres 19 includes security, performance, and observability improvements and features that can be seen in the release notes. I want to focus on the new effective_wal_level server variable. However, before doing that let's explore which WAL levels are available in Postgres and what do they mean? Note that, this will be a high-level overview of WAL levels. I want to delve into WAL in future and link it in this blog post. If you know WAL levels, feel free to skip to the Effective WAL Level section or skip the post completely to not lose time :).

WAL (Write-Ahead Log) Levels#

Postgres has wal_level is an enum that can have three values, minimal, replica and logical. Default WAL level is replica. WAL levels are defined in the source code as:

c
// src/include/access/xlog.h#L74:74
typedef enum WalLevel
{
	WAL_LEVEL_MINIMAL = 0,
	WAL_LEVEL_REPLICA,
	WAL_LEVEL_LOGICAL,
} WalLevel;

There is a mapping:

c
// src/backend/access/rmgrdesc/xlogdesc.c
const struct config_enum_entry wal_level_options[] = {
	{"minimal", WAL_LEVEL_MINIMAL, false},
	{"replica", WAL_LEVEL_REPLICA, false},
	{"archive", WAL_LEVEL_REPLICA, true},	/* deprecated */
	{"hot_standby", WAL_LEVEL_REPLICA, true},	/* deprecated */
	{"logical", WAL_LEVEL_LOGICAL, false},
	{NULL, 0, false}
};

Wait, what is archive and hot_standby and why do they map to replica? First of all, last parameter is hidden field and its only set to true for these two. Postgres 9.6 release notes describes that these two WAL levels are merged into replica and therefore they are deprecated.

WAL level stack
WAL levels — each is a superset of the one belowlogical+ logical-decoding data (new tuple, replica identity as needed) → logical decoding / CDCreplica(default)+ WAL for new/rewritten relations · standby info → archiving · PITR · physical replicationminimalcrash / immediate-shutdown recovery onlyskips row WAL for relations created or rewritten in the same txn⤷ fast bulk loads · cannot archive, replicate, or decodeWAL detail grows minimal → replica → logical · set with wal_level (server start only)

Before explaining WAL levels, first lets go over these:

  • Crash Recovery: In a server crash, Postgres replays WAL to recover
  • Continuous WAL Archiving: Copy completed WAL segments to a durable storage
  • Point-in-time Recovery: Restore Postgres to a specific time using WAL archiving
  • Physical Standby: A standby Postgres server which continuously replays primary server's WAL. Hot standby can accept read-only queries.
  • Logical Decoding: Convert WAL contents into stream of tuples or SQL for ease of understanding
  • Physical Replication: Byte-for-Byte copy of source and destination servers
  • Logical Replication: Replicating data objects and changes on them using logical decoding

There are other functionalities affected by WAL and list above needs further explanations. I'll not include them in the scope of this post.

Minimal Level#

This level contains only enough information for crash recovery. Postgres produces the least WAL volume. However, PITR, physical standbys, physical replication, and logical replication cannot be used.

There are several macros used for deciding what to log:

c
   /* src/include/access/xlog.h */
   #define XLogIsNeeded() (wal_level >= WAL_LEVEL_REPLICA)

   /* src/include/utils/rel.h */
   #define RelationNeedsWAL(relation)                                  \
       (RelationIsPermanent(relation) && (XLogIsNeeded() ||            \
         (relation->rd_createSubid == InvalidSubTransactionId &&       \
          relation->rd_firstRelfilelocatorSubid == InvalidSubTransactionId)))

RelationNeedsWAL requires the relation to be permanent. It then requires either wal_level >= replica or neither the relation nor its current physical storage to have been created or replaced in the current transaction. At minimal, page changes to newly created or rewritten physical storage may be skipped because they have no previous committed state. If the transaction aborts, the new storage is discarded. Before commits, PostgreSQL makes the new storage durable by syncing its files or logging full-page images. Postgres does not need the extra WAL records for every page modification.

minimal WAL optimization
COPY into a freshly-created table (same transaction)wal_level = replica / logicalnew table filepg_walrow 1row 2row 3row 4MULTI_INSERTrows 1–4COPY batches tuples into multi-insert WAL recordswal_level = minimalnew table filepg_walrow 1row 2row 3row 4no per-row WALbelow threshold → full-page imagesat/above threshold → fsync filesCOMMIT chooses one path · default threshold: 2 MBThe optimization fires only for relations created or rewritten in the same transaction(CREATE TABLE AS, COPY into a new table, CLUSTER, TRUNCATE, REINDEX, REFRESH MATERIALIZED VIEW…)

Replica Level#

Replica includes everything logged at the minimal level. It makes RelationNeedsWAL always true (for permanent relations). An example call site is:

c
   /* src/backend/access/heap/heapam.c */
   if (RelationNeedsWAL(relation))
   {
       xl_heap_insert xlrec;
       ...
       XLogRegisterBuffer(HEAP_INSERT_BLKREF_HEAP, buffer, ...);
       XLogInsert(RM_HEAP_ID, XLOG_HEAP_INSERT);
   }
   
   
	/* src/include/access/heapam_xlog.h */
   typedef struct xl_heap_insert
   {
       OffsetNumber offnum;
       uint8        flags;
   } xl_heap_insert;

xl_heap_insert contains the tuples offset and flags. The block reference registered by XLogRegisterBuffer identifies the relation, fork, and block. The block associated data contains tuple contents.

As a result, Postgres produces complete physical WAL stream that it can archive and replay from a backup enabling PITR, physical replication, and warm standby.

Hot standby requires additional information controlled by another macro. The standby manager (registered in src/include/access/rmgrlist.h) contributes three types of records:

c
   /* src/include/access/xlog.h */
   #define XLogStandbyInfoActive() (wal_level >= WAL_LEVEL_REPLICA)

   /* src/include/storage/standbydefs.h */
   #define XLOG_STANDBY_LOCK          0x00   
   #define XLOG_RUNNING_XACTS         0x10
   #define XLOG_INVALIDATIONS         0x20

They are for recording access-exclusive locks, constructing MVCC snapshots and keeping relation and system caches consistent respectively. One example is:

c
  typedef struct xl_running_xacts
  {
      int           xcnt;
      int           subxcnt;
      bool          subxid_overflow;
      TransactionId nextXid;
      TransactionId oldestRunningXid;
      TransactionId latestCompletedXid;
      TransactionId xids[];
  } xl_running_xacts;

This struct gives information about running transactions, next transaction and etc. With these WAL records on top of other recovery-conflict information and complete physical WAL, Postgres can serve read-only queries on a hot standby.

Logical Level#

Logical includes everything logged at the replica level. In addition to that, logical level produces enough information to identify row-level operations and tuple values. This enables flexibility when sending information to target databases such as deciding which rows/columns to send and/or sending to other databases using extracted tuples.

c
  /* src/include/access/xlog.h */
  #define XLogLogicalInfoActive() \
      (wal_level >= WAL_LEVEL_LOGICAL || XLogLogicalInfo)

  /* src/include/utils/rel.h */
  #define RelationIsLogicallyLogged(relation) \
      (XLogLogicalInfoActive() && \
       RelationNeedsWAL(relation) && \
       (relation)->rd_rel->relkind != RELKIND_FOREIGN_TABLE && \
       !IsCatalogRelation(relation))

I'll delve into XLogLogicalInfo at the end of this post since it is related to effective WAL level change. Similar to other levels logical level has macros such that Postgres can decide whether it should log additional information required or not.

c
	/* src/backend/access/heap/heapam.c */
	/*
	 * For logical decoding, we need the tuple even if we're doing a full
	 * page write, so make sure it's included even if we take a full-page
	 * image. (XXX We could alternatively store a pointer into the FPW).
	 */
	if (RelationIsLogicallyLogged(relation) &&
		!(options & HEAP_INSERT_NO_LOGICAL))
	{
		xlrec.flags |= XLH_INSERT_CONTAINS_NEW_TUPLE;
		bufflags |= REGBUF_KEEP_DATA;

		if (IsToastRelation(relation))
			xlrec.flags |= XLH_INSERT_ON_TOAST_RELATION;
	}

Above code snippet is an example of using RelationIsLogicallyLogged to decide logging. You can see the exact comment from a Postgres developer about tuple is needed for logical decoding. Full-page image only shows the page's final bytes and it can be used for physical replication. That's not enough because logical decoding needs the exact inserted tuples to produce events for output. The current logical decoder does not derive these tuples from page's final image. Thus, when level is logical REGBUF_KEEP_DATA flag is set to retain tuple data. Flag is defined and used as:

c
	// src/include/access/xloginsert.h
	#define REGBUF_KEEP_DATA	0x10	/* include data even if a full-page image
										 * is taken */
										   
	// src/backend/access/transam/xloginsert.c
	/* Determine if the buffer data needs to included */
	if (regbuf->rdata_len == 0)
		needs_data = false;
	else if ((regbuf->flags & REGBUF_KEEP_DATA) != 0)
		needs_data = true;

needs_data is set to true and Postgres writes tuple data with it.

One thing worth mentioning is this additional tuple data will cause non-trivial WAL volume increase.

WAL Levels Demo#

WAL entries by level
wal_level
query 6/6 · COMMIT (txn 2)

txn 1 begins — CREATE TABLE t1(id PK, name, balance); the table is new and created in this transaction, which is what lets minimal skip WAL for the initial load. Heap records are shown; primary-key index WAL is omitted.

QueryRepresentative heap WALDurabilityConsumer
INSERT INTO t1 VALUES (1,'alice',100)txn 1
INSERT+INIT0x0071 B
flushed ✓
redo · standby
INSERT INTO t1 VALUES (2,'bob',50)txn 1
INSERT0x0067 B
flushed ✓
redo · standby
COMMITtxn 1 · commit
COMMITflush
fsync() → durable
archived / streamed
UPDATE t1 SET balance=80 WHERE id=1txn 2
HOT_UPDATE0x6071 Bprefix/suffix delta
flushed ✓
redo · standby
DELETE FROM t1 WHERE id=2txn 2
DELETE0x0054 BTID off:2 only
flushed ✓
redo · standby
COMMITtxn 2 · commit
COMMITflush
fsync() → durable
archived / streamed

wal_level = replica (default) — each DML query writes a physical record into the WAL buffer; COMMIT fsync’s them to pg_wal, after which they can be archived/PITR’d and streamed to standbys. The DELETE keeps only a TID, so changes redo physically but cannot be decoded.

Setting WAL Level#

WAL levels are configured cluster-wide by wal_level server variable. As mentioned before, its default value is replica.

conf
  # postgresql.conf
  wal_level = logical

or

sql
  ALTER SYSTEM SET wal_level = 'logical';

Then current value of it can be viewed by:

sql
  SHOW wal_level;

Changing wal_level requires server restart. And the final section addresses this pain point.

Effective WAL Level#

In PG19, there is a new read-only server variable named effective_wal_level. It shows the current WAL level Postgres honors instead of the set wal_level parameter.

Let's check XLogLogicalInfoActive again:

c
  /* src/include/access/xlog.h */
  #define XLogLogicalInfoActive() \
      (wal_level >= WAL_LEVEL_LOGICAL || XLogLogicalInfo)

This macro is used to check whether additional information required for logical decoding is logged or not. But there is an or statement and if XLogLogicalInfo variable is truthy wal_level does not need to be logical. It is a cached value of xlog_logical_info:

c
	// src/backend/replication/logical/logicalctl.c

	typedef struct LogicalDecodingCtlData
	{
		bool		xlog_logical_info;
		bool		logical_decoding_enabled;
		bool		pending_disable;
	} LogicalDecodingCtlData;
	
	/*
	 * A process-local cache of LogicalDecodingCtl->xlog_logical_info. This is
	 * initialized at process startup, and updated when processing the process
	 * barrier signal in ProcessBarrierUpdateXLogLogicalInfo(). If the process
	 * is in an XID-assigned transaction, the cache update is delayed until the
	 * transaction ends. See the comments for XLogLogicalInfoUpdatePending for details.
	 */
	bool		XLogLogicalInfo = false;

How and when it is set to true?

logical decoding flow
Logical slot on a primary: early return or activation casepg_create_logical_replication_slot()EnsureLogicalDecodingEnabled()wal_level >= logical?yes: logicalreturn — already enabledno: replica · decoding disabledEnableLogicalDecoding()effective_wal_levelreplica → logical1shmem: xlog_logical_info = trueprocesses starting after this point carry logical info2ProcSignalBarrierrefresh cached flag · XID transactions defer until transaction endrunning backendsno XIDrefresh nowhas XIDat txn end3enable logical decodingdecoding contexts may now start4emit XLOG_LOGICAL_DECODING_STATUS_CHANGE→ standbys replay this record and followlast slot dropped / invalidated → checkpointer disables logical decoding asynchronouslylazy on purpose (avoids end-of-recovery races & slot create/drop thrash) — so effective_wal_level can lag

As you can see from the diagram, whenever a logical replication slot is created Postgres ensures logical decoding is enabled. If wal_level is logical function early returns since logical decoding is enabled. However, if wal_level is replica then it sets the xlog_logical_info to true. There is also an assert here that checks:

c
Assert(wal_level >= WAL_LEVEL_REPLICA);

Thus, minimal is not a valid state at that point.

Checking the value of this variable is very straightforward. You can use:

sql
SHOW effective_wal_level;

This value is calculated with:

c
// src/backend/access/transam/xlog.c
const char *
show_effective_wal_level(void)
{
	if (wal_level == WAL_LEVEL_MINIMAL)
		return "minimal";

	/*
	 * During recovery, effective_wal_level reflects the primary's
	 * configuration rather than the local wal_level value.
	 */
	if (RecoveryInProgress())
		return IsXLogLogicalInfoEnabled() ? "logical" : "replica";

	return XLogLogicalInfoActive() ? "logical" : "replica";
}

On a primary in steady state, the wal_level and effective_wal_level pairs are:

wal_levelLogical slotseffective_wal_level
minimalNoneminimal
replicaNonereplica
replicaAt least one valid logical slotlogical
logicalAnylogical

Wrapping up#

WAL level explorer
configured wal_level
logical replication slota logical slot elevates the steady-state effective level
configuredreplica
effective_wal_level · steady statereplica
  • Crash / immediate-shutdown recovery
  • Bulk-load WAL optimization (new / rewritten relations)
  • WAL archiving · point-in-time recovery
  • Streaming replication · hot standby
  • Logical decoding · logical replication / CDC

wal_level = replica, slot none → effective_wal_level = replica (last-slot deactivation may lag until the checkpointer runs)

Incoming effective WAL level functionality is a great QoL improvement that has benefits such as:

  • There is no need to restart the server for enabling logical decoding from replica log level
  • Dynamic activation adds logical logging only when needed; after asynchronous deactivation completes, the extra logging overhead is removed

Postgres extensions or consumers that use logical replication may leverage this effective_wal_level. In my team at Microsoft, we've an extension to mirror data from Postgres to Microsoft Fabric. We need server restart for setting WAL level to logical and our validation checks require that WAL level. However, if effective_wal_level is logical we don't need to set WAL level or restart the server.

That was it for this post. Thanks for reading until the end. I'll delve into WAL internals and share it here in the future.