VACUUM FREEZE vs. VACUUM FULL

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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

http://postgresql.1045698.n5.nabble.com/vacuuming-doubt-td5782828.html

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.