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

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


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.

PostgreSQL Documentation - Building Indexes Concurrently Rails documentation - #add_index