Postgres Partitioning Best Practices
summary
Postgres native partitioning plus the PG Partman extension is presented as a practical solution: use list, range, hash or composite partitioning (and subpartitioning) to reduce maintenance and enable partition pruning when queries filter on the partition key. Migrating a huge live table requires creating a new partitioned table and moving data with offline or online PG Partman methods; expect to manage many partitions (example: 2,000 partitions in the first year) and note limits like no global indexes and PKs that must include partition keys.
AI-written summary · about
chapters (14)
- 0:00Postgres partitioning intro
- 2:29Sophia’s Nav Tracker challenge
- 5:41List partitioning by ship ID
- 7:47Range partitioning by log timestamp
- 9:33Hash and composite partitioning
- 10:57Key partitioning decisions
- 13:47Managing daily partitions
- 14:30Automate partition maintenance with PG Partman
- 15:56Migrate tables to partitions
- 16:59Beyond performance benefits
- 19:46Partitioning limitations
- 20:53Testing and query optimization
- 21:34Key takeaways and future use cases
- 22:15Design partitioning early
related talks
31mPostgres Partitioning Best Practices
34mPartitioning and Data Migrations Using PostgreSQL as an Example
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