Re: Help tuning autovacuum - seeing lots of relationbloat

On Wed, 2006-06-21 at 17:27, jody brownell wrote:
> Our application is broken down quite well. We have two main writing processes
> writing to two separate sets of tables. No crossing over, nothign to prohibit the
> vacuuming in the nature which you describe.

It really doesn't matter what table are you touching, as it doesn't
matter if you read or write either, what matters is how long ago was the
last "begin" without "commit" or "rollback". VACUUM will not touch
tuples which were deleted after the oldest not yet finished transaction
started, regardless if that transaction touched the vacuumed table or
not in any way...

> My longest transaction on the tables in question are typically quite short until
> of course they begin to bloat.

Well, your application might be completely well behaved and still your
DBA (or your favorite DB access tool for that matter) can leave open
transactions in an interactive session. It never hurts to check if you
actually have "idle in transaction" sessions. It happened a few times to
us, some of those were bad coding on ad-hoc tools written by us, others
were badly behaved DB access tools opening a transaction immediately
after connect and after each successful command, effectively leaving an
open transaction when leaving it open while having lunch...

So it might very well be that some interactive or ad hoc tools you're
using to manage the DB are your problem.

Cheers,
Csaba.