Scaling PostgreSQL Beyond Query Optimization
summary
Postgres performance improves when concrete settings change: set shared_buffers 25–40% and effective_cache_size 50–75%, lower random_page_cost toward ~1.5, and expect queries to sometimes become orders of magnitude faster (one table saw ~100× improvement). Reduce autovacuum defaults (autovacuum_scale_factor from 0.2 to ~0.01–0.05, and autovacuum_analyze_scale_factor to ≤5%) to avoid bloat and near–XID wraparound, and enforce timeouts (statement_timeout ~≤5s, idle_in_transaction_session_timeout) to kill long transactions. These adjustments measurably tame recurring production spikes.
AI-written summary · about
chapters (5)
- 0:00Introduction
- 5:27PostgreSQL config defaults
- 12:32Tune autovacuum
- 18:55Avoid long transactions
- 24:23Summary
related talks
45mProper PostgreSQL Parameters to Prevent Poor Performance
34mTuning PostgreSQL to work even better
43mPostgres Performance: From Slow to Pro
33mTuning PostgreSQL to work even better
46mHow we made PG Fitter, Happier, More Productive
48mPractical Memory Tuning for PostgreSQL
57mPostgreSQL When It's Not Your Job
52mBoosting Typical Query Patterns: PostgreSQL 18's Performance Enhancements