Index tables from production console

Sometimes you may need to add an index or two in production outside of normal deployment. This is a relatively safe operation, especially if you are the only one tinkering and gem "strong_migrations" is being used. I also recommend only doing this after having gem "pghero" configured, so you can easily inspect things if something goes wrong.

As part of my de-NULL-ification struggle, I need to backfill a column, but to efficiently query for records missing a value, I need the index, so I've decided to add the index manually in production console and add a corresponding migration later to have other envs in sync for this change. A little bonus from this is that I have more control over the indexing process/locks and eventual deployment will be quicker and safer since the migration will not apply any changes.

# run in prod console
ActiveRecord::Migration.add_index :<the_table>, :<the_column>, algorithm: :concurrently, if_not_exists: true

# and drop if something goes wrong, like failing to get a lock
ActiveRecord::Migration.remove_index :<the_table>, name: :index_<the_table>_on_<the_column>

Getting a lock may be complexed by background jobs. Try sleeping them while you're trying to add the index.

Once it goes through, make sure to commit a corresponding migration so that all envs (your local, other dev envs, etc.) are in sync and structure file lists this index:

class IndexColumnOnThings < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index(
      :things, :column, algorithm: :concurrently, if_not_exists: true
    )
  end
end