# How to add a PostgreSQL index without downtime

> **Quick answer:** Use `CREATE INDEX CONCURRENTLY`, it builds the index without
> blocking writes, unlike a plain `CREATE INDEX`, which holds a write-blocking
> lock for the whole build.  The trade-offs: it **cannot run inside a transaction
> block**, it scans the table **twice** so it takes longer, and if it **fails it
> leaves an INVALID index** you must drop and rebuild.

## Why a plain CREATE INDEX is downtime

A normal `CREATE INDEX` takes a `SHARE` lock on the table.  That allows reads but
**blocks all writes**, every `INSERT`, `UPDATE`, and `DELETE` waits, for the
entire duration of the build.  On a small table that is a blink.  On a 100-million-row
table it is a multi-minute write outage, which for most applications is an
incident.

## The fix: CREATE INDEX CONCURRENTLY

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer_id
  ON orders (customer_id);
```

`CONCURRENTLY` builds the index while reads and writes continue.  It does that by
being more careful, and that carefulness is the source of every gotcha below.

### It is not impact-free, it still holds a lock

`CONCURRENTLY` is not lock-free; it holds a `SHARE UPDATE EXCLUSIVE` lock on the
table for the entire build.  That lock allows concurrent `INSERT`, `UPDATE`, and
`DELETE`, but it **self-conflicts** with `VACUUM`, `ANALYZE`, autovacuum, and any
other `CREATE INDEX CONCURRENTLY` or `REINDEX CONCURRENTLY` on the same table, those serialize behind it.  Only **one** concurrent build can run on a given table
at a time, and a long-running build can **starve autovacuum**, letting dead
tuples and bloat pile up while it runs.

### It cannot run in a transaction block

`CREATE INDEX CONCURRENTLY` commits between its internal phases, so it cannot run
inside an explicit `BEGIN … COMMIT`. Most migration frameworks wrap each
migration in a transaction by default, which makes the statement fail.  You have
to disable the wrapping transaction for that migration (Rails:
`disable_ddl_transaction!`; Django: `atomic = False`; or run the statement
outside your tool's transaction).

### It is slower, two scans and a wait

Building concurrently does more total work: it scans the table twice (build, then
a second pass to catch rows changed during the build) and waits for
transactions that started before it to finish.  Expect it to take noticeably
longer than a blocking build.  You are trading wall-clock time for availability.

### It does not work on a partitioned parent table

Concurrent builds on partitioned tables are not supported.  Do it in two steps:
build the index on **each partition** with `CREATE INDEX CONCURRENTLY`, then
create the matching index on the parent *without* `CONCURRENTLY`.  Postgres
attaches the partition indexes you already built, so that step is metadata-only.

### On failure it leaves an INVALID index

If the build fails, a unique violation, a deadlock, a cancelled statement.  It
can leave an index marked **invalid**. That index takes up space and is kept
up to date on writes, but the planner **will not use it**. Find them and clean up:

```sql
SELECT c.relname AS index_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indisvalid = false;
```

```sql
DROP INDEX CONCURRENTLY idx_orders_customer_id;   -- then re-create
```

`DROP INDEX CONCURRENTLY` likewise avoids the `ACCESS EXCLUSIVE` lock a plain
`DROP INDEX` would take.  One exception: it **cannot drop an index backing a
primary key, unique, or exclusion constraint**.  That needs `CASCADE`, which the
concurrent form does not support.  Drop or alter the constraint instead.

#### The unique-index trap

A unique index has an extra wrinkle. `CONCURRENTLY` scans the table twice, and by
the time the **second** scan starts the uniqueness constraint is already being
enforced against other transactions.  So duplicate inserts can start failing
before the index is usable, and per the PostgreSQL docs, *"if a failure does
occur in the second scan, the 'invalid' index continues to enforce its
uniqueness constraint afterwards."*

That is the worst of both worlds: write overhead and rejected duplicates, with no
query benefit (the planner still ignores it).  A build that fails in the *first*
scan leaves the ordinary invalid index, ignored for reads, update overhead only.
Either way the fix is the same: drop it and retry.  But if inserts start failing
on a uniqueness violation you cannot explain, look for a leftover invalid unique
index before anything else.

## Adding a UNIQUE constraint without downtime

Adding a unique constraint directly builds its index under an `ACCESS EXCLUSIVE`
lock.  Do it in two steps instead:

```sql
CREATE UNIQUE INDEX CONCURRENTLY orders_email_uidx ON orders (email);
ALTER TABLE orders ADD CONSTRAINT orders_email_unique
  UNIQUE USING INDEX orders_email_uidx;
```

The `ADD CONSTRAINT ... USING INDEX` step just attaches the already-built index,
so the strong lock is held only briefly.

## Why does aI-generated migration code miss this?

Your AI coding agent (Claude Code, Cursor) writes `CREATE INDEX` because that is
the textbook statement, or it writes `CREATE INDEX CONCURRENTLY` *inside* the
transaction its migration template already opened, and the statement fails at
run time.  It has no view of the table's size (is this a blink or a ten-minute
outage?) or of the fact that a previous concurrent build left an invalid index
sitting there.  Those are runtime and operational facts, not properties of the
schema in the source.

## How do I make this routine?

- Default to `CONCURRENTLY` for any index on a table that takes production writes.
- Configure your migration tool to run index creation outside a transaction.
- After a failed concurrent build, check `pg_index.indisvalid` and drop the
  invalid index before retrying.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the operational context, the table's size, existing indexes, and any invalid
index left from a failed build, so it can recommend `CONCURRENTLY`, keep the
statement out of a transaction, and catch a leftover invalid index.  It surfaces
and explains; it does not run your migrations. [Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**Does CREATE INDEX lock the table?**
A plain `CREATE INDEX` takes a `SHARE` lock that blocks writes (allowing reads)
for the whole build, a write outage on a big table. `CREATE INDEX CONCURRENTLY`
avoids blocking writes.

**Why cannot CREATE INDEX CONCURRENTLY run in a transaction?**
It commits between internal phases, so it cannot run inside an explicit
transaction block.  Disable your migration tool's wrapping transaction for that
statement.

**What is an INVALID index?**
A failed or interrupted concurrent build can leave an index with
`indisvalid = false`: it takes space and is maintained on writes, but the planner
will not use it.  For a **unique** index that failed during the second table scan, it
also keeps enforcing uniqueness, so duplicate inserts keep failing.  Drop it with
`DROP INDEX CONCURRENTLY` and retry.

**Can I use CONCURRENTLY on a partitioned table?**
Not on the partitioned parent, concurrent builds there are not supported.  Build
each partition's index with `CREATE INDEX CONCURRENTLY`, then create the index on
the parent non-concurrently; attaching the existing partition indexes is a
metadata-only operation.

**How do I add a unique constraint without downtime?**
`CREATE UNIQUE INDEX CONCURRENTLY`, then `ALTER TABLE ... ADD CONSTRAINT ... USING
INDEX` to attach it, a fast catalog step.

## Related

- [Which ALTER TABLE statements lock a PostgreSQL table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)
- [How to identify slow queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
- [Why did my PostgreSQL query suddenly get slow?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)