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

Updated . Posted . Visible to the public. Repeats.

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.


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

    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)

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 | 
    "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
Last edit
Michael Leimstädtner
Source code in this card is licensed under the MIT License.
Posted by Arne Hartherz to makandra dev (2015-04-24 09:15)