Can someone explain the difference between these types of VACUUM in PostgreSQL?

I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am I wrong?

  • You have read the doc? That would be a perfect opportunity to include a link to what you have read ... Jan 8, 2014 at 19:32

3 Answers 3


Here's a short concise answer.

Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now).

Vacuum freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear.

For instance, the template0 database is frozen because it never changes (by default you cannot connect to it.)

Every so often the autovacuum daemon will check a database and its tables to see what needs to be vacuumed. If a table is vacuum freeze'd and then never updated, the autovacuum daemon will simply pass it by. Also the "wrap around" protection in postgresql will never kick in on that table either.

tl;dr freezing marks a table as not needing any autovac maintenance. The next update will unfreeze it.

  • So, does VACUUM FREEZE require an exclusive lock on the table? Seems like it should, otherwise you'd risk suddenly making rows from active transactions globally visible. I might be misunderstanding it... Jul 17, 2015 at 17:55
  • No vacuum freeze does not, vacuum full does. Jan 11, 2018 at 21:59
  • 4
    what does "Next update" mean here?
    – user31408
    Feb 26, 2021 at 22:56
  • @pmoubed The next update to a tuple on that page it what that means.
    – Gurmokh
    Nov 17, 2023 at 11:02

To further explain what Jayadevan wrote.

The way that Postgres works with transactions, and to keep track of visible data is by comparing internal Transaction IDs. However, since those transactions are a 32-bit integer sooner or later they will wrap around, and therefore the new transaction will look like it was made in the past (and thus be visible in a current transaction while it shouldn't), while older transactions will look like they are being done in the future (and since the future doesn't exist yet, that data will not be visible any more).

What Postgres do in order to counter that problem is to assign each row that is old enough to be at risk of suffer from this wraparound a special transaction id that always is older than every transaction. You could see it as if valid transaction ids ranges from 0 to 2147483647, it will set the transaction id for all current rows to -1.

However, since the vacuum is basically to mark empty space for re-use, it only works on data pages that have been changed.

What VACUUM FREEZE do is basically freeze the transaction id for all pages no matter if they have been modified or not, so that all the current rows will be seen as old for all new transactions.

However, as of version 8.2 VACUUM FREEZE have been deprecated and should not be used. Instead there is the parameters vacuum_freeze_table_age and autovacuum_freeze_max_age that specifies how many transactions can occur before a complete scan is being done on the table (effectively do an internal VACUUM FREEZE on the table).

  • 4
    How is it that the docs do not mention VACUUM FREEZE being deprecated/discouraged? Jan 9, 2014 at 15:41
  • 1
    Interesting. It did for the versions between 8.2 and 9.0. Perhaps they removed the deprecation because it could be a good feature to have. Jan 9, 2014 at 18:20

Copy/paste from answer when I asked the same question - "regular vacuum marks empty space for re-use, and reclaims the empty space at the end of a relation. So if the empty space is in the middle etc it can't be reclaimed just reused.

vacuum full compacts a relation reclaiming all the empty space. It requires an exclusive lock and is bad for production systems in general because of this.

So the purpose of REGULAR vacuum is not to reclaim space from the tables but to make it available for reuse later. The purpose of vacuum FULL is to reclaim all wasted space at the expense of an exclusive lock and db performance while it's happening.

So, since regular vacuum isn't designed to reclaim everything you shouldn't be surprised that it did not, in fact, reclaim everything. Try your experiment on larger datasets with more random deletions etc to see the difference between regular and full vacuum."


To add to this, vacuum full will actually create new files for the table (the existing files would have shrunk to 0 size). Thus OS can reclaim the space.

  • 6
    The question asks for VACUUM FREEZE in particular. Jan 9, 2014 at 10:01
  • True - "difference between this types of Vacuum in PostgreSQL" - I thought question was about differences between types of vacuum.
    – Jayadevan
    Jan 9, 2014 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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