Re: Catching up Production from Warm Standby after maintenance - Please help

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.