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

jenniferm411(at)hotmail(dot)com (Jennifer Spencer) writes:

>>> 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? 

That kind of situation is one I'd not expect to lead to much, if any
bloat.

The usual cases that lead to bloat is where there are a lot of
updates/deletes. That is the reason to expect to have pages used that
are nearly empty.

If it's nearly insert-only, then pages would only be nearly empty if
they are nearby pages that are completely full (and hence needed to be
split). That doesn't seem like a major problem ;-).

> 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.

PostgreSQL doesn't have clustered indexes of that sort, so that
problem won't emerge ;-).

> 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?

No, you don't need to use VACUUM FULL to avoid ID wraparound. Plain,
NOT-FULL vacuum, which does not block things, handles that perfectly
well. No need to relate FULL vacuum with that.
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/advocacy.html
There's a new language called C+++. The only problem is every time
you try to compile your modem disconnects.