Online Table Repartitioning: Convert Live Tables Without Downtime

Online table repartitioning converts an existing table — unpartitioned, or partitioned on the wrong key — into a declaratively partitioned layout while the application keeps reading and writing it. This guide is part of Shard Migration & Rebalancing Operations and covers the shadow-table-plus-triggers workflow for PostgreSQL, the logical-replication variant, and pt-online-schema-change/gh-ost for MySQL. It is the single-instance sibling of zero-downtime resharding workflows, which moves keyspaces between servers rather than restructuring storage within one.

Problem Framing

An order-management service has a 1.8 TB orders table that was created five years ago as a plain heap. Retention policy says orders older than 24 months can be archived, but DELETE FROM orders WHERE created_at < ... now runs for six hours, bloats the table, and drags replica lag past its SLO. Autovacuum needs most of a day for a single pass. Index rebuilds are effectively impossible. Everyone agrees the table should have been range-partitioned by month from day one — dropping a partition is a metadata operation, vacuum works on month-sized chunks, and time-bounded queries prune to a handful of partitions (the layout tradeoffs are covered in range partitioning strategies).

The blocker is the conversion itself. PostgreSQL cannot turn a heap into a partitioned table in place: PARTITION BY exists only at CREATE TABLE time. The naive path — create a new table, INSERT INTO ... SELECT, rename — locks writes out for the hours the copy takes. With 4 000 writes/s against the table and a strict no-maintenance-window mandate, the conversion has to happen next to live traffic, invisibly, with an atomic swap at the end measured in milliseconds.

Architecture Overview

The workflow builds a shadow table with the desired partitioned layout, keeps it synchronized two ways at once — row triggers replay live changes, a batched backfill copies history — and finishes with a rename swap inside a single transaction. At no point does the application see anything but the name orders.

The two synchronization paths overlap on purpose. Triggers alone would miss all history; backfill alone would miss everything written during the copy. Running both means every row reaches the shadow table at least once — which is why every write into the shadow must be idempotent.

Step 1 — Create the Shadow Partitioned Table

Build orders_new with the target layout. The one schema change PostgreSQL forces on you: the partition key must be part of the primary key and of every unique constraint, so PRIMARY KEY (order_id) becomes PRIMARY KEY (order_id, created_at). Audit application code for upserts that assume order_id alone is unique before you commit to this.

CREATE TABLE orders_new (
  LIKE orders INCLUDING DEFAULTS INCLUDING STORAGE
) PARTITION BY RANGE (created_at);

ALTER TABLE orders_new ADD PRIMARY KEY (order_id, created_at);

-- One partition per month, historical through next month
CREATE TABLE orders_new_2024_07 PARTITION OF orders_new
  FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');
-- ... generate the intervening months with a script ...
CREATE TABLE orders_new_2026_07 PARTITION OF orders_new
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE orders_new_2026_08 PARTITION OF orders_new
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Deliberately do not create secondary indexes yet. The primary key index must exist (the sync trigger’s upserts need it), but every extra index makes the backfill slower and its WAL volume larger. Secondary indexes are built in Step 5, after the data is in place, where they build 3–5× faster.

Generate a partition for every month present in the source plus at least one future month, and wire up automated partition creation before the swap — a missing future partition turns midnight on the first of the month into an insert outage.

Step 2 — Install the Sync Triggers

An AFTER ... FOR EACH ROW trigger on the source replays every change into the shadow. Idempotency is the load-bearing property: the trigger and the backfill will race, and whichever writes a row second must not fail or corrupt.

CREATE OR REPLACE FUNCTION orders_shadow_sync() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO orders_new SELECT NEW.*
    ON CONFLICT (order_id, created_at) DO UPDATE
      SET status = EXCLUDED.status,
          amount_cents = EXCLUDED.amount_cents,
          updated_at = EXCLUDED.updated_at;
  ELSIF TG_OP = 'UPDATE' THEN
    -- created_at is immutable in this schema; if the partition key can
    -- change, DELETE old + INSERT new instead of UPDATE
    INSERT INTO orders_new SELECT NEW.*
    ON CONFLICT (order_id, created_at) DO UPDATE
      SET status = EXCLUDED.status,
          amount_cents = EXCLUDED.amount_cents,
          updated_at = EXCLUDED.updated_at;
  ELSIF TG_OP = 'DELETE' THEN
    DELETE FROM orders_new
    WHERE order_id = OLD.order_id AND created_at = OLD.created_at;
  END IF;
  RETURN NULL;
END $$;

CREATE TRIGGER trg_orders_shadow_sync
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_shadow_sync();

The trigger adds one indexed write per source write — typically 5–15% latency overhead on the write path. Measure it on a staging clone first, and deploy the trigger during a low-traffic period while watching p99. The UPDATE branch upserting (rather than updating) is what makes the trigger safe against rows the backfill hasn’t copied yet: an update to an uncopied row simply materializes it early.

Step 3 — Batched Backfill with Throttling

Copy history in primary-key-ordered batches, committing each batch, sleeping between them, and pausing entirely when replica lag climbs. Keyset pagination (not OFFSET) keeps each batch’s cost constant regardless of position in the table.

import time
import psycopg2

BATCH = 10_000
SLEEP_SECONDS = 0.5
MAX_REPLICA_LAG_BYTES = 64 * 1024 * 1024

COPY_SQL = """
    INSERT INTO orders_new
    SELECT * FROM orders
    WHERE (order_id, created_at) > (%s, %s)
    ORDER BY order_id, created_at
    LIMIT %s
    ON CONFLICT (order_id, created_at) DO NOTHING
    RETURNING order_id, created_at
"""

LAG_SQL = """
    SELECT coalesce(max(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)), 0)
    FROM pg_stat_replication
"""

def backfill(conn_str: str) -> None:
    cursor_key = ("", "1970-01-01")
    conn = psycopg2.connect(conn_str)
    conn.autocommit = True
    with conn.cursor() as cur:
        while True:
            cur.execute(LAG_SQL)
            while cur.fetchone()[0] > MAX_REPLICA_LAG_BYTES:
                time.sleep(5)
                cur.execute(LAG_SQL)

            cur.execute(COPY_SQL, (*cursor_key, BATCH))
            rows = cur.fetchall()
            if not rows:
                break                      # history fully copied
            cursor_key = rows[-1]
            time.sleep(SLEEP_SECONDS)

ON CONFLICT DO NOTHING — not DO UPDATE — is correct here and the asymmetry with the trigger matters: if a row already exists in the shadow, the trigger put it there from a newer image than the backfill’s snapshot, so the backfill must never overwrite it. The trigger always wins; the backfill only fills gaps.

At 10 000 rows per batch with a 0.5 s sleep, a 2-billion-row table backfills in roughly two days. That is a feature, not a bug: the throttle is what keeps autovacuum, replicas, and the application unaffected. Track progress with SELECT count(*) approximations from pg_class.reltuples on both tables rather than exact counts.

Logical replication variant. When the conversion also moves the table to a new primary (a major-version upgrade, new hardware), skip the triggers and let replication do both jobs: CREATE PUBLICATION pub_orders FOR TABLE orders on the old instance, and on the new instance create orders as a partitioned table with the same columns, then CREATE SUBSCRIPTION ... WITH (copy_data = true). PostgreSQL 13+ applies replicated rows through the partitioned parent, routing them to the correct partition. Cutover then follows the connection-string flip described in zero-downtime resharding workflows rather than the rename swap below. On a single instance, triggers are simpler and avoid tying up a replication slot.

Step 4 — The MySQL Path: pt-online-schema-change and gh-ost

MySQL tooling packages this entire workflow — shadow table, sync, backfill, swap — into one command, because ALTER TABLE ... PARTITION BY is just another ALTER to apply to the ghost copy:

pt-online-schema-change \
  --alter "PARTITION BY RANGE (TO_DAYS(created_at)) (
             PARTITION p2024_07 VALUES LESS THAN (TO_DAYS('2024-08-01')),
             PARTITION p2024_08 VALUES LESS THAN (TO_DAYS('2024-09-01')),
             PARTITION pmax     VALUES LESS THAN MAXVALUE)" \
  --max-lag 10 --chunk-size 5000 --critical-load "Threads_running=80" \
  --alter-foreign-keys-method auto \
  D=shop,t=orders --execute

pt-osc uses triggers exactly as in Step 2; gh-ost instead tails the binlog (requires binlog_format=ROW), which avoids trigger overhead on the source and allows pausing/resuming the migration. Both enforce MySQL’s rule that every unique key must include the partition columns, so the same primary-key redesign from Step 1 applies. Keep a pmax catch-all partition and split it later — MySQL’s equivalent of the missing-future-partition outage is an insert error against MAXVALUE-less layouts.

Step 5 — Build Indexes, Verify, and ANALYZE

With backfill complete and triggers keeping the shadow current, build secondary indexes per partition, then create the parent index. Building on the parent with CREATE INDEX would lock; per-partition CONCURRENTLY builds don’t, and the final parent-level ON ONLY + ATTACH assembly is metadata-only:

-- Parent shell first (does not build anything, does not lock children)
CREATE INDEX idx_orders_new_customer ON ONLY orders_new (customer_id, created_at);

-- Per partition, online:
CREATE INDEX CONCURRENTLY idx_2026_07_customer
  ON orders_new_2026_07 (customer_id, created_at);
ALTER INDEX idx_orders_new_customer
  ATTACH PARTITION idx_2026_07_customer;
-- ... repeat per partition; the parent index becomes valid when all attach

Verify equivalence the same way a cross-node migration would — counts, then bucketed checksums:

-- Run against both tables; results must be identical
SELECT date_trunc('month', created_at)                              AS month,
       count(*)                                                     AS rows,
       md5(string_agg(order_id::text || ':' || updated_at::text, ','
                      ORDER BY order_id))                           AS checksum
FROM orders     -- then orders_new
GROUP BY 1 ORDER BY 1;

Transient mismatches in the current month are expected while writes flow; re-run until only in-flight rows differ. Finally, ANALYZE orders_new; — the shadow table has never been analyzed, and swapping in a table with no statistics produces a plan-quality cliff at the worst possible moment.

Step 6 — Atomic Swap via Rename

The swap is two renames in one transaction. Its only danger is the lock queue: ALTER TABLE ... RENAME needs an ACCESS EXCLUSIVE lock, and if it waits behind a long-running query, every subsequent query on orders queues behind it. lock_timeout plus a retry loop converts that outage scenario into a few harmless retries:

SET lock_timeout = '2s';
BEGIN;
ALTER TABLE orders     RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

# Retry wrapper: attempt the swap up to 30 times, backing off between tries
for i in $(seq 1 30); do
  if psql "$DSN" -v ON_ERROR_STOP=1 -f swap_orders.sql; then
    echo "swap complete on attempt $i"; break
  fi
  sleep $((i * 2))
done

After the swap, the triggers on orders_old are inert (nothing writes to it), but drop them and keep orders_old around for a rollback window — a reverse swap is the same two renames in the opposite order, and it only stays honest while orders_old still receives no direct writes. Two follow-ups are easy to forget:

• Incoming foreign keys. Constraints on other tables that referenced orders follow the rename — they now point at orders_old, because FKs bind by table identity, not name. Drop and recreate each one against the new orders (NOT VALID + VALIDATE CONSTRAINT keeps it online).
• Sequence ownership and grants. ALTER SEQUENCE orders_id_seq OWNED BY orders.order_id; and re-apply any per-table grants; LIKE does not copy ACLs.

Once the rollback window closes (24–48 hours of clean operation), DROP TABLE orders_old; reclaims the 1.8 TB instantly.

Configuration Reference

Operational Contrast

Reach for this guide when the problem is physical layout on one instance: bloat, vacuum runtime, retention deletes, un-prunable scans. Zero-downtime resharding workflows solve capacity problems that layout cannot — when the box itself is out of headroom, data must move to another server, which brings routers, dual writes, and cross-node verification into scope. The two compose naturally: teams often repartition a table first (making per-partition data movement cheap) and shard it later.

Shard rebalancing & data movement sits at fleet scope: it redistributes existing well-formed partitions or keyspace slots across nodes, usually continuously. Repartitioning is a one-shot structural conversion with a beginning and an end; if you find yourself repartitioning the same table repeatedly, the partition key is wrong — fix the key choice first rather than industrializing the conversion.

Failure Modes

Common Mistakes

• Building all secondary indexes on the shadow table before backfilling. Every backfilled row then pays for every index, multiplying WAL and runtime. Backfill against the primary key only; build the rest in Step 5.
• Using session_replication_role = replica or disabling the trigger “to speed up a batch job”. Any write that bypasses the trigger silently diverges the shadow and invalidates verification. If a bulk job must run mid-migration, let it run through the trigger and extend the timeline.
• Forgetting objects that don’t follow a rename. Incoming foreign keys, sequence ownership, grants, and views bound to the old table’s identity all need explicit fixes; scripted pre-swap and post-swap checklists catch what memory won’t.
• Swapping without ANALYZE. The fresh partitioned table has empty statistics; the planner’s first minutes against it produce pathological plans exactly when everyone is watching the dashboard. ANALYZE is a mandatory pre-swap step, not a cleanup task.

FAQ

Related

• Shard Migration & Rebalancing Operations — parent overview of migration, rebalancing, and repartitioning workflows
• Migrating an Unpartitioned PostgreSQL Table to Partitions with Zero Downtime — the full command-by-command walkthrough of this conversion
• Zero-Downtime Resharding Workflows — moving keyspaces between servers when one instance’s capacity is the real limit
• Range Partitioning Strategies — choosing boundaries and keys for the target layout before you convert