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)?

>Solution :

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.

Leave a Reply