Add index validation for NullBatchingStrategy in background migrations

Problem

Background migrations using NullBatchingStrategy can cause severe performance issues on large registry tables when the required partial index is missing and the background migration developer is unaware. Currently, there's no validation to ensure that an appropriate index exists on the paginating column for efficiently selecting null records.

This was highlighted here: !2504 (comment 2736509525)

Current Behavior

When a background migration uses NullBatchingStrategy to process records with null values in specific columns, the migration worker executes queries like:

SELECT EXISTS(SELECT 1 FROM large_table WHERE column_name IS NULL LIMIT 1

to check if more bbm jobs should be run, or

.... WHERE column_name IS NULL LIMIT 1000

to update the column as part of the migration

Without a partial index on (column_name) WHERE column_name IS NULL, this query is not efficient and may perform a full table scan on potentially billions of rows, causing:

  • Excessive database CPU and I/O usage
  • Slow batch selection (minutes instead of milliseconds)
  • Overall migration performance degradation
  • Risk of impacting production registry operations

Proposed Solution

While BBM developers are expected to add the index, we can not always trust that this is done. We want to implement a guard rail that validates the presence of an appropriate partial index before allowing background migrations with NullBatchingStrategy to proceed.

Requirements

  1. When a migration uses NullBatchingStrategy, check for a helpful index matching the target column target_column `
  2. If the index is missing, the migration should fail to start with a clear error message and log the missing index details

Impact

This guard rail will prevent accidental performance degradation during background migrations and ensure efficient processing of large-scale data transformations in the container registry metadata database.

Edited by SAhmed