Splitting a Hot Shard with PostgreSQL Logical Replication

This walkthrough splits a saturated PostgreSQL shard in half using a row-filtered publication — one concrete run of the zero-downtime resharding workflow from Shard Migration & Rebalancing Operations. The scenario: shard 3 owns tenants 2000–2999 in a range-routed multi-tenant fleet, its p99 write latency has tripled, and disk I/O sits above 90% for most of the business day. The fix is to move the upper half of the range — tenants 2500–2999, about 400 GB across three tables — to a freshly provisioned shard 7, while both halves keep serving reads and writes. If a poor key choice caused the imbalance in the first place, choosing a partition key for multi-tenant workloads covers prevention; this page covers the surgery.

Prerequisites

Step 1 — Provision shard 7 and copy the schema

The new node starts empty but structurally identical: same tables, indexes, constraints, and extensions as shard 3. Copy the schema only — logical replication moves the rows — and immediately offset every sequence far past the source’s current values, so the first post-cutover insert on shard 7 cannot collide with a replicated row.

# Copy schema, indexes, and constraints from shard 3 to shard 7
pg_dump --schema-only --no-owner -h db-03.internal -U admin payments \
  | psql -h db-07.internal -U admin payments

# Generate and apply sequence offsets: source last_value + 10M headroom
psql -h db-03.internal -U admin -d payments -At -c \
  "SELECT format('SELECT setval(%L, %s);',
                 schemaname || '.' || sequencename, last_value + 10000000)
   FROM pg_sequences
   WHERE last_value IS NOT NULL" \
  | psql -h db-07.internal -U admin payments

Operational note: create the indexes now, before the data copy, and accept the slower initial load. The alternative — copy first, index later — is faster for a pure bulk load but leaves a window where the subscription is applying live changes against unindexed tables, and a single slow apply loop can push catch-up lag into hours.

DBA tip: diff the two schemas before going further: run pg_dump --schema-only against both hosts and compare. A missing NOT NULL, a divergent default, or a forgotten extension surfaces here as a two-line fix; it surfaces during catch-up as a stream of ERROR: null value in column ... messages and a stalled subscription.

Step 2 — Create the row-filtered publication on shard 3

PostgreSQL 15 lets a publication carry a WHERE clause per table, so only rows for the moving tenants ever leave shard 3. One catch: for UPDATE and DELETE, the filter is evaluated against the replica identity, so tenant_id must be part of it. REPLICA IDENTITY FULL is the blunt, safe choice for the migration’s duration.

-- On shard 3. Row filters on UPDATE/DELETE can only see replica-identity
-- columns, and tenant_id is not in these tables' primary keys.
ALTER TABLE orders       REPLICA IDENTITY FULL;
ALTER TABLE invoices     REPLICA IDENTITY FULL;
ALTER TABLE audit_events REPLICA IDENTITY FULL;

-- Shard 3 only holds tenants 2000-2999, so >= 2500 selects exactly
-- the upper half: tenants 2500-2999.
CREATE PUBLICATION pub_split_t2500
  FOR TABLE orders       WHERE (tenant_id >= 2500),
      TABLE invoices     WHERE (tenant_id >= 2500),
      TABLE audit_events WHERE (tenant_id >= 2500);

Operational note: skip the replica-identity change and the publication still gets created — the failure is deferred. The first UPDATE to a published row then fails on shard 3 itself with ERROR: cannot update table "orders" because the row filter cannot be evaluated. That error hits production writes on the source, not the migration tooling, which is why the ALTER TABLE statements come first in this step, not as a footnote.

SRE tip: name the publication after the operation (pub_split_t2500), never something generic like pub_orders. Six months from now, SELECT * FROM pg_publication should tell the on-call engineer exactly which migration owns each slot — orphaned replication objects with vague names are how source disks fill up at 3 a.m.

Step 3 — Subscribe from shard 7: initial copy plus catch-up

The subscription does two jobs in sequence: copy_data = true bulk-copies the filtered rows (the WHERE clause is applied on the publisher, so only the moving ~400 GB crosses the network), then the apply worker streams every change that committed since the copy began until shard 7 is current.

-- On shard 7
CREATE SUBSCRIPTION sub_split_t2500
  CONNECTION 'host=db-03.internal dbname=payments user=repl password=...'
  PUBLICATION pub_split_t2500
  WITH (copy_data = true, streaming = on, binary = true);

-- Per-table copy state: i = init, d = copying, s/r = synced & streaming
SELECT srrelid::regclass AS table_name, srsubstate AS state
FROM pg_subscription_rel
ORDER BY 1;

Operational note: the initial copy of each table runs in a single transaction on the publisher, which holds back vacuum on shard 3 for its duration. At a throttled 60 MB/s, 400 GB takes roughly two hours — schedule the CREATE SUBSCRIPTION at the start of the daily traffic trough so the copy and the bulk of catch-up complete before load returns.

SRE tip: watch lag from the publisher side, where a broken subscription is visible even when shard 7 is unreachable: SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) FROM pg_replication_slots WHERE slot_name = 'sub_split_t2500';. Wire that number into your dashboards before the copy starts — alerting on cross-shard replication lag with Prometheus shows the exporter and rules — and treat sustained growth as a stop-the-line signal.

Step 4 — Verify with per-tenant row counts and checksums

Matching row counts are the cheap first gate; matching checksums are the real one. Hashing each row and summing the hashes per tenant gives an order-independent fingerprint that is cheap to compare and — because it is grouped by tenant — localizes any mismatch to a single tenant instead of a 400 GB haystack.

#!/usr/bin/env python3
"""Compare per-tenant row counts + checksums between shard 3 and shard 7."""
import psycopg

SHARDS = {
    "shard_03": "host=db-03.internal dbname=payments user=verify",
    "shard_07": "host=db-07.internal dbname=payments user=verify",
}
QUERY = """
SELECT tenant_id,
       count(*)                        AS row_count,
       sum(hashtext(t::text)::bigint)  AS checksum
FROM {table} t
WHERE tenant_id BETWEEN 2500 AND 2999
GROUP BY tenant_id
"""

def snapshot(dsn: str, table: str) -> dict:
    with psycopg.connect(dsn) as conn:
        rows = conn.execute(QUERY.format(table=table))
        return {t: (n, c) for t, n, c in rows}

for table in ("orders", "invoices", "audit_events"):
    src = snapshot(SHARDS["shard_03"], table)
    dst = snapshot(SHARDS["shard_07"], table)
    bad = sorted(t for t in src if src[t] != dst.get(t))
    print(f"{table}: {len(src)} tenants compared, "
          f"{len(bad)} mismatched {bad[:5]}")

Operational note: while replication is live, a tenant that took a write between the two snapshots legitimately mismatches. Re-check only the mismatched tenants two or three times with short pauses; a tenant that stays mismatched across retries is a real divergence and blocks the cutover until explained.

DBA tip: hashtext folds the whole row through a 32-bit hash, so also reconcile one aggregate the business would notice — sum(amount_cents) per tenant on invoices is a strong end-to-end signal that costs one extra query per side.

Step 5 — Flip the shard map with a brief write pause

Cutover is a metadata change gated by a drain. Pause writes for the moving range only, wait for every router to acknowledge the pause, let the subscription drain to zero, then publish the new ownership and resume. Tenants 2000–2499 never notice.

def split_cutover(meta, routers):
    # 1. Pause writes for the moving range only (tenants 2500-2999)
    v = meta.execute(
        "UPDATE shard_map SET state = 'paused', version = version + 1 "
        "WHERE tenant_min = 2500 AND tenant_max = 2999 "
        "RETURNING version").fetchone()[0]
    wait_for_router_acks(routers, version=v)        # all routers hold writes

    # 2. Drain: no new writes are arriving, so lag must reach exactly 0
    wait_for_zero_lag(slot="sub_split_t2500", timeout_s=30)

    # 3. Re-point the range at shard 7 and resume in one version bump
    v = meta.execute(
        "UPDATE shard_map SET shard = 'shard_07', state = 'active', "
        "version = version + 1 "
        "WHERE tenant_min = 2500 AND tenant_max = 2999 "
        "RETURNING version").fetchone()[0]
    wait_for_router_acks(routers, version=v)        # writes flow to shard 7

Operational note: the unanimous-acknowledgment gate is what makes this safe without dual writes. If even one router instance still serves the old map version, it will send writes to shard 3 after the drain check passed, and those writes replicate onward — but a router serving the paused version cannot write to either shard, so a straggler stalls the cutover rather than corrupting it. Time out after 30 seconds and roll the version back rather than waiting indefinitely.

SRE tip: keep pub_split_t2500 and sub_split_t2500 running through the flip and for a 24–72 hour burn-in afterwards, and arm a reverse publication from shard 7 back to shard 3 immediately after the flip — the parent guide covers that rollback window in depth. Rolling back then costs one more map-version bump instead of a data-recovery incident.

Step 6 — Delete moved rows from shard 3 in throttled batches

Shard 3 still holds a dead copy of tenants 2500–2999. Once the burn-in window closes, tear down replication first — a live publication would happily replicate your cleanup DELETEs to shard 7 and destroy the moved data — then delete in small batches with pauses, so autovacuum and the standbys of shard 3 keep up.

#!/usr/bin/env bash
set -euo pipefail

# FIRST: stop replication, or the deletes below replicate to shard 7
psql -h db-07.internal -d payments -c "DROP SUBSCRIPTION sub_split_t2500;"
psql -h db-03.internal -d payments -c "DROP PUBLICATION pub_split_t2500;"

for table in orders invoices audit_events; do
  while :; do
    deleted=$(psql -h db-03.internal -d payments -At -c "
      WITH victims AS (
        SELECT ctid FROM ${table}
        WHERE tenant_id BETWEEN 2500 AND 2999
        LIMIT 20000)
      DELETE FROM ${table} t
      USING victims v WHERE t.ctid = v.ctid
      RETURNING 1" | wc -l)
    echo "$(date -Is) ${table}: deleted ${deleted}"
    [ "${deleted}" -eq 0 ] && break
    sleep 2
  done
  psql -h db-03.internal -d payments -c "VACUUM (ANALYZE) ${table};"
done

Operational note: DROP SUBSCRIPTION on shard 7 also drops the replication slot on shard 3, releasing the retained WAL. Confirm with SELECT slot_name FROM pg_replication_slots; on shard 3 before starting the deletes — an orphaned slot left behind by a failed drop pins WAL until the disk fills.

DBA tip: the deletes free space inside the tables for reuse; they do not shrink the files. That is usually fine — shard 3 will regrow into the space. If you must return disk to the OS, VACUUM FULL (with an exclusive lock) or pg_repack (online) are the follow-ups, and now is the moment to revert REPLICA IDENTITY to DEFAULT on all three tables.

Verification

After Step 5, prove placement from both sides — every moved tenant answers from shard 7, and shard 3 (pre-cleanup) receives no new writes for the range:

psql -h db-07.internal -d payments -At -c \
  "SELECT count(DISTINCT tenant_id) FROM orders
   WHERE tenant_id BETWEEN 2500 AND 2999"
psql -h db-03.internal -d payments -At -c \
  "SELECT count(*) FROM orders
   WHERE tenant_id BETWEEN 2500 AND 2999
     AND created_at > now() - interval '10 minutes'"

Expected output — all 500 tenants present on shard 7, zero fresh rows on shard 3:

500
0

And the Step 4 checksum script, re-run after the flip when no replication is in flight, must report zero mismatches:

orders: 500 tenants compared, 0 mismatched []
invoices: 500 tenants compared, 0 mismatched []
audit_events: 500 tenants compared, 0 mismatched []

After Step 6, the placement query against shard 3 (without the time filter) must also return 0, and pg_replication_slots on shard 3 must be empty of split-related slots.

Failure mode table

Failure mode Root cause SRE mitigation
Production UPDATEs on shard 3 fail with cannot update table "orders" right after Step 2 Row-filtered publication created before the replica identity included tenant_id, so the filter cannot be evaluated for updates Always run ALTER TABLE ... REPLICA IDENTITY FULL before CREATE PUBLICATION; if already hit, drop the publication to restore writes, fix identity, recreate
Shard 3 disk fills during the copy window The replication slot retains WAL for the whole multi-hour copy, or the subscription broke and the slot pins WAL indefinitely Set max_slot_wal_keep_size (e.g. 100 GB) as a hard cap, alert on pg_replication_slots.active = false within minutes, and size the copy window from peak WAL rate
Moved tenants disappear from shard 7 during cleanup Batch deletes on shard 3 ran while pub_split_t2500 was still active, so every DELETE replicated to the new shard Make DROP SUBSCRIPTION / DROP PUBLICATION the first, non-skippable lines of the cleanup script; restore from the reverse-replication copy or backup if hit

FAQ

Can I run this split on PostgreSQL 14 or older?

Not with native row-filtered publications — those arrived in PostgreSQL 15. On 14 and older you have three options: replicate the whole table set to the new shard and delete the tenants that did not move after cutover (double the transfer, same end state); use the pglogical extension, which has supported row filtering for years; or schedule the major-version upgrade first — a shard split is a strong forcing function. The rest of the workflow — subscription catch-up, checksum verification, router flip, throttled cleanup — is identical in all three variants.

How long does the write pause last, and does it affect tenants 2000-2499?

The pause applies only to the moving range, tenants 2500–2999. Tenants 2000–2499 keep writing to shard 3 without interruption, and every other shard is untouched. The pause itself lasts as long as it takes every router instance to acknowledge the paused map version plus the time for the subscription to drain to zero lag — typically one to three seconds when you flip during a quiet period. Routers should queue or retry writes for a paused range rather than surface errors, so well-behaved clients see a brief latency blip, not failures.

Do the tables need REPLICA IDENTITY FULL permanently?

No. FULL is a migration-time setting: row-filtered publications require the filter column in the replica identity for UPDATE and DELETE, and FULL is the fastest way to satisfy that. It makes every update ship the entire old row, which inflates WAL volume, so revert once the publication is dropped: ALTER TABLE orders REPLICA IDENTITY DEFAULT;. If you split shards regularly, a cleaner long-term option is a unique index that includes tenant_id and REPLICA IDENTITY USING INDEX, which keeps identity payloads small between migrations.

Back to Zero Downtime Resharding Workflows