Zero-Downtime Resharding Workflows: Split, Merge & Cutover

Resharding moves ownership of a keyspace slice from one shard to another — splitting an overloaded shard in two, or merging underused shards into one — while the application keeps reading and writing the whole time. This guide is part of Shard Migration & Rebalancing Operations and covers the full workflow: the dual-write phase, logical-replication backfill, checksum verification, the router config flip, and the rollback window that makes the flip reversible. It complements online table repartitioning, which restructures a table inside one database, and shard rebalancing & data movement, which applies the same mechanics continuously across many shards.

Problem Framing

A payments platform runs sixteen PostgreSQL shards keyed by customer_id through a 128-slot hash map. Shard 07 owns slots 96–111 and, thanks to three enterprise customers, now takes 34% of cluster writes. Its p99 insert latency has climbed from 4 ms to 38 ms, autovacuum falls behind every business day, and the next hardware tier doubles cost for maybe a year of headroom. The fix is a split: move slots 104–111 (roughly half of shard 07’s data, about 900 GB) to a new shard 17.

The constraints are what make this hard. Writes arrive at 12 000 rows/s and cannot pause for more than a router-level retry (a few hundred milliseconds). The copy itself takes hours, during which the source keeps mutating. And if anything looks wrong after traffic moves, the team must be able to return to shard 07 without losing a single committed write. A maintenance window is off the table; a pg_dump/restore would be stale the moment it finished.

The same workflow, run in reverse, merges shards: an underused pair created during a growth spurt can be consolidated by treating one as the “target” for the other’s entire keyspace. Every phase below applies identically; only the slot arithmetic changes.

The Cutover State Machine

Every zero-downtime reshard walks through the same six states. Each state has an entry condition, an exit condition, and — until the final one — a cheap path back to safety. Teams get into trouble when they blur the states together, most often by cutting over while backfill lag is “probably fine” or by decommissioning before the rollback window has proven itself.

The dashed abort path is the whole point of the design: from the moment traffic flips until decommission, a single reverse router flip returns the system to the dual-write state with the source fully current, because the target has been replicating its writes back the entire time.

Step 1 — Define the Split and Provision the Target

Resharding starts in metadata, not in data. The shard map — whether it lives in a database table, a config service, or files consumed by your routers — must express the intended end state before anything moves. If your fleet routes through consistent hashing, the same principle holds; the mechanics of ring changes are covered in shard rebalancing & data movement.

-- Authoritative shard map (lives on a small metadata database)
CREATE TABLE shard_map (
  slot        SMALLINT PRIMARY KEY CHECK (slot BETWEEN 0 AND 127),
  shard_name  TEXT     NOT NULL,
  host        TEXT     NOT NULL,
  state       TEXT     NOT NULL DEFAULT 'active'
              CHECK (state IN ('active', 'migrating', 'draining')),
  version     BIGINT   NOT NULL
);

-- Mark the moving slots so routers and dual-write code can see the split
UPDATE shard_map
SET state = 'migrating', version = version + 1
WHERE slot BETWEEN 104 AND 111;

Provision shard 17 as an empty PostgreSQL instance with the identical schema (pg_dump --schema-only from the source, applied verbatim, including all indexes and constraints). Two details deserve care now, because they bite at cutover otherwise:

• Sequences. If any table uses bigserial or identity columns, set the target’s sequences far above the source’s current values (SELECT setval('orders_id_seq', (SELECT max(id) FROM orders) + 10000000) — run against source values). Otherwise the first insert after cutover collides with a replicated row.
• Replica identity. Logical replication row filters on UPDATE/DELETE can only reference columns in the replica identity. Ensure the routing column is covered: ALTER TABLE orders REPLICA IDENTITY FULL; is the blunt-but-safe option for the migration’s duration.

Step 2 — Enable Dual Writes

During migration, every write for the moving slots is applied to both shards. The source remains authoritative — the application commits there first and treats that commit as success — while the target write is best-effort and idempotent. Losing an occasional target write is fine: the replication stream in Step 3 repairs it, and Step 4 proves convergence.

import hashlib

SLOT_COUNT = 128
MIGRATING = range(104, 112)   # slots moving from shard_07 to shard_17

def slot_for(customer_id: str) -> int:
    digest = hashlib.md5(customer_id.encode()).digest()
    return int.from_bytes(digest[:2], "big") % SLOT_COUNT

def write_order(order, pools, shard_map):
    slot = slot_for(order.customer_id)
    source = pools[shard_map.owner(slot)]

    # 1. Authoritative write — failure here is a real failure
    with source.transaction() as tx:
        tx.execute(INSERT_ORDER_SQL, order.as_params())

    # 2. Shadow write — idempotent, non-blocking, short timeout
    if slot in MIGRATING:
        try:
            with pools["shard_17"].transaction(timeout_ms=200) as tx:
                tx.execute(INSERT_ORDER_SQL + " ON CONFLICT (order_id) DO NOTHING",
                           order.as_params())
        except Exception:
            metrics.increment("dualwrite.target_miss")  # replication will repair

ON CONFLICT DO NOTHING (or an equivalent upsert keyed on the primary key) is what makes dual writes coexist with the backfill: the same row may arrive via the application and via replication in either order, and both paths must tolerate the other having won. Roll the flag out gradually — 1%, 25%, 100% of migrating-slot traffic — while watching source-side write latency; the shadow write should add nothing to it, because it happens after the authoritative commit.

Dual writes exist for one reason: they guarantee the target never misses a write that happens after verification. Replication alone can achieve the same thing, but dual writes shrink the exposure window at cutover to zero and give you a live rehearsal of the target’s write path under production load.

Step 3 — Backfill with Logical Replication

With dual writes flowing, copy history. PostgreSQL 15+ makes this precise with row-filtered publications: only rows in the moving slots cross the wire.

-- On shard_07 (publisher). hashtext-based filter must match the app's slot math,
-- so in practice teams add a stored generated column `slot` and filter on it.
ALTER TABLE orders      ADD COLUMN IF NOT EXISTS slot SMALLINT
  GENERATED ALWAYS AS (abs(hashtext(customer_id)) % 128) STORED;
ALTER TABLE order_items ADD COLUMN IF NOT EXISTS slot SMALLINT
  GENERATED ALWAYS AS (abs(hashtext(customer_id)) % 128) STORED;

CREATE PUBLICATION pub_split_104_111
  FOR TABLE orders      WHERE (slot BETWEEN 104 AND 111),
      TABLE order_items WHERE (slot BETWEEN 104 AND 111);

-- On shard_17 (subscriber): copies existing rows, then streams changes
CREATE SUBSCRIPTION sub_split_104_111
  CONNECTION 'host=db-07.internal dbname=payments user=repl password=...'
  PUBLICATION pub_split_104_111
  WITH (copy_data = true, create_slot = true, streaming = on);

One caveat: the generated-column expression must produce the same slot as the application’s hash. If the app hashes with MD5 (as above) and the database with hashtext, they will disagree — pick one function and use it in both places, or populate slot from the application at write time instead of generating it.

Watch convergence from the publisher side and alert if the slot’s retained WAL grows without bound:

SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
         AS replication_lag_bytes
FROM pg_replication_slots
WHERE slot_name = 'sub_split_104_111';

Backfill of 900 GB at a throttled 80 MB/s takes around three hours; the stream then chews through the changes that accumulated meanwhile. You are ready for verification when lag holds near zero for a sustained period (say, 15 minutes) under normal traffic.

MySQL equivalent. MySQL has no row-filtered publications, so the standard split is coarser: clone the entire source shard with the CLONE plugin, attach the clone as a GTID replica (CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1), and let it catch up. Both machines temporarily hold all 16 slots’ data; the router flip in Step 5 assigns each machine its half, and decommission (Step 6) deletes the rows each side no longer owns. Frameworks like Vitess automate a true row-filtered version of this (VReplication), but plain MySQL plus a disciplined runbook gets to the same cutover safely.

Step 4 — Verify with Checksums and Count Reconciliation

Row counts are necessary but nowhere near sufficient — a count can match while an UPDATE was lost or applied twice. Verify with bucketed checksums over business-meaningful columns, comparing source and target bucket by bucket so a mismatch localizes itself:

-- Run identically on shard_07 and shard_17; diff the outputs
SELECT abs(hashtext(order_id::text)) % 64                          AS bucket,
       count(*)                                                    AS rows,
       md5(string_agg(order_id::text || ':' ||
                      status || ':' ||
                      amount_cents::text || ':' ||
                      updated_at::text, ','
                      ORDER BY order_id))                          AS checksum
FROM orders
WHERE slot BETWEEN 104 AND 111
GROUP BY 1
ORDER BY 1;

A small driver script runs both queries at the same wall-clock moment, retries mismatched buckets twice (in-flight replication legitimately causes transient differences), and only flags buckets that mismatch persistently. Persistent mismatches almost always trace to one of the failure modes in the table below — investigate before cutover, never after.

Include every replicated table, and reconcile aggregates that the business would notice — sum(amount_cents) per day for the last 90 days is a strong end-to-end signal. On MySQL, pt-table-checksum performs the same job using statement-based checksum injection through replication.

Step 5 — Cutover via Router Config Flip

Cutover is a metadata operation. Because dual writes mean the target is already receiving every live write, flipping reads and authoritative writes to shard 17 does not race the data plane — it only changes which commit the application waits for.

# router-config.yaml — version 43 (was 42)
version: 43
slot_count: 128
shards:
  shard_07: { host: db-07.internal, port: 5432, slots: "96-103" }
  shard_17: { host: db-17.internal, port: 5432, slots: "104-111" }
  # ... other shards unchanged

The runbook for the flip itself, top to bottom in under a minute:

1. Freeze deploys and confirm checksum verification passed within the last 15 minutes.
2. Arm reverse replication (Step 6’s publication from shard 17 back to shard 07) before the flip, so not a single post-cutover write is unprotected.
3. Publish version 43 to the config store. Routers poll or receive a push; each acknowledges the version it is serving.
4. Wait for unanimous acknowledgment. This is the critical gate: until every router instance reports version 43, some traffic still writes to shard 07 as authoritative. Dual writes make this overlap harmless, but only if you keep dual-write mode enabled until acknowledgment is unanimous.
5. Flip write authority in the application: for slots 104–111, shard 17 is now the transactional write and shard 07 becomes the shadow (dual writes now run in reverse — this is your rollback safety net at the application layer, redundant with reverse replication).
6. Watch for 15 minutes: target p99 latency, error rate on the moved slots, and dualwrite.target_miss (now counting misses against shard 07).

If anything degrades, publish version 42 again. Because both shards have both datasets and both receive all writes for the moving slots, flipping back is exactly as safe as flipping forward.

Step 6 — Rollback Window, Then Decommission

Immediately after cutover, shard 17 is authoritative but shard 07 must stay current — that is what makes rollback a config change instead of a data-recovery incident. Reverse the replication direction:

-- On shard_17 (new publisher)
CREATE PUBLICATION pub_return_104_111
  FOR TABLE orders      WHERE (slot BETWEEN 104 AND 111),
      TABLE order_items WHERE (slot BETWEEN 104 AND 111);

-- On shard_07: drop the old forward subscription, subscribe to the return feed
DROP SUBSCRIPTION sub_split_104_111;
CREATE SUBSCRIPTION sub_return_104_111
  CONNECTION 'host=db-17.internal dbname=payments user=repl password=...'
  PUBLICATION pub_return_104_111
  WITH (copy_data = false, create_slot = true);  -- history already present

Hold this state for a full business cycle — long enough that hourly jobs, nightly batch, and weekly reporting have all exercised the new topology (24–72 hours for most systems). Then close out deliberately:

-- 1. Tear down the return path
DROP SUBSCRIPTION sub_return_104_111;          -- on shard_07
DROP PUBLICATION  pub_return_104_111;          -- on shard_17

-- 2. Delete moved rows from the source in throttled batches
DELETE FROM orders
WHERE ctid IN (
  SELECT ctid FROM orders
  WHERE slot BETWEEN 104 AND 111
  LIMIT 20000
);
-- repeat until 0 rows, sleeping between batches; then the same for order_items
VACUUM (ANALYZE) orders;

Disable dual writes last, update shard_map.state back to active, and record the final config version in the migration log. A merge ends the same way, except the “source” being cleaned is the shard being retired, which you then remove from the map entirely.

Configuration Reference

Operational Contrast

Choose this workflow when data must change servers. Online table repartitioning solves a different problem with related tools: the data stays on one database instance and only its physical layout changes (an unpartitioned heap becomes a declaratively partitioned table). Repartitioning ends in a rename swap inside a single transaction, so it needs no router, no dual writes, and no cross-node verification — if you can fix your hot shard by pruning and archiving inside the box, that guide is the cheaper path.

Shard rebalancing & data movement generalizes this page: instead of one deliberate split or merge, rebalancing runs many small keyspace moves — often continuously and framework-driven — to smooth load across a fleet, especially after nodes join a consistent-hash ring. The per-move mechanics (copy, verify, flip, clean) are the ones described here; rebalancing adds scheduling, move sizing, and concurrency control on top. If your topology changes weekly, invest in rebalancing automation; if this is a once-a-quarter surgical event, this runbook is enough.

Failure Modes

Common Mistakes

• Cutting over on row counts alone. Counts match even when an UPDATE was lost or a row differs. Bucketed checksums over the columns the business cares about are the actual gate; counts are just the cheap first pass.
• Skipping the reverse replication step “because dual writes cover it”. Application-level dual writes die with a deploy or a crashed pod; database-level reverse replication does not. Run both during the rollback window — they protect against different failure domains.
• Leaving the replication slot behind after an aborted attempt. An orphaned slot silently pins WAL until the source’s disk fills, usually at 3 a.m. weeks later. Make “drop the slot” part of every abort path, and alert on inactive slots regardless.
• Treating the router flip as instantaneous everywhere. Config propagation is eventually consistent across router instances. Design the overlap to be safe (dual writes + unanimous-ack gating) rather than pretending it doesn’t exist.

FAQ

Related

• Shard Migration & Rebalancing Operations — parent overview of migration, rebalancing, and repartitioning workflows
• Splitting a Hot Shard with PostgreSQL Logical Replication — hands-on walkthrough of the split described here, command by command
• Online Table Repartitioning — restructuring a table’s partition layout inside one instance, no router involved
• Shard Rebalancing & Data Movement — continuous, fleet-wide keyspace moves built on these same per-move mechanics