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.
Date: Wed, 19 Jun 2019 19:50:22 -0400
Message-ID: <31037.1560988222@sss.pgh.pa.us> (raw)
In-Reply-To: <15eddc60-6441-e55e-f742-bd7ea37f1873@quadratum-braccas.com>
References: <15eddc60-6441-e55e-f742-bd7ea37f1873@quadratum-braccas.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, sbob@quadratum-braccas.com
Subject: Re: Vacuum not cleaning up rows.
In-Reply-To: <31037.1560988222@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox