Adding an index to an existing table column might seem straightforward. However, PostgreSQL requires a SHARE lock to create a standard index. This mode conflicts with common write operations like INSERT, UPDATE, and DELETE, which use the ROW EXCLUSIVE lock mode. These articles explain the issue in depth:

• Postgres Schema Migration without Downtime Best Practice
• How to Use Postgres CREATE INDEX CONCURRENTLY

Creating the index concurrently allows those write operations to happen while the index is being created, which can take quite a few minutes in huge tables. If those operations were blocked during that time the application would experience many request timeouts and the application would become unresponsive until the index creation is completed.

I am currently working on a Rails application hosted on Heroku. This application makes use of the strong_migrations gem, which sets a lock timeout of 10 seconds by default. Under heavy load, it can take a long time to acquire the lock necessary to complete index creation.

When that happens the whole deployment fails because the db:migrate task failed, leaving the created index in an invalid state. It gets worse because indices can’t be created concurrently in a transaction, which means the migration must call disable_ddl_transaction!. Any changes already made to the database are not rolled back if the migration fails.

Even if the only operation in the migration is the index creation, the migration can’t be retried until we remove the invalid index first if the migration specifies the index name.

Other issues caused by concurrent index creation

The strong_migrations gem can automatically get rid of invalid indices, through the StrongMigrations.remove_invalid_indexes = true setting. That fixes part of the problem with retrying failed migrations. Since indices can’t be created concurrently within a transaction, we should create indices in dedicated migrations.

There’s no guarantee we’ll get the lock before the lock timeout. If the migration fails, the whole deployment will fail and it will prevent us from deploying other changes as well until the migrations are applied successfully. We want to prevent the situation where a single index creation migration could block all deployments until off hours when the application is less busy and the lock can be acquired within 10 seconds.

Delayed migrations to the rescue

In order to fix those issues we decided to handle index creations in delayed migrations. They work just like regular migrations except that they are not executed during the release phase. We run those migrations after a successful deployment instead. These migrations are stored in a separate location and new Rake tasks have been created to run them.

It changes the way we implement some changes too. Instead of deploying database changes alongside with code changes we first prepare the database before deploying the new code. Once the indices are created we can merge the new code using the index.

I’ve implemented a robust set of Rake tasks to handle this automated process. Here is the implementation:

It’s now a matter of generating a new migration, adding the index creation logic, moving the migration to db/delayed_migrations, and running bin/rails delayed_migrations:run.

We can run this command through heroku run bash or using a background job scheduled after each release, for example. If the command times out it’s retried later on. Once it’s run successfully we can proceed with the code changes that use the new index.

Final thoughts

This is a very common problem with applications running in production that I’m surprised I don’t see an out-of-the-box solution from Rails or any other gem. Maybe I’m missing something so please let me know in the article comments if there’s a built-in solution to this problem that I’m not aware of.