Adding indexes with migrations in Rails

Imagine, that you want to add some indexes into your DB and of course you want to do it with zero downtime for your app.

If you know, each migration in Rails wrapped by transaction and in this case - you database becomes unavailable for write operations.

Any suggestions?

Use concurrent algorithm option

In Rails migrations, you can use the algorithm option to trigger a concurrent index build on your database table.

For example, we recently noticed that we miss a database index for accessing our products database table.

Our products table is really big - for example 10 million rows and we can’t allow migration to block it.

Let’s add a concurrent index build:

def change
  add_index :products, :product_statistics_id, algorithm: :concurrently
end

Disable table lock

As I said before - Active Record creates a transition around every migration step. To avoid this, we can use the disable_ddl_transaction! introduced in Rails 4 to run this one migration without a transaction wrapper:

class AddIndexToProducts < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index :products, :product_statistics_id, algorithm: :concurrently
  end
end

This allows PostgreSQL to build the index without locking, that prevent concurrent inserts, updates, or deletes on the table. Using indexes migration lock out writes (but not reads) on the table.

Check for invalid indexes

Before adding new indexes, you can check for invalid indexes with sql query on your db console:

❯❯❯ psql -U postgres -W -d evendy_api_development
Password for user postgres: 
psql (10.5, server 9.6.10)
Type "help" for help.

evendy_api_development=# SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relh
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-----
(0 rows)

But what if you need to rebuild invalid indexes? Usually you have two choices - use REINDEX command or drop the index and try to re-build it again.

Please keep in mind, that REINDEX command lock the table for writes, and works without concurrency. So, it’s not optimal and can be used only on small tables.

Using second option you should drop the index and then re-build the index using the concurrently flag again.

That’s it.