Contents
What does create index concurrently do?
Building Indexes Concurrently. Creating an index can interfere with regular operation of a database. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX.
What is index concurrently?
When you build the index concurrently there are multiple transactions involved: “In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions”.
How does concurrent index build work in PostgreSQL?
In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions. Before each table scan, the index build must wait for existing transactions that have modified the table to terminate.
What happens when you create an index in PostgreSQL?
Of course, the extra CPU and I/O load imposed by the index creation might slow other operations. In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions.
What happens when you build an index concurrently?
When you build the index concurrently there are multiple transactions involved: “In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions”.
What makes CIC unique in PostgreSQL 8.3?
A unique distinguishing factor of CIC is that it can build a new index on the table, without blocking it from updates/inserts/deletes. But even before that, let’s understand how Heap-Only-Tuple (HOT) works. It was a landmark feature added in PostgreSQL 8.3 to reduce table bloat and improve performance significantly.