Partitioning your Postgres tables for 20x better performance
summary
Shows converting inheritance-based partitions to native partitioning, adding check constraints and denormalising ledger joins so everything partitions by transaction ID, which lets the planner prune at execution time instead of touching dozens of child tables. Fixes prepared-statement planning inefficiencies and avoids lock-manager blowups from hundreds of fast-path locks. Targets 100–200GB partitions to stay well under the 32TB table limit. Under load some queries become 50x faster (occasional 70x) after the redesign.
AI-written summary · about
chapters (6)
- 0:00Intro
- 0:36Partitioning and LEGO
- 2:55Introduction and Agenda
- 7:39Single table partitioning
- 14:21Design flaws
- 21:08Target setup
related talks
30mOvercoming Performance Hurdles in Postgres Partitioned Tables
31mPostgres Partitioning Best Practices
23mPostgres Partitioning Best Practices
26mMy Postgres partitioning cookbook
50mPerformance Improvements of Partitioning: Past and Future
06mSomething Got Better For Partitioning in v18 Unless Reverted
27mLeveraging table partitioning for query performance and data archiving
34mPostgres 10, Performance, and You