Schema migrations and avoiding downtime

If you’re developing an application that is backed by a SQL database, sooner or later you will need to do a schema migration. Maybe you’ll need to add a new table or a new column, create a new index, or change some constraint.

Luckily there are plenty of tools to help you! Migratus for Clojure, Flyway for Java, and dbmate as a more language-independent option are examples of the most common design pattern: you write migrations as DDL commands in SQL files and the tool keeps track of which of the migrations have been applied to the database.

Let’s say you forgot to make the e-mail field of your user table unique and now you want to fix it. With these tools, the migration could look something like this:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Many object-relational mappers such as Django’s ORM and ActiveRecord from Ruby on Rails exhibit another pattern: the migrations are written using a DSL in the ORM’s programming language (Python and Ruby, respectively). Here’s what the migration above could look if it was created with Django:

from django.db import migrations, models

class Migration(migrations.Migration):
    operations = [
        migrations.AlterField(
            model_name='user',
            name='email',
            field=models.TextField(unique=True),
        ),
    ]

One tricky thing about migrations is that to alter a table, you have to lock it potentially for a long time. This means that migrations can cause downtime. Braintree has published a great guide for avoiding downtime with PostgreSQL migrations but even then you have to be careful.

For example, when you add a uniqueness constraint in PostgreSQL, an index gets created. The guide advises that the migration above should be done in two steps to avoid grabbing an exclusive lock while the index is being created:

1. First, create a unique index using CREATE INDEX CONCURRENTLY.
2. Then, add the constraint with USING INDEX which only requires a short-lived exclusive lock to alter the table’s metadata.

If you’re like me, you might think that it’d be a great idea to encode all this knowledge into a tool. And it turns out that people have done it!

There are linters such as Squawk that check your SQL files. Here’s what Squawk has to say about our migration:

example.sql:1:0: warning: disallowed-unique-constraint

   1 | ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

  note: Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock which blocks reads.
  help: Create an index CONCURRENTLY and create the constraint using the index.

Seems good – this is exactly what I was looking for.

Since Django migrations are specified on a higher level, could it do this kind of tricks automatically? Possibly, but as far as I know, currently it doesn’t. For ActiveRecord, there’s pg_ha_migrations which implements these ideas.

Tools like gh-ost and pt-online-schema-change for MySQL are another stab at this problem. They’re only concerned with running ALTER TABLE statements in a safe, robust way. The problem of managing migrations is left to you.

My point is that be careful with your migrations but my meta-point is this: migrations of heavily-loaded databases take skill and your tools can help you. I’ve heard it said that all you need for migrations is a bunch of SQL files in a directory. It goes a long way, yeah, but you can go further.