Spinning up a preview deploy per pull request is the easy half — every modern host does it with a config flag. The hard half is the database: a shared staging database turns previews into a queue of people breaking each other's data, and a restored production snapshot puts real customer records in an environment that anyone with the PR link can reach. The pattern that survives is an ephemeral database per PR, created from schema plus a small deterministic seed, with production data used only after it has been scrubbed on a machine that is allowed to see it.
What actually breaks in a preview environment?
The deploy almost never fails. What fails looks like this:
Everything points at one staging database. Two PRs land migrations in different orders and the third developer gets ERROR: relation "order_line_items" does not exist from a branch that never ran the migration adding it — or someone's test loop truncates a table another person was demoing from.
Connection exhaustion. Each preview app opens its own pool. Ten open PRs at ten connections each, against a small instance with a 100-connection ceiling, gives you FATAL: sorry, too many clients already in whichever environment connected last — usually the one the reviewer just opened. Previews multiply idle connections rather than traffic, which is why they surface pooling problems first.
Migrations that only run forward. Preview infrastructure will create a database for you; it will not un-apply your migration when you force-push a rewritten one. If your runner tracks applied versions in the database, recreate the environment rather than reusing it.
Seed scripts that assume an empty database. Run them twice on a reused environment and you get duplicate key value violates unique constraint "users_email_key". Write every seed as an upsert (INSERT ... ON CONFLICT DO NOTHING) and you stop caring which state you started from.
Secrets. Preview builds inherit project-level environment variables by default on most hosts — that is how a preview ends up holding a live payment key. Give the preview scope its own secret set, test-mode credentials only.
The takeaway: a preview environment is only as isolated as its database and its secrets, and both default to shared.
Where should the preview database come from?
Four options, and they differ mostly in how fast they are and how much real data they expose.
| Approach | Setup cost | Time to fresh env | Data realism | Main risk |
|---|---|---|---|---|
| Shared staging DB | None | Instant | Medium | Cross-PR interference, migration drift |
| Ephemeral Postgres container + seed | Low | Seconds | Low (what you seed) | Seed rot: diverges from real schema use |
| Branch on managed Postgres (copy-on-write) | Low | Seconds | High (mirrors parent) | Real data in a low-trust environment |
| Restore of a scrubbed prod snapshot | High | Minutes | High | Scrub pipeline must be airtight |
For most small teams the ephemeral container plus a seed file is the correct default, and it is free. You reach for branching when the bugs you keep shipping are data-shaped — queries that are fast on 200 seeded rows and hopeless on the real distribution.
If you want copy-on-write branches without building them, Neon is the managed Postgres whose branching is the product itself: a branch is a cheap pointer at the parent's storage, so a per-PR database costs you the diff rather than a full copy. Supabase offers git-linked preview branches if your app already lives on its stack, with the tradeoff that the branch is a whole project and carries that startup latency. As of mid-2026, treat either as a pre-production surface no matter how convenient it is — a branch of production is production data.
The takeaway: pick branching for data realism, containers for isolation and cost, and never a shared database for either.
How do you seed data without copying production?
Build the seed from three layers, in this order.
1. Schema from your migration runner, never from a dump. The whole point is to exercise the migrations the PR contains.
2. A committed deterministic seed. Fixed UUIDs and fixed timestamps, so a failing test is reproducible and reviewers can bookmark /orders/0000...0001.
-- seed/01_core.sql — idempotent, safe to re-run
INSERT INTO users (id, email, display_name, created_at) VALUES
('00000000-0000-0000-0000-000000000001', 'owner@example.test', 'Test Owner', '2026-01-02T00:00:00Z'),
('00000000-0000-0000-0000-000000000002', 'viewer@example.test', 'Test Viewer', '2026-01-02T00:00:00Z')
ON CONFLICT (id) DO NOTHING;
INSERT INTO orders (id, user_id, status, total_cents, created_at)
SELECT
('00000000-0000-0000-0000-0000000100' || lpad(n::text, 2, '0'))::uuid,
'00000000-0000-0000-0000-000000000001',
(ARRAY['pending','paid','refunded'])[1 + (n % 3)],
1000 + n * 37,
timestamptz '2026-01-03T00:00:00Z' + (n || ' hours')::interval
FROM generate_series(1, 50) AS n
ON CONFLICT (id) DO NOTHING;
Use a reserved test domain like example.test for every address. Seeds leak into outbound email eventually, and a domain that cannot resolve is the cheapest guardrail there is.
3. Volume, generated rather than copied. If the PR touches a query, 50 rows will lie to you. generate_series into the hot table buys realistic row counts and index behavior without a single real record:
INSERT INTO events (user_id, kind, payload, created_at)
SELECT
'00000000-0000-0000-0000-000000000001',
'page_view',
jsonb_build_object('path', '/p/' || (n % 500)),
now() - (n || ' minutes')::interval
FROM generate_series(1, 200000) AS n;
ANALYZE events;
That ANALYZE matters. Freshly bulk-loaded tables have no statistics, and the planner will pick a plan that has nothing to do with what production does.
When you genuinely need production shapes, scrub inside the trusted environment and ship only the output — never pg_dump production to a laptop first:
#!/usr/bin/env bash
set -euo pipefail
pg_dump "$PROD_URL" --no-owner --no-privileges \
--exclude-table-data='audit_log' \
--exclude-table-data='sessions' \
-Fc -f /tmp/raw.dump
pg_restore -d "$SCRUB_URL" --no-owner --clean --if-exists /tmp/raw.dump
psql "$SCRUB_URL" -v ON_ERROR_STOP=1 -f scrub.sql
pg_dump "$SCRUB_URL" --no-owner -Fc -f /tmp/preview-seed.dump
-- scrub.sql: irreversible, and verified by a test
UPDATE users SET
email = 'user' || id || '@example.test',
display_name = 'User ' || left(md5(id::text), 6),
phone = NULL;
DELETE FROM payment_methods;
The scrub needs a test in the same job that fails loudly — a query asserting zero rows still match your real email domain. A scrub nobody verifies is a scrub that stopped matching the schema three migrations ago. If you would rather not hand-roll the rules, PostgreSQL Anonymizer is the extension that lets you declare masking rules on columns, so they live with the schema instead of in a script that drifts.
The takeaway: generate volume, scrub only where you must, and assert the scrub worked in CI rather than trusting it.
Wiring it into CI without leaving databases behind
The failure mode here is cost, not correctness: ephemeral environments are only ephemeral if something deletes them. Tie creation and teardown to the same PR lifecycle events.
name: preview-db
on:
pull_request:
types: [opened, synchronize, reopened, closed]
concurrency:
group: preview-db-${{ github.event.pull_request.number }}
cancel-in-progress: true
jobs:
up:
if: github.event.action != 'closed'
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Create or reset the PR database
env:
ADMIN_URL: ${{ secrets.PREVIEW_ADMIN_URL }}
DB: pr_${{ github.event.pull_request.number }}
run: |
psql "$ADMIN_URL" -v ON_ERROR_STOP=1 \
-c "DROP DATABASE IF EXISTS \"$DB\" WITH (FORCE)" \
-c "CREATE DATABASE \"$DB\""
- run: ./scripts/migrate.sh
env:
DATABASE_URL: ${{ secrets.PREVIEW_BASE_URL }}/pr_${{ github.event.pull_request.number }}
- run: psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f seed/01_core.sql
env:
DATABASE_URL: ${{ secrets.PREVIEW_BASE_URL }}/pr_${{ github.event.pull_request.number }}
down:
if: github.event.action == 'closed'
runs-on: ubuntu-latest
steps:
- name: Drop the PR database
env:
ADMIN_URL: ${{ secrets.PREVIEW_ADMIN_URL }}
run: |
psql "$ADMIN_URL" -v ON_ERROR_STOP=1 \
-c "DROP DATABASE IF EXISTS \"pr_${{ github.event.pull_request.number }}\" WITH (FORCE)"
WITH (FORCE) (Postgres 13+) is what makes the drop reliable — without it, one leftover connection leaves you with database "pr_412" is being accessed by other users and a database that lives forever. Add a scheduled job that drops any pr_* database whose PR is closed, because the closed event does get missed.
On the app side, Vercel's preview deployments are the least-effort way to get a URL per PR for a frontend, while Render's preview environments declared in render.yaml cover the case where the PR needs a long-running backend process. Both still expect you to supply the database strategy above; neither isolates your data for you.
The takeaway: every create path needs a matching delete path plus a sweeper, because webhook-driven teardown is not reliable enough to be the only one.
What about webhooks and OAuth callbacks that can't reach a preview URL?
Third-party services cannot deliver to an unpredictable per-PR hostname, and OAuth providers reject unregistered redirect URIs. Best first: register one stable proxy URL that routes to the right preview by header or path segment; use the provider's CLI forwarding where it exists; or stub the integration and exercise the real thing only in staging. Do not register thirty redirect URIs — that list becomes permanent.
When is paid branching worth it?
The honest baseline is a small always-on Postgres instance plus the CI job above: a few dollars a month and an afternoon of setup. Branching starts paying when your data has enough real-world skew that seeds stop predicting production, when the seed job lands on the critical path of every review, or when reviewers are non-engineers who need plausible data to judge a UI.
The takeaway: pay for branching to buy data realism, not to skip the seed file — you need the seed file either way.
FAQ
Can I just use a copy of production for preview environments?
Only after scrubbing, and only if the scrub runs inside the environment that is already allowed to hold production data. A preview URL is typically reachable by anyone with the link and protected by far weaker controls than production, so unscrubbed copies turn every open PR into an additional place a breach can start.
How do I give each pull request its own database on Postgres?
Create a database named after the PR number in a CI job triggered by pull_request, run your migrations against it, apply an idempotent seed, and drop it on the closed event with DROP DATABASE ... WITH (FORCE). Add a scheduled sweeper for the PRs whose close event got lost, otherwise abandoned databases accumulate.
Why does my preview environment run out of database connections?
Each preview app holds its own connection pool, so idle previews consume connections even with no traffic. Either put a pooler in front of the instance and let previews connect through it in transaction mode, or cap preview pools to one or two connections each — previews serve one reviewer, not production traffic.
Bottom line
With a handful of open PRs, run an ephemeral database per PR with a committed idempotent seed plus a generate_series block for volume: free, isolated, and it exercises your migrations on every push. If seeded data keeps failing to predict production, move to copy-on-write branching on a managed Postgres and apply production-level access controls to those branches. If you need production shapes without production risk, build the scrub pipeline once, run it inside the trusted environment, and test the scrub in CI. Whichever you pick, write the teardown job before the create job — the environments that cost real money are the ones nobody remembers to delete.
Top comments (0)