skip to content

Checkpoints & Commit Durability

How PostgreSQL makes a COMMIT survive a crash: WAL segments and LSNs, full-page writes, checkpoints and synchronous_commit. Interviewers probe checkpoint spikes and the async-commit loss window.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

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

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

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

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