CODEWITHSUNDEEP
sqlpostgresqlindexingconcurrency
CodeOverload
CodeOverload

Reputation: 48505

Are indexes built CONCURRENTLY different in any way?

Does PostgreSQL CREATE INDEX CONCURRENTLY mean that even after initial index build, new and future inserts won't be indexed immediately (strongly consistent in terms of indexing)?

Upvotes: 1

Views: 1060

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657727

No. CREATE INDEX with the modifier CONCURRENTLY produces exactly the same kind of index as without the modifier.

CONCURRENTLY only changes the way the index is created initially, meaning it does not block concurrent writes. A side effect is that creating the index can take considerably longer than without CONCURRENTLY, especially under concurrent write load (which would otherwise be blocked until the command is done).

The manual:

CONCURRENTLY

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently below.

Follow the quoted link for details.

Upvotes: 2

Related Questions