In a Rails application using Postgres 16, I'm working on an automated process to detect indexes with high fragmentation, and reindex them whenever a certain threshold is reached. I'm not having any problem with the detection of the fragmented indexes, my problem comes with the actual reindexing part.
If this was a regular Rails migration, I would implement a low lock_timeout
+ retries to make sure I don't block other queries on the table for too long, and I would run a migration like
class ReindexWhateverIndex < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def up
safety_assured do # From strong_migrations gem
with_lock_acquisition_retries do # custom implementation to retry lock acquisition errors
execute "REINDEX INDEX CONCURRENTLY <my-index-name>;"
end
end
end
def down
# no-op
end
end
However, within let's say an ActiveJob job, I don't know how to open a transaction with a low lock_timeout
, just to run this reindexing. I want to make sure the low timeout remains untouched after this transaction has executed, even if it fails.
There is no need to mess with lock_timeout
. With REINDEX CONCURRENTLY
, the operation won't be blocked by concurrent data modifications.