The Database Migration Safety Checklist Builder: Turn a Schema Change Into a Rollout Plan That Won't Lock Your Database
Why this prompt matters
A single mismanaged ALTER TABLE on a large, high-traffic table can hold an exclusive lock for minutes, during which every write to that table queues or times out — on an e-commerce site, a six-minute lock during business hours can mean thousands of failed checkouts and a very public incident report. Migration tooling defaults (Rails, Django, Prisma) frequently generate the unsafe single-step pattern unless a developer knows to override it.
What we use it for
Your team is two days from a major product launch and needs to add a required boolean column to your 50-million-row users table to support a new feature — but the last time someone ran a similar migration, it locked the table for six minutes and paged the entire on-call rotation during peak traffic.
Prompt
Role: Act as a senior database reliability engineer who has been paged at 3am for migrations that locked production tables and caused outages. Context: - Database engine and version: [E.G., POSTGRESQL 14, MYSQL 8] - Table name and approximate row count: [E.G., "users", 50 MILLION ROWS] - Current write traffic on this table: [E.G., "800 writes/sec during business hours, 200 off-hours"] - Proposed schema change: [PASTE THE DDL STATEMENT OR DESCRIBE THE CHANGE, E.G., "ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false"] - Deployment setup: [E.G., "Kubernetes, rolling deploy, old and new app code run simultaneously for ~10 minutes during rollout"] Task: Analyze this migration for locking behavior, backward compatibility, and rollback safety. Produce a step-by-step rollout plan that a team can execute without an outage. Constraints: - Explicitly name the lock type this operation takes (e.g., ACCESS EXCLUSIVE, SHARE, metadata-only) and estimate how long it will hold that lock given the stated row count and traffic - Assume old and new application code run simultaneously during rollout — flag anything that would break the old code path before it's fully retired - Never recommend a single all-at-once migration for any table over 1 million rows — break it into backward-compatible stages instead - Flag any irreversible operation (dropped columns, renamed columns, changed types) and require a two-step expand-and-contract pattern instead - Do not assume the team has a maintenance window — the plan must work against a live, serving database Output Format: 1. Risk Classification: Safe / Caution / Dangerous, with the one-sentence reason 2. Locking Analysis: exact lock type, estimated hold duration at the stated traffic level, what queries would block during that window 3. Backward-Compatibility Check: will currently-running old code break against the new schema mid-rollout? Yes/no with explanation 4. Step-by-Step Rollout Plan: each step must be independently revertable, numbered, with what to verify before moving to the next step 5. Rollback Plan: the exact reversal for each step above, in case something goes wrong mid-migration
Result
1. Risk Classification: Caution
Adding a NOT NULL column with a static default is safe on PostgreSQL 11+ (the default is stored as metadata, not rewritten row-by-row), but a naive migration tool or an ORM auto-migration can still generate the pre-11 pattern (full table rewrite) without realizing it — that's the real risk here, not the column addition itself.
2. Locking Analysis: If executed as a single ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false; on PostgreSQL 14+, this takes a brief ACCESS EXCLUSIVE lock only long enough to update the table's metadata — typically under 50ms even on a 50-million-row table, since the default value doesn't require rewriting existing rows. At 800 writes/sec, that's a negligible queuing window. However, if your ORM's migration tooling generates a SET NOT NULL as a separate step from adding the column, that specific step DOES require a full table scan to verify no existing rows are null, holding a SHARE UPDATE EXCLUSIVE lock for an estimated 4-7 minutes at this row count — long enough to cause write timeouts under sustained load.
3. Backward-Compatibility Check: No breakage expected. Old application code that doesn't know about the email_verified column will continue to function normally — it simply won't read or write that field. New code reading the column during the ~10-minute rollout window will get the default value (false) for all pre-existing rows, which is the intended behavior here.
4. Step-by-Step Rollout Plan:
Step 1: Add the column as nullable first: ALTER TABLE users ADD COLUMN email_verified BOOLEAN; — this is instant regardless of table size. Verify: check pg_stat_activity for lock waits during and after execution; confirm zero.
Step 2: Backfill existing rows in small batches (e.g., 10,000 rows per transaction) during off-peak hours: UPDATE users SET email_verified = false WHERE email_verified IS NULL AND id BETWEEN [batch_start] AND [batch_end]; — this avoids a single long-running transaction. Verify: monitor replication lag and query latency between batches; pause if either spikes.
Step 3: Once all rows are backfilled, add the NOT NULL constraint using ALTER TABLE users ADD CONSTRAINT email_verified_not_null CHECK (email_verified IS NOT NULL) NOT VALID; followed by VALIDATE CONSTRAINT email_verified_not_null; — the VALIDATE step scans the table but does NOT block concurrent writes, unlike a direct SET NOT NULL. Verify: confirm the constraint validates without errors.
Step 4: Deploy application code that reads/writes the new column. Verify: monitor error rates and the column's null-rate in production for 24 hours before considering the migration complete.
5. Rollback Plan:
Step 4 rollback: redeploy the previous application version; the column being present but unused is harmless.
Step 3 rollback: ALTER TABLE users DROP CONSTRAINT email_verified_not_null; — instant, no lock concerns.
Step 2 rollback: no action needed; backfilled data can remain even if the migration is aborted.
Step 1 rollback: ALTER TABLE users DROP COLUMN email_verified; — safe as long as no application code depends on the column yet.
Most database migration disasters share a common cause: a developer runs a schema change that looks simple in isolation — add a column, rename a field, add a constraint — without understanding what lock that specific operation takes on their specific database version, or how long it will hold that lock against their specific table size and traffic pattern. This prompt forces that analysis explicitly, before a single line of migration code runs.
The constraint requiring the model to name the exact lock type (ACCESS EXCLUSIVE, SHARE UPDATE EXCLUSIVE, or metadata-only) is the core of what makes this useful instead of generic. Different lock types have wildly different blast radii — a metadata-only lock resolves in milliseconds regardless of table size, while a full-table-rewrite lock can block every write for minutes on a large table. Most migration guidance treats "add a column" as one operation, when in practice the specific SQL generated by your ORM determines which category you're actually in.
The backward-compatibility check exists because rolling deployments mean old and new application code run side by side for a window of time — often minutes, sometimes longer. A migration that's perfectly safe for the new code can silently break the old code path still serving live traffic, and that failure mode is invisible until it's already causing errors in production.
The mandatory expand-and-contract pattern for irreversible changes — never renaming or dropping a column in one step — is a deliberate constraint against convenience. It's always tempting to do a rename in a single migration; it's also how teams end up unable to roll back when the new code has a bug and the old column no longer exists to fall back on.
The step-by-step format with independent verification at each stage matters because migrations rarely fail instantly and obviously. They fail as replication lag creeping up, or query latency doubling under load — the kind of signal you only catch if you're explicitly told to check for it between steps rather than treating the whole migration as one atomic event.