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.


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.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.