7

When the autovacuum process is vacuuming a large table, will queries like DROP and TRUNCATE be blocked until vacuuming completed? In the documentation,it says

Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.)

But I wonder if DROP or TRUNCATE is included.

Bill Greens
  • 83
  • 4

1 Answers1

9

Autovacuum and VACUUM behave differently. They do both obtain the same lock, but the autovacuum will get cancelled if it is detected to be blocking something else. DROP or TRUNCATE will be blocked, but in the case of autovacuum it will only for at most 1 second (or whatever "deadlock_timeout" is set to).

An exception to this is an autovacuum "to prevent wraparound". Those will not get cancelled, which can be a major problem. Especially in the case of DROP or TRUNCATE, since allowing those to happen would obviate the need for the wraparound vacuum in the first place.

(For lovers of technical details: the lock mode is set in line 1590 of src/backend/commands/vacuum.c, it doesn't depend on auto or manual (on 12dev HEAD) . The cancellation is done in src/backend/storage/lmgr/proc.c based on a flag set in src/backend/storage/lmgr/deadlock.c. It is done there because the code that checks for deadlocks is a convenient place to check for being blocked by autovac.)

András Váczi
  • 31,778
  • 13
  • 102
  • 151
jjanes
  • 42,332
  • 3
  • 44
  • 54