LLM Best Practices

WAL Mode

2 min read

View markdown source

Definition

Write-Ahead Logging (WAL) guarantees durability by writing every change to the WAL (a sequential log on disk) before modifying data pages in shared_buffers. On crash, PostgreSQL replays WAL records from the last checkpoint to recover committed changes and roll back uncommitted ones.

WAL is also the mechanism for:

  • Streaming replication: the primary streams WAL to replicas; replicas apply WAL to stay current. A read replica is a standby receiving WAL.
  • Logical replication: WAL is decoded into row-level changes (INSERT, UPDATE, DELETE) for downstream consumers.
  • Point-in-time recovery (PITR): archive WAL segments to object storage; restore the base backup, then replay archived WAL to any committed moment.

Key configuration parameters:

  • wal_level: minimal, replica (default), logical. Logical replication requires logical.
  • synchronous_commit: on (default, safe) flushes WAL to disk before confirming commit; off risks losing the last few seconds of commits on crash in exchange for lower latency.
  • checkpoint_completion_target: spread checkpoint writes; set to 0.9 to avoid I/O spikes.
  • max_wal_size: limit WAL accumulation between checkpoints.

In SQLite, WAL mode (PRAGMA journal_mode=WAL) provides a different but analogous benefit: readers do not block writers and writers do not block readers.

When it applies

Use wal_level = logical if you need logical replication or change-data capture. Leave synchronous_commit = on for financial or transactional workloads. Archive WAL for PITR on any database you cannot afford to lose.

Example

SHOW wal_level;
SHOW synchronous_commit;
 
SET synchronous_commit = off;
INSERT INTO page_view_log SELECT ...; -- batch import, loss acceptable
RESET synchronous_commit;
  • vacuum - VACUUM and autovacuum generate WAL for their writes.
  • transaction-isolation - WAL ensures committed transactions survive crashes regardless of isolation level.
  • read-replica - read replicas are Postgres standbys that apply WAL from the primary.
  • postgres - the Postgres deep-dive.
  • autovacuum - heavy autovacuum loads increase WAL volume.

Citing this term

See WAL Mode (llmbestpractices.com/glossary/wal-mode).

Graph View

Backlinks