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 (
DELETE) will block.
Therefore it is best in this situation to create the index concurrently, using the
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
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.