Treat migrations like surgery
"We write, test, and execute database migrations under strict controls to prevent data loss and downtime."
Why this exists
A bad migration can destroy user data permanently. At a previous company, a single dropped column wiped out 18 months of customer order history. The blast radius of database mistakes is larger than almost any other error. We enforce strict rules not out of paranoia, but because recovery is incredibly painful.
Operational Flow
Write: Generate migrations through your ORM or write them by hand. Never run manual SQL statements directly against a production console.
Reversibility: Every migration must include a working down migration. Test the rollback path in your local environment before opening a pull request.
Expand-contract: For destructive changes like dropping or renaming columns, add the new field first, update the application code, backfill data, and then drop the old field in a separate deployment.
Dry-run: Run migrations against a staging database of similar size and schema to production. If a migration fails on staging, do not proceed.
Locks: Track which operations require table locks and how long they will run. Create indexes concurrently in PostgreSQL, or use tools like pg_repack for tables under active load.
Runbook: Provide a short runbook detailing what the migration does, how you will verify its success, and how you will roll it back.
Verification: Check row counts, query performance, and error logs immediately after the migration runs.
What good looks like
- Adding a NOT NULL column requires three separate pull requests: add the nullable column, backfill the data, then add the constraint.
- An engineer notices a backfill takes 40 minutes on staging, so they schedule the production run for Sunday at 3:00 AM.
- You test a down migration locally and verify the resulting schema matches the starting state exactly.
What NOT to do
- Do not run raw ALTER TABLE commands on production. It drifts the database from the repository schema.
- Do not ship a migration without a verified rollback path.
- Do not add a NOT NULL column without a default value to an active table. This will cause database write failures.
- Do not assume a query is fast because it executed in 5ms on a 10-row local database.
A database migration is a bet on your code and your schema. If you lose, you spend your night resolving an incident instead of sleeping. The expand-contract pattern breaks these migrations into smaller, reversible actions. This seems slow until you watch a bad migration lock a production table for six minutes.
Every migration requires a staging run, a tested down migration, and a runbook. If any of these are missing, we do not merge the pull request. This caution is the baseline for operations that can delete data. Write the runbook while you are clear-headed. You will not have time to write it when a table lock begins blocking production traffic.