On Wed, 2006-06-21 at 18:21, jody brownell wrote: > That is interesting. > > There is one thread keeping a transaction open it appears from ps > > postgres: app app xxx(42644) idle in transaction That shouldn't be a problem on itself, "idle in transaction" happens all the time between 2 commands in the same transaction... you only have a problem if you see the same PID always "idle", that means somebody left an open transaction and left for lunch. [snip] > this was with the "Idle in transaction" though..... This probably means you don't have long running transactions currently. However, if you happen to have just one such long transaction, the dead space accumulates and normal vacuum will not be able to clean that anymore. But I guess if you didn't find one now then you should take a look at Tom's suggestion and bump up debug level to see if autovacuum picks your table at all... > Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence. > Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline. > > I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often. > > make sense? I don't think that's your problem... vacuum wouldn't be locked out by any activity which doesn't lock exclusively the table (and I guess you're not doing that). If your persistence finishes quickly then that's not the problem. Oh, just occured to me... in order to use autovacuum you also need to enable the statistics collector on row level: stats_start_collector = on stats_row_level = on See also: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM This was not mentioned in the settings in your original post, so I guess you didn't touch that, and I think they are disabled by default. If this is disabled, you should enable it and "pg_ctl reload ....", that should fix the problem. Cheers, Csaba.
