David Kerr wrote:
I know that "Idle in TXs" can interfere with Vaccums for example, but I'm not sure if that's due to them usually having some form of lock on a table.
Locks aren't the issue. When you have a transaction open, the database makes sure it can deliver a consistent view of the database for the lifetime of that transaction, using MVCC: http://wiki.postgresql.org/wiki/MVCC
What this means in practice is that VACUUM may stop cleaning up old data because your open transaction might still need to look at it. Table maintenance can grind to a halt when you have one of these long running transactions. Dead rows (ones left behind by DELETE or UPDATE) will stop being recycled, tables will grow, queries will slow down.
If you're running 8.4 or later, there is a significant improvement to how pessimistic that gets in a typical case. To quote Alvaro, the author of that patch:
"I expect to be able to remove dead rows created by transactions that are no longer in progress, but which started more recently than some currently-open long-running transaction."
It's still something to be wary of. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
