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?
