Leveraging table partitioning for query performance and data archiving
summary
Starts recommending range partitioning once tables reach ~100 GB, claiming maintenance and query wins up to 50x faster after aligning partitions around a single leading ID and denormalizing where needed. Demonstrates using UUIDv7 as a timestamped unique key for both joins and date boundaries, enabling safe detach-and-archive via partition detach plus a foreign data wrapper to a slower archive, and turning on enable_partitionwise_join/enable_partitionwise_aggregate to avoid scanning hundreds of partitions.
AI-written summary · about
chapters (10)
- 0:00Introduction
- 2:423 Reasons to Partition
- 4:313 Types of Partitioning
- 7:41Date Range Partitioning
- 10:32Integer Partitioning
- 13:28Joining Tables
- 16:17Align your partitioning around a leading figure
- 19:15UUIDv7 (Derk’s new favorite data type)
- 21:29Data Lifecycle Management
- 24:26Summary
related talks
50mBeyond the How: Why Table Partitioning Truly Matters
26mPartitioning your Postgres tables for 20x better performance
26mMy Postgres partitioning cookbook
50mPerformance Improvements of Partitioning: Past and Future
30mPostgreSQL Partitioning: Slicing and Dicing for Performance and Easier Maintenance
31mPostgres Partitioning Best Practices
45mIt's Not You, It's Me: Breaking Up with Massive Tables via Partitioning
23mPostgres Partitioning Best Practices