30 Indexes on a 6TB Postgres Table: Challenges and Solutions
summary
Doctolib describes managing a 30TB Postgres cluster with a single 6TB table that had 30 indexes: engineers identified and removed duplicate and unused indexes, added tests to block reintroducing them, and benchmarked index candidates using pgbench plus an explain-comparison tool that runs transactional DDL to quickly try index sets. The work reclaimed about 3TB of index bloat, improved write performance by ~15% and some query plans by ~70%, and delivered automated alerts and open-source tooling to prevent regressions.
AI-written summary · about
chapters (19)
- 0:00Introduction
- 1:23Index write amplification
- 3:12Table and index analysis
- 4:38PG bench performance
- 6:04Drop unused indexes
- 7:32Unique and duplicate indexes
- 8:35Never-used indexes
- 10:03Obsolete indexes and alerts
- 14:46Query and index fixes
- 16:53Used indexes and sharing
- 19:01Partial indexes optimization
- 22:15Shrink index size
- 24:04Production migration
- 25:51Load tests and regressions
- 28:44Automation and tooling
- 33:21Benchmark tool demo
- 38:41Results and benefits
- 40:30Automations and improvements
- 41:56Q&A
related talks
52mPostgreSQL Index Strategy Guide
48mHow to Tame A Mastodon: Lessons for PostgreSQL at Scale
36mPostgres at any Scale
52mDoctolib Needs a More Powerful Database. Okay, ... ?
37mPostgres at Any Scale
30mManaging Postgres at scale: Challenges, Tools & Techniques
43mTuning PostgreSQL for High Write Workloads
27mPostgres Performance in 15 Minutes