BUG #13426: table bloat - dead rows not removed after vacuum

On Wed, Jun 10, 2015 at 10:02 AM, <jkoceniak@mediamath.com> wrote:

The following bug has been logged on the website:

Bug reference: 13426
Logged by: Jamie
Email address: jkoceniak@mediamath.com
PostgreSQL version: 9.1.14
Operating system: x86_64-unknown-linux-gnu debian
Description:

I have a table that is growing out of control.

After doing a vacuum, the dead rows still remain. Why can't they be
removed?

DETAIL: 214063622 dead row versions cannot be removed yet.

​One possibility is that ​
you have a long-running transaction holding up cleanup.

Viewing "pg_stat_activity" will be informative.

​David J.

Hi David,

No long running queries are in pg_stat_activity.
I was also able to take this same table and import onto another server, perform millions of deletes and inserts to recreate the problem.
The ‘Vacuum tablename‘ command is not removing dead row versions.

Thanks for the quick reply,
Jamie

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Wednesday, June 10, 2015 10:13 AM
To: Jamie Koceniak
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13426: table bloat - dead rows not removed after vacuum

On Wed, Jun 10, 2015 at 10:02 AM, <jkoceniak@mediamath.com<mailto:jkoceniak@mediamath.com>> wrote:
The following bug has been logged on the website:

Bug reference: 13426
Logged by: Jamie
Email address: jkoceniak@mediamath.com<mailto:jkoceniak@mediamath.com>
PostgreSQL version: 9.1.14
Operating system: x86_64-unknown-linux-gnu debian
Description:

I have a table that is growing out of control.

After doing a vacuum, the dead rows still remain. Why can't they be
removed?

DETAIL: 214063622 dead row versions cannot be removed yet.

​One possibility is that ​
you have a long-running transaction holding up cleanup.

Viewing "pg_stat_activity" will be informative.

​David J.

Jamie Koceniak <jkoceniak@mediamath.com> wrote:

David G. Johnston [mailto:david.g.johnston@gmail.com] wrote:

One possibility is that you have a long-running transaction
holding up cleanup.

No long running queries are in pg_stat_activity.

It doesn't take a long-running *query* to cause a problem; it only
takes a long-running *transaction*. Look for transactions which
are "idle in transaction" with an old xact_start value. Also check
the pg_prepared_xacts view for rows with a "prepared" value that is
more than a few seconds old. Either will prevent the cleanup of
dead rows.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs