Menu

CREATE INDEX on a live Postgres table locks writes. CONCURRENTLY fixes that, if you keep it out of a transaction
📰

CREATE INDEX on a live Postgres table locks writes. CONCURRENTLY fixes that, if you keep it out of a transaction

DEV Community: postgres·labyrinthlab·17 days ago
#C9PoBii2
#devtopostgresql#devto#dev#index#create
Reading 0:00
15s threshold

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 );

Enter fullscreen mode

Exit fullscreen mode

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 outright: a standard index build "locks out writes (but not reads) on the table until it's done."

The fix is also in those docs:

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders ( customer_id );

Enter fullscreen mode

Exit fullscreen mode

CONCURRENTLY builds without taking locks that block concurrent inserts, updates, or deletes.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.