Migrating a Huge Table to Partitions Online
Convert an existing monolithic table into a partitioned one with minimal locking and zero data loss.
Why Migrate a Huge Table?
Imagine an events table holding 800 million rows of append-only log data. Every query scans a monstrous index, VACUUM runs for hours, and dropping old data with DELETE bloats the table.
Partitioning splits one logical table into many physical child tables based on a key (for example, created_at by month). Benefits include:
- Partition pruning — the planner skips irrelevant partitions entirely.
- Instant data retention —
DROPorDETACHa whole month in milliseconds, no row-by-row delete. - Cheaper maintenance —
VACUUMand reindex run per partition.
The challenge: doing this on a live, write-heavy table without long locks or data loss.
The Naive Approach and Its Trap
PostgreSQL cannot turn an existing plain table into a partitioned one with a single ALTER TABLE. A partitioned parent is a different kind of object created with PARTITION BY.
The tempting one-shot plan is: create the partitioned table, then move all rows in a single transaction.
This blocks the table with heavy locks for the entire copy and holds a giant transaction open. On 800M rows that means hours of downtime and enormous WAL. We need an online strategy instead.
-- This single INSERT...SELECT locks and runs for hours.
-- Holds one transaction open across the whole 800M-row copy.
INSERT INTO events_partitioned
SELECT * FROM events_old; -- DON'T do this on a live huge tableAll lessons in this course
- Choosing a Partition Key and Strategy
- Partition Pruning at Plan and Execution Time
- Automating Partition Creation and Retention
- Migrating a Huge Table to Partitions Online