How to remove index in rails

Question

I have found that I have two "survey_id" columns in my schema and that's been causing some problems for me. Specifically I need to remove the second index as I don't want survey_id to be unique.

 add_index "completions", ["survey_id"], name: "index_completions_on_survey_id"
 add_index "completions", ["survey_id"], name: "index_completions_on_survey_id_and_user_id", unique: true

I've tried

def change
  remove_index "completions", ["survey_id"], name => "index_completions_on_survey_id_and_user_id"
end

and

def change
  remove_index "completions", ["survey_id"], name: "index_completions_on_survey_id_and_user_id"
end

But neither of those seems to work. What's the correct syntax for this migration to remove the index? I feel like this is basic and I'm just missing something.

Lipps

Answer 1

You don't supply the columns in the index when removing one. Try:

remove_index :completions, name: "index_completions_on_survey_id_and_user_id"

Professorate

And if you're using Postgres, you might want to read up on the options, including CONCURRENTLY. – Archivist

The columns aren't required when removing an index, but if this is in a change method and you want it to be reversible, you have to add the columns. – Bookcase

Answer 2

The accepted answer here doesn't work when you need to roll back the migration, it will be given ActiveRecord::IrreversibleMigration error.

remove_index is only reversible if given a :column option.

def change
  remove_index "completions", column: [:survey_id], name: "index_completions_on_survey_id_and_user_id"
end

this will remove the index and also be reversible.

Contractor

Answer 3

From rails console, run following

ActiveRecord::Migration.remove_index "completions", name: "index_completions_on_survey_id_and_user_id"

Landis

This doesn't remove it from schema.rb though. – Manstopper

How do we specify the DB name when running this in the console? – Postnasal

Answer 4

You can supply the column name(s) to remove_index. The remove_index method takes table_name and options as parameters. With the options passed in the name of the index is determined via index_name_for_remove private method, which simply does (if it is an array):

...
column_names = Array(options).map(&:to_s)
...

if column_names.any?
  checks << lambda { |i| i.columns.join('_and_') == column_names.join('_and_') }
end

Examples from the API documentation:

Removes the index on branch_id in the accounts table if exactly one such index exists.

remove_index :accounts, :branch_id

Or

remove_index :accounts, column: :branch_id

Removes the index on branch_id and party_id in the accounts table if exactly one such index exists.

remove_index :accounts, column: [:branch_id, :party_id]

Removes the index named by_branch_party in the accounts table.

remove_index :accounts, name: :by_branch_party

Apart from the above, you could just do:

remove_index :accounts, %i[branch_id party_id]

Guise

Answer 5

This is an expanded suggestion from Eric Walker to use CONCURRENTLY with PostgreSQL 12+ when removing indexes. I would have replied inline but the multiline code formatting wasn’t working.

Here’s Eric’s answer: How to remove index in rails

Here, we’ll create a sample Rails Migration and call out three items needed when doing a index drop with the concurrently option:

• (1) Use disable_ddl_transaction! inside your migration, which is required when using concurrently: true later on
• (2) Check whether the index exists (optional, but nice if you’ve already removed it manually, but are using the migration to keep all DBs in sync)
• (3) Use CONCURRENTLY via Active Record (PG 12+)

class MyMigration < ActiveRecord::Migration[6.0]
  disable_ddl_transaction! # (1)

  def change
    # (2) check existence below
    if index_exists?(:completions, [:survey_id, :user_id], name: 'index_completions_on_survey_id_and_user_id')
      remove_index :completions,
        name: "index_completions_on_survey_id_and_user_id"
        algorithm: :concurrently   # (3) use `CONCURRENTLY`
    end
  end
end

This migration was tested on a 6.0 Rails app, but in Rails 6.1 if_exists: true exists as an argument to remove_index, which is even nicer to use.

More info on if_exists: true https://blog.saeloun.com/2020/02/10/rails-support-for-if_exists-if_not_exists-on-remove_column-add_column-in-migrations/

Ceremonious