Tuning PostgreSQL for High Write Workloads
summary
Starts with a 9-index test table (UUID PK, many random columns) that peaks ~25k TPS then collapses; updates run ~3.7k TPS. Major causes are full-page writes and log-buffer throughput. Turning on wal_compression barely helps for random data; raising max_wal_size=16GB extends good performance but raises recovery from ~3s to ~91s. Removing a few indexes yields steady ~20–26k TPS for days. Making UUIDs right-leaning and vacuuming in memory (3.5s vs 165s) also cuts cost; Aurora Postgres’ log-based storage reaches ~26k inserts and ~17k updates.
AI-written summary · about
chapters (27)
- 0:00Introduction
- 0:52High Write Workloads
- 2:20Table Setup
- 3:10Indexing
- 3:34Performance
- 4:33Updates
- 5:19Checkpoints
- 6:33Full page writes
- 6:49Full block writes
- 7:00Why do we do full page writes
- 8:06Cut down view
- 8:55Wall compression
- 9:26Reran test
- 9:47Data compression
- 10:27Longer checkpoints
- 11:56Update workload
- 12:07Async
- 13:53Tradeoff
- 14:07Example
- 22:11Results
- 22:52Vacuuming
- 26:20Vacuums
- 30:00Aurora Postgres
- 30:32Stop and Go
- 32:26Aurora PostgreSQL
- 33:17Update PostgreSQL
- 33:43Questions
related talks
37mPostgres at Any Scale
31mOptimizing Postgres for write heavy workloads ft. Checkpoint and WAL configs
34mTuning PostgreSQL to work even better
53mVisualizing Postgres I O Performance for Development
27mPostgres Performance in 15 Minutes
25mScaling PostgreSQL Beyond Query Optimization
31mPostgres Performance for Humans
43mPostgres Performance: From Slow to Pro