Understanding and Optimizing commit sychronization for maximum performance
summary
Explains how PostgreSQL guarantees durability via WAL and which knobs change real behavior: default 16 MB wal segment, WAL is 64‑bit LSN (effectively 16 exabytes), and wal_writer_delay defaults to 200 ms, so asynchronous commits can lose that window; xlog insert uses 8 locks which creates contention under extreme write concurrency. Covers fsync must be on, full_page_writes, synchronous_commit modes (on, remote_write, local), archiver/wal sender/receiver mechanics, and common pitfalls with virtualization and missing archiving.
AI-written summary · about
chapters (12)
- 0:00Intro
- 0:31What is this talk about
- 1:29Life of Data Manipulation
- 2:44Why WAL?
- 6:05What is a WAL & WAL segment
- 9:43WAL Record
- 13:32Control flow of a transaction
- 19:42WAL 10 path
- 23:12WAL segment completion
- 25:05WAL Senders
- 31:51commit_delay & commit_siblings
- 32:28Common Mistakes
related talks
31mPostgreSQL WAL and Commit Synchronization, Optimization Opportunities
57mDurability in Postgres
44mUnderstanding Write-Ahead Logging (WAL) in PostgreSQL
31mOptimizing Postgres for write heavy workloads ft. Checkpoint and WAL configs
25mPostgres Storytelling: What's going on with Synchronous Replication?
26mMyths and Truths about Synchronous Replication in PostgreSQL
47mA Batch of Commit Batching
29mLogical replication theory and concepts