CREATE INDEX CONCURRENTLY implementation details - Andrey Borodin - PGCon 2022

Explore CREATE INDEX CONCURRENTLY in PostgreSQL, covering concurrent index creation and interactions with transactions.

Key takeaways

The problem of concurrent index creation is that it can cause issues when other transactions are also updating the same table, leading to inconsistencies.
A prepared transaction is a transaction that has been prepared but not yet committed.
The concurrent index creation algorithm needs to ensure that the index creation is done concurrently with other transactions that are updating the same table.
The algorithm should also ensure that the transactions that are updating the table are aware of the index creation.
The problem of concurrent index creation is more complex when dealing with prepared transactions, as they need to be handled differently than regular transactions.
The index creation algorithm should be able to handle transactions that are being prepared and transactions that are already committed.
The algorithm should also be able to handle transactions that are being rolled back.
The process of creating an index concurrently involves creating a system catalog record, building the index, and then marking the old index as obsolete.
The concurrent index creation algorithm should be able to handle cases where the index creation is interrupted, such as when the database is restarted.
The algorithm should also be able to handle cases where the index creation is rolled back, such as when a transaction is rolled back.
The process of creating an index concurrently involves creating a system catalog record, building the index, and then marking the old index as obsolete.
The concurrent index creation algorithm should be able to handle transactions that are being prepared and transactions that are already committed.
The algorithm should also be able to handle transactions that are being rolled back.
The concurrent index creation algorithm should be able to handle cases where the index creation is interrupted, such as when the database is restarted.
The algorithm should also be able to handle cases where the index creation is rolled back, such as when a transaction is rolled back.
The process of creating an index concurrently involves creating a system catalog record, building the index, and then marking the old index as obsolete.
The concurrent index creation algorithm should be able to handle transactions that are being prepared and transactions that are already committed.
The algorithm should also be able to handle transactions that are being rolled back.
The concurrent index creation algorithm should be able to handle cases where the index creation is interrupted, such as when the database is restarted.
The algorithm should also be able to handle cases where the index creation is rolled back, such as when a transaction is rolled back.

CREATE INDEX CONCURRENTLY implementation details - Andrey Borodin - PGCon 2022

More talks