Is it possible to run VACUUM FULL in Postgres asynchronously?

I am working on some unused data purging scripts on Postgres 9.1. VACUUM FULL is necessary to reclaim physical space after purging. However, with my data volume (5M+ rows), it takes several minutes to execute it. Since the vacuum needs to be incorporated in the script, I was wondering if it is possible to kick it off asynchronously (or schedule it) so that my script can just finish without waiting?

Do you need VACUUM FULL?


VACUUM FULL is necessary to reclaim physical space after purging

Yes, if you want to return that space to the OS or use it in other tables.

If you expect to just re-use the space for new rows in the same table, you're better off just running normal VACUUM (or letting autovaccum do its job), so the blocks containing deleted rows are marked as free for re-use. Shrinking and growing tables is a heavyweight operation that's best avoided if you're just going to re-use the space.

VACUUM FULL without locks

Pg doesn't provide a VACUUM FULL CONCURRENTLY.

There's a client-based effort to do the equivalent, called pg_repack. I can't speak for its safety/reliability.

In general we tend to avoid VACUUM FULL in favour of just letting normal VACUUM clear space for re-use within the table.

StackExchange Database Administrators Q#71686, answer score: 3