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