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)
- CREATE INDEX CONCURRENTLY
- CREATE INDEX without CONCURRENTLY (because of Share Lock)
- DROP TABLE
- 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.