Postgres Partitioning Best Practices
summary
Partitioning splits a huge table into child tables so operations like purging 2023 become a fast drop partition instead of a slow DELETE; example uses one range partition per year and recommends choosing list vs range vs hash by access patterns. Native Postgres supports partition pruning and per-partition indexes but not global indexes, requires the partition key in primary keys, and needs a new partitioned table plus data migration (PG Partman offers automated creation/deletion and online/offline migration helpers).
AI-written summary · about
chapters (8)
- 0:00Introduction to partitioning
- 3:56How partitioning works
- 9:12Advantages and performance
- 15:03Best practices for implementation
- 19:11Using pg_partman
- 20:17Design and migration strategies
- 22:43Pitfalls and conclusions
- 25:09Question and answer session
related talks
23mPostgres Partitioning Best Practices
30mPostgreSQL Partitioning: Slicing and Dicing for Performance and Easier Maintenance
42mBeginner's Guide to Partitioning vs Sharding in Postgres
26mPartitioning your Postgres tables for 20x better performance
26mMy Postgres partitioning cookbook
34mPartitioning and Data Migrations Using PostgreSQL as an Example
30mOvercoming Performance Hurdles in Postgres Partitioned Tables
40mBeginner's Guide to Partitioning vs. Sharding in Postgres