Re: Vacuuming DVs with few/no updates?

Preston <unsane(at)idl(dot)com(dot)au> writes:
> Just wondering about how useful the vacuumdb functionality is for a database
> that pretty much only ever gets inserts/selects.

> I've got a database that is intended for next-to-no deletion of
> records, which is where I see the vacuum facility mostly intended
> for. I.e., while there'll be the occasional record edits, these edits
> will primarily be of the form of adjusting integer records (and
> therefore shouldn't adjust any space requirements for a row).

You have an important misconception lurking in there. In Postgres,
an UPDATE is equivalent to INSERT (of the new row version) followed by
DELETE (of the old row version). Therefore, it creates dead rows that
need to be reclaimed by VACUUM, just as much as DELETE would do.

But yeah, if you have very very few updates or deletes then you don't
need to vacuum very often. (You might possibly need to ANALYZE more
often than you VACUUM, if statistics like column min/max values are
changing significantly due to the insertion traffic.)

> I'm thinking that with this database I'll configure vacuumdb to only
> run once a month or so. But before I do that I want to make sure I'm
> not missing some other important functionality that it does...

You should read the discussion of transaction wraparound in the Admin
Guide's chapter about routine maintenance (specifically VACUUM ;-)).
Once-a-month vacuum is fine if your total transaction load doesn't
exceed 1 billion per month ...

regards, tom lane