safe-pg-migrations: Rails migrations that do not lock a table

The problem, which is a lock and a queue

The gem's one-line description is that it makes Postgres migrations safe, and the readme's example is the migration that breaks a service.

The migration adds a reference column to a table. If the table is large, running this migration will likely cause downtime. And the reason is worth being precise about, because it is not obvious and it is the reason a perfectly reasonable migration takes a site down.

Most data definition operations, and the readme names adding a column, removing a column and adding a default value, take an exclusive lock on the table they are altering. While the operation waits to acquire that lock, other statements are blocked. So the sequence is: the migration asks for the lock, the lock is held by a long-running query, every statement from the application queues behind the migration, and the application appears to hang.

The operation itself might take a second. The outage lasts as long as the lock is held, which is a function of the longest-running query in the system, not of the size of the table being altered.

And there is a second, worse variant. Adding a foreign key or a not-null constraint to a large table takes a long time, because the database has to scan the table to check the constraint. That work happens while holding the same exclusive lock. So the table is blocked for the entire duration of a full scan, and the duration is proportional to the table.

The readme links to two articles on altering tables and long transactions and to a third on the hard parts of zero-downtime migrations, and the fact that it links three is a hint about the audience: this is a problem that people keep re-discovering, and the articles are the current explanation of it.

What the gem actually changes

The mechanism is stated plainly: the gem patches the migration class and extends the connection so that potentially dangerous methods, and the example given is adding a reference, behave safely.

That is a monkey patch, and it is worth being clear-eyed about what that means. The gem does not ask you to change your migration code; your existing migrations, run under the gem, take a different path. Which is the whole point, and also the whole risk.

The motivation section explains the reasoning well. Writing a safe migration can be daunting, a number of articles have been written about the topic, and a few gems try to address the problem. Even for somebody with a good command of Postgres, remembering all the subtleties of explicit locking is not a piece of cake. And then the analogy that explains the design: Active Record means developers do not have to be proficient in SQL to interact with a database, and in the same way this gem was created so that developers do not have to understand the ins and outs of Postgres to write a safe migration.

The feature set is four things, and the first two are timeouts.

Before running a migration, the gem sets a short lock timeout, five seconds by default, so that statements are not blocked for too long. And it runs the operations that can take a long time, the foreign key and the not-null constraint, with a short statement timeout, also five seconds by default.

The two together are the belt and braces. The lock timeout says: if you cannot get the lock quickly, give up. The statement timeout says: if the work will take too long, give up. And in both cases, giving up is much better than the alternative, because a failed statement is a migration that stopped, and a stopped migration can be retried when the system is quiet. A migration that holds a lock for an hour is an outage that cannot be cancelled.

The defaults are configurable, and the release notes mention that the lock timeout option was increased in a later version, which tells you that five seconds was found to be too aggressive for somebody's workload.

The same migration, rewritten

The readme shows the migration before and after, and the after version is the most useful thing in the document because it shows what the gem actually generates.

The original migration adds a reference and nothing else. The rewritten one disables the transaction wrapper, sets the lock timeout, sets the statement timeout, adds the column, creates the index with the concurrent algorithm, adds the foreign key without validating it, validates the foreign key separately, and then resets the statement timeout. The comments in the generated code are the author's own, and they are worth reading: the transaction is disabled so that locks are held for a shorter time, the timeouts are described as seatbelts, and the concurrent algorithm is used to avoid locking the tables.

Three of those are the interesting ones.

Creating an index concurrently is the standard way to build an index without blocking writes, and it is the operation that categorically cannot run inside a transaction, which is why the transaction is disabled at all.

Adding a foreign key without validating it is the other half of that trick. The constraint is added but not checked, which is fast, and the check is then run separately, which takes a scan but does not hold the exclusive lock the whole time. The readme's comment says exactly that: validate the foreign key separately, because it avoids taking a lock on the entire tables.

And the final line of the generated code resets the statement timeout, with a comment that says you need to set the timeouts back to their initial values if needed. Which is a reminder that the gem's state is the connection's state, and a migration that leaves a connection configured for five-second timeouts is a migration that has quietly changed the behaviour of everything that runs afterwards on that connection.

So the value of the gem is not that it makes your migrations clever. It is that it makes the safe version of a migration the default version, and the safe version is longer, and nobody writes the longer version by hand.

The cost: no transaction, and no rollback

The third feature is the one with a real cost, and the readme is unusually direct about it.

When the gem is used, migrations are not wrapped in a transaction. And there are three reasons. They want to release locks as soon as possible. They need to be outside a transaction in order to retry statements that failed because of a lock timeout, since a failed statement inside a transaction has already poisoned it. And they need to be outside a transaction to add an index concurrently.

All three are good reasons. And then the warning, which is the sentence I would put on the first page of the documentation: if a migration fails, it will not be rolled back. This can result in migrations being partially applied, in which case they need to be manually reverted.

That is a genuine trade and it should be understood before the gem is added to a project, not after the first failure. Rails migrations are transactional by default, and that default buys you atomicity: either the whole migration lands or none of it does. Giving that up in exchange for not blocking a table is usually the right trade for a large table, where the outage is worse, but it is a trade.

And the partial application case is the one that will surprise people. A migration that adds a column and then fails on the index leaves the column in place. Retrying the migration may then fail again because the column exists. So a failed migration under this gem is a thing a person has to look at, and the readme is right to say that it needs manual reverting.

The practical mitigation is to make the migration idempotent, and to retry it when the system is quiet, which is exactly what the timeout design invites. But that is a change in how your team handles deploys, and it should be a decision rather than a discovery.

And there is one operational prerequisite that the readme puts in bold right after the install command, and it is the kind of note that saves a debugging session: do not run migrations through a connection pooler that is configured for transactional or statement pooling. You must use a direct connection, or configure the pooler for session pooling. The reason is not spelled out, but it follows from the transaction design: a pooler that hands out a different backend connection for different statements would break the assumptions the gem makes about where its state lives.

Adding a column, and the volatile default problem

The fourth feature is about adding a column, and it is the one with the sharpest edges.

The gem handles the change in how newer Postgres versions add a column with a default by not backfilling the default value for existing rows, because the engine now handles that natively. That is a change in the underlying database's behaviour, and a library that predates it would be doing a full table rewrite for nothing.

The warning is about volatile defaults. If the default value is a volatile function, the database will still update every row, and on a large table it will most likely hit the statement timeout. In that case the gem can backfill automatically, with an option named for what it does.

And then the error callout, which is the most emphatic thing in the document: using that option with a volatile default is not allowed and will raise an error.

The reason is given in the warning above it. Volatile defaults are non-deterministic functions evaluated per row, so a backfill of that kind causes the migration to hang for a very long time on large tables, and you must backfill them manually with proper monitoring and control. Non-volatile defaults, the fixed strings, numbers and booleans, are still safe to use with automatic backfill.

So the gem draws a line that a careless implementation would not. An automatic backfill of a non-volatile default is a bounded operation: the value is the same for every row, so the database can apply it with a metadata change. An automatic backfill of a volatile default is an unbounded per-row evaluation, which is the operation the whole library exists to prevent. Refusing it is the correct behaviour, and raising rather than warning is the correct severity.

The two examples in the readme show the API, and they show something else too. Both are wrapped in a block whose name says the migration has been checked.

# ✅ SAFE - non-volatile default with automatic backfill
safety_assured do
  add_column :users, :status, :string, default: 'active', default_value_backfill: :update_in_batches
end

That block is an explicit assertion by the author that they have thought about the safety of what is inside it. It is the opposite of the gem's usual philosophy, which is to make the safe thing happen by default without asking. The explanation is that a backfill is an operation the gem will do on your behalf, and an operation that rewrites every row of a table should be something you asked for explicitly.

And the backfill itself, when allowed, is done in two steps: the column is created without a default and without a null constraint, so the exclusive lock is held for the least possible time, and then the default is added without backfilling, again with a lock, and the data is brought up to date afterwards.

The repository, and where the gem is used

The top level of the repository is small, and a couple of the entries are worth a paragraph.

There is a directory for the library, a test directory, a gemspec, a gemfile and a lock file, a task file, a linter configuration, a pinned interpreter version, an editor settings directory, and the usual governance files including a file that assigns ownership of parts of the code to people. A code owners file in a library this small is a signal: it means the library is maintained by more than one person, and that parts of it have somebody responsible for them.

There is also a directory of gemfiles, which is the convention for testing a library against several sets of dependency versions rather than only the newest. For a library that patches another library's internals, that matrix is not optional, because the thing being patched changes between releases, and a version of the framework that has moved a method is a version where the patch silently stops applying. The readme's stated requirement of a minimum framework version, and the version of the database, and of the language, is the same concern stated in the other direction.

The topic tags are the most revealing thing in the repository metadata, and they are a strange mixture. There are the expected ones, the database, the language, the framework, the migrations. And then there are tags that are clearly internal: a name for one of the organisation's services, a tag for infrastructure managed as code, a tag for continuous integration, and a criticality tier.

That is a library that came out of a large production service and was published. The criticality tier is the giveaway: somebody at the organisation classified this code as important enough to be governed, which is a much stronger signal than a badge.

And the release history is consistent with that. The versions are in the three series, and the release notes for the most recent are specific: a larger lock timeout option and a safer drop column for composite indexes. Those are two changes that came from somebody's production incident, and the second of them is the kind of bug that only appears once somebody drops a composite index in the wrong order and holds a lock longer than they meant to.

The project's own article, linked from the readme, is about not worrying about Postgres locks in Rails migrations, and the name of it is a good summary of what the gem is for: the problem is not that Postgres is difficult, it is that remembering the details under time pressure is difficult, and a library that makes the safe path the default path removes the need to remember.

Editorial conclusion

safe-pg-migrations fits a Rails service whose tables have grown past the point where a schema change can be taken for granted, and whose team would rather the migration fail than the site go down. It does not fit a small application where the table is small and the migration is quick. Verify first that your connection pooler is configured for session pooling rather than transaction or statement pooling, since the gem says outright that migrations must not run through those modes. Read the failure path before you rely on it: a failed migration is no longer rolled back and may be partly applied. And do not use the automatic backfill for a volatile default, because the gem will refuse it and for good reason.

Frequently asked questions

Official sources

1. doctolib/safe-pg-migrations on GitHub
2. Issues
3. License: MIT
4. README
5. Releases