skip to content

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.