Database Migration Language

Migration up/down vocabulary:

Option B is correct. The down() function is intended to undo the up() migration — enabling rollback — but its value is frequently debated in engineering teams.

Why down() is controversial:

• Destructive migrations — if up() drops a column, down() can recreate the column structure, but the data is already gone. The rollback function creates a false sense of safety.
• Data migrations — if up() transforms data (e.g., splits a name column into first_name/last_name), reversing that transformation requires preserving the original, which adds complexity.
• Skipped in practice — many teams argue that forward-only migrations paired with a well-tested rollback deployment procedure are safer than relying on down().

Function	Direction	Triggered by
up()	Forward — applies the schema change	migrate up / deploy
down()	Reverse — undoes the schema change	migrate rollback

Zero-downtime migration vocabulary:

Option D describes the standard expand-and-contract (or parallel-write) pattern for zero-downtime column renames.

Why ALTER TABLE RENAME COLUMN (Option A) causes downtime: In PostgreSQL, renaming a column acquires an ACCESS EXCLUSIVE lock on the table, blocking all reads and writes for the duration — unacceptable on large, high-traffic tables.

The expand-and-contract sequence:

1. Expand: Add the new username column (nullable, no lock required on most databases)
2. Backfill: Copy data from user_name → username in batches to avoid lock contention
3. Dual-write: Deploy application code that writes to both columns on every insert/update
4. Migrate reads: Once backfill is complete, point all reads at username
5. Contract: In a later migration, drop user_name after confirming no code references it

Term	Meaning in migration context
Backfill	Populating a new column with data derived from existing rows
Dual-write	Application writes to both old and new columns during transition
ACCESS EXCLUSIVE lock	PostgreSQL lock that blocks all concurrent access to the table
Expand-and-contract	Two-phase pattern: add new structure, migrate, then remove old structure

Backfill vocabulary:

Option A is the correct definition. Backfilling populates a new column (or repairs missing data) across existing rows that were created before the column existed.

Backfill risk vocabulary for PR descriptions:

• Batch backfill — running the UPDATE in chunks (e.g., 1,000 rows at a time) to avoid a long-running transaction that locks the table
• Backfill duration estimate — communicate to reviewers: "This backfill runs against ~2M rows; estimated time: 45 minutes on production"
• Idempotent backfill — the UPDATE can be re-run safely without double-applying changes
• Online backfill — runs concurrently with live traffic, using small batches and short transactions

How to describe a backfill in a PR:

"This migration adds the is_verified column and backfills it for existing rows using a batched UPDATE (500 rows per transaction). Estimated backfill time on production: ~20 minutes. The migration is safe to run with live traffic — it uses row-level locks only and no table-level lock."

Breaking vs. non-breaking schema change vocabulary:

Option C is the only non-breaking change. Adding a nullable column without a default is safe during a rolling deployment because: existing application code that does not know about the column will simply ignore it; new inserts by old app versions will set the column to NULL, which is valid.

Schema change	Breaking?	Why
Remove a column	Yes	Old app versions reading that column get an error
Add NOT NULL col without default	Yes	Old app inserts that don't supply the column fail
Add nullable column	No	Old app inserts silently produce NULL; no error
Rename a column	Yes	Queries using the old name break immediately
Add an index	No (CONCURRENTLY)	Transparent to application; no column reference change

Key vocabulary for migration risk communication:

• Breaking change — a schema change that causes errors in the currently running application version
• Rolling deployment — deploying new app version gradually, with multiple versions running simultaneously
• Forward compatibility — the new schema must work with the old application code during the window between migration and full rollout

Migration risk communication in PRs:

Option B demonstrates professional database migration communication. It gives the reviewer every piece of information they need to make a risk decision without requiring database expertise.

What makes Option B effective:

Element	Value to the reviewer
Row count (8M rows)	Quantifies the scope of the operation
Lock duration estimate (12–20 min)	Makes the downtime risk concrete and time-bounded
Safer alternative proposed	Shows the author has thought about mitigation
Staging test result with dataset size	Evidence that the estimate is data-driven, not guessed

Migration PR description vocabulary checklist:

• State the table name and approximate row count
• Identify whether the migration takes a table lock and estimate the duration
• Classify the change as breaking or non-breaking
• Describe the rollback plan if the migration fails partway
• Report staging test results with dataset size proxy