Database Migration Checklist

What this checklist is

A pre-release checklist for schema and data migrations on a live database. It covers backward-compatible expand and contract steps, how each kind of change locks a table in PostgreSQL and MySQL, building indexes online, batching backfills, rehearsing on production-sized data, backups and rollback, deploy ordering with application code and what to watch while it runs, with notes for MongoDB.

It has 11 chapters and 49 checks, each with how to verify it and how much it matters. For backend developers and anyone who ships schema changes.

Chapter by chapter

1. Plan and classify the change (4 checks)

Checks that the migration is tracked, small, and that you know what each statement will do to a live table before you run it.

2. Backward compatibility: expand and contract (5 checks)

Checks that both the old and new versions of the application keep working at every step, because during a rolling deploy both are running.

3. Locks and rewrites in PostgreSQL (6 checks)

Checks that a PostgreSQL migration cannot block the table for longer than you can afford, and that slow steps run in forms that allow reads and writes to continue.

4. Online DDL in MySQL (5 checks)

Checks that a MySQL (InnoDB) migration uses the online algorithm you expect, fails fast when it cannot, and does not stall behind metadata locks.

5. Creating indexes online (5 checks)

Checks that new and replaced indexes are built without blocking writes, and that a failed build does not leave a broken index behind.

6. Backfills and data migrations (5 checks)

Checks that rewriting existing rows happens in small, throttled, restartable batches instead of one huge transaction.

7. Rehearse on production-sized data (4 checks)

Checks that the migration has been run, timed and rolled back on data that looks like production before production sees it.

8. Backups and rollback plan (3 checks)

Checks that you can get back to a known good state if the migration goes wrong, and know how long that takes.

9. Deploy ordering with application code (4 checks)

Checks that the migration and the code that needs it reach production in the right order, exactly once.

10. Monitoring during and after (3 checks)

Checks that you can see the migration hurting the system while it runs, and confirm afterwards that it left the schema and data as intended.

11. Notes for MongoDB (5 checks)

MongoDB has no ALTER TABLE, but documents still change shape, indexes still have to be built on live collections and old and new code still run side by side.

How it works on Sefism

The checklist is one page: each check has a severity (must, should or nice to have) and a concrete way to verify it, and members can tick checks off as they go. The opening chapters are open to everyone. Adding it to your account uses credits, opens every chapter and keeps your progress.

Every claim is checked against the sources listed on the page (8 cited).