O'Reilly logo

PostgreSQL High Performance Cookbook by Dinesh Kumar, Chitij Chauhan

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Concurrent indexes

In this recipe, we will be discussing how to create an online index that will not block the base table for the incoming transactions.

Getting ready

In general, while creating/dropping an index, it will block the table to avoid further write operations into the index. Until the index operation is complete, the whole table will be locked for write operations, which will be an outage to the application. To avoid this table lock problem, PostgreSQL provides an option called concurrent, which will avoid this blocking behavior. It will also keep this index status as invalid until its creation is successfully completed. Building an index concurrently takes more time, as the operation needs to allow the incoming write operations.

How to ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required