How to add `unique` constraint to already existing index by migration

The accepted answer is not exactly safe for huge tables. What might happen is:

1. Index is removed successfully
2. Adding a new index takes forever and/or fails

In this situation you end up having no index at all. And if you have a fair amount of traffic and loads of queries that used to use the index you might run into big trouble.

For large tables I suggest:

1. De-dupe records having the same value on the indexed column.
2. Add a unique index with a different name than the non-unique one (you'll temporarily have two indexes on the same column, with different names):

class AddUniqueUuidIndexToItems < ActiveRecord::Migration[7.1]
  def up
    add_index :items, :uuid, name: index_name, unique: true
  end

  def down
    remove_index :items, column: :uuid, name: index_name
  end

  private

  def index_name
    'unique_index_items_on_uuid'
  end
end

3. Remove the old, non-unique index (cleanup step):

class RemoveNonUniqueIndexFromItemsUuid < ActiveRecord::Migration[7.1]
  def up
    remove_index :items, column: :uuid, name: index_name
  end

  def down
    add_index :items, :uuid, name: index_name
  end

  private

  # old index name, check db/schema.rb to find the actual name
  def index_name
    'index_items_on_uuid'
  end
end

If it's the existing index then you may need to do more than that:

1. Delete duplicated data.
2. Add uniqueness index.

This is the safest way to add uniqueness constraints into existing indexes with large data in production.

class AddStoreIdUniquenessIndexToOrders < ActiveRecord::Migration[5.2]
  disable_ddl_transaction!

  def up
    delete_duplicated_records

    rename_index :orders, :index_orders_on_store_id, :non_uniqueness_index_orders_on_store_id
    add_index :orders, :store_id, unique: true, algorithm: :concurrently
    remove_index :orders, name: :non_uniqueness_index_orders_on_store_id, column: :store_id
  end

  def down
    remove_index :orders, name: :index_orders_on_store_id, column: :store_id
    add_index :orders, :store_id, name: :index_orders_on_store_id
  end

  private

  def delete_duplicated_records
    dup_store_ids = Order.group(:store_id).having('COUNT(*) > 1').pluck(:store_id)
    dup_store_ids.each_slice(400) do |store_ids|
      not_remove_order_ids = Order.where(store_id: store_ids).group(:store_id).having('COUNT(*) > 1').pluck('MIN(id)')
      Order.where(store_id: store_ids).where.not(id: not_remove_order_ids).destroy_all
    end
  end
end

Note:

As you see, I rename the index index_orders_on_store_id before deleting it. It's for performance purposes. This means if the migration fails while adding the new index, and we have to re-run the migration, we’re now executing the query without an index on the retail_orders column.

If you’re doing a big ol’ query against a few hundred thousand rows of data, with the index, it’ll take a few seconds. Without the index, it could take… many minutes.