CREATE INDEX on a live Postgres table locks writes. CONCURRENTLY fixes that, if you keep it out of a transaction
Here is the failure. You ship a migration that adds an index on a busy table: CREATE INDEX idx_orders_customer_id ON orders ( customer_id ); Postgres builds the index with a SHARE lock. Other sessions can still SELECT . They cannot INSERT , UPDATE , or DELETE until the build finishes. On a large table that can be minutes or hours of blocked writes. The PostgreSQL CREATE INDEX docs say so…
When a migration adds an index to a frequently accessed Postgres table, PostgreSQL creates the index using a SHARE lock. This prevents other sessions from performing INSERT, UPDATE, or DELETE operations on the table until the index is fully built. This can lead to significant delays, ranging from minutes to hours, especially for large tables. The PostgreSQL documentation explicitly states that a standard index build locks out writes, but not reads, on the table.
To avoid this issue, PostgreSQL provides the CREATE INDEX CONCURRENTLY command. This command builds the index without taking locks that would block concurrent inserts, updates, or deletes. It performs more work, including two table scans and waiting on open transactions, but it is the recommended solution for maintaining read and write operations during index creation.
However, there is a crucial rule to follow when using CREATE INDEX CONCURRENTLY: it cannot be executed within a transaction block. Attempting to do so results in an error: "CREATE INDEX CONCURRENTLY cannot run inside a transaction block." This limitation causes problems for many migration tools that wrap their operations in transactions, such as Flyway and Rails.
To resolve this issue, create a dedicated migration file containing only the CREATE INDEX CONCURRENTLY statement. Configure your migration tool to exclude this migration from being wrapped in a transaction. Additionally, set a reasonable value for the lock_timeout parameter to ensure that blocked locks fail quickly instead of waiting indefinitely.
After the concurrent index build completes, verify that the index is valid by executing a query that checks the index validity status. If the index is found to be invalid, drop it and retry the CREATE INDEX CONCURRENTLY command.
It is common for agents, such as migration tools, to generate plain CREATE INDEX statements without realizing that the concurrent version requires a standalone migration with no surrounding transaction. To prevent this issue, agents should be configured to use CREATE INDEX CONCURRENTLY alone in its own migration, with the lock_timeout set first. This approach ensures that index creation does not interfere with ongoing read and write operations on the table.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.