Is CREATE INDEX CONCURRENTLY safe?

For availability, yes: it is the supported way to add an index to a table that is being written to, and it does not block reads or writes for the duration. It is not risk-free in other directions. It takes longer, it cannot run inside a transaction block, it can be stalled indefinitely by somebody else’s open transaction, and when it fails it leaves an unusable index behind that you have to clean up.

Why it is slower, and what stalls it

The ordinary form scans the table once behind a lock that guarantees nothing changes underneath it. The concurrent form gives up that guarantee, so it scans twice: once to build, then again to catch the rows that changed while it was building. Between and after those passes it waits for every transaction that could still be modifying the table to finish, because it cannot know what they will do.

That waiting is the part that surprises people. A single long transaction anywhere in the database, even one touching nothing related, can hold the build at the waiting stage for as long as it runs. The build is not stuck and it is not consuming much; it is blocked on the same cutoff that stops vacuum cleaning up, which is the xmin horizon. Look for the old transaction rather than restarting the build.

It also holds an open snapshot of its own while it waits, which is a small irony worth knowing: a long index build is itself a reason vacuum stops making progress elsewhere.

Failure leaves something behind

If the build is cancelled or hits an error, the index remains in the catalog marked invalid. It is never used for queries, it is still maintained on every write, and it still occupies space. So a failed build makes the table slower to write until somebody drops it, and nothing will tell you it is there unless you look.

The cleanup is to drop it, concurrently as well if the table is busy, and start again. Retrying on top of it without dropping produces a second one with a different name and the same problem.

Two practical guards. Set a lock timeout so the brief locks the build does need cannot queue behind a long query and take the table down with them, and check afterwards that the index is valid rather than assuming a command that returned means a command that worked.

Before adding the index at all, it is worth confirming it will be used and that the write cost is repaid, which is what unused and missing indexes is for. If the build is waiting and you need to know on what, lock contention and blocking trees covers reading the wait.