Thread: Vacuuming DVs with few/no updates?
Hi all, 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). 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... Cheers, -Preston de Guise. -- Oops.
Preston <unsane@idl.com.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
Quoting Tom Lane <tgl@sss.pgh.pa.us>: > Preston <unsane@idl.com.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.
Preston <unsane@idl.com.au> writes:
> Does that transaction load include selects?
Yes.
regards, tom lane