How to drop a column(and index) in Postgres concurrently?

Question

I need to drop a column from my (large) Postgres table. This is simple enough, but there is also an index on the column.

I can see that the index is implicitly dropped when I remove the column with ALTER TABLE, but I understand I should use CONCURRENTLY when dropping an index.

So my question is:
Is it appropriate to perform this operation as two queries. i.e.:

DROP INDEX CONCURRENTLY IF EXISTS myTable_myColumn_idx;
ALTER TABLE myTable DROP COLUMN IF EXISTS myColumn;

Or could that result in a race condition, where the alter table is executed while the index is still being dropped?

Laurenz Albe · Accepted Answer · 2020-04-21 09:11:44Z

4

Dropping an index is normally a very fast operation. I would test it first with a big index and see if it is really worth the effort to drop the index separately.

If you find that it indeed takes a long time for you, use the statements you propose, but don't try to run them in parallel (they will lock each other), but in a single database session. That will keep the time when the table is locked to a minimum.

answered Apr 21, 2020 at 9:11

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to drop a column(and index) in Postgres concurrently?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related