On Tue, Jul 7, 2009 at 11:01 AM, Jennifer
Spencer<jenniferm411@hotmail.com> wrote:
>>> 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?
Nope. Updates and deletes are where bloat comes from.
> 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.
You could, they don't cause bloat in pgsql. In fact they help get rid of it.
> 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.
I think you thought you saw the word full in the wrong order with the
requirement to do a full vacuum of the database.
> Are you saying that most admins avoid VACUUM FULL as much as possible? What
> about XID?
Yes, and it's handled by both autovacuum and running a full database
wide regular old vacuum.