Zero-Downtime Schema Migrations in Practice

For a long time I treated schema migrations as something to worry about later. Then a small change to our Django setup turned into a week of debugging, and "later" arrived all at once.

The first symptom was subtle. Nothing failed outright, but schema migrations kept drifting in the wrong direction, and every fix seemed to make the next one harder.

Some background

We had good intentions. We even had a document describing how schema migrations should work. The trouble was that the document and the code had quietly gone their separate ways.

Most of the complexity was accidental. Over the years we'd layered three different approaches to schema migrations on top of each other, and each one made sense at the time.

How it works

We started by writing down the one rule we actually cared about, then deleted every bit of code that didn't serve it. It's not glamorous, but it works.

Here's a trimmed-down version of what we ended up with:

UPDATE jobs
SET status = 'running', started_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING id, payload;

This alone let us delete a surprising amount of code elsewhere in the project.

A few things that helped along the way:

• Keep the old path around until the new one has survived a real incident.
• Measure before and after, even if it's just a rough number in a spreadsheet.
• Delete dead code aggressively; it's the cheapest refactor there is.
• Agree on what "done" means before the first pull request.

What it cost us

There are cases where this falls apart. If your team is very small or your traffic is very spiky, the extra moving parts may not pay for themselves.

The biggest cost was social, not technical. Changing how people think about schema migrations takes much longer than changing the code.

Wrapping up

I don't think there's a universal answer here, but I hope this gives you a few ideas to try on your own project. If you do, I'd love to hear how it goes.