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?
Asked
Active
Viewed 4,323 times
3
amphibient
- 354
- 1
- 6
- 17
-
Is there any reason why you have turned of normal autovaccum? This should address this in background. Also how do you delete the lines? – frlan Jul 16 '14 at 19:36
-
I never turned it off. How do I check the value of that setting on the server? – amphibient Jul 16 '14 at 19:38
-
If you never turned it off, it should be running in background -- so unless you are really at some very special case, you don't need to trigger it manually. Check documentation for more details. It's really a huge, but interessting topic. – frlan Jul 17 '14 at 06:07
1 Answers
3
Do you need VACUUM FULL?
VACUUM FULLis 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.
Craig Ringer
- 56,343
- 5
- 158
- 190