Learn more about CREATE INDEX CONCURRENTLY in PostgreSQL. Our PostgreSQL Support team is here to help you with your questions and concerns.

PostgreSQL CREATE INDEX CONCURRENTLY – About

The CREATE INDEX CONCURRENTLY statement in PostgreSQL lets us create an index on a table without blocking write operations on the table.

In other words, other transactions can continue to insert, update, or delete data while the index is being created. However, the CREATE INDEX CONCURRENTLY operation may sometimes encounter a “waiting” state.

When we run a CREATE INDEX CONCURRENTLY statement, PostgreSQL acquires a ShareUpdateExclusiveLock on the table. This lock lets the index creation process read the table’s existing data while allowing other write operations to proceed.

In case there are other transactions that hold incompatible locks, like an exclusive lock or AccessExclusiveLock, on the table, the CREATE INDEX CONCURRENTLY operation will enter a “waiting” state. In other words, the operation waits until the conflicting locks are released before proceeding with the index creation.

The duration of the waiting state varies according to the lock conflicts and the activity on the table. If there are no or minimal conflicting locks it will be a short wait. However, if there are active transactions holding exclusive locks on the table, it will be a longer wait.

Our experts would like to point out that the waiting state during concurrent index creation is a normal part of the process and does not indicate an error or problem with the database. In fact, it helps balance the need for creating the index and maintaining write concurrency.

Additionally, we can query the pg_stat_progress_create_index system view to monitor the progress of the concurrent index creation. This helps us check if it is still waiting or actively building the index.

In fact, it offers us information about the progress of index creation, including the current state, percentage completion, etc.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In brief, our Support Techs introduced how to use the CREATE INDEX CONCURRENTLY statement in PostgreSQL