Well, the short answer is: NEVER. But given how often I see people ask about it, I'll try to expand my answer a bit…
Every now and then on IRC, Slack, Discord
, or Reddit people ask questions about VACUUM FULL.
And they do. They tend to put vacuum full calls in crons, or just run it manually when they feel is the right moment.
The reality is that it's generally never good moment to run vacuum full.
Why? Because it gets Access Exclusive lock on the table for the whole duration. And with non-trivially sized tables it will take long time. Nothing can access the table. No select, insert, update, delete.
It works that way because VACUUM FULL rewrites the whole table. And, all of its indexes. Which, again, can take non-trivial time.
So what should I do? It depends on why one would consider running it. There are generally two situations (that I often encounter) that lead people this way:
- autovacuum doesn't handle everything as it should, and they think that they have to fix the thing that autovac missed, with FULL vacuum, because it's better
- people with very bloated tables that want to remove the bloat from the table, and they found that normal vacuum doesn't really remove bloat, but FULL does
In case of the “autovacuum issues" case – even if autovacuum doesn't handle everything nicely, it's enough to run normal vacuum, without FULL. It will not lock anything, but will fix whatever is there to fix. Also, please note that autovacuum should be configured.
If it's the case of bloat then the situation is more complex, but luckily not bad. Instead of using VACUUM FULL, I strongly suggest that you install and use pg_repack. This is well tested tool that does table rewrite without long locks. There are locks, even Access Exclusive one, but they are taken in a way that doesn't lock anything out for long time.
What's more, newish versions of pg_repack can rebuild indexes in parallel, thus making the whole process faster. And it can be used to move tables to another tablespace, if one needs. All while allowing concurrent reads and writes to the table that is being repacked. It's amazing tool.
If you're on some kind of Linux, chances are that repack is already available, prepackaged for your distribution. If not, compilation is really simple.
As for usage, once you installed repack, all you need to do is:
- In the database, run: create extension pg_repack
- And then, in shell (not sql!): pg_repack -U … -t some_table database_name
The only thing is that you should run pg_repack in a place that will not be susceptible to network issues – preferably on the db server itself, or on some host in the same network.
Even if you're using hosted/managed PostgreSQL – chances are that you can use pg_repack, for example, both AWS and GCP do support it.
So, please, please, please, pretty please with cherry on top: don't use VACUUM FULL. Use tools that don't lock other things from using your DB.
Just out of curiosity… If pg_repack is so much better then why doesn’t VACUUM FULL just work that way? Couldn’t it be changed to do what pg_repack does?
@Chris:
that is a question to pg developers, isn’t it?
Is there a case to use VACUUM FULL if your pg_catalog tables have bloat, for example, if you use a lot of temp tables and pg_attribute has a lot of wasted space?
Or is there a better tool for that too?
@Lana:
well, I stand on a position, that if you’re doing this (lots of temp tables to the point of bloating catalog) – then you’re doing wrong things. But, properly configured autovacuum should handle it.
You explained nicely. Sometimes we need to run full vacuum in PostgreSQL when dead tuple is huge. Obviously normal vacuum will not scan dead tuple but still it will not release the space. But the problem with full vacuum is that we need to bring down the application before running it and need to wait until operation is completed. We need to wait for some enhancement in PG functionality, may be developer community will think about it.
@sAn:
No, you are missing the point. If you have lots of dead tuples then what you should do is:
1. fix your configuration of autovacuum
2. run pg_repack
DON’T use vacuum full. It looks that you read first paragraph, and then commented just to plug your post -I wrote about this particular case in paragraph starting with “If it’s the case of bloat…”
What if your table has no Primary Key?
pg_repack will not work. Isn’t VACUUM FULL then not the only solution to fix things (apart from finding out which configuration of autovacuum will work for you?)
@Bart:
well, in such case, I’d say that bloat is the least of your problems.
Generally, from my POV, table without primary key has no place in relational database.