Heavy DB Index Operation

Overview

The heavy-db-index-operation skill helps you generate migration modules that create, drop, or rename database indexes on large tables in a controlled, non-blocking manner. These operations use the Explorer.Migrator.HeavyDbIndexOperation behavior and are tracked via Explorer.Migrator.MigrationStatus.

What this skill generates:

1. Migration module files (create/drop/rename) in apps/explorer/lib/explorer/migrator/heavy_db_index_operation/
2. Updates to apps/explorer/lib/explorer/chain/cache/background_migrations.ex:
   • Cache keys for tracking completion status
   • Module aliases
   • Fallback handlers for cache population

When to Use

• When creating new indexes on large tables (logs, internal_transactions, token_transfers, addresses, transactions, blocks, etc.)
• When dropping existing indexes as part of schema optimization
• When renaming indexes (typically as the final step in a create → drop → rename workflow)
• When the index operation might take significant time and should run in the background
• When you need to track the progress of index creation/deletion/rename
• When index operations need to depend on other completed migrations
• When you want CONCURRENT index operations on PostgreSQL

Module Structure

Each heavy index operation module must implement the Explorer.Migrator.HeavyDbIndexOperation behavior with these callbacks:

Required Callbacks

1. migration_name/0 - Automatically generated from operation_type and index_name. Format: heavy_indexes_{operation_type}_{lowercase_index_name}
2. table_name/0 - Returns the table atom (:logs, :internal_transactions, :addresses, etc.)
3. operation_type/0 - Returns :create, :drop, or :rename
4. index_name/0 - Returns the index name as a string (for renames, return the final/new index name)
5. dependent_from_migrations/0 - Returns list of migration names this depends on (or [])
6. db_index_operation/0 - Executes the actual index creation/deletion/rename
7. check_db_index_operation_progress/0 - Checks operation progress
8. db_index_operation_status/0 - Returns operation status
9. restart_db_index_operation/0 - Restarts the operation if needed
10. running_other_heavy_migration_exists?/1 - Checks for conflicting migrations
11. update_cache/0 - Updates the BackgroundMigrations cache when migration completes

Index Definition Methods

Method 1: Using @table_columns (Simple Indexes)

Use this for straightforward indexes on one or more columns:

@table_columns ["address_hash", "block_number DESC", "index DESC"]

@impl HeavyDbIndexOperation
def db_index_operation do
  HeavyDbIndexOperationHelper.create_db_index(@index_name, @table_name, @table_columns)
end

When to use:

• Simple multi-column indexes
• No WHERE clause needed
• Standard column ordering acceptable
• Most common use case

Method 2: Using @query_string (Complex Indexes)

Use this for more complex index definitions:

@query_string """
CREATE INDEX #{HeavyDbIndexOperationHelper.add_concurrently_flag?()} IF NOT EXISTS "#{@index_name}"
ON #{@table_name} ((1))
WHERE verified = true;
"""

@impl HeavyDbIndexOperation
def db_index_operation do
  HeavyDbIndexOperationHelper.create_db_index(@query_string)
end

When to use:

• Partial indexes (with WHERE clause)
• Expression indexes (e.g., ((1)) for existence check)
• Custom index types (GIN, GIST, etc.)
• Fine-grained control over SQL

Naming Conventions

Module Names

• Creation: CreateTableNameColumnNameIndex

  • Example: CreateLogsAddressHashBlockNumberDescIndexDescIndex
  • Example: CreateAddressesVerifiedIndex

• Deletion: DropTableNameIndexName

  • Example: DropInternalTransactionsCreatedContractAddressHashPartialIndex
  • Example: DropLogsAddressHashIndex

• Renaming: RenameOldIndexNameToNewIndexName or RenameTableNameIndexDescriptor

  • Example: RenameTransactions2ndCreatedContractAddressHashWithPendingIndexA

Index Names

• Follow PostgreSQL naming: table_name_column_name_suffix_index
• For partial indexes: include _partial in the name
• For descending columns: include _desc in the name
• Examples:
  • logs_address_hash_block_number_DESC_index_DESC_index
  • addresses_verified_index
  • internal_transactions_created_contract_address_hash_partial_index

File Names

• Convert module name to snake_case
• Example: CreateLogsAddressHashIndex → create_logs_address_hash_index.ex

Dependencies via dependent_from_migrations/0

Specify migrations that must complete before this one runs:

# No dependencies
@impl HeavyDbIndexOperation
def dependent_from_migrations, do: []

# Depends on another migration
alias Explorer.Migrator.EmptyInternalTransactionsData

@impl HeavyDbIndexOperation
def dependent_from_migrations do
  [EmptyInternalTransactionsData.migration_name()]
end

# Multiple dependencies
alias Explorer.Migrator.HeavyDbIndexOperation.{
  DropLogsIndexIndex,
  DropLogsBlockNumberAscIndexAscIndex
}

@impl HeavyDbIndexOperation
def dependent_from_migrations do
  [
    DropLogsIndexIndex.migration_name(),
    DropLogsBlockNumberAscIndexAscIndex.migration_name()
  ]
end

Complete Example: Creating an Index

defmodule Explorer.Migrator.HeavyDbIndexOperation.CreateLogsAddressHashBlockNumberDescIndexDescIndex do
  @moduledoc """
  Create B-tree index `logs_address_hash_block_number_DESC_index_DESC_index` on `logs` table 
  for (`address_hash`, `block_number DESC`, `index DESC`) columns.
  """

  use Explorer.Migrator.HeavyDbIndexOperation

  require Logger

  alias Explorer.Chain.Cache.BackgroundMigrations
  alias Explorer.Migrator.{HeavyDbIndexOperation, MigrationStatus}
  alias Explorer.Migrator.HeavyDbIndexOperation.Helper, as: HeavyDbIndexOperationHelper

  @table_name :logs
  @index_name "logs_address_hash_block_number_DESC_index_DESC_index"
  @operation_type :create
  @table_columns ["address_hash", "block_number DESC", "index DESC"]

  @impl HeavyDbIndexOperation
  def table_name, do: @table_name

  @impl HeavyDbIndexOperation
  def operation_type, do: @operation_type

  @impl HeavyDbIndexOperation
  def index_name, do: @index_name

  @impl HeavyDbIndexOperation
  def dependent_from_migrations, do: []

  @impl HeavyDbIndexOperation
  def db_index_operation do
    HeavyDbIndexOperationHelper.create_db_index(@index_name, @table_name, @table_columns)
  end

  @impl HeavyDbIndexOperation
  def check_db_index_operation_progress do
    operation = HeavyDbIndexOperationHelper.create_index_query_string(@index_name, @table_name, @table_columns)
    HeavyDbIndexOperationHelper.check_db_index_operation_progress(@index_name, operation)
  end

  @impl HeavyDbIndexOperation
  def db_index_operation_status do
    HeavyDbIndexOperationHelper.db_index_creation_status(@index_name)
  end

  @impl HeavyDbIndexOperation
  def restart_db_index_operation do
    HeavyDbIndexOperationHelper.safely_drop_db_index(@index_name)
  end

  @impl HeavyDbIndexOperation
  def running_other_heavy_migration_exists?(migration_name) do
    MigrationStatus.running_other_heavy_migration_for_table_exists?(@table_name, migration_name)
  end

  @impl HeavyDbIndexOperation
  def update_cache do
    BackgroundMigrations.set_heavy_indexes_create_logs_address_hash_block_number_desc_index_desc_index_finished(
      true
    )
  end
end

Complete Example: Dropping an Index

defmodule Explorer.Migrator.HeavyDbIndexOperation.DropInternalTransactionsCreatedContractAddressHashPartialIndex do
  @moduledoc """
  Drops index "internal_transactions_created_contract_address_hash_partial_index" on 
  internal_transactions(created_contract_address_hash, block_number DESC, transaction_index DESC, index DESC).
  """

  use Explorer.Migrator.HeavyDbIndexOperation

  alias Explorer.Chain.Cache.BackgroundMigrations
  alias Explorer.Migrator.{EmptyInternalTransactionsData, HeavyDbIndexOperation, MigrationStatus}
  alias Explorer.Migrator.HeavyDbIndexOperation.Helper, as: HeavyDbIndexOperationHelper

  @table_name :internal_transactions
  @index_name "internal_transactions_created_contract_address_hash_partial_index"
  @operation_type :drop

  @impl HeavyDbIndexOperation
  def table_name, do: @table_name

  @impl HeavyDbIndexOperation
  def operation_type, do: @operation_type

  @impl HeavyDbIndexOperation
  def index_name, do: @index_name

  @impl HeavyDbIndexOperation
  def dependent_from_migrations do
    [EmptyInternalTransactionsData.migration_name()]
  end

  @impl HeavyDbIndexOperation
  def db_index_operation do
    HeavyDbIndexOperationHelper.safely_drop_db_index(@index_name)
  end

  @impl HeavyDbIndexOperation
  def check_db_index_operation_progress do
    operation = HeavyDbIndexOperationHelper.drop_index_query_string(@index_name)
    HeavyDbIndexOperationHelper.check_db_index_operation_progress(@index_name, operation)
  end

  @impl HeavyDbIndexOperation
  def db_index_operation_status do
    HeavyDbIndexOperationHelper.db_index_dropping_status(@index_name)
  end

  @impl HeavyDbIndexOperation
  def restart_db_index_operation do
    HeavyDbIndexOperationHelper.safely_drop_db_index(@index_name)
  end

  @impl HeavyDbIndexOperation
  def running_other_heavy_migration_exists?(migration_name) do
    MigrationStatus.running_other_heavy_migration_for_table_exists?(@table_name, migration_name)
  end

  @impl HeavyDbIndexOperation
  def update_cache do
    BackgroundMigrations.set_heavy_indexes_drop_internal_transactions_created_contract_address_hash_partial_index_finished(
      true
    )
  end
end

Supported Table Names

Valid values for @table_name (from behavior typespec):

• :addresses
• :address_coin_balances
• :address_current_token_balances
• :address_token_balances
• :blocks
• :internal_transactions
• :logs
• :token_transfers
• :tokens
• :transactions

File Location

All generated modules must be placed in:

apps/explorer/lib/explorer/migrator/heavy_db_index_operation/

Required Aliases

Standard aliases to include:

alias Explorer.Chain.Cache.BackgroundMigrations
alias Explorer.Migrator.{HeavyDbIndexOperation, MigrationStatus}
alias Explorer.Migrator.HeavyDbIndexOperation.Helper, as: HeavyDbIndexOperationHelper

For dependencies, add specific aliases:

alias Explorer.Migrator.EmptyInternalTransactionsData

Helper Functions Available

For Index Creation:

• HeavyDbIndexOperationHelper.create_db_index/1 - With query string
• HeavyDbIndexOperationHelper.create_db_index/3 - With index name, table, columns
• HeavyDbIndexOperationHelper.create_index_query_string/3 - Generate query string
• HeavyDbIndexOperationHelper.db_index_creation_status/1 - Check creation status
• HeavyDbIndexOperationHelper.add_concurrently_flag?/0 - For CONCURRENT keyword

For Index Deletion:

• HeavyDbIndexOperationHelper.safely_drop_db_index/1 - Drop with safety checks
• HeavyDbIndexOperationHelper.drop_index_query_string/1 - Generate drop query
• HeavyDbIndexOperationHelper.db_index_dropping_status/1 - Check dropping status

Common:

• HeavyDbIndexOperationHelper.check_db_index_operation_progress/2 - Monitor progress

Cache Invalidation

When dropping indexes, you may need to invalidate caches as shown in the module docstring:

@impl HeavyDbIndexOperation
def restart_db_index_operation do
  HeavyDbIndexOperationHelper.safely_drop_db_index(@index_name)
  BackgroundMigrations.invalidate_cache(__MODULE__.migration_name())
end

Complete Example: Renaming an Index

For rename operations (typically used after create + drop to swap indexes):

defmodule Explorer.Migrator.HeavyDbIndexOperation.RenameTransactions2ndCreatedContractAddressHashWithPendingIndexA do
  @moduledoc """
  Renames index "transactions_2nd_created_contract_address_hash_with_pending_index_a" 
  to "transactions_created_contract_address_hash_with_pending_index_a".
  """

  use Explorer.Migrator.HeavyDbIndexOperation

  require Logger

  alias Explorer.Chain.Cache.BackgroundMigrations
  alias Explorer.Migrator.{HeavyDbIndexOperation, MigrationStatus}
  alias Explorer.Migrator.HeavyDbIndexOperation.Helper, as: HeavyDbIndexOperationHelper
  alias Explorer.Migrator.HeavyDbIndexOperation.DropTransactionsCreatedContractAddressHashWithPendingIndexA
  alias Explorer.Repo

  @table_name :transactions
  @old_index_name "transactions_2nd_created_contract_address_hash_with_pending_index_a"
  @new_index_name "transactions_created_contract_address_hash_with_pending_index_a"
  @operation_type :rename

  # Note: migration_name will be:
  # "heavy_indexes_rename_transactions_created_contract_address_hash_with_pending_index_a"

  @impl HeavyDbIndexOperation
  def table_name, do: @table_name

  @impl HeavyDbIndexOperation
  def operation_type, do: @operation_type

  @impl HeavyDbIndexOperation
  def index_name, do: @new_index_name

  @impl HeavyDbIndexOperation
  def dependent_from_migrations do
    [DropTransactionsCreatedContractAddressHashWithPendingIndexA.migration_name()]
  end

  @impl HeavyDbIndexOperation
  # sobelow_skip ["SQL"]
  def db_index_operation do
    case Repo.query(rename_index_query_string(), [], timeout: :infinity) do
      {:ok, _} ->
        :ok

      {:error, error} ->
        Logger.error("Failed to rename index from #{@old_index_name} to #{@new_index_name}: #{inspect(error)}")
        :error
    end
  end

  @impl HeavyDbIndexOperation
  def check_db_index_operation_progress do
    HeavyDbIndexOperationHelper.check_db_index_operation_progress(@new_index_name, rename_index_query_string())
  end

  @impl HeavyDbIndexOperation
  def db_index_operation_status do
    old_index_status = HeavyDbIndexOperationHelper.db_index_exists_and_valid?(@old_index_name)
    new_index_status = HeavyDbIndexOperationHelper.db_index_exists_and_valid?(@new_index_name)

    cond do
      # Rename completed: old index doesn't exist, new index exists and is valid
      old_index_status == %{exists?: false, valid?: nil} and new_index_status == %{exists?: true, valid?: true} ->
        :completed

      # Rename not started: old index exists, new index doesn't exist
      old_index_status == %{exists?: true, valid?: true} and new_index_status == %{exists?: false, valid?: nil} ->
        :not_initialized

      # Unknown state
      true ->
        :unknown
    end
  end

  @impl HeavyDbIndexOperation
  def restart_db_index_operation do
    # To restart, we need to rename back to the old name
    case Repo.query(reverse_rename_index_query_string(), [], timeout: :infinity) do
      {:ok, _} ->
        :ok

      {:error, error} ->
        Logger.error("Failed to reverse rename index from #{@new_index_name} to #{@old_index_name}: #{inspect(error)}")
        :error
    end
  end

  @impl HeavyDbIndexOperation
  def running_other_heavy_migration_exists?(migration_name) do
    MigrationStatus.running_other_heavy_migration_for_table_exists?(@table_name, migration_name)
  end

  @impl HeavyDbIndexOperation
  def update_cache do
    BackgroundMigrations.set_heavy_indexes_rename_transactions_created_contract_address_hash_with_pending_index_a_finished(
      true
    )
  end

  defp rename_index_query_string do
    "ALTER INDEX #{@old_index_name} RENAME TO #{@new_index_name};"
  end

  defp reverse_rename_index_query_string do
    "ALTER INDEX #{@new_index_name} RENAME TO #{@old_index_name};"
  end
end

When to use rename operations:

• After creating a new index and dropping an old one
• To swap temporary index names with permanent ones
• Part of a create → drop → rename workflow for index replacement

Important notes for rename operations:

• Use @operation_type :rename (not :create)
• index_name/0 should return the new (final) index name
• The migration name will be heavy_indexes_rename_{new_index_name_lowercase}
• Example: For index_name = "transactions_created_contract_address_hash_with_pending_index_a", the migration name is "heavy_indexes_rename_transactions_created_contract_address_hash_with_pending_index_a"

Updating BackgroundMigrations Cache

After creating migration modules, you must update the cache tracking in apps/explorer/lib/explorer/chain/cache/background_migrations.ex:

Step 1: Add Cache Keys

Add keys for each new migration at the top of the module:

use Explorer.Chain.MapCache,
  name: :background_migrations_status,
  # ... existing keys ...
  key: :heavy_indexes_create_transactions_2nd_created_contract_address_hash_with_pending_index_a_finished,
  key: :heavy_indexes_drop_transactions_created_contract_address_hash_with_pending_index_a_finished,
  key: :heavy_indexes_rename_transactions_created_contract_address_hash_with_pending_index_a_finished

Step 2: Add Module Aliases

Add aliases in the HeavyDbIndexOperation alias block:

alias Explorer.Migrator.HeavyDbIndexOperation.{
  # ... existing aliases ...
  CreateTransactions2ndCreatedContractAddressHashWithPendingIndexA,
  DropTransactionsCreatedContractAddressHashWithPendingIndexA,
  RenameTransactions2ndCreatedContractAddressHashWithPendingIndexA
}

Step 3: Add Fallback Handlers

Add handle_fallback/1 functions for each migration:

defp handle_fallback(:heavy_indexes_create_transactions_2nd_created_contract_address_hash_with_pending_index_a_finished) do
  set_and_return_migration_status(
    CreateTransactions2ndCreatedContractAddressHashWithPendingIndexA,
    &set_heavy_indexes_create_transactions_2nd_created_contract_address_hash_with_pending_index_a_finished/1
  )
end

defp handle_fallback(:heavy_indexes_drop_transactions_created_contract_address_hash_with_pending_index_a_finished) do
  set_and_return_migration_status(
    DropTransactionsCreatedContractAddressHashWithPendingIndexA,
    &set_heavy_indexes_drop_transactions_created_contract_address_hash_with_pending_index_a_finished/1
  )
end

defp handle_fallback(:heavy_indexes_rename_transactions_created_contract_address_hash_with_pending_index_a_finished) do
  set_and_return_migration_status(
    RenameTransactions2ndCreatedContractAddressHashWithPendingIndexA,
    &set_heavy_indexes_rename_transactions_created_contract_address_hash_with_pending_index_a_finished/1
  )
end

Cache key naming convention:

• Format: heavy_indexes_{operation}_{snake_case_index_name}_finished
• Operation: create, drop, rename, etc.
• Always ends with _finished

Step 4: Add to Application Supervisor

Add each migration module to the application supervisor in apps/explorer/lib/explorer/application.ex:

Find the section with other heavy DB index operations and add:

configure_mode_dependent_process(
  Explorer.Migrator.HeavyDbIndexOperation.CreateTransactions2ndCreatedContractAddressHashWithPendingIndexA,
  :indexer
),
configure_mode_dependent_process(
  Explorer.Migrator.HeavyDbIndexOperation.DropTransactionsCreatedContractAddressHashWithPendingIndexA,
  :indexer
),
configure_mode_dependent_process(
  Explorer.Migrator.HeavyDbIndexOperation.RenameTransactions2ndCreatedContractAddressHashWithPendingIndexA,
  :indexer
),

Important: These entries must be added to start the migration processes during application startup.

Update Cache Implementation

Each migration module must implement update_cache/0:

@impl HeavyDbIndexOperation
def update_cache do
  BackgroundMigrations.set_heavy_indexes_create_my_index_finished(true)
end

The setter function name follows: set_heavy_indexes_{operation}_{index_name}_finished/1

Checklist for New Modules

• Module name follows Create*/Drop*/Rename* convention
• File name is snake_case version of module name
• @moduledoc describes the index and its columns
• use Explorer.Migrator.HeavyDbIndexOperation declared near module top
• All required callbacks implemented
• @table_name, @index_name, @operation_type module attributes defined
• Index definition uses @table_columns OR @query_string (or custom for rename)
• Dependencies specified via dependent_from_migrations/0
• Proper aliases added at module top
• File saved in apps/explorer/lib/explorer/migrator/heavy_db_index_operation/
• update_cache/0 implemented with correct setter name
• BackgroundMigrations cache updated with key, alias, and fallback handler
• Application.ex updated with configure_mode_dependent_process entry

Common Pitfalls

❌ Incorrect table name - Must be one of the supported atoms
❌ Missing dependencies - If index depends on other migrations, specify them
❌ Wrong helper function - Use creation helpers for :create, dropping helpers for :drop
❌ Inconsistent naming - Index name should match module name semantically
❌ Missing CONCURRENT - Use add_concurrently_flag?() in query strings
❌ No progress tracking - Always implement check_db_index_operation_progress/0
❌ Forgot cache updates - Must update BackgroundMigrations cache module
❌ Missing update_cache/0 - Every module must implement this callback

Workflow for Index Replacement (Create → Drop → Rename)

When replacing an existing index with a new version (e.g., adding a WHERE clause):

1. Create the new index with a temporary name (e.g., _2nd_ prefix)
   • Depends on: latest heavy DB operation on the table
2. Drop the old index
   • Depends on: the create operation completing
3. Rename the new index to the old index name
   • Depends on: the drop operation completing

This ensures zero downtime - the old index remains available until the new one is ready.

References

• Behavior definition: apps/explorer/lib/explorer/migrator/heavy_db_index_operation.ex
• README: apps/explorer/lib/explorer/migrator/heavy_db_index_operation/README.md
• Helper module: apps/explorer/lib/explorer/migrator/heavy_db_index_operation/helper.ex