Adding and Removing Big Indexes in PostgreSQL

In this post we’ll look at a recipe for adding and removing big indexes on big tables. The create operation can take hours, so it’s helpful to have a plan in place for this to run in the background and to monitor progress. We’ll assume these operations take place at the same time as the client application queries, which means they run concurrently and we’ll need to monitor the resources being used. Using a lock timeout Without using concurrently, create index takes a ShareLock (https://pglocks.org/?pgcommand=CREATE%20INDEX) lock on the table and other important write operations require this same lock level, which means they conflict. This conflict means that operations that hold the same lock type are put into a queue and wait until the lock they require is available. This is a problem on a live system! This means that queued write operations requiring the same lock type are blocked until the lock is available. That means we want to add a safeguard against this. What we’re guarding is…