Migrating a Live Spatial Table to Partitions

← Back to Table Partitioning for Large Spatial Datasets

This page walks through converting a large, continuously written PostGIS table into a partitioned one, with the API serving traffic throughout and a rollback that stays one rename away.

# Context & When to Use

PostgreSQL cannot convert a table to a partitioned table in place. The strategy is fixed when the parent is created, so a migration means building a new hierarchy and moving the data — which on a 700 GB tracking table is hours of I/O that cannot happen inside a maintenance window. The design goal is therefore not speed but interruptibility: every step must be resumable, and the table must stay readable and writable while it runs.

There is a shortcut worth knowing. If you only need future data partitioned and are content to leave history as one lump, create the parent and ATTACH the existing table as a single catch-all partition. That takes seconds. It gives you cheap partitioning going forward and no pruning benefit on the historical rows, which is often exactly the right trade for a table whose queries are all recent-window anyway.

The full migration below is for the case where history matters: retention needs to expire month by month, or the historical index is what no longer fits in memory. It assumes the partition design from Table Partitioning for Large Spatial Datasets is already settled — key, width and retention.

# Runnable Implementation

-- 1. Shadow parent: same shape, composite PK, indexes defined on the parent
CREATE TABLE positions_new (
    LIKE positions INCLUDING DEFAULTS INCLUDING CONSTRAINTS,
    PRIMARY KEY (id, observed_at)
) PARTITION BY RANGE (observed_at);

CREATE INDEX positions_new_geom_gix     ON positions_new USING GIST (geom);
CREATE INDEX positions_new_vehicle_time ON positions_new (vehicle_id, observed_at DESC);

-- 2. Backfill one month at a time, as a STANDALONE table, then attach it.
--    Building the index off-hierarchy avoids holding locks on the live parent.
CREATE TABLE positions_2026_03 (LIKE positions_new INCLUDING DEFAULTS);

INSERT INTO positions_2026_03 (id, vehicle_id, observed_at, geom, speed_kph)
SELECT id, vehicle_id, observed_at, geom, speed_kph
FROM   positions
WHERE  observed_at >= '2026-03-01' AND observed_at < '2026-04-01';

-- The CHECK lets ATTACH skip its validation scan entirely
ALTER TABLE positions_2026_03
  ADD CONSTRAINT positions_2026_03_range
  CHECK (observed_at >= '2026-03-01' AND observed_at < '2026-04-01');

CREATE INDEX ON positions_2026_03 USING GIST (geom);
CREATE INDEX ON positions_2026_03 (vehicle_id, observed_at DESC);
ALTER TABLE positions_2026_03 ADD PRIMARY KEY (id, observed_at);

ALTER TABLE positions_new ATTACH PARTITION positions_2026_03
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');   -- instant, no scan

The CHECK constraint before ATTACH is the difference between a millisecond catalogue update and a full sequential scan under an ACCESS EXCLUSIVE lock. PostgreSQL uses the constraint to prove every row already satisfies the partition bound, so it skips validation.

# Key Parameters & Options

The dual-write trigger is short enough to read in one go:

CREATE OR REPLACE FUNCTION positions_dual_write()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO positions_new (id, vehicle_id, observed_at, geom, speed_kph)
    VALUES (NEW.id, NEW.vehicle_id, NEW.observed_at, NEW.geom, NEW.speed_kph)
    ON CONFLICT DO NOTHING;      -- backfill may already have copied this row
    RETURN NEW;
END $$;

CREATE TRIGGER positions_dual_write_trg
    AFTER INSERT ON positions
    FOR EACH ROW EXECUTE FUNCTION positions_dual_write();

ON CONFLICT DO NOTHING matters because the backfill and the trigger overlap at the boundary of the current month; without it the migration aborts on a duplicate key the first time a row is written to a range the copy has already reached.

# What the swap actually costs

The swap itself is four statements in one transaction:

BEGIN;
ALTER TABLE positions     RENAME TO positions_old;
ALTER TABLE positions_new RENAME TO positions;
DROP TRIGGER positions_dual_write_trg ON positions_old;
COMMIT;

# Tracking backfill progress

A six-hour backfill needs a progress signal, or the only way to know whether it is halfway or stuck is to watch disk usage. Record each completed month in a small control table, and the job becomes both resumable and reportable.

CREATE TABLE migration_progress (
    month        date PRIMARY KEY,
    rows_copied  bigint,
    finished_at  timestamptz DEFAULT now()
);

The backfill loop checks that table before each month and skips what is already done, which is what makes an interrupted run safe to restart. It also gives operations a straight answer to “how long left” — months remaining multiplied by the observed rate per month.

# Gotchas & Failure Modes

• Dual-write started after the backfill. Rows written during the copy never reach the new table and are silently missing after the swap. Always enable dual-write first, then backfill.
• ERROR: duplicate key value violates unique constraint during backfill — the trigger already inserted the row. ON CONFLICT DO NOTHING on the trigger insert, not on the backfill, is the right place to absorb it.
• Sequence left behind. id keeps its sequence through the rename because the sequence is owned by the column, but confirm with SELECT last_value FROM positions_id_seq before and after; a mismatch means the new table got its own sequence from LIKE INCLUDING DEFAULTS.
• Foreign keys pointing at the old table. They follow the rename, so a child table now references positions_old. Drop and recreate them against the new parent, and note that a foreign key to a partitioned table needs PostgreSQL 12+.
• Views and functions with search_path surprises. A view defined on positions binds to the OID, not the name, so after the rename it still reads the old table. Recreate every dependent view — list them with pg_depend before starting.
• Backfill starving autovacuum. Long batches hold snapshots that prevent cleanup on the live table, and bloat accumulates exactly while you are trying to migrate. Keep batches short and watch n_dead_tup, as described in Observability for Spatial Endpoints.

# Verification Snippet

-- Per-month reconciliation before the swap; every row must match
SELECT date_trunc('month', observed_at) AS month,
       count(*) FILTER (WHERE src = 'old') AS old_rows,
       count(*) FILTER (WHERE src = 'new') AS new_rows
FROM (
    SELECT observed_at, 'old' AS src FROM positions
    UNION ALL
    SELECT observed_at, 'new' AS src FROM positions_new
) t
GROUP BY 1 ORDER BY 1;

-- Geometry checksum per month catches a truncated or reprojected copy
SELECT date_trunc('month', observed_at) AS month,
       md5(string_agg(ST_AsBinary(geom)::text, '' ORDER BY id)) AS digest
FROM   positions_new
GROUP  BY 1 ORDER BY 1;

# After the swap: the API should be unchanged, and the plan should prune
psql -c "EXPLAIN SELECT count(*) FROM positions WHERE observed_at >= now() - interval '2 days'" \
  | grep -c positions_20
# 1  → one partition in the plan

# Related

• Table Partitioning for Large Spatial Datasets — the partition design this migration implements
• Automating Spatial Database Migrations in CI — running the steps as reviewed, repeatable migrations
• Managing Async Transactions for Bulk Geometry Writes — batch sizing for the backfill

← Back to Table Partitioning for Large Spatial Datasets