Re: large table

On Mon, 22 Sep 2014 12:46:21 -0700
John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 9/22/2014 12:33 PM, Luke Coldiron wrote:
> >
> > It is possible and that is part of what I am trying to discover
> > however I am very familiar with the system / code base and in this
> > case there is a single process updating the timestamp and a single
> > process reading the timestamp. There are no other user processes
> > programmed to interact with this table outside of potentially what
> > Postgres is doing.
>
> ANY other connection to the same postgres server, even to a different
> database, that has an open long running transaction (most frequently,
> "Idle In Transaction") will block autovacuum from marking the old tuples
> as reusable.

As a possibility, I've seen this happen when people connected to the DB
using various GUI tools (can't remember the exact one where we saw this)
that started and held open a transaction without the user realizing it.
This prevented autovacuum from getting any useful work done until our
Nagios monitoring detected the idle transaction and an engineer tracked
down who was doing it and had them close the program. IMHO, too many
GUI tools make it too easy to do something without realizing the
consequences.

On a related note, I'm curious as to how an open transaction affects HOT
updates (if at all). This is an area of behavior I have little experience
with to date.

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com