Safe database migrations without downtime

A migration changes the database structure (add a column, an index, rename a table). On a live system, a careless one can lock a big table for minutes, break running code, or lose data.

The core problem

During a deploy, old code and new code run at the same time, and the database must work for both. So every schema change must be backward compatible with the code that is currently live.

The expand and contract pattern

Make a risky change in several safe steps, each deployed separately.

Example: rename users.name to users.full_name.

1. Expand: add the new column full_name (nullable). Old code keeps working.
2. Write both: deploy code that writes to both columns, and reads the old one.
3. Backfill: copy old data into the new column, in small batches.
4. Switch reads: deploy code that reads full_name.
5. Stop writing the old column.
6. Contract: drop name in a later migration, after you are sure nothing uses it.

It is slower than a single RENAME, but there is never a moment where running code breaks.

Operations that can hurt

• Adding an index locks writes in some databases. In PostgreSQL use CREATE INDEX CONCURRENTLY. In MySQL use online DDL options.
• Adding a column with a default used to rewrite the whole table in older PostgreSQL versions; newer versions handle common cases without rewriting. Check your version.
• Adding a NOT NULL or foreign key constraint scans the table. Add it as unvalidated, then validate separately where supported.
• Changing a column type often rewrites the table.
• Dropping or renaming a column breaks code that still uses it.

Backfills

Do not update ten million rows in one transaction. Process in batches (a few thousand rows), commit between them, sleep briefly, and make the job restartable. Run it outside peak hours.

Protect yourself

• Back up before you start, and know how to restore.
• Set a lock_timeout so a migration fails fast instead of queueing behind a long transaction and blocking everyone.
• Test on a copy with production-sized data.
• Keep migrations small, reviewed and in version control, and run them with a tool, not by hand.
• Have a rollback plan. Many data changes are not truly reversible, so a forward fix may be the real rollback.
• Run migrations separately from app startup in multi-server deploys, so you do not run them twice.

SQLite notes

SQLite supports fewer ALTER TABLE operations. Larger changes are done by creating a new table, copying the data and swapping. Take a copy of the file first.

Takeaway: make every change backward compatible, split risky changes into expand and contract steps, batch backfills, and always have a backup and a lock timeout.

---

Backend & Database series, Part 2 (performance and operations): 5 of 6. Previous: PostgreSQL vs MySQL vs SQLite: which one to choose.