Rehearse Your Data Migration on a Real Copy, Not a Staging Guess

The shape of this incident is so consistent it may as well be a template. A big migration gets written: a Rails 6 to 7 schema change, a Mongo-to-Postgres cutover, a 400 million row backfill, a Postgres major version upgrade. It is tested against staging, where it completes in eight minutes and everyone signs off. It is scheduled for a Saturday. At 01:00 it starts. At 03:00 it is still running, holding a lock the ORM's changelog never mentioned, and someone is reading the Postgres documentation on NOT VALID constraints for the first time in their life, on a phone, in the dark.

The staging run was not a test. It was a syntax check with a stopwatch attached. Staging had twelve thousand rows seeded in 2023 by a script that generates the rows the author imagined, in the shapes the author expected. Production has four years of accumulated reality, and every property that makes a migration slow is a property of the real data, not of the SQL.

I'm Ajay; I build PandaStack, a Firecracker microVM platform, and migration rehearsal keeps showing up in my inbox as an isolation problem wearing a database costume. Teams know they should rehearse on real data. They do not, because the only two venues available are production (obviously not) and a shared staging database that four other people are also using this afternoon. This post is about a third venue: a real, isolated, disposable copy of production that you create for one rehearsal, measure, and destroy. And then repeat until the numbers stop being interesting.

What staging structurally cannot reproduce

This is not a list of things your staging environment happens to be missing today. It is a list of things a seeded environment cannot have, by construction, because they are artifacts of time and traffic rather than of schema.

• Volume. The obvious one, and still underrated: migration cost is rarely linear in row count. Index rebuilds, WAL generation and constraint validation all have their own curves, and they cross each other at sizes you will not hit with fixtures.
• Cardinality skew. Production has one tenant holding 60% of the rows and forty thousand holding six each. Seeded data is uniform, so the batched backfill that looks smooth on staging spends four hours on a single customer's partition.
• Dead tuples and bloat. A table that has been UPDATE-heavy for three years is physically much larger than its live row count suggests. A full table rewrite reads the bloat too. Your freshly loaded staging table has none.
• NULL patterns from older code. Rows written before three migrations ago have nulls and defaults the current model has never emitted. Your NOT NULL backfill discovers them at row 38 million, having already burned two hours.
• Encoding junk and length outliers. The name with a zero-width joiner, the text column with a 4 MB blob in it because someone pasted a stack trace into a support field in 2024. Type changes and re-encodings find these; fixtures do not contain them.
• Orphaned foreign keys. Rows whose parent was deleted before the constraint existed, or during a window when it was NOT VALID. Adding the constraint for real means a full validation scan that fails on the first orphan, after scanning everything before it.
• Index size versus shared_buffers. On staging the whole working set fits in RAM and every page is a memory read. On production the index you are rebuilding is larger than shared_buffers and every page is disk I/O. That single difference is often the whole 8-minutes-to-6-hours gap.

The rehearsal loop

A rehearsal is a loop, not an event. The output is not "it worked" — it is a number, and then a smaller number, and then a number that is boring. Ordered, because the order matters:

1. Branch the data. Clone or point-in-time-restore production into a new, separate database with its own id and its own connection string. Never rehearse against something anyone else is connected to.
2. Record the starting facts. Row counts per affected table, a checksum over the columns you are about to touch, the size on disk of the relevant tables and indexes. You cannot assert correctness later without these.
3. Start the clock and start a lock sampler. Two connections: one runs the migration, one samples pg_locks and pg_stat_activity every quarter second. Wall clock alone will not tell you which minutes were dangerous.
4. Run the migration exactly as production will run it. Same tool, same transaction wrapping, same lock_timeout, same batch sizes. A rehearsal that runs raw SQL when production runs it through Prisma or Alembic is rehearsing a different program.
5. Assert. Row counts match expectations, checksums over untouched columns are unchanged, the new column has no unexpected nulls, and the constraint you added actually validated rather than sitting NOT VALID forever.
6. Rehearse the rollback on the same branch, immediately, before you have forgotten what state it was in.
7. Destroy the branch. Every clone that survives its rehearsal becomes a stale copy of production data with a weaker access story than the original.
8. Change one thing and go again. Batch size, index strategy, whether the backfill is one statement or ten thousand. Each iteration is cheap because step one is an API call rather than a two-hour restore.

The value is in the repetition. A single rehearsal tells you a duration. Five rehearsals with one variable changed each time tell you which knob actually controls it, which is the thing you need at 01:00 on Saturday when the number is worse than expected and someone asks whether a smaller batch would help.

Why the rehearsal needs its own machine, not just its own database

This is the part teams skip and it invalidates everything downstream. If your rehearsal database shares a host with production — same kernel, same page cache, same block device queue — then two things happen at once and both of them are bad.

First, your measurement is a lie in the optimistic direction. Your rehearsal is reading pages that production already warmed into the page cache, so the sequential scan that would cost you disk I/O costs you a memcpy. You measure 40 minutes, production takes three hours, and you attribute the gap to bad luck.

Second, and worse, your measurement is a lie in the other direction for production. A full table rewrite is an I/O firehose. Run it next to production and you evict production's hot pages, saturate the device queue, and push real user latency up while you are supposedly doing something safe. I have watched a team convince themselves their p99 regression was a code deploy when it was their colleague rehearsing a backfill on the same box. Both sets of numbers were fiction, and they were fiction about each other.

So the rehearsal wants a hard boundary: its own kernel, its own page cache, its own I/O accounting. That is the argument for doing this in a microVM rather than a container or a second schema on the same server. A container shares the host kernel and the host page cache — it is a polite suggestion about resource accounting, and the page cache does not read your cgroup limits and feel guilty. A microVM has its own guest kernel and its own memory, so a rehearsal that thrashes its disk thrashes only its own.

The practical shape on PandaStack is that a managed Postgres is a dedicated Firecracker VM with a durable volume, and a clone is a separate database with its own id, its own VM and its own connection URL. The source is never touched. Creating one lands in the 30 to 90 second range, because the API waits for Postgres to genuinely accept connections rather than returning a hopeful "provisioning" status. The sandbox you run the migration from is a different, cheaper animal — a snapshot restore at p50 179ms — so the environment around the rehearsal is free and the data is the only thing you wait for.

Branching the database and running the migration against it

Two API calls and a wait. The clone reads from the source database's archive rather than the live instance, so cloning does not put load on production, and passing a timestamp gives you the state as of that moment — useful when you want to rehearse against last Tuesday, before someone deleted the rows that made it interesting.

# 1. Branch production into a throwaway database of its own.
NEW=$(curl -sS -X POST https://api.pandastack.ai/v1/databases/$PROD_DB_ID/clone \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"label": "rehearse-0042-batch5k"}' | jq -r .id)

# Or rehearse against a specific moment -- point-in-time restore into a
# NEW database id. target_time must be at least a couple of minutes ago.
#   -d '{"label":"rehearse-0042","target_time":"2026-08-29T02:00:00Z"}'

# 2. Wait for it. 30-90s is the honest range: the API blocks until
#    Postgres actually accepts a connection, not until a row is inserted.
until [ "$(curl -sS -H "Authorization: Bearer $PANDASTACK_API_KEY" \
      https://api.pandastack.ai/v1/databases/$NEW | jq -r .status)" = "running" ]; do
  sleep 5
done

CLONE_URL=$(curl -sS -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  https://api.pandastack.ai/v1/databases/$NEW | jq -r .connection_url)

# 3. ... rehearse ... 4. and then, unconditionally:
# curl -sS -X DELETE .../v1/databases/$NEW -H "Authorization: ..."

Run the migration from a sandbox rather than from your laptop. Not for purity: because your laptop's connection is going to drop somewhere around minute ninety, and because the sandbox is where your migration tool, its exact version and its exact config already live. The base template is 4 GiB and 8 vCPU, which is enough to run a migration tool and a lock sampler side by side without either of them being the bottleneck.

import json, time, requests
from pandastack import Sandbox

API = "https://api.pandastack.ai"
H = {"Authorization": f"Bearer {TOKEN}", "Content-Type": "application/json"}

def branch(source_id, label, target_time=None):
    body = {"label": label}
    if target_time:
        body["target_time"] = target_time
    db_id = requests.post(f"{API}/v1/databases/{source_id}/clone",
                          headers=H, json=body).json()["id"]
    # 30-90s: the API blocks until Postgres accepts connections.
    while True:
        db = requests.get(f"{API}/v1/databases/{db_id}", headers=H).json()
        if db["status"] == "running":
            return db_id, db["connection_url"]
        if db["status"] == "error":
            raise RuntimeError(f"clone failed: {db}")
        time.sleep(5)

def rehearse(source_id, migration_sql, budget_seconds):
    db_id, url = branch(source_id, "rehearse-0042")
    sbx = Sandbox.create(template="base", ttl_seconds=3600)
    try:
        sbx.filesystem.write("/work/migration.sql", migration_sql)

        before = sbx.exec(f"psql '{url}' -tAc "
                          f"\"select count(*), sum(hashtext(status::text)) from orders\"")

        # Same wrapping production uses. lock_timeout so a rehearsal that
        # cannot get its lock fails in 5s instead of parking for an hour.
        t0 = time.monotonic()
        run = sbx.exec(
            f"psql '{url}' -v ON_ERROR_STOP=1 "
            f"-c \"set lock_timeout='5s'\" -f /work/migration.sql")
        elapsed = time.monotonic() - t0

        after = sbx.exec(f"psql '{url}' -tAc "
                         f"\"select count(*), sum(hashtext(status::text)) from orders\"")

        assert run.exit_code == 0, run.stderr
        assert before.stdout.split("|")[0] == after.stdout.split("|")[0], \
            "row count changed -- this migration was not supposed to do that"

        print(json.dumps({"seconds": round(elapsed, 1),
                          "budget": budget_seconds,
                          "ok": elapsed < budget_seconds}))
        return elapsed < budget_seconds
    finally:
        sbx.kill()
        requests.delete(f"{API}/v1/databases/{db_id}", headers=H)

Measure the lock, not just the wall clock

Total duration is the number people report and it is the less important one. A migration that takes two hours while holding nothing worse than a SHARE UPDATE EXCLUSIVE is a long afternoon. A migration that takes ninety seconds while holding ACCESS EXCLUSIVE on your busiest table is an outage. You need to know which minutes were which, and that means sampling from a second connection while the first one works.

-- Session B. Run in a loop (\watch 0.25) for the whole rehearsal and
-- keep the output. The interesting rows are granted = false: something
-- is queueing, and everything arriving after it queues too.
SELECT
  clock_timestamp()                AS at,
  a.pid,
  l.mode,
  l.granted,
  a.wait_event_type,
  a.wait_event,
  clock_timestamp() - a.xact_start AS xact_age,
  left(regexp_replace(a.query, '\s+', ' ', 'g'), 60) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted, a.xact_start;

-- The summary you actually report: how long the strongest lock was held.
-- ACCESS EXCLUSIVE blocks reads. SHARE UPDATE EXCLUSIVE does not.
-- If your rehearsal never shows ACCESS EXCLUSIVE, say so explicitly --
-- 'we did not observe it' is a finding, not an omission.

Set lock_timeout in the rehearsal even though nothing else is connected. Not because it will fire — it almost certainly will not on an idle clone — but because production will run with it set, and a migration whose statements are not individually short enough to survive a five second lock_timeout is a migration you have not finished designing yet. Discovering that on the clone is the entire point.

Rehearse the rollback too, because yours is fiction

Almost every team rehearses forward. Almost no team rehearses backward, which is unfortunate, because the down migration is the least-tested code in the repository and it is the code you will need while your judgement is at its worst.

The down migration is usually fiction in one of four specific ways, and all four are visible on a clone in about ninety seconds:

• It was auto-generated and never read. The framework wrote a drop_column that mirrors the add_column, and nobody checked whether the data it drops can be reconstructed. It cannot.
• It is slower than the forward migration. A backfill that took four hours takes four hours to undo, which means your rollback plan is not a rollback plan, it is a second outage.
• It takes a stronger lock than the forward one. Dropping a column is metadata-only in Postgres, but reversing a type change is a full rewrite under ACCESS EXCLUSIVE while your incident is ongoing.
• It assumes an empty new column. Fine when you roll back thirty seconds after deploying, wrong when you roll back on Monday after the new code has written two days of rows into it.

Rehearse the rollback on the same clone, straight after the forward run, and assert that you got back to the checksums you recorded in step two. If you cannot get back, the honest conclusion is that this migration is one-way and the recovery plan is restore-from-backup, not migrate-down. That is a completely acceptable answer. It is only dangerous when nobody has written it down, and the runbook still says "roll back if there are problems" as though that were a thing that would work.

What a clone genuinely cannot give you

Here is the part that gets left out of posts like this one. A clone reproduces production's data. It does not reproduce production's traffic, and traffic is half of why migrations go wrong.

On an idle clone, every lock is granted instantly. Nothing is holding a long-running SELECT for your ALTER TABLE to queue behind, nothing is competing for autovacuum, nothing is generating WAL alongside your backfill, and no connection pool is filling up while your migration parks a lock. So the specific catastrophe of a migration waiting for its lock and blocking every query that arrives behind it — the thing that turns a slow migration into a full table outage before it has done any work — will not appear in a clean rehearsal. Ever. Your clone will happily report that the dangerous version of the migration is fine.

If lock contention is your risk, you have to manufacture the contention. Run pgbench or a replay of your own query shapes against the clone at roughly production's write rate, in a second sandbox, while the migration runs. It is a synthetic approximation and you should describe it as one — the shape of your real traffic has diurnal patterns and hot keys that a uniform load generator does not — but a rehearsal with imperfect load tells you far more than a rehearsal with none. Two things to check with load running that are meaningless without it: how deep the lock queue got, and whether your connection pool exhausted.

The other honest limit: a clone from an archive is not bit-identical to the live primary. Cache state is cold, planner statistics may need an ANALYZE, and if you cloned to a point in time you are missing everything after it. None of these invalidate the timing work — a cold cache makes your rehearsal pessimistic, which is the direction you want to be wrong in — but do run ANALYZE before you draw conclusions about query plans specifically.

Then put it in CI, on every migration PR

Manual rehearsal happens for the migration everyone is scared of. The migration that takes you down is the one nobody was scared of, which is why this belongs in automation rather than in judgement.

The check is small: when a pull request touches the migrations directory, branch production, run the new migrations against the branch with timing and lock sampling, fail the build if the duration exceeds a budget or if an ACCESS EXCLUSIVE lock was held for longer than a threshold, post the numbers as a PR comment, and destroy the branch. Because the environment is created and destroyed per run, there is no standing box slowly accumulating a copy of production.

#!/usr/bin/env bash
# ci/rehearse-migrations.sh -- runs when migrations/ changes.
set -euo pipefail

BUDGET_SECONDS=${BUDGET_SECONDS:-120}
NEW=""
cleanup() {
  [ -n "$NEW" ] && curl -sS -X DELETE "$API/v1/databases/$NEW" \
    -H "Authorization: Bearer $PANDASTACK_API_KEY" >/dev/null || true
}
trap cleanup EXIT   # runs on failure too. This is the important line.

NEW=$(curl -sS -X POST "$API/v1/databases/$PROD_DB_ID/clone" \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" -H 'Content-Type: application/json' \
  -d "{\"label\": \"ci-pr-$PR_NUMBER\"}" | jq -r .id)

# wait_for_running is the poll loop from earlier.
CLONE_URL=$(wait_for_running "$NEW")

psql "$CLONE_URL" -qc 'ANALYZE'            # cold clone: refresh statistics
START=$(date +%s)
psql "$CLONE_URL" -v ON_ERROR_STOP=1 -c "set lock_timeout='5s'" \
  -f migrations/pending.sql
ELAPSED=$(( $(date +%s) - START ))

echo "migration: ${ELAPSED}s against production-shaped data (budget ${BUDGET_SECONDS}s)"
[ "$ELAPSED" -lt "$BUDGET_SECONDS" ] || {
  echo "over budget -- needs CONCURRENTLY, batching, or a NOT VALID + VALIDATE split"
  exit 1
}

# And the half everyone skips:
psql "$CLONE_URL" -v ON_ERROR_STOP=1 -f migrations/pending.down.sql
echo "rollback rehearsed clean"

Track the timings as a series rather than as pass/fail. A migration creeping from 20 seconds to 90 seconds over six months, against a table that is growing, is the signal you actually want — and it is completely invisible if you only look at the exit code.

A migration plan without a measured duration is not a plan. It is a hope with SQL attached.

The short version

• Staging cannot reproduce volume, skew, bloat, historical nulls, encoding junk, orphaned FKs, or an index that no longer fits in shared_buffers. Those are what make migrations slow.
• Rehearse on a real branch of production: clone or point-in-time restore into a new database with its own id and connection string, and leave the source untouched.
• Give the rehearsal its own machine. Sharing a host with production corrupts your numbers and production's latency simultaneously, and you will misattribute both.
• Measure lock mode and lock duration from a second connection, not just wall clock. Two hours under a weak lock beats ninety seconds under ACCESS EXCLUSIVE.
• Rehearse the rollback on the same branch and assert your checksums come back. If they cannot, write down that the migration is one-way rather than pretending otherwise.
• Be honest that an idle clone hides lock contention. Add synthetic load if that is your risk, and say it is synthetic.
• Destroy the branch in the same automation that made it, in a step that runs on failure.
• Then automate the whole loop on every migration PR, and let it fail the build when the number gets worse.

None of this is free. You are maintaining a rehearsal harness, a set of assertions that has to move when the schema moves, and a load generator that is an approximation of your traffic. What you buy is that the sentence changes. Instead of "it should be quick, it was fast in staging," you get "it took 94 seconds against a copy of last night's production, held ACCESS EXCLUSIVE for 1.2 of them, and the rollback took 40." One of those is a plan. The other is the reason someone is awake at 3am reading about NOT VALID.