Convert a Large Table to Partitions Online

Attach a partitioned twin, dual-write or replicate into it, backfill by range, and swap names in one transaction with minimal lock time.

~60 mindatabasepostgresoperations

Task

Migrate a monolithic table into a range-partitioned layout without a maintenance outage or a long exclusive lock.

When to use this

The events table passed a terabyte. Deletes for retention take hours, backups are unwieldy, and queries by month scan everything. Partitioning by month fixes all three but cannot afford an outage.

Prerequisites

PostgreSQL 11+ with declarative partitioning and ATTACH PARTITION
Disk for the new partitioned copy alongside the old table
A replication slot, trigger-based dual-write, or an ETL window for ongoing inserts

Steps

1. Create the partitioned twin with the same shape.

CREATE TABLE events_p (
  LIKE events INCLUDING ALL
) PARTITION BY RANGE (occurred_at);

Expected output: The new table has identical columns and constraints, partitioned by the timestamp column, and starts empty.

2. Route ongoing writes into both tables.

CREATE FUNCTION events_dual_write() RETURNS trigger AS $$
BEGIN
  INSERT INTO events_p VALUES (NEW.*);
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER events_dw AFTER INSERT ON events
FOR EACH ROW EXECUTE FUNCTION events_dual_write();

Expected output: Every new row lands in both tables; updates and deletes to historical rows are not expected during the migration window.

3. Backfill history one partition at a time.

CREATE TABLE events_p_2025_12 PARTITION OF events_p
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

INSERT INTO events_p_2025_12
SELECT * FROM events WHERE occurred_at >= '2025-12-01' AND occurred_at < '2026-01-01';

Expected output: Each month becomes its own partition, backfilled in a bounded transaction that can rerun on failure by truncating that partition.

4. Catch up the tail and verify counts.

SELECT (SELECT count(*) FROM events) - (SELECT count(*) FROM events_p) AS delta;

Expected output: Delta reaches zero once the trigger-fed rows plus backfill cover everything; a nonzero delta identifies the missing range to redo.

5. Swap in a single short transaction.

BEGIN;
LOCK TABLE events, events_p IN ACCESS EXCLUSIVE MODE;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_p RENAME TO events;
COMMIT;

Expected output: The rename pair holds its exclusive lock for milliseconds, and the application now reads and writes the partitioned table.

Troubleshooting

Problem

Updates to old rows diverge

Solution

The trigger covers inserts only; if historical rows can change, either freeze them contractually during the window or add an ON UPDATE OR DELETE trigger mirroring the same change.

Problem

ATTACH PARTITION validates all rows under a lock

Solution

Create partitions empty and fill them afterwards, or add the constraint beforehand so attach validation is skipped.

Problem

Primary key does not include the partition key

Solution

Declarative partitioning requires unique keys to contain the partition column; widen the key to include occurred_at before starting.

Limitations

The dual-write trigger adds write overhead for the whole migration window; schedule it tightly and remove it immediately after the swap.
The swap requires a brief exclusive lock; coordinate it with a low-traffic moment even though it lasts milliseconds.

Grow the partitioned twin next to the original

You cannot afford to lock a terabyte table while rewriting it, but you can afford a twin. Create the partitioned copy beside the original, mirror new writes into it with a trigger, backfill history one bounded partition at a time, verify the delta reaches zero, and finally rename both tables inside one short transaction. The exclusive lock appears only for milliseconds at the very end, and every step before it is restartable.

-- 1. Twin
CREATE TABLE events_p (LIKE events INCLUDING ALL)
  PARTITION BY RANGE (occurred_at);

-- 2. Mirror writes
CREATE TRIGGER events_dw AFTER INSERT ON events
  FOR EACH ROW EXECUTE FUNCTION events_dual_write();

-- 5. Swap in one transaction
BEGIN;
LOCK TABLE events, events_p IN ACCESS EXCLUSIVE MODE;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_p RENAME TO events;
COMMIT;

Delta is the finish line

The migration is complete exactly when count of the old table equals count of the twin, and that number is cheap to poll during the backfill. Because the trigger feeds the twin continuously, the delta only ever reflects not-yet-backfilled history, so a nonzero delta names the exact month to redo rather than requiring a full restart.

Retention becomes a drop, not a delete

The payoff arrives immediately after the swap: expiring a month is now DETACH or DROP of one partition, an instant catalog operation instead of a multi-hour DELETE that bloats the table. The same property accelerates backup granularity and lets month-scoped queries read one partition instead of a terabyte.