A Quantitive Look at Partitions
summary
Benchmarks use EC2 and RDS to measure insert and read cost for monolithic tables, partial indexes, and various partitioning strategies with 50,000 partitions (and a failed run at 100,000). Insert throughput shows hump patterns: indexed monolith slows with size, case/if triggers start fast then degrade, dynamic EXECUTE is consistently slower; partial indexes and application-level routing change tradeoffs. Explain/planning time grows roughly linearly with number of child tables, reads collapse somewhere between hundreds and thousands of partitions, and there is a 65,535 hard limit in the planner; triggers vs dynamic insertion that creates partitions on demand proves too slow.
AI-written summary · about
chapters (18)
- 0:00Introduction
- 3:28C vs PG
- 4:38Description
- 5:48Insert Times
- 7:59Partial Index
- 9:08Partition Triggered
- 12:27Partition Triggers
- 19:31String Concatenation
- 25:33Table Scan
- 27:55Reads
- 29:07Planning Time
- 32:58What Happened
- 38:56Conventional Wisdom
- 40:18Complex Joins
- 42:11Schema
- 43:01Nesting partitions
- 45:26Constraint exclusion
- 46:16Out of pocket
related talks
30mOvercoming Performance Hurdles in Postgres Partitioned Tables
50mBeyond the How: Why Table Partitioning Truly Matters
50mPerformance Improvements of Partitioning: Past and Future
41mGlobal unique index a different approach
26mPartitioning your Postgres tables for 20x better performance
26mWhere do the performance cliffs come from?
45mIt's Not You, It's Me: Breaking Up with Massive Tables via Partitioning
26mMy Postgres partitioning cookbook