Database Migrations

A migration is code that runs once against production data. It deserves the same review as application code, and more caution, because it cannot be rolled back by redeploying.

Rules that prevent most incidents

• Version every change. Migrations live in the repository, in order, applied by a tool that records which have run. Manual ALTER TABLE in a production console is how environments drift apart. See Schema Migration Tools.
• One logical change per migration. Small migrations fail in ways that are easy to understand and resume.
• Forward-only in practice. Write down whether each change is reversible. A down-migration that drops a column is not a rollback — the data is gone. For most teams the honest answer is "roll forward with a fix".
• Test against production-like data. A migration that takes two seconds against a thousand rows may take four hours against two hundred million.
• Set a lock timeout. This is the single most valuable habit; see below.

Lock behaviour is what actually hurts

The danger is rarely the statement's duration. It is that a DDL statement needs an exclusive lock, queues behind a long-running query, and every subsequent query on the table then queues behind the DDL. A change that takes milliseconds takes the table offline for as long as the query in front of it runs.

-- Always. If the lock is not free almost immediately, fail instead of queueing.
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN dispatch_note text;

Then retry. Failing fast and retrying is strictly better than blocking the table.

Cost of common operations

Exact behaviour is version-specific — confirm against your engine's documentation before running anything on a large table.

Migrations and deploys must be ordered

The application and the schema change at different moments, and both versions must work against both schemas during the window between them. That constraint is what makes expand-and-contract the standard approach — covered in Zero-Downtime Migrations.