CREATE INDEX CONCURRENTLY is not free, and half the time you do not need it

The standard advice is "always CREATE INDEX CONCURRENTLY in production". It is good advice and it is also a little lazy, because it skips over what the concurrent form costs and when you are paying that cost for nothing. So here are numbers.

The setup

Postgres 16, one table, twenty million rows, 1.6 GB on disk. While each index builds, a second session inserts one row every fifty milliseconds and records how long the slowest insert had to wait. Laptop hardware, so the absolute times are small; the ratios are what matter.

CREATE TABLE orders (
  id bigserial PRIMARY KEY, customer_id int NOT NULL,
  status text NOT NULL DEFAULT 'open', total numeric(12,2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now());
INSERT INTO orders (customer_id, total)
  SELECT (random()*1000000)::int, (random()*500)::numeric(12,2)
  FROM generate_series(1, 20000000);

The result

	Build time	Worst insert wait
CREATE INDEX	4.4 s	4216 ms
CREATE INDEX CONCURRENTLY	7.3 s	123 ms

Read the second column first. The plain build holds a SHARE lock on the table for its whole duration. Reads go through, every write waits, and the slowest insert waited almost exactly as long as the build took, because it arrived just after the lock was taken and sat there until it was released. On a table where a build takes four seconds that is four seconds of every checkout, signup and webhook handler hanging. On a table where it takes four minutes it is an incident.

The concurrent build never blocked a write for more than an eighth of a second. It took two thirds longer to finish, and that is the honest price: it scans the table twice, once to build the index and once to catch the rows that changed during the first scan, and it waits at the end for every transaction that was open when it started. On a busy server it can take several times longer than the plain form. It also can not run inside a transaction block, which is why migration frameworks make you opt out of their wrapper for it. BV003 fires on the plain form for the reason in the first row.

What wrong and right look like

-- wrong on a table that has writers: blocks every insert, update and delete for the whole build
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- right: two scans, no blocked writes; must run outside a transaction block
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

-- and if the migration runner wraps every file in a transaction, tell it not to for this one:
-- Rails: disable_ddl_transaction!   Django: atomic = False   Flyway: -- flyway:executeInTransaction=false

The concurrent build finishes, on a busy table, minutes later than the plain one would have. That is time the migration job spends waiting, not time the application spends blocked, and that is the trade you want.

The failure mode nobody mentions

If a concurrent build fails part way, because of a deadlock, a unique violation, or a cancelled statement, it leaves behind an invalid index. The index exists, it is maintained on every write, it costs the same as a real one, and the planner never uses it. Nothing tells you. The next migration that tries to create the same name fails with "already exists", and the usual response is to drop it and retry, which is right, but the invalid index can sit there for weeks first, slowing every insert on the table for no benefit.

SELECT indexrelid::regclass
  FROM pg_index WHERE NOT indisvalid;   -- should return nothing

Put that query in the runbook next to every concurrent build, and drop with DROP INDEX CONCURRENTLY when it finds something, for the same reason: a plain DROP INDEX takes the exclusive lock, BV016.

When the plain form is fine

• A new table. Zero rows, no writers yet, nothing to block. The plain form is faster and it works inside the migration transaction. Creating the index in the same statement as the table is the cleanest version of this.
• A tiny table. A few thousand rows builds in milliseconds either way; the concurrent form's second scan and end-of-build wait cost more than the lock does.
• A maintenance window with writes actually stopped. If nobody can write, the lock is free, and the plain form is simpler and can not leave an invalid index behind.
• Inside a rebuild. When you are populating a new table and swapping it in, build the indexes on the new table after the bulk copy and before the swap, plain. Nothing is reading it yet.

The decision is not about the statement. It is about whether anything is writing to the table while it runs. A checker can tell you the statement is the plain form on an existing table, and it should, every time, so that the times you choose it are choices rather then accidents.