Building an index without stopping writes
A plain CREATE INDEX holds SHARE for the whole build, and SHARE conflicts with every write. CONCURRENTLY takes two and a half times as long and blocks nothing.
9 minutes · SHARE · SHARE UPDATE EXCLUSIVE · invalid index · two-pass build
The plain form stops every write
CREATE INDEX takes a SHARE lock on the table. SHARE does not conflict with ACCESS SHARE, so reads carry on — but it does conflict with ROW EXCLUSIVE, so every INSERT, UPDATE and DELETE waits until the build is finished.
On a forty-one-million-row table that is well over a minute during which the shop cannot take an order. The reads look fine the whole time, which is exactly why this one gets shipped: the dashboard everybody watches stays green.
CREATE INDEX orders_status_idx ON orders (status);
Watch: The blocked-writes figure, and how many queries were turned away when the pool ran out.
What CONCURRENTLY costs
The concurrent form makes two passes over the table and then waits for every transaction older than the build to finish, so it is meaningfully slower on the clock.
Predict
CREATE INDEX CONCURRENTLY takes about two and a half times as long. What does that buy?
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
Watch: Longer on the clock. Zero blocked writes, zero queries turned away.
The two things CONCURRENTLY takes away
It cannot run inside a transaction block. That means it cannot be one step of an all-or-nothing migration, and a tool that wraps every migration in BEGIN and COMMIT will refuse it.
And it can fail. When it does, PostgreSQL leaves the half-built index behind, marked invalid: the planner will not use it, but every write still maintains it. It has to be found and dropped by hand, and nothing tells you it is there.
An invalid index is the one failure mode of CONCURRENTLY that is worse than the blocking form's, because it is silent. It costs write throughput and buys nothing until somebody notices.
MySQLInnoDB builds most secondary indexes with ALGORITHM=INPLACE and LOCK=NONE, which permits concurrent DML without a separate keyword. The failure mode is different too: there is no invalid-index state to clean up.
Dropping one is not free either
DROP INDEX is instant — it unlinks a file. But it takes ACCESS EXCLUSIVE on the table, not just on the index, and ACCESS EXCLUSIVE stops every SELECT. Instant does not mean safe: it still has to get the lock, and getting it means waiting behind whatever is running.
DROP INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead, for the same reason the build does.
DROP INDEX CONCURRENTLY orders_placed_at_idx;
Watch: The lock level, next to what the plain form would have taken.
The database
The same one every claim above was made about. Nothing here is graded — run whatever you like.
Answer the 1 prediction above first.
All tracks