Scaling Postgres
summary
Recommendations and concrete tooling for scaling Postgres: prefer lots of RAM (64 GB minimum, 128–512 GB or more) and RAID10 SSDs, tune shared_buffers and effective_cache_size and set checkpoint_completion_target≈0.9, run PG Badger hourly reports and alert on slow queries, front with pgbouncer connection pooling, use streaming plus WAL shipping hybrid replication across a 3‑node master + two replicas to allow read reporting and catch‑up, prefer trigger‑based partitioning with pg_partman, and consider PG_FDW or Bucardo/Slony for selective or multi‑master replication.
AI-written summary · about
chapters (33)
- 0:00Scaling Postgres introduction
- 1:13Scalability for databases
- 3:00Database scalability challenges
- 4:46Vertical vs horizontal scaling
- 8:18Horizontal scaling pros and cons
- 10:08Postgres hardware specs
- 11:54Tuning PostgreSQL config
- 15:13Importance of logging
- 19:33PG Badger log analysis
- 21:19Monitoring performance trends
- 22:43Slow queries with EXPLAIN
- 23:48Tracking function performance
- 25:13When to partition tables
- 27:23Partitioning strategies
- 28:47PG Partman for partitions
- 30:55Load with connection pooling
- 31:18PG Bouncer connection overhead
- 33:45Caching layers for Postgres
- 35:11Postgres replication options
- 37:23OmniPITR replication management
- 39:53Cross-version FDW queries
- 40:29Third-party replication tools
- 42:20Database sharding intro
- 45:11Application-aware sharding
- 46:35Instagram schema sharding
- 47:46Postgres XC sharding
- 49:28Sharding challenges
- 50:10Scaling Postgres obstacles
- 52:16Team scaling collaboration
- 53:18Avoiding serialization
- 54:42Right tools for problems
- 55:06Further reading
- 55:50Q&A
related talks
57mPostgreSQL When It's Not Your Job
55mScaling PostgreSQL: Horizontal vs. Vertical
51mScaling Postgres to the next level at OpenAI
37mPostgres at Any Scale
36mPostgres at any Scale
30mManaging Postgres at scale: Challenges, Tools & Techniques
48mHow to Tame A Mastodon: Lessons for PostgreSQL at Scale
13mBuilding reliable infrastructure with Postgres