How to branch a Postgres database for a pull request

Almost every team reviews schema migrations the same way: read the SQL, imagine what it does to a table with forty million rows, and approve it. Then the deploy takes a lock nobody predicted, or backfills a column for eleven minutes, or the migration is fine and the application code that depended on it was not.

The alternative is to stop imagining. Give each pull request a database that looks like production, run the migration against it, run the test suite against the result, and throw it away when the pull request closes. This is a walkthrough of doing that — the mechanics first, then the four things that will go wrong.

First, know which kind of branch you have

"Branch" means three different things across providers, and the one you have determines what workflow is possible.

• Copy-on-write clone — the storage is shared with the parent until you write, so creation is seconds regardless of database size, and the branch diverges as your migration modifies it. This is the version that makes per-pull-request workflows practical.
• Restore from backup — correct data, but creation takes as long as a restore. Fine for a nightly refresh of a shared staging environment, too slow to attach to a pull request.
• Fresh database plus migrations and seed data — not really a branch, works on every provider, and genuinely sufficient when your seed data is small. If this covers you, use it: it is the most portable option and it costs nothing.

The rest of this assumes the first kind. On PandaStack a branch is a copy-on-write reflink of the parent's durable volume, created on the parent's host, and it comes up warm by default: the child is restored from a memory snapshot of the running parent, so Postgres is already up with a hot buffer cache instead of cold-booting and crash-recovering. The parent is paused for the length of that snapshot window and then resumed.

The three calls you need

Branching is asynchronous: the call returns a new database id immediately with status `provisioning`, and you poll until it is `running`. That is not a wart, it is the honest interface — a VM is booting and Postgres is being reconfigured with its own credentials.

# 1. Branch. Returns 202 with a NEW database id. The parent is untouched
#    apart from a brief pause while the point-in-time image is taken.
#
#    warm defaults to true: the branch inherits the parent's hot cache.
#    Pass "warm": false for a cold disk-reflink branch, which pauses the
#    parent for less time but starts with an empty buffer cache.
BRANCH_ID=$(curl -sS -X POST \
  "https://api.pandastack.ai/v1/databases/$PARENT_DB_ID/branch" \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"label": "pr-482"}' \
  | jq -r '.id')

# 2. Poll until running. Bound the wait -- a hung branch should fail your
#    job, not hold a runner for an hour.
for i in $(seq 1 90); do
  STATUS=$(curl -sS \
    -H "Authorization: Bearer $PANDASTACK_API_KEY" \
    "https://api.pandastack.ai/v1/databases/$BRANCH_ID" | jq -r '.status')
  [ "$STATUS" = "running" ] && break
  [ "$STATUS" = "error" ] && { echo "branch failed"; exit 1; }
  sleep 2
done
[ "$STATUS" = "running" ] || { echo "branch timed out after 180s"; exit 1; }

# 3. Read the connection string OFF THE BRANCH. It has its own
#    credentials -- never reuse the parent's, that is the whole point.
export DATABASE_URL=$(curl -sS \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  "https://api.pandastack.ai/v1/databases/$BRANCH_ID" | jq -r '.connection_url')

The GitHub Actions workflow

Two jobs: one on pull-request open and synchronise that creates or reuses a branch and runs the suite, one on close that deletes it. The second job is the one people skip, and it is the one that decides whether this pattern is cheap or expensive.

name: pr-database
on:
  pull_request:
    types: [opened, synchronize, reopened, closed]

jobs:
  test-against-branch:
    if: github.event.action != 'closed'
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      # Deterministic label from the PR number. Re-running the workflow
      # must NOT create a second branch -- that is how you end up with
      # forty orphaned databases and a confusing invoice.
      - name: Create or reuse the branch
        id: db
        env:
          PANDASTACK_API_KEY: ${{ secrets.PANDASTACK_API_KEY }}
          PARENT_DB_ID: ${{ vars.STAGING_DB_ID }}
          LABEL: pr-${{ github.event.pull_request.number }}
        run: |
          set -euo pipefail
          API=https://api.pandastack.ai/v1/databases
          AUTH="Authorization: Bearer $PANDASTACK_API_KEY"

          EXISTING=$(curl -sS -H "$AUTH" "$API" \
            | jq -r --arg l "$LABEL" '.items[]? | select(.label==$l) | .id' \
            | head -1)

          if [ -n "$EXISTING" ]; then
            ID="$EXISTING"
          else
            ID=$(curl -sS -X POST "$API/$PARENT_DB_ID/branch" -H "$AUTH" \
              -H 'Content-Type: application/json' \
              -d "{\"label\":\"$LABEL\",\"size\":\"1g\"}" | jq -r '.id')
          fi

          for i in $(seq 1 90); do
            S=$(curl -sS -H "$AUTH" "$API/$ID" | jq -r '.status')
            [ "$S" = "running" ] && break
            [ "$S" = "error" ] && { echo "::error::branch failed"; exit 1; }
            sleep 2
          done
          [ "$S" = "running" ] || { echo "::error::branch timed out"; exit 1; }

          URL=$(curl -sS -H "$AUTH" "$API/$ID" | jq -r '.connection_url')
          echo "id=$ID" >> "$GITHUB_OUTPUT"
          # Mask it: connection strings contain a password and CI logs
          # are forever.
          echo "::add-mask::$URL"
          echo "url=$URL" >> "$GITHUB_OUTPUT"

      - uses: actions/setup-node@v4
        with: { node-version: 22, cache: npm }
      - run: npm ci

      # THE POINT OF ALL THIS: the migration runs against real data
      # volumes, so a lock or a slow backfill shows up here, in review,
      # instead of during a deploy.
      - name: Run migrations
        env:
          DATABASE_URL: ${{ steps.db.outputs.url }}
          DIRECT_URL: ${{ steps.db.outputs.url }}
        run: |
          /usr/bin/time -f 'migration wall clock: %e seconds' \
            npx prisma migrate deploy

      - name: Integration tests
        env:
          DATABASE_URL: ${{ steps.db.outputs.url }}
        run: npm run test:integration

  cleanup:
    if: github.event.action == 'closed'
    runs-on: ubuntu-latest
    steps:
      - name: Delete the branch
        env:
          PANDASTACK_API_KEY: ${{ secrets.PANDASTACK_API_KEY }}
          LABEL: pr-${{ github.event.pull_request.number }}
        run: |
          set -euo pipefail
          API=https://api.pandastack.ai/v1/databases
          AUTH="Authorization: Bearer $PANDASTACK_API_KEY"
          ID=$(curl -sS -H "$AUTH" "$API" \
            | jq -r --arg l "$LABEL" '.items[]? | select(.label==$l) | .id' \
            | head -1)
          [ -n "$ID" ] && curl -sS -X DELETE -H "$AUTH" "$API/$ID" || true

Four things that will go wrong

1. Orphaned branches

A pull request closed without the cleanup job running — because the job failed, or because someone merged from the API, or because the workflow file changed — leaves a database running forever. Two defences, and you want both. Make the label deterministic so re-runs reuse rather than duplicate, and add a scheduled job that lists branches, checks each label against the open pull requests, and deletes the ones with no matching PR.

2. You just cloned production data into CI

A copy-on-write branch of production is production data, with all of the obligations that implies — and now it is reachable from a CI runner, with a connection string that passed through workflow logs. If your parent database holds personal data, branching it into a pull-request environment is a data-protection decision, not a convenience.

The clean pattern is to branch from a sanitised parent rather than from production. Maintain one staging database, refreshed on a schedule from a production restore with a masking step applied — emails rewritten, payment identifiers replaced, free-text fields scrubbed — and branch pull requests from that. You keep the realistic data volumes and index statistics that make the exercise worthwhile, and you stop shipping customer data to CI.

3. Migrations need an unpooled connection

If there is a transaction pooler between your migration runner and the database, migrations will misbehave in confusing ways: Prisma's advisory lock is taken on one backend and looked for on another, so `prisma migrate deploy` hangs rather than failing. Point migrations at the database directly and reserve the pooler for runtime queries. On a fresh branch there is usually no pooler at all, which makes this easy — just do not copy a pooled URL pattern over from production out of habit.

4. The test passed and the migration is still unsafe

A branch tells you the migration completes and the suite passes. It does not tell you the migration is safe to run against a live production database, because the branch has no concurrent traffic. A `ALTER TABLE` that takes an `ACCESS EXCLUSIVE` lock finishes instantly on an idle branch and blocks every read on a busy primary.

So use the branch for what it is good at — does it complete, how long does it take on real data volumes, does the application still work — and review the lock behaviour separately. The habits that matter are the usual ones: add columns nullable and backfill in batches, create indexes concurrently, split a rename into add-write-both-drop, and set a `lock_timeout` so a migration that cannot get its lock quickly fails instead of queueing behind itself.

-- What a branch cannot tell you: lock contention. Two habits that make
-- migrations safe on a busy primary, independent of any test.

-- 1. Never wait indefinitely for a lock. Without this, a migration that
--    cannot get ACCESS EXCLUSIVE queues -- and every query behind it
--    queues too, which is how one ALTER TABLE takes down a service.
SET lock_timeout = '3s';
SET statement_timeout = '5min';

-- 2. Add the column nullable, backfill in batches, add the constraint
--    afterwards. The one-liner version rewrites the whole table under a
--    lock; this version never holds one for long.
ALTER TABLE orders ADD COLUMN region text;           -- instant, no rewrite

-- ... backfill in a loop, committing every batch, outside the migration:
--   UPDATE orders SET region = 'unknown'
--   WHERE region IS NULL AND id IN (
--     SELECT id FROM orders WHERE region IS NULL LIMIT 5000
--   );

-- 3. Then validate separately, which takes a weaker lock than adding a
--    validated constraint in one step.
ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

-- Indexes: always CONCURRENTLY on a live table. It cannot run inside a
-- transaction, so most migration tools need an explicit escape hatch.
CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

What this costs, roughly

The economics work because pull-request databases are idle almost all the time. A branch is busy for the two minutes your test suite runs and asleep for the rest of the day, and it shares storage with its parent until it writes. On PandaStack that means compute metered per second while awake, storage only while suspended, and a copy-on-write volume that costs the size of your divergence rather than the size of your database.

The failure mode is not the running branches, it is the forgotten ones. Ten active pull requests each running a suite for two minutes a day is nothing. Ten branches from last quarter that nobody deleted is a line item. Which is the whole argument for writing the reaper first.

The short version

1. Maintain one sanitised parent database, refreshed from production on a schedule with a masking step. Branch pull requests from that, never from production directly.
2. Branch on pull-request open with a deterministic label derived from the PR number, so re-runs reuse the branch instead of creating another.
3. Poll for `running`, with a bounded timeout that fails the job rather than holding a runner.
4. Read the connection string off the branch, mask it in the log, and point both your migration runner and your tests at it.
5. Time the migration. That number, against real data volumes, is the thing you could not get any other way.
6. Delete the branch on pull-request close — and run a scheduled reaper anyway, because the cleanup job will not always fire.