Migrating a Tenant Between List Partitions

A tenant outgrows the shared pool, or shrinks back into it. Either way the row set has to move between two list partitions of the same table while the application keeps writing. This guide does it in batches, cuts over through the tenant directory rather than a deploy, and verifies that nothing was written into the gap. It builds on the tiered layout from List Partitioning Techniques inside Partitioning Implementation Patterns & Routing.

Prerequisites

Step 1 — Create the destination partition empty

-- promote tenant 8842 out of the shared pool
CREATE TABLE events_bigco PARTITION OF events FOR VALUES IN (8842);

This fails if the tenant’s rows already live in a partition covered by another list — which is exactly the case here, because they are in the pool. So create the destination as a standalone table first and attach it after the data moves:

CREATE TABLE events_bigco (LIKE events INCLUDING ALL);
ALTER TABLE events_bigco
  ADD CONSTRAINT events_bigco_tenant CHECK (tenant_id = 8842) NOT VALID;

Operational note: INCLUDING ALL copies indexes, defaults and constraints, which is what makes the later attach cheap and the copy correct.

DBA tip: Add the CHECK constraint now and validate it after the copy. A validated constraint that implies the list bound turns ATTACH into a catalog operation rather than a full scan.

Where writes go during each phase of the move During the batched copy and the catch-up pass, writes for the tenant continue to the shared pool and routing is unchanged. The cutover pauses writes for that tenant, runs a final catch-up, swaps the directory row and attaches the new partition. After it, writes go to the dedicated partition and the tenant's rows are deleted from the pool in throttled batches. 1 · create targetempty, constraint NOT VALID 2 · batched copykeyset paged, throttled 3 · cutover< 2 s, one tenant 4 · soaksource kept 5 · purgedelete from pool writes → events_pool (routing unchanged) writes → events_bigco Only phase 3 is disruptive, and only for this tenant. Everything before it is abandonable by dropping the target table; everything after it is reversible by running the same procedure in the opposite direction. Keep the source rows through the soak period. Deleting them immediately makes a rollback a restore rather than a copy-back.

Step 2 — Copy in throttled batches with a watermark

# move_tenant.py — keyset paged, resumable, throttled by measured lag
BATCH = 10_000

def copy_batch(conn, tenant_id, after_id):
    rows = conn.query("""
        INSERT INTO events_bigco
        SELECT * FROM events_pool
        WHERE  tenant_id = %s AND id > %s
        ORDER  BY id
        LIMIT  %s
        RETURNING id
    """, (tenant_id, after_id, BATCH))
    return rows[-1].id if rows else None

def run(conn, tenant_id):
    watermark = 0
    while True:
        last = copy_batch(conn, tenant_id, watermark)
        if last is None:
            return watermark
        watermark = last
        record_watermark(conn, tenant_id, watermark)
        throttle(conn)          # sleep proportional to replica lag and source p99
The watermark advances; new rows land behind it The copy walks the tenant's rows in id order, recording a watermark after each batch. Rows written during the copy have higher ids and therefore fall after the watermark, so they are picked up by later batches or by the final catch-up. This is why the copy converges instead of chasing a moving target forever. copied — batches of 10,000, id ascending not yet copied, plus rows arriving now watermark: id 41,882,014 these rows are already on the target these will be copied by later batches or the final catch-up Because ids increase monotonically, a row written during the copy is always ahead of the watermark. An update to an already-copied row is not, which is why the verification compares checksums rather than only counts. Persist the watermark after every batch: it is what makes an interrupted copy resumable instead of restartable.

Operational note: Recording the watermark after every batch is what makes the copy resumable. A copy that restarts from zero after an interruption will re-insert rows and fail on the primary key, which is recoverable but wastes the whole run.

DBA tip: ORDER BY id LIMIT n with id > watermark is keyset pagination — its cost is constant per batch. OFFSET would make each batch slower than the last.

Step 3 — Cut over through the directory

The cutover is short because everything expensive already happened:

BEGIN;
  SET LOCAL lock_timeout = '2s';

  -- 1. pause writes for this tenant only
  UPDATE tenant_directory SET write_paused = true WHERE tenant_id = 8842;

  -- 2. final catch-up: rows written since the last watermark
  INSERT INTO events_bigco
  SELECT * FROM events_pool
  WHERE  tenant_id = 8842 AND id > (SELECT watermark FROM tenant_move WHERE tenant_id = 8842)
  ON CONFLICT DO NOTHING;

  -- 3. make the constraint trustworthy, then attach
  ALTER TABLE events_bigco VALIDATE CONSTRAINT events_bigco_tenant;
  ALTER TABLE events ATTACH PARTITION events_bigco FOR VALUES IN (8842);

  -- 4. repoint routing and resume
  UPDATE tenant_directory
     SET partition_name = 'events_bigco', write_paused = false, version = version + 1
   WHERE tenant_id = 8842;
COMMIT;

Operational note: The pause is a flag the application honours, not a database lock. Requests for that tenant return a retryable error for a second or two; every other tenant is unaffected.

SRE tip: lock_timeout on this transaction matters. If the ATTACH cannot get its lock quickly, failing and retrying is far better than queueing behind a long-running query while writes are paused.

Step 4 — Verify before purging the source

-- row counts and an order-independent checksum, per side
SELECT 'pool' AS side, count(*), sum(hashtext(t::text)::bigint) AS checksum
FROM   events_pool t WHERE tenant_id = 8842
UNION ALL
SELECT 'bigco', count(*), sum(hashtext(t::text)::bigint)
FROM   events_bigco t;
 side  |  count   |      checksum
-------+----------+---------------------
 pool  | 41882014 |  -2210448891120038
 bigco | 41882014 |  -2210448891120038
What the two-sided check catches that a row count alone does not Matching row counts with matching checksums proves the copy is complete and identical. Matching counts with differing checksums means a row was modified after being copied. A higher count on the source means rows arrived after the final catch-up, which points at a write path that bypassed the pause flag. counts =, checksums = counts =, checksums ≠ source count higher the copy is complete and byte-identical → safe to purge the source after the soak period a row changed after it was copied → re-run the catch-up; check for an UPDATE path rows arrived after the final catch-up → a writer ignored the pause flag; find it first The third case is the important one. It means some code path writes without consulting the directory — a background job, an admin script, or a service that caches routing indefinitely. Purging the source before finding it loses those rows. Run the comparison again at the end of the soak period, not only immediately after cutover.

Operational note: Purge the source in throttled batches, exactly like the copy. A single DELETE FROM events_pool WHERE tenant_id = 8842 on 42 million rows is the bloat event the whole procedure was designed to avoid.

DBA tip: After the purge, VACUUM (ANALYZE) the pool partition. Its statistics now describe a very different row population, and stale statistics after a large delete produce bad plans for every other tenant in the pool.

Verification

Confirm routing, pruning and the physical location all agree:

SELECT tableoid::regclass AS lives_in, count(*)
FROM   events WHERE tenant_id = 8842 GROUP BY 1;
   lives_in   |  count
--------------+----------
 events_bigco | 41882014

EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE tenant_id = 8842;

One partition in the plan, the directory version incremented, and the application’s own health check reporting the new mapping for that tenant.

Failure mode table

Failure mode Root cause SRE mitigation
Rows written during the move are lost a writer bypassed the directory and wrote to the pool after the final catch-up verify counts on both sides before purging; audit for write paths that do not consult the directory, and make the pool’s tenant check reject them
ATTACH takes hours the CHECK constraint was never validated, so PostgreSQL scanned the whole table add the constraint at creation and VALIDATE it before the cutover transaction
Purge causes a latency spike for other tenants the source rows were deleted in one statement, bloating the shared pool partition delete in throttled batches and vacuum afterwards; schedule the purge outside peak hours

FAQ

Why not just UPDATE the partition key?

An UPDATE that changes the partition key is executed as a delete plus an insert across partitions, holding locks on both and generating WAL for every row twice. On a tenant with tens of millions of rows that is hours of work in one statement. A batched copy with a directory-driven cutover does the same job in controllable pieces with a short lock at the end.

How are writes handled during the copy?

They keep going to the old partition, because routing still reads the directory and the directory still points there. The copy uses a watermark so rows written during it are picked up by a catch-up pass, and the final cutover happens inside a short transaction that pauses writes for that tenant only.

Can the tenant be moved back?

Yes — demotion is the same procedure with source and target swapped, and it is worth rehearsing because it is the rollback path. Keep the source partition for a soak period after promotion so a demotion is a copy-back rather than a restore.

Back to List Partitioning Techniques