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@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jennifer Spencer
Sent: Tuesday, July 07, 2009 12:02 PM
To: kevin.grittner@wicourts.gov; scott.lists@enterprisedb.com
Cc: scott.marlowe@gmail.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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>