Overcoming Performance Hurdles in Postgres Partitioned Tables
summary
Experiments on Azure Postgres (PG17) show planning time rising from milliseconds to ~10s as partitions grow to 5000+ partitions (10,000 in one test) while execution stays millisecond‑fast; equivalence class linear lookups and derived‑clause scans cause the planner slowdown. High concurrency queries that miss the partition key trigger LWLock:lock_manager wait events, drive CPU toward 100% and halve TPS. Practical fixes: pick a true partition key, reduce partition granularity, drop unused indexes, or avoid partitioning; PG18 adds array/indexed equivalence lookups, hashed derived‑clause lookups and max_locks_per_transaction and avoids locking pruned partitions.
AI-written summary · about
chapters (10)
- 0:00Start
- 0:08Introduction - Postgres Partitioning Challenges
- 1:36What is Partitioning & Why is it Important?
- 4:00Types of Partitioning
- 7:06Partition Pruning
- 11:39Scenarios where Pruning Does not Happen
- 14:07What is Equivalence Class?
- 16:38Common Partitioning Challenges
- 23:13Solutions
- 26:30What is new in Postgres 18
related talks
26mPartitioning your Postgres tables for 20x better performance
50mPerformance Improvements of Partitioning: Past and Future
23mPostgres Partitioning Best Practices
31mPostgres Partitioning Best Practices
26mMy Postgres partitioning cookbook
06mSomething Got Better For Partitioning in v18 Unless Reverted
34mPostgres 10, Performance, and You
42mPushing Postgres Forward: From Open Source Contributions to Intelligent T