Hello. Thanks for the response. There are several 'idle in transaction' on this server/app, but to a different db/schema. The "repository" (JCR) schema has only a few 'idle', none 'in transaction' .
By "routine maintenance", do you mean autovacuum, or something else? Autovacuum does appear to usually get 'auto-canceled' by a lock. However, even when it runs successfully, it doesn't seem to help with this ws_bundle Toast table size. I am rather looking for a root cause here. Surely this table is not supposed to grow so much (100s of GB). It is even bigger than the data store! I agree that this may not be an 'error' in Postgres, but somehow it is not playing well with Jackrabbit clustering. On Mon, Jul 23, 2012 at 5:40 PM, Joshua D. Drake <j...@commandprompt.com>wrote: > > On 07/23/2012 02:13 PM, Gary Webster wrote: > >> Hello. I'm hoping someone has seen this before. >> >> We are trying to use Postgres Plus v9.1.3 as the Persistence Manager in >> Jackrabbit (Apache JCR) clustering >> (http://wiki.apache.org/**jackrabbit/Clustering<http://wiki.apache.org/jackrabbit/Clustering> >> ). >> Whenever the JCR is under load, the ws_bundle TOAST table in the >> repository schema, grows out of control ! >> >> Some of my team members maintain that this problem doesn't occur with >> MySQL, but I would rather stay with Postgres if possible... >> > > I don't really know anything about jackrabbit but generally this problem > presents when you have a lot of transactions that are idle. Meaning, you > have transactions that are just open, doing nothing. This will present a > problem with routine maintenance. > > Under load you can check your process list to see if you have long running > transactions that are idle ( idle in transaction ). If you do, you have a > code problem not a postgres problem and it is presenting itself through > bloat. > > Note: IDLE is fine. It is specifically IDLE IN TRANSACTION that is a > problem. > > > Sincerely, > > Joshua D. Drake > > > >> Thanks. >> >> > > -- > Command Prompt, Inc. - http://www.commandprompt.com/ > PostgreSQL Support, Training, Professional Services and Development > High Availability, Oracle Conversion, Postgres-XC > @cmdpromptinc - 509-416-6579 >
