It's Not You, It's Me: Breaking Up with Massive Tables via Partitioning
summary
Shows four concrete migration recipes: a full offline duplicate-and-rename, an online duplicate with triggers and backfill, a single-host view+trigger trick for tight disk, and a cross-host cutover using logical replication with partition_route to seed a new partitioned instance. Explains tradeoffs (need partition key in primary/unique constraints, hash fixes partition count, default partitions hide missing rules), gives numbers (100 GB as a rough threshold, thousands of partitions hurts planning), and recommends PGPartman for automated maintenance.
AI-written summary · about
chapters (20)
- 0:00Introduction and speaker background
- 1:49What is partitioning
- 3:14Partitioning vs sharding
- 3:58PostgreSQL partitioning history
- 5:02Partition types in PostgreSQL
- 6:49Range partitioning explained
- 7:52List partitioning explained
- 9:17Hash partitioning explained
- 10:39Benefits of partitioning
- 14:12Drawbacks and gotchas
- 15:55When to partition tables
- 17:42Logical replication for partitions
- 21:10Migrating existing tables
- 23:16Offline migration with extra disk space
- 26:24Online migration with duplicated tables
- 29:36Online migration with limited disk space
- 32:47Logical replication migration
- 37:43Post-migration maintenance
- 39:49Configuration and observability
- 40:54Organizational support and Q&A
related talks
23mPostgres Partitioning Best Practices
26mMy Postgres partitioning cookbook
34mPartitioning and Data Migrations Using PostgreSQL as an Example
34mPartitioning and Data Migrations Using PostgreSQL as an Example
50mBeyond the How: Why Table Partitioning Truly Matters
31mPostgres Partitioning Best Practices
24mPartitioning strategy for Oracle to PostgreSQL migrations on Azure
26mPartitioning your Postgres tables for 20x better performance