Create PostgreSQL indexes concurrently to minimize performance impact

Creating new indexes for an existing table in Postgres–particularly a large one–can impact performance, as locks are taken and the index is built in a single pass.

While this process completes, reads will be permitted, but modifications (INSERT, UPDATE, DELETE) will block.

Therefore it is best in this situation to create the index concurrently, using the CONCURRENTLY parameter:

CREATE INDEX CONCURRENTLY customer_name_index ON customers (name);

Creating indexes concurrently in Rails

For Rails users, there is an easy way to create concurrent indexes with a database migration:

class AddNameIndexToCustomers < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index :customers, :name, unique: true, algorithm: :concurrently
  end
end

Closing

There are some caveats attached to this method, that are covered in detail in the documentation. However in most cases involving production databases, creating indexes concurrently will be the best option.

Links

PostgreSQL Documentation - Building Indexes Concurrently Rails documentation - #add_index