Write-Ahead Logging, LSNs, WAL Records, Segments, and Durability Guarantees

Learning outcomes

A ServiceHub dispatcher submits a work order and receives “COMMIT”. Seconds later the host loses power. The first durability question is not whether the changed heap page had already reached disk. PostgreSQL is designed so that durable recovery can depend on a smaller, sequential record of the change being made durable first. That record stream is the Write-Ahead Log (WAL).

WAL is the bridge between SQL transactions and crash recovery. To reason about it precisely, separate three positions in the WAL stream: where new records have been inserted into PostgreSQL's WAL buffers, how far those records have been written to the operating system, and how far they have been flushed to durable storage. PostgreSQL exposes all three as Log Sequence Numbers (LSNs).

1. A log sequence number is a position, not a transaction number

An LSN is a byte position in the WAL stream, represented by PostgreSQL's pg_lsn type and printed as two hexadecimal components such as 0/16B6C50. LSNs increase as WAL is generated. They identify positions in the physical WAL stream; they do not identify a business event, and one SQL statement can generate many WAL records.

SELECT  pg_is_in_recovery() AS in_recovery,  pg_current_wal_insert_lsn() AS insert_lsn,  pg_current_wal_lsn() AS write_lsn,  pg_current_wal_flush_lsn() AS flush_lsn,  pg_last_wal_replay_lsn() AS last_replay_lsn;

On an ordinary primary, pg_is_in_recovery() should be false and pg_last_wal_replay_lsn() is normally NULL because this server is not replaying WAL. The three current WAL positions can briefly differ under load. Do not turn a one-time equality between them into a universal claim.

SELECT  pg_wal_lsn_diff(pg_current_wal_insert_lsn(), pg_current_wal_flush_lsn()) AS inserted_not_yet_flushed_bytes,  pg_wal_lsn_diff(pg_current_wal_lsn(), pg_current_wal_flush_lsn()) AS written_not_yet_flushed_bytes;

A zero result means the observed positions happened to coincide at that instant. A nonzero result is an instantaneous backlog measurement, not proof of a durability failure.

2. WAL is divided into segment files

WAL records are appended to files under PGDATA/pg_wal. PostgreSQL normally uses 16 MiB WAL segments, but segment size is selected when the database cluster is initialized and is therefore something to inspect rather than assume. Segment filenames also encode the timeline and segment sequence.

SHOW wal_segment_size;SELECT  pg_current_wal_lsn() AS write_lsn,  pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

The WAL file name tells you which segment contains the supplied LSN. It does not mean that the whole segment belongs to one transaction or one database. WAL is cluster-wide.

WAL records describe enough physical change to redo crash-sensitive state, but they are not a one-record-per-row history. Heap changes, index changes, transaction status, sequence-related activity, catalog work, and other resource managers can all contribute records. A single business transaction can therefore touch many WAL records and even multiple segments. Conversely, temporary relations and some explicitly unlogged workflows have different WAL behavior; durability assumptions must follow the object and operation actually used.

This physical orientation is also why an LSN is useful for replication and recovery progress: it gives every participant a position in the same ordered byte stream. It is not a portable identifier to store in business tables as “the transaction ID.” If the application needs a durable event identifier, create one explicitly in the application schema.

3. Build a small WAL-producing ServiceHub workload

Use the Chapter 01 owner-capable connection to servicehub_lab. The table is disposable and deliberately uses a text payload large enough to make the change visible without creating an excessive lab.

DROP TABLE IF EXISTS app.ch12_wal_events;CREATE TABLE app.ch12_wal_events (    event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    work_order_id bigint NOT NULL,    event_type text NOT NULL,    payload text NOT NULL,    created_at timestamptz NOT NULL DEFAULT clock_timestamp());ANALYZE app.ch12_wal_events;

SELECT pg_current_wal_insert_lsn() AS lsn_before \gsetSELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_resetFROM pg_stat_wal;

BEGIN;INSERT INTO app.ch12_wal_events (work_order_id, event_type, payload)SELECT g,       'dispatch_note',       repeat(md5(g::text), 24)FROM generate_series(1, 5000) AS g;COMMIT;

SELECT  :'lsn_before'::pg_lsn AS lsn_before,  pg_current_wal_insert_lsn() AS lsn_after,  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), :'lsn_before'::pg_lsn)) AS wal_generated_since_baseline;SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_resetFROM pg_stat_wal;

The byte delta is local evidence for this workload on this server. It will vary with page state, full-page images, indexes, WAL compression, checksums, and concurrent activity. pg_stat_wal is cluster-wide and cumulative since its reset time, so subtract before/after snapshots if you need a workload delta and isolate other activity as much as practical.

4. What does COMMIT actually depend on?

For the ordinary default local durability path, PostgreSQL writes the transaction's commit WAL record and waits until the relevant WAL has been flushed to durable storage before acknowledging success. Data pages can reach their permanent files later. If a crash occurs first, startup recovery uses the WAL stream to reconstruct a consistent data-file state.

This is why the WAL-before-data ordering matters. A data page that reaches disk before its recovery information would break the recovery contract; PostgreSQL's write and flush ordering prevents that in a correctly configured storage stack.

SELECT name, setting, unit, context, source, pending_restartFROM pg_settingsWHERE name IN (  'wal_level','fsync','synchronous_commit','wal_sync_method',  'full_page_writes','wal_compression','wal_buffers')ORDER BY name;

5. Wrong approach: “pg_wal is my backup”

A common emergency response is to copy the current pg_wal directory and call it a backup. That is incomplete. Crash recovery assumes a compatible set of data files plus the necessary WAL chain starting from a valid recovery point. WAL files by themselves do not reconstruct an arbitrary missing cluster. Likewise, WAL is physical recovery machinery, not a durable application-level audit schema with stable row-level semantics.

6. Optional host-level observation

If you administer the local server host, pg_waldump can render WAL records for diagnostics. It is a PostgreSQL server utility, not a routine application API. Do not build application logic that depends on its output format or internal resource-manager details.

pg_waldump --versionpostgres --versionpsql --version

7. Production judgment and lab cleanup

Monitor WAL as a rate and a retention problem, not as a single absolute number. Useful evidence includes WAL bytes per interval, full-page-image rate, insert/write/flush gaps, archiver health, replication lag, replication-slot retention, checkpoint frequency, and pg_wal filesystem capacity. Always record the statistics reset time and topology when comparing values.

DROP TABLE IF EXISTS app.ch12_wal_events;

Authoritative references

Durability behavior depends on PostgreSQL version, storage semantics, configuration source, replication topology, and the exact failure mode. The references below are the primary source for the mechanisms used in this lesson.

• PostgreSQL 18 — Write-Ahead Logging (WAL)
• PostgreSQL 18 — WAL Internals
• PostgreSQL 18 — System Administration Functions
• PostgreSQL 18 — pg_lsn Type
• PostgreSQL 18 — Cumulative Statistics / pg_stat_wal
• PostgreSQL 18 — Continuous Archiving and PITR