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?
Add a comment
|
1 Answer
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.
6 Comments
sofs1
Let say today my table has 10 records in production and I add
create index concurrently on (column_A) From tomorrow, millions of records gets inserted to the table and indices will be created on the fly. Isn't it? At that time, concurrently prevents locks during writes (insert/updates). Is my understanding correct?sofs1
still confused. For the new records getting inserted after create index command is used, wouldn't it be a concurrent data insert/update?
Luke
Here's another good piece of documentation which goes through the index creation phases: 2ndquadrant.com/en/blog/create-index-concurrently . And as a note: "The index is fully ready when the third pass finishes. It’s now being actively maintained by all other backends, following usual HOT rules." - so there's no difference after the indexed is created.
|