This content originally appeared on DEV Community and was authored by Augusts Bautra
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
This content originally appeared on DEV Community and was authored by Augusts Bautra
Augusts Bautra | Sciencx (2024-08-22T10:16:24+00:00) Index tables from production console. Retrieved from https://www.scien.cx/2024/08/22/index-tables-from-production-console/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.