Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
CREATE INDEX CONCURRENTLY is worth using 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 can put extra CPU and I/O load on the system. If the table can tolerate a brief write block, a standard CREATE INDEX may be the simpler, faster choice.
The phrase “half the time” is not a measured PostgreSQL statistic. The practical question is whether keeping writes available during this particular build is worth its added work and operational constraints.
Does CREATE INDEX CONCURRENTLY block writes?
A standard CREATE INDEX allows reads, but blocks writes to the table until the build finishes. CREATE INDEX CONCURRENTLY avoids locks that prevent concurrent inserts, updates, and deletes, so applications can continue writing during the build. It does not mean the operation has no effect on other work: the build consumes resources and can slow activity.
PostgreSQL’s CREATE INDEX documentation describes the locking and build behavior.
#1 Best Overall
Why does a concurrent index build take longer?
A regular index build scans the table once. A concurrent build scans it twice and waits for relevant transactions to finish. PostgreSQL says the method “requires more total work than a standard index build and takes significantly longer to complete.” CPU and I/O use during that extra work can also compete with application activity.
There is no universal table-size or elapsed-time threshold in the documentation for choosing one method. The right decision depends on whether the write interruption from a regular build is acceptable in your environment.
Rank #2
When should you use each command?
| Choice | Writes during the build | Build work and time | Best fit |
|---|---|---|---|
CREATE INDEX |
Blocked until the build finishes | One table scan; less total work than a concurrent build | A maintenance window or other situation where blocking writes is acceptable |
CREATE INDEX CONCURRENTLY |
Inserts, updates, and deletes can continue | Two table scans, transaction waits, and significantly longer completion | Production changes where preventing write blocking is worth the added cost and complexity |
Indexes can speed up queries, but they are not automatically beneficial. PostgreSQL’s planner uses an index when it estimates that doing so is more efficient than a sequential scan; inappropriate indexes can also slow performance. See the PostgreSQL introduction to indexes.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11What operational constraints apply?
- Only one concurrent index build can run on a given table at a time.
CREATE INDEX CONCURRENTLYcannot run inside a transaction block. Check whether your migration tool wraps changes in a transaction before scheduling the command.- For a partitioned table, PostgreSQL documents building indexes concurrently on each partition, then creating the parent index non-concurrently.
What happens if a concurrent build fails?
A failure during a scan—for example, from a deadlock or uniqueness violation—can leave behind an invalid index. PostgreSQL ignores an invalid index for queries because it may be incomplete, but it still adds overhead to table updates. The documented recovery is to drop the invalid index and retry; REINDEX INDEX CONCURRENTLY is also a documented option.
For a concurrent unique index build, uniqueness enforcement can begin before the second scan finishes. As a result, other queries may encounter uniqueness errors before the index is ready for ordinary use. If the build fails during that second scan, the invalid index may continue enforcing uniqueness, so inspect the failed index and its effects before deciding how to recover.
Quick Recap
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.
