Skip to content
dbexplore

Question: Vacuum, bloat and wraparound

Does VACUUM FULL lock the table?

Answered in the first paragraph. Last updated .

Yes. VACUUM FULL holds an access exclusive lock on the table for its entire duration, so nothing can read it, write it or plan against it until the command finishes. It also builds a fresh copy of the table and its indexes before dropping the old files, so the filesystem needs room for both at once. Ordinary VACUUM does neither.

Why the two commands share a name and nothing else

They solve different problems. The everyday command marks space inside the table as reusable and updates the bookkeeping that lets future scans skip pages; it takes a weak lock, runs alongside your traffic, and deliberately leaves the file the size it was. The full variant exists to give the space back to the operating system, and the only way to do that is to rewrite every live row into a new file, which cannot be done while anyone else is looking at the old one.

The lock is therefore not an implementation detail somebody could remove. It is the price of the guarantee, and it is why the command is a maintenance-window tool rather than a remedy you reach for when a table looks large.

Two consequences catch people out. The duration scales with the live data, not with the bloat, so a mostly dead table finishes quickly and a mostly live one does not. And the lock request queues: once the command is waiting, every query arriving behind it waits too, so a table that was merely slow becomes a table that is unavailable several seconds before the rewrite even starts.

What to do when a table genuinely has to shrink

First establish that it has to. Reclaimed free space inside a table is reused by that table, so a file that stopped growing is usually working correctly, and dead tuples falling to zero after a vacuum tells you nothing about the file size either way.

If the space really must go back to the filesystem, the extensions that rewrite a table online do the same work while holding the heavy lock only for the moment of the file swap, which turns hours of unavailability into a few seconds. They are the right answer for a production table, and VACUUM FULL is the right answer for a table nobody is using.

If the table keeps returning to the same size instead, rewriting it is treating the symptom. Autovacuum and table bloat covers the three reasons space stops being reused, and only one of them is fixed by a rewrite.

Put every Postgres you run on autopilot.

We onboard teams in small batches. Tell us about your fleet and we will reach out when a seat opens. One email, no drip campaign.