Re: Vacuum time degrading

On 2/28/05 6:53 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> If you are suffering bloat, the fastest route to a solution would
> probably be to CLUSTER your larger tables. Although VACUUM FULL
> would work, it's likely to be very slow.

How can there be bloat if there are no deletes or modifies?

Even if there were deletes or modifies (there will be in about another year
and a half), if a vacuum is being performed every night, how can there be
bloat? The vacuum should release the dead space and it should be reused.
Am I missing something?

>> There are currently no deletes or modifies to the database - only inserts.
>
> You *certain* about that? It's hard to see how the vacuum time wouldn't
> be linear in table size if there's nothing to do and no dead space.

Absolutely sure. The only case that would approach a delete is if a batch
load fails, the transaction is rolled back. That very seldom happens.

Why am I running vacuum nightly if I have no deletes or updates, you ask?
Two reasons - to have it in the cron schedule for when there are deletes
(there will never be updates), and as a check on database integrity. If
there is a database problem, vacuum at least has a chance of flagging it
since it reads the entire database. This was instigated after we had a
couple of instances of corruption a while back that went undetected for too
long. I'm also doing a weekly pg_dumpall as an additional check/fallback.

> Again, VACUUM VERBOSE info would be informative (it's sufficient to look
> at your larger tables for this).

I'll set that up to run tonight and see if it gives any clues. Last night,
vacuum ran over 5 hours.

Wes