Re: When to use Vacuum?

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(at)2ndQuadrant(dot)com www.2ndQuadrant.us