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