My Postgres partitioning cookbook
summary
After four years of experiments the claim is clear: start partitioning before ~100 GB and use range-based partitioning not hash. Notes include Postgres 14 allowing concurrent DETACH (but not with default partitions), Postgres 18 changing default constraints, and a real case of reading 50 partitions instead of 2 fixed by aligning boundaries and turning on enable_partitionwise_join. The workflow uses NOT VALID CHECKs and risky catalog edits to attach partitions fast, plus a concurrent-index-then-parent-index strategy; it works but causes foreign-key/catalog surprises.
AI-written summary · about
chapters (15)
- 0:00Music & introduction
- 2:28Why large tables need partitioning
- 4:42Why range partitioning wins
- 5:52Time and integer partitioning examples
- 7:02The hidden risks of default partitions
- 8:29How default partitions save you
- 9:48Catalog tricks for partitioned tables
- 12:58Index creation challenges in partitioning
- 14:18Foreign key challenges in partitioned tables
- 15:14Automating partition management
- 15:37Why joining partitioned tables is hard
- 17:23Fixing partitioned table join problems
- 20:13Associative table design challenges
- 21:30Using partitioning for data archiving
- 23:20Partitioning summary and key takeaways
related talks
30mPostgreSQL Partitioning: Slicing and Dicing for Performance and Easier Maintenance
26mPartitioning your Postgres tables for 20x better performance
23mPostgres Partitioning Best Practices
31mPostgres Partitioning Best Practices
50mBeyond the How: Why Table Partitioning Truly Matters
30mOvercoming Performance Hurdles in Postgres Partitioned Tables
45mIt's Not You, It's Me: Breaking Up with Massive Tables via Partitioning
42mBeginner's Guide to Partitioning vs Sharding in Postgres