ruby-on-railspostgresqllocking

Configure lock_timeout in Rails application for reindexing outside migrations


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.


Solution

  • There is no need to mess with lock_timeout. With REINDEX CONCURRENTLY, the operation won't be blocked by concurrent data modifications.