0

I would like to know what the difference is between CREATE INDEX and CREATE INDEX CONCURRENTLY (if any) after the creation of the index has built. I have been having a discussion with a colleague about how they work.

My understanding is that CREATE INDEX takes a full lock on the table to build the index quickly causing down time, but for CREATE INDEX CONCURRENTLY only a partial lock is taken on the table. This means that indexes are calculated for every new transaction/insert as the table has writes going into it, and whenever there are no transactions/inserts happening, it is "backfilling" or processing the indexes for the data that was already there. Once this "backfilling" process has finished there is no strict difference between either CREATE INDEX and CREATE INDEX CONCURRENTLY.

My colleague's understanding is that CREATE INDEX CONCURRENTLY behaves differently, and that even after the "backfilling process" is finished, CREATE INDEX CONCURRENTLY remains being less load intensive on writes.

Can someone please provide us a better insight as to how index creation works? When can we say that an index has been created? What happens after the index has finished creating whenever you insert data? And whether there is a difference in the calculation of the index after it has finished building while using CONCURRENTLY versus not?

2 Answers 2

1

The difference is only in the way the indexes are built, but once that is done, the results is the same. A concurrently created index can have some bloat, but that difference will even out after a while.

0

The difference is in "how it works". It is about "How long?" and "Can we block the indexed table changes for the indexing time?". The results are the same.

Any CREATE INDEX should scan its table, sort values from your target field, and store the result.

The regular one LOCKs the table. It means no INSERT, UPDATE, or DELETE query can not be processed until the index creation is finished. On Production, depending on the data amount, it could take a very long time. The change queries can timeout, and a service could be partially inoperable. SELECT queries work normally with this lock. But from the total DB processing time, such a request will be optimal and fast. It takes only 1 full table scan and sorting.

The CONCURRENT one doesn't make a LOCK. So any updates can flow into the table. The change queries will flow as expected. But indexing will take much more time. This operation will take at least 2 full table scans and sorting. If during the operation there were changes then - update the new index data and scan again. This step can be iterated multiple times. So its execution is not optimal and takes much more time and resources, than the base one.

Hope, this will help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.