Waiting for 9.2 – DROP INDEX CONCURRENTLY

On 6th of April, Simon Riggs committed patch:

Add DROP INDEX CONCURRENTLY [IF EXISTS], uses ShareUpdateExclusiveLock

The log message is pretty laconic, so let's see what it actually means.

Before I can show you new functionality, we need to see and understand what was happening before.

For my tests, I have this table:

$ \d plans
                       TABLE "public.plans"
    COLUMN     │           TYPE           │       Modifiers
───────────────┼──────────────────────────┼────────────────────────
 id            │ text                     │ NOT NULL
 plan          │ text                     │ NOT NULL
 entered_on    │ TIMESTAMP WITH TIME zone │ NOT NULL DEFAULT now()
 is_public     │ BOOLEAN                  │ NOT NULL DEFAULT TRUE
 is_anonymized │ BOOLEAN                  │ NOT NULL DEFAULT FALSE
 title         │ text                     │
Indexes:
    "plans_pkey" PRIMARY KEY, btree (id)
    "zz" btree (entered_on)

Dropping the index is simple, but let's see what lock it needs:

$ BEGIN;
BEGIN
 
*$ DROP INDEX zz;
DROP INDEX
 
*$ SELECT mode, relation::regclass FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'relation';
        mode         │            relation
─────────────────────┼─────────────────────────────────
 AccessShareLock     │ pg_locks
 AccessShareLock     │ pg_attribute_relid_attnum_index
 AccessShareLock     │ pg_attribute_relid_attnam_index
 AccessShareLock     │ pg_attribute
 AccessShareLock     │ pg_class_relname_nsp_index
 AccessShareLock     │ pg_class_oid_index
 AccessShareLock     │ pg_namespace_oid_index
 AccessShareLock     │ pg_namespace_nspname_index
 AccessShareLock     │ pg_namespace
 AccessShareLock     │ pg_class
 AccessExclusiveLock │ plans
 AccessExclusiveLock │ 18771
(12 ROWS)

Important part is the AccessExclusiveLock on plans. It means that whenever you're dropping index, you need to get full exclusive lock on a table. And this can, and will, cause problems in production environments.

Of course – the dropping is usually fast, but getting the lock itself can be long, and this would cause all other accesses to block.

Now, however, we have the “CONCURRENT" drop of indexes. Let's see how it works. Since concurrent drops cannot be in transaction, I will have to somehow “stop it" from finishing to be able to see the locks. That's easy:

psql1 $ BEGIN;
BEGIN
 
psql1 *$ SELECT * FROM plans LIMIT 1;
  id │ plan │ entered_on │ is_public │ is_anonymized │ title
 ────┼──────┼────────────┼───────────┼───────────────┼───────
 (0 ROWS)
 
psql2 $ DROP INDEX concurrently zz;
(this hangs)
 
psql1 *$ SELECT mode, relation::regclass FROM pg_locks WHERE "pid of psql2" = pid AND locktype = 'relation';
           mode           │ relation
──────────────────────────┼──────────
 ShareUpdateExclusiveLock │ plans
 ShareUpdateExclusiveLock │ zz
(2 ROWS)
 
psql1 *$ commit;
COMMIT
 
psql2 - finishes DROP INDEX

Just as commit promised – we're seeing new lock level – ShareUpdateExclusiveLock. What exactly it prevents? Documentation says:

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs.
Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE.

Which means that it conflicts only with:

  • ALTER TABLE (some due to Share Update Exclusive Lock, and some due to Access Exclusive Lock)
  • ANALYZE
  • CLUSTER
  • CREATE INDEX CONCURRENTLY
  • CREATE INDEX without CONCURRENTLY (because of Share Lock)
  • DROP TABLE
  • REINDEX
  • TRUNCATE
  • VACUUM FULL
  • VACUUM (without FULL)

The biggest point is that now it doesn't conflict with normal queries – like UPDATE, DELETE, INSERT or SELECTs. And that's a very good thing.

2 thoughts on “Waiting for 9.2 – DROP INDEX CONCURRENTLY”

  1. Now it is time actually for somebody to implement REINDEX CONCURRENTLY… as all the missing parts are already there! Cool 🙂

Comments are closed.