Migrating an Unpartitioned PostgreSQL Table to Partitions with Zero Downtime

This walkthrough converts a live 400 GB unpartitioned orders table on PostgreSQL 16 into a natively range-partitioned table with no write outage — the flagship scenario of online table repartitioning within shard migration & rebalancing operations. The plan: build a partitioned twin, sync it with logical replication while production traffic continues, then cut over with a single fenced transaction that completes in under a second.

One design decision up front. Logical replication matches tables on the subscriber by fully qualified name, so a twin named orders_new inside the same database can never receive the stream — the apply worker would resolve public.orders back to the source table itself. The twin therefore lives in a sibling database (shopdb_part) on the same PostgreSQL 16 instance, carries the name public.orders from day one, and the connection pooler flips the database alias at cutover while the old table is renamed out of the way.

Prerequisites

Step 1 — Create the partitioned twin

Build the twin in shopdb_part with the exact column list and defaults of the source, but declared PARTITION BY RANGE (created_at). PostgreSQL requires every primary key on a partitioned table to include the partition key, so the single-column key on id becomes a composite (id, created_at). Choose boundaries that match your retention and query patterns — see range partitioning strategies for boundary selection — and always end with a DEFAULT partition so no incoming row can be unroutable during the sync.

-- Run in the twin database: psql "host=10.0.1.5 dbname=shopdb_part user=migrator"
CREATE SEQUENCE orders_id_seq;

CREATE TABLE public.orders (
    id          bigint      NOT NULL DEFAULT nextval('orders_id_seq'),
    customer_id bigint      NOT NULL,
    status      text        NOT NULL DEFAULT 'pending',
    total_cents bigint      NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_p2023   PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_p2024   PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_p2025   PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_p2026h1 PARTITION OF orders FOR VALUES FROM ('2026-01-01') TO ('2026-07-01');
CREATE TABLE orders_p2026h2 PARTITION OF orders FOR VALUES FROM ('2026-07-01') TO ('2027-01-01');
CREATE TABLE orders_pdefault PARTITION OF orders DEFAULT;

CREATE INDEX orders_customer_idx ON orders (customer_id, created_at);

Operational note: Relaxing the primary key from (id) to (id, created_at) means the database now enforces uniqueness per partition, not globally. Global uniqueness of id is carried by the sequence alone — acceptable for surrogate keys, but audit any application code that relied on an ON CONFLICT (id) clause, which must become ON CONFLICT (id, created_at) or move to application-level logic.

DBA tip: For a 400 GB copy, comment out the secondary index and create it after Step 4 reports the copy finished. A tablesync COPY into an unindexed heap is typically 30–40% faster, and one CREATE INDEX pass on the parent afterwards parallelises across partitions with max_parallel_maintenance_workers.

Step 2 — Publish the source table and create the slot

On the source database, publish only orders and create the logical slot by hand. The manual slot matters: CREATE SUBSCRIPTION normally creates its own slot over the replication connection, but when publisher and subscriber are the same cluster that command deadlocks waiting on itself — the documented workaround is a pre-created slot plus create_slot = false on the subscription.

-- Run in the source database: psql "host=10.0.1.5 dbname=shopdb user=migrator"
CREATE PUBLICATION pub_orders_part FOR TABLE public.orders;

SELECT pg_create_logical_replication_slot('sub_orders_part', 'pgoutput');

Operational note: From the moment this slot exists, the source retains every byte of WAL the subscriber has not yet confirmed. If the migration is abandoned, drop the slot immediately — an orphaned slot is the classic way to fill a primary’s disk over a weekend.

SRE tip: Alert on pg_replication_slots.safe_wal_size dropping below ~50 GB for this slot. Combined with max_slot_wal_keep_size, that gives you a controlled failure (slot invalidated, migration restarted) instead of an uncontrolled one (primary out of disk, production down).

Step 3 — Create the subscription with copy_data

In the twin database, create the subscription pointing back at the source. copy_data = true triggers an initial snapshot copy before streaming begins. Because the target is partitioned, two subscriber-side requirements apply: columns are matched by name (extra columns on the target are filled from defaults), and every incoming row must route to an existing partition — the DEFAULT partition from Step 1 guarantees that.

-- Run in the twin database: psql "host=10.0.1.5 dbname=shopdb_part user=migrator"
CREATE SUBSCRIPTION sub_orders_part
    CONNECTION 'host=10.0.1.5 port=5432 dbname=shopdb user=migrator password=********'
    PUBLICATION pub_orders_part
    WITH (create_slot = false, slot_name = 'sub_orders_part', copy_data = true);

Operational note: The apply worker runs as a single process per subscription. If it hits an error — a routing failure, a unique violation from a non-empty twin — it logs, restarts, and retries the same change forever while the slot pins WAL on the source. Treat any logical replication apply worker error in the twin’s log as a page-worthy event during this migration.

DBA tip: Verify the twin was empty before this step. Any pre-seeded rows collide with the initial copy and wedge the tablesync worker in a crash loop; the fix is TRUNCATE on the twin and ALTER SUBSCRIPTION sub_orders_part REFRESH PUBLICATION — cheaper to check first.

Step 4 — Watch the copy finish and the lag reach zero

The migration has two phases visible in the catalogs: the initial copy (pg_subscription_rel.srsubstate moves through d = data copy to r = ready) and continuous streaming, where slot lag on the source should hover near zero. Do not plan cutover until state is r and lag stays under a few megabytes through your daily traffic peak — the same discipline covered in replication lag & capacity alerting.

-- In shopdb_part: 'r' means initial copy done, live streaming active
SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel;

-- In shopdb: WAL the twin still has to replay
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_name = 'sub_orders_part';

Operational note: Sequence values do not replicate. The twin’s orders_id_seq still reads 1 even with 400 GB of rows applied, because replicated inserts carry explicit id values. The cutover script in Step 5 must setval the sequence before the first application insert lands on the twin.

SRE tip: Rebuild the secondary index now (if you deferred it in Step 1) and run ANALYZE orders; on the twin. Cutting over to a table with no statistics produces a brief storm of terrible plans that looks exactly like an outage on your latency dashboards.

Step 5 — Cut over with a fenced lock-and-rename transaction

The cutover fences writes at the pooler, renames the old table inside a single short transaction guarded by lock_timeout and a retry loop, waits for the twin to replay the final WAL, aligns the sequence, and flips the PgBouncer alias so shopdb now points at the partitioned twin. Application connections simply queue for the duration — typically well under two seconds.

#!/usr/bin/env bash
# cutover.sh — fence writes, drain replication, rename, flip the pool
set -euo pipefail
SRC="host=10.0.1.5 dbname=shopdb user=migrator"
DST="host=10.0.1.5 dbname=shopdb_part user=migrator"
BOUNCER="host=10.0.1.5 port=6432 dbname=pgbouncer user=pgb_admin"

psql "$BOUNCER" -c 'PAUSE shopdb;'   # new queries queue at the pooler

for attempt in 1 2 3 4 5; do        # lock-and-rename, one transaction
  if psql "$SRC" -v ON_ERROR_STOP=1 -q <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '2s';
LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;
ALTER TABLE public.orders RENAME TO orders_retired;
COMMIT;
SQL
  then break
  elif [ "$attempt" -eq 5 ]; then
    psql "$BOUNCER" -c 'RESUME shopdb;'; echo 'cutover aborted'; exit 1
  fi
  sleep 1
done

FENCE_LSN=$(psql -At "$SRC" -c 'SELECT pg_current_wal_lsn();')
until psql -At "$SRC" -c "SELECT pg_wal_lsn_diff(confirmed_flush_lsn, '$FENCE_LSN') >= 0
    FROM pg_replication_slots WHERE slot_name = 'sub_orders_part';" | grep -qx t; do
  sleep 0.2                          # drain the last committed changes
done

psql "$DST" -c "SELECT setval('orders_id_seq', (SELECT max(id) + 1000 FROM orders));"
psql "$DST" -c 'ALTER SUBSCRIPTION sub_orders_part DISABLE;'

# pgbouncer.ini already edited: shopdb = host=10.0.1.5 port=5432 dbname=shopdb_part
psql "$BOUNCER" -c 'RELOAD;'
psql "$BOUNCER" -c 'RESUME shopdb;'

Operational note: The rename is the safety fence, not the routing mechanism. Any straggler client that bypasses the pooler and connects to the old database now fails loudly with relation "orders" does not exist instead of silently writing to a retired table — exactly the failure you want.

SRE tip: lock_timeout = '2s' plus retries beats one patient lock attempt. An ACCESS EXCLUSIVE request queues behind long-running transactions and blocks every reader queued behind it; timing out and retrying releases that convoy each attempt. If all five attempts fail, hunt the blocker in pg_stat_activity rather than raising the timeout.

Step 6 — Keep the rollback path warm

Do not drop anything for at least a week. The retired table plus a reverse rename is a complete rollback, provided you reconcile rows written to the twin after cutover — easy to find because their id values sit above the pre-cutover maximum.

#!/usr/bin/env bash
# rollback.sh — point the pool back at the original table
set -euo pipefail
BOUNCER="host=10.0.1.5 port=6432 dbname=pgbouncer user=pgb_admin"

psql "$BOUNCER" -c 'PAUSE shopdb;'
psql "host=10.0.1.5 dbname=shopdb user=migrator" \
     -c 'ALTER TABLE public.orders_retired RENAME TO orders;'
# pgbouncer.ini restored: shopdb = host=10.0.1.5 port=5432 dbname=shopdb
psql "$BOUNCER" -c 'RELOAD;'
psql "$BOUNCER" -c 'RESUME shopdb;'

Operational note: After a rollback, the forward subscription must not simply be re-enabled — rows written to the twin during the flipped window would conflict on replay. Copy the delta back (SELECT * FROM orders WHERE id > <pre-cutover max> on the twin), then drop and recreate the subscription for a fresh attempt.

DBA tip: Once the migration is declared final, retire in order: DROP SUBSCRIPTION sub_orders_part; on the twin (this drops the slot), DROP PUBLICATION pub_orders_part; on the source, then DROP TABLE orders_retired;. Dropping the table first leaves a slot pinning WAL with nothing to consume it. From here, keep future partitions flowing with a scheduled job — see automating quarterly partition creation in PostgreSQL.

Verification

Confirm rows landed in the right partitions and nothing leaked into the safety net:

SELECT tableoid::regclass AS partition, count(*) AS rows
FROM orders
GROUP BY 1
ORDER BY 1;

Expected output — every year routed to its partition, and orders_pdefault empty (a non-zero count there means a partition boundary is missing):

    partition    |   rows
-----------------+----------
 orders_p2023    | 38114202
 orders_p2024    | 41283920
 orders_p2025    | 66102451
 orders_p2026h1  | 40277813
 orders_p2026h2  |  1093342
 orders_pdefault |        0

Then prove the sequence and routing survive a live insert:

INSERT INTO orders (customer_id, status, total_cents)
VALUES (42, 'pending', 1999)
RETURNING id, created_at, tableoid::regclass AS landed_in;
    id     |          created_at           |   landed_in
-----------+-------------------------------+----------------
 186772415 | 2026-07-06 11:02:17.83112+00  | orders_p2026h2

The returned id must be above the old table’s maximum (headroom from setval), and landed_in must be the current half-year partition.

Failure mode table

Failure mode Root cause SRE mitigation
Initial copy restarts in a loop, twin log shows unique violations Twin table was not empty, or a previous half-finished copy left rows behind TRUNCATE the twin, ALTER SUBSCRIPTION ... REFRESH PUBLICATION, and gate the runbook on a SELECT count(*) = 0 pre-check
Source disk fills during the sync window Replication slot retains WAL while the 400 GB copy or a stalled apply worker lags Cap with max_slot_wal_keep_size, alert on safe_wal_size < 50 GB, and treat apply-worker errors in the twin’s log as pages
Cutover aborts — all five lock attempts time out Long-running transaction or autovacuum (wraparound mode) holds a conflicting lock on orders Query pg_locks joined to pg_stat_activity for the blocker, terminate or wait it out, re-run cutover.sh; schedule cutover away from batch jobs

FAQ

Why can't the partitioned twin live in the same database as the source table?

Logical replication matches tables on the subscriber by fully qualified name, and there is no remapping option. A subscription created inside the source database would resolve public.orders to the source table itself, so the initial copy and every applied change would collide with live rows. The twin therefore needs its own database — a second database on the same instance works fine — where it can carry the name public.orders, and the pooler alias flip makes the swap invisible to the application.

Does logical replication route rows into the correct partitions automatically?

Yes. Since PostgreSQL 13 a subscription target may be a partitioned table, and both the initial copy and streamed changes are routed through the partition tree to the correct leaf. A row whose partition key matches no declared partition makes the apply worker error and replication stalls — which is why the twin includes a DEFAULT partition as a safety net during the migration. Check it is empty before cutover.

How long does the initial copy of a 400 GB table take?

A single table syncs on one tablesync worker doing a server-side COPY, so throughput is bounded by one CPU core plus target I/O — typically 3 to 8 hours for 400 GB on NVMe-backed instances. Dropping secondary indexes on the twin during the copy and recreating them before cutover commonly saves 30–40%. The source stays fully online for the entire copy; the only cost is WAL retention on the slot.

Back to Online Table Repartitioning