Re: Vacuuming DVs with few/no updates?

Quoting Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> 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.

Ah OK - thanks for explaining that to me. It changes my outlook quite a bit.

However, the inserts will still outnumber the updates on a scale of about 30:1.

> 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.)

OK, will look into that side of it.

> > 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 ...

Thanks - will find and read that... Once again caught by the "too much to do not
enough time" bug.

Does that transaction load include selects?

Due to automated web-based refreshing of views of tables I'd expect the selects
to number around 2,000 to 10,000 per day... I know that's still not anywhere
near a billion but it's to the point of a more interesting amount if transaction
load includes selects...

Cheers,

-Preston.

--
Oops.