PostgreSQL WAL and Commit Synchronization, Optimization Opportunities
summary
Explains what each WAL record actually does, how WAL moves from shared 16 MB WAL buffer to OS and disk, and which OS IO behavior matters; shows small code snippets and demos and claims upping buffer to 64–128 MB doubles throughput on extreme concurrency; gives concrete tuning knobs at instance/database/user/session/transaction scope, explains remote write/flush/apply semantics, measures a ~TPS collapse with ~200 ms remote latency and notes that shipping WAL to a standby does not hide a slow local disk.
AI-written summary · about
chapters (21)
- 0:00Intro
- 1:54What is WAL?
- 3:34Why WAL?
- 6:27WAL Record Generation
- 8:40Transaction processing by session
- 10:06WAL Writing
- 11:22WAL Archives
- 12:22Wait Events Associated with WAL Gen/Writing
- 13:27Flushing
- 14:03Synchronization to Local Disk
- 15:30Synchronization with Standbys
- 17:20History and Features
- 18:33Synchronization with "Remote Write"
- 19:46Remote Flush/"on" With Synch Standby
- 20:13Remote Apply
- 21:01IO Recommendations
- 21:43WAL Compression
- 23:23WAL Propagation
- 24:09How Fast/Slow?
- 27:35Thank You! Q&A
- 28:10Selecting Synchronization at Different Scopes
related talks
35mUnderstanding and Optimizing commit sychronization for maximum performance
44mUnderstanding Write-Ahead Logging (WAL) in PostgreSQL
57mDurability in Postgres
26mThe Wonderful World of WAL
52mOn WAL Redundancy in Postgres
33mJourney of developing a performance optimization feature in PGSQL
24mPostgreSQL physical replication - internals, latest development and opportunities
31mOptimizing Postgres for write heavy workloads ft. Checkpoint and WAL configs