Quoting Tom Lane <[EMAIL PROTECTED]>: > Preston <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
