PostgreSQL Partitioning: Slicing and Dicing for Performance and Easier Maintenance
summary
PostgreSQL declarative partitioning claims faster queries and easier maintenance by making partitions ordinary child tables that can be dropped or detached for retention; it supports RANGE, LIST, HASH and requires partitions to exist before inserts unless a default partition is used (which complicates maintenance). Primary key must include the partition column; ranges are lower-inclusive upper-exclusive; you cannot convert an existing table in-place so you must create a new partitioned table; use pg_partman to automate creating/detaching partitions and avoid >~1000 partitions to reduce planner overhead.
AI-written summary · about
chapters (8)
- 0:00Intro
- 1:22The what & why of partitioning
- 3:57What are partitions
- 5:09Declarative partitioning
- 9:55Data retention & archiving
- 11:37Three types of partitions in Postgres
- 15:37Creating & maintaining partitions
- 24:36SQL tips & best practices
related talks
31mPostgres Partitioning Best Practices
23mPostgres Partitioning Best Practices
42mBeginner's Guide to Partitioning vs Sharding in Postgres
26mMy Postgres partitioning cookbook
50mPerformance Improvements of Partitioning: Past and Future
50mBeyond the How: Why Table Partitioning Truly Matters
34mPartitioning and Data Migrations Using PostgreSQL as an Example
40mBeginner's Guide to Partitioning vs. Sharding in Postgres