Hey Peter, glad you’re back up and running. Wanted to share some more context and a workaround for anyone else who hits this.
Why CONCURRENTLY wasn’t used in the migration: CREATE INDEX CONCURRENTLY cannot run inside a transaction, and Alembic (our migration tool) wraps each migration in one.
Workaround for large databases: If you need to get past the migration without the table lock, you can pre-create a no-op index with the same name before running the migration. Since the migration uses IF NOT EXISTS, it checks the index name and skips if it already exists:
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_spans_session_id
ON spans ((attributes #>> '{session,id}'))
WHERE false;
The WHERE false makes it instant — PostgreSQL recognizes the predicate is always false and skips the heap scan entirely, so the index is created in milliseconds regardless of table size. Once the migration sees the name exists, it’s a no-op.
You can then backfill the real index at your leisure:
DROP INDEX CONCURRENTLY IF EXISTS ix_spans_session_id;
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_spans_session_id
ON spans (((attributes #>> '{session,id}')::varchar))
WHERE ((attributes #>> '{session,id}')::varchar) IS NOT NULL;
Sorry again for the disruption, and thanks for the callout.
