One word keeps your table writable while an index builds

One word in a Postgres migration decides whether your API can still write to a table while an index builds: CONCURRENTLY.

Small example. Your orders table is busy and you add an index on user_id. Say the build takes 4 minutes.

With plain CREATE INDEX, Postgres lets reads through but blocks every INSERT, UPDATE and DELETE on orders until the build is done. Checkout just waits for 4 minutes.

CREATE INDEX idx_orders_user ON orders (user_id);
-- reads OK, INSERT / UPDATE / DELETE on orders wait

With CREATE INDEX CONCURRENTLY, it builds in passes while writes keep going. It's slower and uses more IO, but your users never notice.

// 1. can't run inside a transaction block
export const config = { transaction: false }
 
export async function up(knex) {
  // 2. no IF NOT EXISTS: it would skip a broken INVALID index
  await knex.raw(`CREATE INDEX CONCURRENTLY idx_orders_user
             ON orders (user_id)`)
}
 
export async function down(knex) {
  await knex.raw(`DROP INDEX CONCURRENTLY idx_orders_user`)
}

So the fix looks like one extra word. In practice there are 4 things that bite people:

1. It can't run inside a transaction block. Most migration tools wrap every file in one, so you get an error on deploy. Put it in its own migration and turn the transaction off (in Knex that's config = { transaction: false }).
2. If it fails halfway, it leaves an INVALID index behind. Queries ignore it, but every write still pays to update it. Drop it and run it again.
3. IF NOT EXISTS makes that worse. It only checks the name, so on retry it skips the broken index and your migration reports success.
4. It waits for every transaction that was already open when it started. One session stuck "idle in transaction" and your deploy hangs, even though writes are still flowing.

This query finds invalid indexes. Worth running on your prod database once, you might find one from a deploy years ago.

SELECT indexrelid::regclass AS index_name
FROM   pg_index
WHERE  NOT indisvalid;
-- found one? DROP INDEX CONCURRENTLY it, then rebuild

None of this shows up in staging, because staging doesn't have real traffic writing to the table while you deploy.