Testing database migrations

Testing backends Senior Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Pick the fix

A migration adds a NOT NULL column with a volatile default to a 200 GB table. Tests passed on an empty DB. What should the test plan add?

Options
Hint 1 of 1

What does an empty table hide?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A A rehearsal on a production-sized copy measuring locks and duration

Correct. Empty tables hide rewrites and lock time.

Lock time and duration depend on table size. A volatile default rewrites every row, which takes an instant on an empty test database and possibly hours under an exclusive lock on 200 GB.

Why not the others?
  • B Run the same test twice. Still an empty table.
  • C Skip tests for schema changes. Riskier.
  • D Only check the migration file's syntax. Syntax isn't the risk.

30-second answer

Run every migration in CI against a database built from the previous release's schema with realistic data, checking it applies, that old and new code both work against the result (for expand-and-contract), and that it finishes in acceptable time. For big tables, rehearse on a production-sized copy to see locks and duration. Check lock-heavy operations with a linter (squawk for Postgres) and set lock_timeout in migrations.

Practise saying it · 30 s. No recording, just a timer

Test migrations.

Say your answer out loud, then check which of these points you covered:

  • Previous schema + data
  • Old and new code compatible
  • Production-size rehearsal
  • Lock linting, lock_timeout

Deep dive

Test the down path only if you'll really use it; many teams roll forward.

A schema diff between migrated and expected states catches drift.

Data migrations need tests on real-looking edge cases (NULLs, duplicates).

Measure lock acquisition, not just runtime.

Fail fast instead of queueing behind a lock Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
SET lock_timeout = '2s';
CREATE TEMP TABLE accounts (id int);
ALTER TABLE accounts ADD COLUMN plan text;
SELECT count(*) FROM information_schema.columns
WHERE table_name = 'accounts';

Common mistakes

  • Testing migrations only on an empty database.
  • No lock_timeout, so a migration queues behind a long query and blocks everything.
  • Code and migration deployed as one indivisible step.

Senior take & follow-ups

Apply on previous schema with data, old+new code compatibility, timing, locks.

Follow-up questions

Think of your answer first, then open the hint.

  1. Why lock_timeout?

    Show hint

    An ALTER waiting for a lock blocks every query queued behind it.

  2. What does squawk do?

    Show hint

    Flags risky Postgres migration statements.

Related questions

Suggest a fix if something here is wrong or out of date.