Posted over 7 years ago. Visible to the public. Repeats.

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.

Copy
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:

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

Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Owner of this card:

Avatar
Arne Hartherz
Last edit:
over 4 years ago
by Arne Hartherz
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more