Performance Impact of "DROP NOT NULL" in Postgres with unique index

Question

We had a column that we wanted to make nullable and so we ran the following migration:

 ALTER TABLE example_table ALTER COLUMN example_column DROP NOT NULL;

Unexpectedly, this took a long time to run and resulted in downtime for our web service. We've been scratching our heads about why. This table is frequently scanned but never written, and it has a unique index on example_column.

Why did this cause a drop in performance?

Our best theory is that the unique index was dropped in Postgres internals behind-the-scenes and then rebuilt, but there's nothing in any documentation I've read that indicates that would happen for this kind of column interaction.

Why did this A) take a long time, and B) wreck performance?

jjanes · Accepted Answer · 2019-11-05 20:58:12Z

4

This is probably a case of lock escalation. Dropping the NOT NULL requires a very brief ACCESS EXCLUSIVE lock on the table. But if that lock is not available, then it queues itself behind the existing (weaker) lock which is blocking it, and then everything else queues up behind this lock request. See a discussion here: https://dba.stackexchange.com/questions/132851/database-frozen-on-alter-table

answered Nov 5, 2019 at 20:58

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

4 Comments

Laurenz Albe Over a year ago

I agree with everything, but I thought the term "log escalation" means that a lot of row locks are turned into a table lock or similar.

jjanes Over a year ago

Maybe I'm not using the correct term of art. It is similar to "priority inversion", but not quite that either.

Masklinn Over a year ago

@LaurenzAlbe in the postgres model, there is always a table lock taken before the row locks. The table locks (and their incompatibilities) depend on the row-level access necessary. But ACCESS EXCLUSIVE is a table-level lock which always conflicts with every other table lock. It's literally what it says: it requires exclusive access to the table, to the exclusion of everyone else.

Masklinn Over a year ago

The problem here is as jjanes notes the ACCESS EXCLUSIVE request will be waiting behind the current transactions (which conflict with it), then every following transaction will be blocked by it. So if the DB is online and has lots of relatively long transactions, this will cause a huge pileup.

Collectives™ on Stack Overflow

Performance Impact of "DROP NOT NULL" in Postgres with unique index

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related