CODEWITHSUNDEEP
postgresqlindexinglocking
user2671057
user2671057

Reputation: 1533

Drop index locks

My Postgres version is 9.6

I tried today to drop an index from my DB with this command:

drop index index_name;

And it caused a lot of locks - the whole application was stuck until I killed all the sessions of the drop (why it was devided to several sessions?).

When I checked the locks I saw that almost all the blocked sessions execute that query:

SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod    
FROM
pg_attribute a LEFT JOIN pg_attrdef d
                       ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = <index_able_name>::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Is that make sense that this will block system actions?

So I decided to drop the index with concurrently option to prevent locks.

drop index concurrently index_name;

I execute it now from PGAdmin (because you can't run it from noraml transaction).

It run over that 20 minutes and didnt finished yet. Index size is 20MB+-.

And when I'm checking the DB for locks I see that there is a select query on that table and that's blocks the drop command.

But when I took this select and execute in another session - this was vary fast (2-3) seconds.

So why is that blocking my drop? is there another option to do that? maybe to disable index instead?

Upvotes: 5

Views: 9058

Answers (1)

Tometzky
Tometzky

Reputation: 23890

drop index and drop index concurrently are usually very fast commands, but they both, as all DDL commands, require exclusive access to the table.

They differ only in how they try to achieve this exclusive access. Plain drop index would simply request exclusive lock on the table. This would block all queries (even selects) that try to use the table after the start of the drop query. It will do this until it will get the exclusive lock - when all transactions touching the table in any way, which started before the drop command, would finish and the transaction with a drop is committed. This explains why your application stopped working.

The concurrently version also needs brief exclusive access. But it works differently - it will not block the table, but wait until there no other query touching it and then does its (usually brief) work. But if the table is constantly busy it will never find such a moment, and wait for it infinitely. Also I suppose it just tries to lock the table repeatedly every X milliseconds until it succeeds, so a later parallel execution can be more lucky and finish faster.

If you see multiple simultaneous sessions trying to drop an index, and you do not expect that, then you have a bug in your application. The database would never do this on its own.

Upvotes: 10

Related Questions