CODEWITHSUNDEEP
postgresqlindexing
sofs1
sofs1

Reputation: 4176

Does using 'create index concurrently' in postgres, help future rows which will be inserted to be free from locks?

Does the create index concurrently works only when the table is created for the first time or does it even work for the records which will be inserted in future?

Upvotes: 8

Views: 4694

Answers (1)

user330315
user330315

Reputation:

You are misunderstanding what concurrently does: it avoids locking the table for write access while the index is created.

Once the index is created, there is no difference between an index created with the concurrently option and one without. If new rows get inserted, the index is updated with the new values. Inserting new rows, does not "rebuild" the entire index.

Once an index is created, inserting rows into the table does not lock the table at all, regardless of how the index was created. Non-unique indexes always allow concurrent insert to the table.

A unique index however will block concurrent inserts for the same value(s) - but not for different values.

Upvotes: 9

Related Questions