Adding indexes to Postgresql in Rails + concurrently indexes

02 Sep Adding indexes to Postgresql in Rails + concurrently indexes

In this post, I’m going to write down how to add indexes to your tables in Rails if you are using Postgresql.

Primary keys
You don’t need to create these indexes manually PostgreSQL automatically creates an index for primary keys to enforce uniqueness

If you want to add an index to an existent table you can create a new migration and include you indexes something as follow:

rails g migration AddIndexPatientsQuestions

In the file generated you can include something like:

class AddIndexPatientsQuestions < ActiveRecord::Migration
  def up
    add_index :patients, :user_id
    add_index :questions, :user_id
    add_index :patients, :custom_fields, algorithm: :concurrently
  end

  def down
    remove_index :patients, :user_id
    remove_index :questions, :user_id
    remove_index :patients, :custom_fields
  end
end

If you notice in the previous migration I’m using the algorithm: :concurrently which allows you to create your indexes concurrently so that your table isn’t locked as the index builds(it is included in the latest Rails 4.XX version).

If you want to create a new table and include and index in the same migration you can do it like so:

class CreateQuestions < ActiveRecord::Migration
def change
  create_table :questions do |t|
    t.string :section_name
    t.boolean :required, default: false
    # I'm using postgres_ext
    t.text :custom_fields, array: true

    t.timestamps null: false
  end
end

add_index :patients, :custom_fields, algorithm: :concurrently
end

Now let’s take a quick look a different kind of indexes:

Partial indexes

 add_index :questions, :required, where: "required = false"

Sorted indexes

 add_index :questions, :created_at, order: { created_at: "DESC NULLS LAST" }

Unique indexes

 add_index :users, :email, unique: true

Foreign keys

 add_index :questions, :user_id