Thread: When to use Vacuum?

I'm putting some large read-only data sets together and occasionally I
change my mind about something, drop a table or two and replace them
with something different. Do I need to use VACUUM when I drop or
re-arrange tables? Or does PG release disk space when you drop tables?
And finally, if the tables  are strictly read-only, then do I need to
use VACUUM at all on a regular basis? (Like, do selections take up disk
space that  needs to be cleaned up on a regular basis? It's my
understanding that VACUUM only clears or marks for re-use deleted
records, but the docs say nothing about deleted tables and I don't want
to get into an intolerable situation someday (like disk full.)


TIA,
- Bill Thoen

Bill Thoen wrote:
> I'm putting some large read-only data sets together and occasionally I
> change my mind about something, drop a table or two and replace them
> with something different. Do I need to use VACUUM when I drop or
> re-arrange tables? Or does PG release disk space when you drop tables?

Dropping or truncating a table immediately releases the disk space back
to the operating system.

> And finally, if the tables  are strictly read-only, then do I need to
> use VACUUM at all on a regular basis? (Like, do selections take up
> disk space that  needs to be cleaned up on a regular basis?

The only additional disk space SELECT statements allocate relates to
temporary files used for things like sorting, which should all get
cleaned up automatically.

VACUUM is one way to get all the Hint Bits set for a table:
http://wiki.postgresql.org/wiki/Hint_Bits which can be a source of
writes when doing a SELECT.  It can be useful to run one during a slow
period shortly after the data is inserted, just to get that out of the way.

You will still need to run some form of VACUUM occasionally to prevent
transaction ID wraparound.  Autovacuum will take care of that for you,
but you might prefer to schedule a VACUUM every now and then for that
purpose, rather than exposing yourself to the possibility it will happen
at a really busy period.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us