The dangers of long running open transactions

Summary: Long running transaction from an orphaned database connection
caused major slowness on very active and frequently vacuumed tables
because vacuum could not remove rows that were newer than the long
running transaction.

A while ago, I asked for opinions on the effects of long running
transactions on the ability of the system to effectivly vacuum tables.
I got a response from Tom Lane saying that having transactions open for
long periods of time could indeed cause issues with vacuuming.

On one of our database servers, we ended up having this happen -- mind
you that we have monitoring to tell us if we get close to issues in
regards to the actual frozenxids getting too old to ensure vacuuming
happens and do have regular (in fact, multiple times a day) vacuums.
Our problem came from a very active table becoming quite slow to deal
with. Explain analyze select count(*) on this table showed us that even
though there were less than a thousand rows in this table, the cost in
rows was much higher for the sequential scan. I don't remember the
exact number, but select count(*) was taking around 20 seconds, and
inserts / deletes were happening to this table on a regular basis. The
number of rows analyze was exposed to would NOT go down after vacuums of
this table.

Due to the question I asked the list earlier, I determined that it was
best to kill all the 'idle in transaction' connections (which we thought
were not holding any transactions for more than 60 seconds and cycling
to new xids since we were aware that long running transactions were bad)
and try vacuuming again. This did the trick, the table was successfully
vacuumed and was 'instant' fast once again.

A review of system monitor logs showed me that one of the 'idle in
transaction' connections did NOT come back (our software will handle
database disconnections and reconnect). It turns out that a 'worker
thread' in a process on a remote machine had either become stuck / died
during an open transaction and the database connection was leaked since
the process itself did not terminate. This left a long running
transaction that was never committed / rolled back for a long period of
time, up until it caused massive slowness when vacuums were proving to
be worthless on very active tables.

We have started to monitor for potentially stuck / long running
transactions with the following query:

select pid, transaction, age(transaction) from pg_locks where
transaction is not null order by age(transaction) desc;

This can be changed around obviously for whatever monitoring software
you use / write, but the basic idea is that the maximum age column tells
you 'how long ago in xids' vacuum must preserve data when it vacuums
tables due to a given connection process. If this number gets big, in
our case, we warn if they get over 20k (we have lots of transactions) so
we can track down and prevent any issues in advance, action should be
taken to prevent database performance problems / at worst, xid roll
around problems if the problem went on for too long.

Wayne