Ecto 3.0 Concurrent Index Creation: Managing Failed Migrations and Manual Cleanup

Detecting a partially created concurrent index

When create_index/3 is called with concurrently: true, Ecto runs the statement outside a transaction. If the migration fails, PostgreSQL may leave an index entry with indisvalid = false. You can detect it by querying the pg_index catalog:

SELECT i.indisvalid, c.relname AS index_name\nFROM pg_index i\nJOIN pg_class c ON i.indexrelid = c.oid\nJOIN pg_namespace n ON c.relnamespace = n.oid\nWHERE n.nspname = current_schema()\n  AND c.relname = 'your_index_name';

A result with indisvalid = false (or no row) indicates a broken index that must be removed before retrying.

Safely dropping the orphaned index

Use the concurrent drop form to avoid blocking reads/writes:

DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

This command waits for any pre‑existing transactions that touched the table to finish, then removes the index definition without taking an exclusive lock on the table.

Automation inside an Ecto migration

Ecto provides no built‑in helper for this cleanup, but you can wrap the index creation in a try/rescue block and execute the concurrent drop manually. Remember to disable the migration transaction:

@disable_transaction true\n\ndef up do\n  # Attempt to clean a possible leftover from a prior failed run\n  try do\n    create_index(:my_table, [:my_column], concurrently: true)\n  rescue\n    Exception -> \n      execute(\"DROP INDEX CONCURRENTLY IF EXISTS index_my_table_on_my_column\")\n      # Re‑try the creation after cleanup\n      create_index(:my_table, [:my_column], concurrently: true)\n  end\nend\n\ndef down do\n  drop_index(:my_table, [:my_column], concurrently: true)\nend

If you prefer a proactive check, run the SELECT indisvalid query first and conditionally execute the drop.

Missing diagnostic detail

To confirm that the index is indeed invalid, you need the exact index name as generated by Ecto (including any prefix/suffix). If you are unsure of the name, run:

SELECT indexrelid::regclass AS index_name\nFROM pg_index i\nJOIN pg_class c ON i.indexrelid = c.oid\nWHERE indisvalid = false\n  AND c.relname = 'my_table';

Providing that name lets you target the drop precisely.