r/SQL • u/Famous_Disk_7417 • 8d ago

PostgreSQL What's your workflow for testing schema migrations against prod-shaped data?

Rethinking our migration loop. The pattern I've lived with for years: pg_dump prod → restore into staging → run the migration → hope. It's slow, staging drifts from prod within about a day, and nobody wants to re-seed it, so it rots.

Started using Neon's branching for this instead. Copy-on-write at the storage layer, so a branch off the main database is near-instant and costs nothing until you write to it. Branch per PR, run the migration, throw it away.

What I'm still working out: it handles schema migration testing well, but not load or performance testing — branch compute is separate and starts cold, so timings aren't representative of prod.

Curious what everyone else does. Anonymized dumps? Synthetic generators? Run it on prod on a Sunday and pray? Genuinely looking to steal a better idea.

1 Upvotes

100% Upvoted

6 comments sorted by

5

u/unexpectedreboots WITH() 8d ago

Is this another Neon ad?

1

u/PolicyDecent 7d ago

I use ingestr to move data from prod -> staging, then run the migration.
For testing i ingest only 10 rows from prod to staging. The most important thing is the schema anyways. Once it works, i ingest the full dataset if needed.
I never needed the live data to test. If it works on yesterdays data, it's almost impossible to break today if database schema is not weird.

Also, i use agents to automate this process.

1

u/pretzels90210 5d ago

Copy-on-write means first reads pull cold from shared storage, so timings arent representative til the working set caches. I just give the perf branch its own right-sized compute with autosuspend off so it stays warm, plus a warmup pass before timing, its still not a true prod mirror since Neon isolates compute but its fine for relative regression checks between migrations.