Kevin --
You wrote:
<...>
>>> running transactions can cause autovacuum processes to stall
>>> out or be autocancelled. "Long running transactions" - is now
>>> long? In our system it's rare to have a transaction (even a
>>> prepared transaction) last much longer than a few minutes. Is that
>>> enough time to cause problems with AutoVacuum?
>
>The only situation where I would expect that to be a problem is in a
>very small table which is updated hundreds of times per second.
>
Could you elaborate on this, or point me to a previous thread ?
I've got precisely such a database; currently we do an hourly reindex on all user tables in addition to some fairly
standardautovac settings.
The tables themselves don't seem to bloat much during ordinary operations but if we don't reindex performance tanks;
whenthe site has issues we sometimes see table bloat but it seems to be dealt with by autovac.
This is a postgres 9.1 instance w/ SSDs, lots of RAM (24 gigs) and relatively small tables (maybe a few thousands of
rowsin most cases, total footprint of the database is 240 megs) and being updated in the low thousands of times per
second.
Thanks!
Greg Williamson