skip to content

Write-Ahead Log & Replicas

Every PostgreSQL change is logged to the WAL first, and that one log drives crash recovery, streaming standbys, slots and logical decoding. Interviewers probe what each consumer costs.

part ofPostgreSQLread it →
on this pageshow

explore

questions

28

In PostgreSQL, what does the pg_wal directory hold, how is an LSN written, and why must you never delete its files by hand?

level: juniorimportance: must knowfreq 62%

answer

  1. the log that comes before data files
  2. numbered 16 MB segment files
  3. pg_lsn like 16/B374D848
  4. the checkpoint's redo point decides what is old

basics

~20 s

pg_wal holds PostgreSQL's write-ahead log as numbered 16 MB segment files; an LSN is a byte position in that log, written like 16/B374D848. Deleting segments by hand can remove WAL crash recovery needs, so the server may not start.

solid answer

~40 s

`pg_wal` in the data directory holds the write-ahead log as **segment files**, 16 MB each by default (chosen with `initdb --wal-segsize`), named with 24 hex digits such as `000000010000000000000001`. A position in that stream is an **LSN**, the `pg_lsn` type, shown as two hex numbers like `16/B374D848`: `pg_current_wal_lsn()` gives the current write position, `pg_walfile_name()` maps an LSN to its file, and subtracting two LSNs gives the bytes between them. PostgreSQL recycles or removes old segments itself, after a checkpoint has moved the redo point past them and nothing else (archiving, a replication slot, `wal_keep_size`) still needs them. Deleting files by hand can take WAL that crash recovery must replay from the last checkpoint, so the server refuses to start or comes up inconsistent. When `pg_wal` grows, find what is holding WAL instead.

go deeper

for a junior

Recall that pg_wal holds the write-ahead log in 16 MB segment files and that an LSN is a byte position written like 16/B374D848. Say plainly that you never delete its files by hand.

for a middle

Explain how segments are recycled after a checkpoint moves the redo point, and show the LSN functions: pg_current_wal_lsn, pg_walfile_name and LSN subtraction to measure WAL volume.

for a senior

Show the production reflex: when pg_wal grows, list the holders - slots, archiving, wal_keep_size, stalled checkpoints - and fix the holder; know what a missing redo segment does to startup.

for a principal

Frame WAL volume as a capacity input: measure bytes per job with LSN arithmetic, size the pg_wal volume and archive bandwidth from it, and alert on growth before the disk fills.

## What `pg_wal` holds Every change PostgreSQL makes to a table, an index or a system catalog is first described in the **write-ahead log (WAL)**. The WAL lives in the `pg_wal` subdirectory of the data directory, and it is not one ever-growing file but a series of **segment files**: - Each segment is **16 MB** by default. The size is fixed when the cluster is created with `initdb --wal-segsize` (a power of two from 1 to 1024 MB) and is shown by the read-only setting `wal_segment_size`. No reload changes it; `pg_resetwal --wal-segsize` can change it on a cleanly stopped cluster. - Segment names are **24 hexadecimal digits** and only ever increase, starting at `000000010000000000000001`. `pg_split_walfile_name()` decodes a name into its timeline ID and segment number. - Inside a segment, WAL is written in 8 kB pages, and every WAL record carries its own CRC, so a damaged record is noticed during replay rather than applied. - `pg_waldump` reads segment files and prints their records; `pg_waldump --stats` summarises records and full-page images by type. ## LSNs: positions in the WAL A **Log Sequence Number (LSN)** is a byte offset into the WAL stream that increases with every record appended. PostgreSQL exposes it as the **`pg_lsn`** data type, printed as two hexadecimal numbers separated by a slash, for example `16/B374D848`. Because it is a byte offset, two LSNs can be subtracted to get the number of bytes of WAL between them. | Function or operator | What it returns | |---|---| | `pg_current_wal_lsn()` | the write position: the end of what has left the server's WAL buffers | | `pg_current_wal_insert_lsn()` | the insert position: the logical end of the WAL at this instant | | `pg_current_wal_flush_lsn()` | the flush position: the last WAL known to be on durable storage | | `pg_walfile_name(lsn)` | the name of the segment file holding that position | | `lsn1 - lsn2`, or `pg_wal_lsn_diff(lsn1, lsn2)` | the bytes between two positions, as `numeric` | This makes WAL volume easy to measure for a job, such as a payments ledger's nightly settlement batch: ```sql -- Before the batch: note the position and the file it falls in SELECT pg_current_wal_lsn() AS start_lsn, pg_walfile_name(pg_current_wal_lsn()) AS segment; -- After the batch, paste the start_lsn you noted SELECT pg_size_pretty(pg_current_wal_lsn() - '16/B374D848'::pg_lsn) AS wal_generated; ``` LSNs also appear in the header of every data page (the LSN of the last WAL record that changed the page) and in the replication and checkpoint views, which is why the same `X/Y` notation shows up all over PostgreSQL's monitoring. ## How segments leave `pg_wal` PostgreSQL manages the directory itself, and the sequence matters: 1. A **checkpoint** writes every dirty page to the data files and records a **redo point**: the LSN where crash recovery would start replaying. 2. Segments older than the one holding the redo point are no longer needed for crash recovery. 3. Before releasing them, the server checks every other holder: WAL archiving must have archived them, a replication slot may still need them, `wal_keep_size` may ask for a margin, and WAL summarization, when enabled, must have read them. 4. Free segments are **recycled** (renamed to become future segments) up to the expected need, never below `min_wal_size` (80 MB by default), and the rest are removed. ## Why deleting segments by hand is dangerous A `pg_wal` that fills a disk tempts people to remove the oldest files. That is the wrong move for several reasons: - The file that looks old may contain the redo point or records after it. Crash recovery begins there; if the record is missing the server logs errors such as `could not locate a valid checkpoint record` or `could not find redo location ...` and does not start. - Replay that silently skips records would leave pages that disagree with each other: corruption, not a clean rollback. - A standby, a replication slot consumer or the archive that still needed the segment loses it permanently, so the standby must be rebuilt and point-in-time recovery has a gap. - `pg_resetwal` is **not** a cleanup tool. It throws WAL away and the manual reserves it as a last resort for a server that will not start because of corruption; afterwards the data may be inconsistent. The right response to a growing `pg_wal` is to find what is holding WAL - an inactive replication slot, failing archiving, an oversized `wal_keep_size`, or checkpoints that cannot keep up - fix that holder, and let PostgreSQL release the segments at the next checkpoint. Each of those holders has its own diagnosis; none of them is fixed with `rm`.

  • How would you measure how much WAL one statement in the ledger batch generates?
    Run it under `EXPLAIN (ANALYZE, WAL)`: the plan reports WAL records, full-page images (`fpi`) and bytes for that statement. For a whole batch, note `pg_current_wal_lsn()` before and after and subtract the two `pg_lsn` values; `pg_stat_wal.wal_bytes` gives the cluster-wide total since the last statistics reset.
  • Can you change the 16 MB segment size of an existing cluster?
    Not with a reload: `wal_segment_size` is read-only and reports what `initdb --wal-segsize` chose. `pg_resetwal --wal-segsize` can change it on a cleanly shut down cluster without a new `initdb`. Larger segments mean fewer files and fewer archive calls; smaller ones mean finer-grained archiving.

  • Old files in pg_wal are leftovers you can delete to free space.
  • An LSN is a transaction ID that counts commits.
  • Each WAL segment file belongs to one transaction.
  • pg_resetwal is a routine way to shrink a large pg_wal.
  • WAL segment size can be changed in postgresql.conf with a reload.
open as a page

In PostgreSQL, how do you replicate three tables from an orders database to an analytics server using CREATE PUBLICATION and CREATE SUBSCRIPTION?

level: juniorimportance: must knowfreq 66%

basics

~20 s

Set wal_level = logical on the orders server and publish the three tables there; create the same tables on the analytics server, then CREATE SUBSCRIPTION, which makes a slot, copies existing rows and streams every later change.

open as a page

In PostgreSQL, how do you promote a streaming standby to primary, and why does a standby never promote itself when the primary fails?

level: juniorimportance: must knowfreq 64%

basics

~20 s

Run pg_ctl promote on the standby's host or call SELECT pg_promote() over SQL; both end recovery and open the server for writes. Core PostgreSQL ships no failure detector, so promotion is always an operator's or HA tool's decision.

open as a page

In PostgreSQL streaming replication, what does a replication slot guarantee that a standby without one lacks, and what risk does it add?

level: juniorimportance: must knowfreq 72%

basics

~20 s

A PostgreSQL replication slot makes the primary keep all WAL its consumer has not yet confirmed, even while that consumer is offline, so the standby cannot fall behind recycled WAL. The risk: an abandoned slot retains WAL until the disk fills.

open as a page

In PostgreSQL 18, what makes a server start as a streaming standby, and what can clients do while connected to it?

level: juniorimportance: must knowfreq 72%

basics

~20 s

An empty standby.signal file in the data directory plus primary_conninfo pointing at the primary make PostgreSQL start in standby mode and stream WAL; with hot_standby on (the default) clients may run read-only queries, never writes.

open as a page

In PostgreSQL, what does synchronous_commit = off risk for a click-tracking workload, and why is it safe where fsync = off is not?

level: middleimportance: must knowfreq 72%

basics

~20 s

synchronous_commit = off lets COMMIT return before its WAL is flushed, so a crash can lose up to about the last 600 ms of acknowledged commits but leaves the database consistent; fsync = off can corrupt the whole cluster after an OS crash.

open as a page

Why do UPDATEs on a PostgreSQL table without a primary key fail once it joins a publication, and which REPLICA IDENTITY setting fixes it?

level: middleimportance: must knowfreq 62%

basics

~20 s

Published UPDATEs and DELETEs need a replica identity; DEFAULT means the primary key, so without one the publisher rejects them with SQLSTATE 55000. Add a primary key, use USING INDEX on a qualifying unique index, or fall back to FULL.

open as a page

In PostgreSQL, how do the FIRST and ANY forms of synchronous_standby_names pick the standbys a commit waits for, and how does pg_stat_replication show it?

level: middleimportance: must knowfreq 55%

basics

~20 s

FIRST n (list) waits for the n highest-priority listed standbys that are streaming, later names standing by as replacements; ANY n (list) waits for any n listed standbys. pg_stat_replication.sync_state shows sync, potential, quorum or async.

open as a page

After a PostgreSQL failover the old primary cannot follow the promoted standby's new timeline; how do you rejoin it with pg_rewind, and what must already be configured?

level: seniorimportance: must knowfreq 55%

basics

~20 s

With the old primary shut down, run pg_rewind against its data directory with --source-server pointing at the new primary and -R, then start it as a standby. It needs data checksums, on by default in initdb since 18, or wal_log_hints = on.

open as a page

A PostgreSQL primary filled its disk after the data-warehouse change consumer stopped; how do you confirm a replication slot caused it, recover safely, and prevent a repeat?

level: seniorimportance: must knowfreq 58%

basics

~20 s

Free or add disk space (never delete pg_wal files), start the primary, find the inactive slot whose restart_lsn trails far behind in pg_replication_slots, drop it, then checkpoint. Prevent repeats with max_slot_wal_keep_size, idle_replication_slot_timeout and retained-WAL alerts.

open as a page

In PostgreSQL, what triggers an automatic checkpoint, what are the defaults of checkpoint_timeout, max_wal_size and checkpoint_completion_target, and what does raising them trade?

level: middleimportance: should knowfreq 55%

basics

~20 s

A checkpoint starts after checkpoint_timeout (5 min) or when WAL growth would exceed max_wal_size (1 GB), and its writes are spread over checkpoint_completion_target (0.9) of the interval. Raising them smooths I/O and cuts WAL but lengthens crash recovery.

open as a page

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%

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.

open as a page

In a PostgreSQL publication, how do a row filter and a column list limit what reaches the subscriber, and what rules constrain each?

level: middleimportance: should knowfreq 36%

basics

~20 s

A row filter (WHERE after the table name) sends only matching rows; a column list sends only named columns. If updates or deletes are published, both must stay within the replica identity, or the publisher's next UPDATE or DELETE fails.

open as a page

How does a libpq connection string listing several PostgreSQL hosts with target_session_attrs=read-write find a newly promoted primary, and what does it not do?

level: middleimportance: should knowfreq 48%

basics

~20 s

libpq tries each listed host in order and, with target_session_attrs=read-write, keeps only a server not in hot standby and not default read-only. It acts only when connecting: open connections break at failover and the application must reconnect.

open as a page

In PostgreSQL, what is a timeline ID, what does a file such as 00000002.history contain, and why does promotion create one?

level: middleimportance: should knowfreq 44%

basics

~20 s

A timeline ID numbers one branch of WAL history and forms the first eight hex digits of every WAL file name. Promotion starts a new timeline and writes a .history file listing each ancestor timeline and the LSN where it branched off.

open as a page

In PostgreSQL, what does a physical replication slot hold back on the primary compared with a logical slot, as pg_replication_slots shows it?

level: middleimportance: should knowfreq 48%

basics

~10 s

Both slot types pin WAL from restart_lsn. A physical slot also pins xmin for all tables when its standby sends hot_standby_feedback. A logical slot pins catalog_xmin, keeping old system-catalog rows, and tracks confirmed_flush_lsn.

open as a page

In PostgreSQL 18, how do max_slot_wal_keep_size and idle_replication_slot_timeout each bound what a replication slot retains, and what does each cost?

level: middleimportance: should knowfreq 40%

basics

~20 s

max_slot_wal_keep_size invalidates a slot whose restart_lsn trails the current WAL by more than the given size; idle_replication_slot_timeout, new in 18, invalidates a slot unused for longer than the given time. Both act at checkpoints, protect the primary, and force the consumer to resync.

open as a page

On a PostgreSQL primary, how do you turn pg_stat_replication's LSN columns into byte lag per stage, and what do write_lag, flush_lag and replay_lag measure instead?

level: middleimportance: should knowfreq 52%

basics

~20 s

Subtract the standby's sent_lsn, write_lsn, flush_lsn and replay_lsn from pg_current_wal_lsn() or from each other with pg_wal_lsn_diff() to get bytes behind per stage; the *_lag intervals time how long recent WAL took to be written, flushed and replayed.

open as a page

A PostgreSQL 18 payments ledger shows write-latency spikes every few minutes; which log lines and pg_stat_checkpointer and pg_stat_wal counters show checkpoints cause them, and what do you change?

level: seniorimportance: should knowfreq 46%

basics

~10 s

Match the spikes to log_checkpoints lines: 'checkpoint starting: wal' and 'checkpoints are occurring too frequently' mean max_wal_size is too small. Confirm with num_requested above num_timed and a wal_fpi surge, then raise max_wal_size.

open as a page

A PostgreSQL 18 subscription stops applying changes and logs conflict=insert_exists; how do you diagnose the cause and resume replication safely?

level: seniorimportance: should knowfreq 42%

basics

~20 s

An incoming insert hit a unique key already present on the subscriber, so the apply worker errors and retries in a loop. Read the logged key and finish LSN, fix the local row or skip that transaction, then stop local writes.

open as a page

In PostgreSQL logical replication, which changes never reach the subscriber, and how do you roll out an ALTER TABLE ADD COLUMN without breaking replication?

level: seniorimportance: should knowfreq 52%

basics

~20 s

Only row changes travel: DDL, sequence state, large objects and views do not. Add a column on the subscriber first and the publisher second, because a subscriber may have extra columns but errors when a replicated column is missing.

open as a page

In a planned PostgreSQL 18 switchover for a ticketing platform, in what order do you swap the primary and its streaming standby so nothing committed is lost and the old primary rejoins without pg_rewind?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Confirm the standby is streaming and caught up, stop the primary with a fast shutdown, check the standby replayed the primary's shutdown checkpoint, promote it, then start the old primary with standby.signal and primary_conninfo pointing at the new one.

open as a page

On a PostgreSQL streaming standby serving a newsroom's reporting team, long queries fail with 'canceling statement due to conflict with recovery'; why does this happen, and what are your options?

level: seniorimportance: should knowfreq 48%

basics

~20 s

Replay of the primary's WAL must not wait forever: when a record, such as vacuum cleanup or an ACCESS EXCLUSIVE lock, conflicts with a standby query, replay waits up to max_standby_streaming_delay (30 s) and then cancels the query with SQLSTATE 40001.

open as a page

A PostgreSQL standby without a replication slot stops streaming with 'requested WAL segment has already been removed'; what happened, and how do wal_keep_size and restore_command help?

level: seniorimportance: should knowfreq 36%

basics

~20 s

The primary recycled WAL past its last checkpoint before the slotless standby fetched it. wal_keep_size (default 0, in megabytes) keeps a minimum of past WAL in pg_wal; a restore_command reading a WAL archive lets the standby catch up from there.

open as a page

After updating PostgreSQL to 18.6, why does a wal2json change consumer fail with 'may not be used as an output plugin', and what fixes it?

level: middleimportance: nice to knowfreq 18%

basics

~10 s

PostgreSQL 18.6 added output_plugin_libraries, an allow-list that defaults to 'pgoutput, test_decoding'. Third-party plugins such as wal2json are refused until an administrator reviews them, adds them to the list and reloads the configuration.

open as a page

In PostgreSQL, how do you set up a cascading standby, and what changes for synchronous replication and monitoring when you do?

level: middleimportance: nice to knowfreq 24%

basics

~10 s

Point a downstream standby's primary_conninfo at another standby that accepts replication connections. Cascaded standbys are always asynchronous, appear only in their upstream's pg_stat_replication, and their hot_standby_feedback is passed on upstream to the primary.

open as a page

In PostgreSQL 18, what do data checksums detect and miss, what does a checksum failure look like, and how do you enable them on an existing cluster?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

Data checksums catch storage corruption of data pages when they are read back, failing the query with 'invalid page in block' and SQLSTATE XX001. They miss corruption before a write and outside data pages. Enable them offline with pg_checksums --enable.

open as a page

In PostgreSQL 17 and later, what must be configured so a logical subscriber can resume from a promoted physical standby through its replication slot?

level: seniorimportance: nice to knowfreq 24%

basics

~10 s

Create the logical slot with failover enabled, set sync_replication_slots on the standby (with primary_slot_name, hot_standby_feedback and a dbname in primary_conninfo), and list the standby's physical slot in synchronized_standby_slots on the primary.

open as a page