CREATE INDEX CONCURRENTLY is useful when a normal index build would block writes that must continue. It is not a free safety switch: PostgreSQL scans the table twice, waits for relevant transactions, and spends more time and resources building the index. If writes can pause for the build, a standard CREATE INDEX is often the simpler choice. PostgreSQL’s documentation does not establish that concurrent creation is unnecessary “half the time”; that phrase is not a measured statistic.
What changes when you add CONCURRENTLY?
A standard CREATE INDEX allows reads while the index is being built, but it blocks writes to the table until the build finishes. Adding CONCURRENTLY avoids locks that prevent inserts, updates, and deletes from proceeding during the build. That availability comes with additional work and operational constraints. PostgreSQL 18 CREATE INDEX documentation
As an Amazon Associate I earn from qualifying purchases.
How the two options compare
| Consideration | Standard CREATE INDEX | CREATE INDEX CONCURRENTLY |
|---|---|---|
| Writes during the build | Blocked until the index build finishes. | Inserts, updates, and deletes can continue. |
| Build work | One table scan. | Two table scans, plus waits for relevant existing transactions. |
| Elapsed time and system load | Less total work than concurrent creation; writes remain blocked during the build. | Takes significantly longer and may add CPU and I/O load that slows other operations. |
| Failure recovery | The concurrent-build invalid-index failure behavior described for the concurrent method does not apply. | A failed build can leave an invalid index that is ignored by queries but still adds update overhead; drop and retry, or consider concurrent reindexing. |
| Execution constraints | Can be run in a transaction block. | Cannot run in a transaction block; only one concurrent index build can run on a table at a time. |
PostgreSQL summarizes the cost directly: “Thus this method requires more total work than a standard index build and takes significantly longer to complete.” PostgreSQL 18 CREATE INDEX documentation
Free tools Windows power users keep installed
One-click scans. No signup required.
When to use the concurrent option
- Use
CREATE INDEX CONCURRENTLYwhen blocking writes for the duration of the build is unacceptable and keeping inserts, updates, and deletes available is worth the extra work. - Use standard
CREATE INDEXwhen the table can tolerate blocked writes during the build and you prefer the simpler, single-scan process. - Do not decide based on a made-up table-size or duration cutoff. PostgreSQL’s documentation provides no universal threshold; the practical trade-off is whether write availability outweighs the added time, load, and complexity.
What happens if a concurrent build fails?
A concurrent build can fail during a scan, for example because of a deadlock or uniqueness violation, and leave an invalid index behind. PostgreSQL ignores that index for queries because it may be incomplete, but it still incurs update overhead. The documented recovery is to drop the invalid index and retry; REINDEX INDEX CONCURRENTLY is another documented option. PostgreSQL 18 CREATE INDEX documentation
#1 Best Overall
Unique indexes have an additional wrinkle: PostgreSQL can begin enforcing uniqueness before the second scan has completed. Other queries may therefore report uniqueness errors even if the concurrent build later fails. If failure occurs during the second scan, the invalid index may continue enforcing uniqueness. Plan for that behavior before attempting a concurrent unique index build. PostgreSQL 18 CREATE INDEX documentation
Deployment constraints to check first
- Transaction blocks:
CREATE INDEX CONCURRENTLYcannot run inside a transaction block. Check whether your migration runner wraps every change in a transaction before using it. - Builds on the same table: Only one concurrent index build can run on a given table at a time.
- Partitioned tables: PostgreSQL documents building indexes concurrently on each partition, then creating the parent index non-concurrently. Account for the parent-index step in your deployment plan.
PostgreSQL 18 CREATE INDEX documentation
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Remember that an index has a cost after it is built
An index can make queries faster, but an index that is not useful can slow performance. PostgreSQL’s planner uses an index when it estimates that doing so is more efficient than a sequential scan; creating one does not guarantee that a query will use it. PostgreSQL 17: Introduction to Indexes
Quick Recap
Rank #2
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
