I'd like to phone in with a slightly different opinion on VACUUM FULL. Yeah, it should be avoided when possible, but it's not always possible. In our case, I've got 300ish databases backing to a single database server. Each of those dbs has a couple of hundred tables and a hundred or more views. The product (Journyx Timesheet) is pretty complex, and I find that if I do _not_ perform a full vacuum once per week, my customer dbs start to slow down inordinately. Queries which would run in 1-2 seconds will run in 30-40 seconds after a few weeks of not performing a full vacuum. I've got autovac running on all dbs. Now, that could well be due to index bloat with complex indexes, or it could be due to a variety of other factors, but also my pg_clog directory does not clear out, but continues to create new clog segments. Running my weekly vac-full-analyze resolves that problem for me. This might not be the case for you if you have a less complex schema, especially noting how you say you use it. I _think_ autovacuum, somewhere around early 8.x resolves the transaction wrap-around issues, but someone else should verify that. -----Original Message----- From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Jennifer Spencer Sent: Tuesday, July 07, 2009 12:02 PM To: kevin.grittner@xxxxxxxxxxxx; scott.lists@xxxxxxxxxxxxxxxx Cc: scott.marlowe@xxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx Subject: Re: Catching up Production from Warm Standby aftermaintenance - Please help >> Am I wrong? > > Probably. My first concern is to make sure you aren't doing VACUUM > FULL as part of your maintenance cycle. That option is meant for > recovery from extreme bloat, and is sort of a "last resort". Good - glad to be wrong about that! We do mostly inserts, no updates and very few deletes. We drop entire tables but don't delete often. We have very long rows, though. Do you think the above is a situation likely to create extreme bloat? My Sybase experience with extreme bloat was that it was caused by a three-field clustered index in a very long short-row table over time (~a year). This job doesn't use clustered indexes. I thought we had to do vacuum full to avoid transaction ID wraparound/reset issues? We do have a lot of transactions, a whole lot. Are you saying that most admins avoid VACUUM FULL as much as possible? What about XID? Thanks, Jennifer Other > vacuums coexist with normal usage as long as you have things properly > configured for your environment. You probably won't have a need to > reindex if you stay away from VACUUM FULL and otherwise avoid unusual > activity which causes index bloat; however, if you do need to reindex > without down time, there is CREATE INDEX CONCURRENTLY which can be > used to achieve that. > > -Kevin ________________________________ Windows LiveT: Keep your life in sync. Check it out. <http://windowslive.com/explore?ocid=TXT_TAGLM_WL_BR_life_in_synch_062009> -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
