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:

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.
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

makandra.com

Say thanks25

Last edit

2023-07-04

Michael Leimstädtner

License

Source code in this card is licensed under the MIT License.

Posted by Arne Hartherz to makandra dev (2015-04-24 09:15)