How to Write a Reversible Database Migration

Adding a column is a two-line job right up until the table has forty million rows and is serving traffic. Then the same statement is either instant or a fifteen-minute outage, depending on your database version, the column’s default, and whether anyone thought about locking.

And the down migration? Usually written in a hurry, at the worst possible moment, by whoever is on call.

What a migration is really committing you to

A migration is two statements: the change, and its reverse. Writing both isn’t bureaucracy — the down direction is a design check. If you can’t express the reversal cleanly, the forward change probably destroys information, and that’s worth knowing before it runs.

The second thing a migration commits you to is a lock. Different statements take different locks for different durations, and the difference between a safe deploy and a stalled application is entirely in that detail.

On PostgreSQL, adding a nullable column with no default is a catalogue change — effectively instant. Adding an index without CONCURRENTLY takes a lock that blocks writes for the whole build. On MySQL, the rules differ again by storage engine and version. There’s no universal answer, which is why “it was fine in staging” is such a dangerous sentence when staging has ten thousand rows.

Why people get stuck here

• Locking behaviour is invisible in the SQL. Nothing in CREATE INDEX announces that it will block writes.
• Down migrations written as an afterthought. Or not at all, on the assumption that forward-only is simpler.
• Ordering. Data, constraints and indexes have to arrive in an order that doesn’t fail, and the safe order isn’t always the obvious one.
• Version differences. Behaviour that changed several major versions ago is still described the old way in half the material online.

What a good migration looks like

Both directions, written together

Write the down migration at the same time as the up. It takes two minutes when you have the context and twenty when you don’t.

Small and independent

One change per migration. A file that adds a column, backfills it, adds an index and drops another column is four different risk profiles wrapped in one transaction — and one of them will be the reason it fails.

Non-blocking where the table is live

CREATE INDEX CONCURRENTLY on Postgres takes longer and can’t run inside a transaction, but it doesn’t block writes. On a table serving traffic, that’s the correct trade.

Common mistakes to avoid

• Testing a migration against a development database with a thousand rows and inferring anything about production timing.
• Backfilling a large table in one statement rather than in batches, holding a long transaction open.
• Dropping a column in the same release that stops writing to it — deploy the code change first, drop later.
• Renaming a column in a single migration while old application code is still running against it.
• Running a migration inside a transaction that also does slow work, extending the lock for the duration.

How to do it with Migration SQL Generator

The Migration SQL Generator writes both directions and flags which statements take a lock.

1. Choose the change and the database — locking behaviour differs sharply between Postgres and MySQL.
2. Name the table and column involved.
3. Read both the up and down SQL, and the note about what each statement locks.
4. Split anything that combines several changes into separate migrations before running it.

The PostgreSQL ALTER TABLE documentation is the authority on lock levels for your specific version. Other database utilities are in the tools directory.

Frequently asked questions

Why use CREATE INDEX CONCURRENTLY?

A plain CREATE INDEX on Postgres holds a lock that blocks writes for the whole build. CONCURRENTLY builds in two passes without that lock. It’s slower, can’t run inside a transaction, and can leave an invalid index if it fails — all acceptable next to blocking writes on a live table.

Is adding a column safe on a large table?

Adding a nullable column with no default is fast on current Postgres and MySQL versions. Adding NOT NULL with a default used to rewrite the entire table and still does on older engines. Check your version rather than the general advice.

Why bother with down migrations if we never roll back?

Because the one time you need it, you’ll need it under pressure. It’s also a design check: a change that can’t be reversed cleanly is a change that deserves a second look before it ships.

Final thought

Before running anything against production, ask one question of each statement: what does this lock, and for how long? If you can’t answer it, that’s the work — not the SQL.

Try the free Migration SQL Generator