From: Tom Lane <tgl@sss.pgh.pa.us>
To: "S. Bob" <sbob@quadratum-braccas.com>
cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Vacuum not cleaning up rows.
In-reply-to: <15eddc60-6441-e55e-f742-bd7ea37f1873@quadratum-braccas.com>
References: <15eddc60-6441-e55e-f742-bd7ea37f1873@quadratum-braccas.com>
Comments: In-reply-to "S. Bob" <sbob@quadratum-braccas.com>
	message dated "Wed, 19 Jun 2019 17:39:53 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <31036.1560988222.1@sss.pgh.pa.us>
Date: Wed, 19 Jun 2019 19:50:22 -0400
Message-ID: <31037.1560988222@sss.pgh.pa.us>
Precedence: bulk

"S. Bob" <sbob@quadratum-braccas.com> writes:
> we have a table that consistently has 2x the number of dead rows than 
> live rows.
> I've done some digging and it looks like somehow the application is 
> holding shared locks that force vacuum to not cleanup rows.

This is not a locking issue; if it were, vacuum would be unable to
scan the table at all.

What it is is a snapshot issue: that is, some transaction has a snapshot
that's old enough to allow it to see those dead rows if it were to look.
So vacuum can't remove the rows for fear of causing transactional
consistency failures.

What you want to do is look into pg_stat_activity for transactions with
very old start times, and then cancel them or otherwise force them to
terminate.  Also, if there are no obvious candidates there, you might
be having a problem with prepared transactions --- look into
pg_prepared_xacts to see if there are any old ones of those.

			regards, tom lane