PSA: "index: true" in Rails migrations does not work as you'd expect

Several Rails migration methods accept index: true as an option to create an index. In some cases (like #add_column), this option is silently discarded. Know what you are doing, or use #add_index instead.

Example

Consider the following migration.

class CreateExamples < ActiveRecord::Migration
  def change
    create_table :examples do |t|
      t.references :category, index: true
      t.boolean :positive, index: true
      t.integer :number_of_participants, index: true
    end

    add_reference :examples, :user, index: true
    add_column :examples, :negative, :boolean, index: true # spoiler alert: this won't work
    add_column :examples, :age, :integer, index: true      # (same here)
  end
end

Now which indexes would you expect to see? Probably not these:

(PostgreSQL) => \d examples
                                 Table "public.examples"
         Column         |  Type   |                       Modifiers                       
------------------------+---------+-------------------------------------------------------
 id                     | integer | not null default nextval('examples_id_seq'::regclass)
 category_id            | integer | 
 positive               | boolean | 
 number_of_participants | integer | 
 user_id                | integer | 
 negative               | boolean | 
 age                    | integer | 
Indexes:
    "examples_pkey" PRIMARY KEY, btree (id)
    "index_examples_on_category_id" btree (category_id)
    "index_examples_on_number_of_participants" btree (number_of_participants)
    "index_examples_on_positive" btree (positive)
    "index_examples_on_user_id" btree (user_id)

So what happened?

  • Rails created indexes for all fields that we added inside our create_table statement.
  • There is an index for user_id that was added via add_reference :examples, :user, index: true
  • There are no indexes for negative or age that were added via add_column, even though their similar counterparts from the create_table statement received an index.
  • rake db:migrate did not raise an error or at least show a warning for the incorrect index: true option passed to add_column.

What should I do?

You have 2 options:

  1. Be careful when using the index: true option. Use it only inside create_table or for add_reference (= add_belongs_to) statements, and use add_index for other cases.
  2. Never use index: true but only use add_index for the sake of consistency.

We suggest you prefer the 2nd.

In any case: When adding indexes you usually do this for performance reasons. So please inspect the database schema, just to confirm your changes actually have an effect.

Arne Hartherz almost 8 years ago