Durability in Postgres
summary
Explains how Postgres actually achieves durability (WAL in 16MB segments, checkpoints that fsync dirty shared_buffers) and what to tune: checkpoint_timeout (default 5m), checkpoint_completion_target (default 0.55), max_wal_size (pre‑9.5 defaults ~48MB, newer defaults ~1GB), and wal_compression (since 9.5) to cut WAL volume. Shows tradeoffs: full_page_writes can be ~98% of WAL after frequent checkpoints, unlogged tables skip WAL, async commits give a ~4,000→~180,000 tx/s order‑of‑magnitude boost on local SSDs with a ~3s durability window, and synchronous_standby_names/remote_apply control cross‑node guarantees.
AI-written summary · about
chapters (12)
- 0:00Intro
- 0:28Durability Definition
- 1:56ValLogging
- 8:05Checkpoint Parameters
- 11:25Checkpoint Spreading
- 17:19Right Ahead
- 23:03Synchronous Commit
- 33:05No unlocked tables
- 35:54High availability
- 43:18Synchronous replication
- 46:53Twophase commit
- 50:50Checkpoint
related talks
35mUnderstanding and Optimizing commit sychronization for maximum performance
31mPostgreSQL WAL and Commit Synchronization, Optimization Opportunities
31mOptimizing Postgres for write heavy workloads ft. Checkpoint and WAL configs
34mTuning PostgreSQL to work even better
33mTuning PostgreSQL to work even better
44mUnderstanding Write-Ahead Logging (WAL) in PostgreSQL
43mTuning PostgreSQL for High Write Workloads
48mReplication Refresh