CREATE INDEX CONCURRENTLY Fails in Transactions

CREATE INDEX CONCURRENTLY fails within a transaction block because Postgres implicitly wraps multi-statement SQL queries, even those without explicit BEGIN statements, into a single transaction. This prevents operations like CONCURRENTLY index creation, which require exclusive locks, from running.

What went wrong

Our automated trading system at Tradewink relies on robust database migrations to keep our infrastructure in sync. During a routine deployment, we encountered a critical failure: ActiveSQLTransactionError: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. This error halted our deployment pipeline and, more importantly, prevented a crucial index from being created on a high-traffic table. The migration file in question contained a sequence of operations: DROP INDEX, CREATE INDEX CONCURRENTLY, and ANALYZE. When executed as a single unit by our migration runner, the CREATE INDEX CONCURRENTLY statement, which is designed for zero-downtime operations, was blocked by the implicit transaction.

Why it happens

The root cause lies in how Postgres handles query execution, particularly with its simple query protocol. When you send a multi-statement SQL string to Postgres, even if you haven't explicitly written BEGIN, the database wraps the entire string in an implicit transaction block. This is a safety mechanism to ensure atomicity for a series of commands. However, CREATE INDEX CONCURRENTLY has a specific requirement: it cannot run within a transaction block because it needs to acquire certain locks that are incompatible with transactional isolation levels. Other commands that also have this restriction include VACUUM and ANALYZE when used in certain contexts. When these restricted commands are part of a larger, implicitly transacted SQL string, Postgres throws the cannot run inside a transaction block error.

What we changed

To address this, we modified our migration runner. The core change involves detecting statements that cannot run within a transaction block. Specifically, our runner now identifies CREATE INDEX CONCURRENTLY, VACUUM, and ANALYZE within a migration file. Upon detection, instead of executing the entire file as a single execute call, it splits the file into individual statements based on semicolons. Each of these individual statements is then executed separately. This ensures that CREATE INDEX CONCURRENTLY is run in its own context, outside of any implicit or explicit transaction block, allowing it to acquire the necessary locks and complete successfully without blocking other operations. For destructive column changes, we now wrap them in anonymous DO blocks. These blocks include checks against the information schema to verify column existence and data type before proceeding, preventing crashes if a migration is re-run against an already-migrated database.

How to check your own system

To ensure your automated trading system's database migrations are safe and won't encounter this issue, perform the following checks:

• Review Migration Files: Manually inspect your migration files for any sequence of statements that includes CREATE INDEX CONCURRENTLY, VACUUM, or ANALYZE alongside other DDL or DML operations within a single execution block.
• Test Migration Runner Logic: Verify that your migration runner splits multi-statement SQL files into individual commands for execution, especially when encountering the aforementioned commands.
• Simulate Re-runs: Test re-running migrations against a database that has already had the migration applied. Ensure that destructive changes are safely handled, for example, by checking for the existence of objects before attempting to create or drop them.

We also learned that migration numbering can be tricky. When multiple development branches merge quickly, migration numbers can collide. We experienced a reused number, renumbered it, only to have two more files land on the same next number. A test now fails the build on duplicate numeric prefixes. Renaming a migration that has already deployed is even worse, as the runner treats the new name as a new migration and attempts to re-run it. This highlights the importance of a robust migration numbering and management strategy.

Disclaimer

This article describes engineering decisions in a trading system. It is not investment advice. Trading involves substantial risk of loss and is not suitable for all investors. Past performance does not guarantee future results. Always do your own research and consider your financial situation before trading.