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

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.

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.
>

=E2=80=8BOne possibility is that =E2=80=8B
you have a long-running transaction holding up cleanup.

Viewing "pg_stat_activity" will be informative.

=E2=80=8BDavid J.

SGkgRGF2aWQsDQoNCk5vIGxvbmcgcnVubmluZyBxdWVyaWVzIGFyZSBpbiBwZ19zdGF0X2FjdGl2
aXR5Lg0KSSB3YXMgYWxzbyBhYmxlIHRvIHRha2UgdGhpcyBzYW1lIHRhYmxlIGFuZCBpbXBvcnQg
b250byBhbm90aGVyIHNlcnZlciwgcGVyZm9ybSBtaWxsaW9ucyBvZiBkZWxldGVzIGFuZCBpbnNl
cnRzIHRvIHJlY3JlYXRlIHRoZSBwcm9ibGVtLg0KVGhlIOKAmFZhY3V1bSB0YWJsZW5hbWXigJgg
Y29tbWFuZCBpcyBub3QgcmVtb3ZpbmcgZGVhZCByb3cgdmVyc2lvbnMuDQoNClRoYW5rcyBmb3Ig
dGhlIHF1aWNrIHJlcGx5LA0KSmFtaWUNCg0KDQpGcm9tOiBEYXZpZCBHLiBKb2huc3RvbiBbbWFp
bHRvOmRhdmlkLmcuam9obnN0b25AZ21haWwuY29tXQ0KU2VudDogV2VkbmVzZGF5LCBKdW5lIDEw
LCAyMDE1IDEwOjEzIEFNDQpUbzogSmFtaWUgS29jZW5pYWsNCkNjOiBwZ3NxbC1idWdzQHBvc3Rn
cmVzcWwub3JnDQpTdWJqZWN0OiBSZTogW0JVR1NdIEJVRyAjMTM0MjY6IHRhYmxlIGJsb2F0IC0g
ZGVhZCByb3dzIG5vdCByZW1vdmVkIGFmdGVyIHZhY3V1bQ0KDQpPbiBXZWQsIEp1biAxMCwgMjAx
NSBhdCAxMDowMiBBTSwgPGprb2Nlbmlha0BtZWRpYW1hdGguY29tPG1haWx0bzpqa29jZW5pYWtA
bWVkaWFtYXRoLmNvbT4+IHdyb3RlOg0KVGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2Vk
IG9uIHRoZSB3ZWJzaXRlOg0KDQpCdWcgcmVmZXJlbmNlOiAgICAgIDEzNDI2DQpMb2dnZWQgYnk6
ICAgICAgICAgIEphbWllDQpFbWFpbCBhZGRyZXNzOiAgICAgIGprb2Nlbmlha0BtZWRpYW1hdGgu
Y29tPG1haWx0bzpqa29jZW5pYWtAbWVkaWFtYXRoLmNvbT4NClBvc3RncmVTUUwgdmVyc2lvbjog
OS4xLjE0DQpPcGVyYXRpbmcgc3lzdGVtOiAgICB4ODZfNjQtdW5rbm93bi1saW51eC1nbnUgZGVi
aWFuDQpEZXNjcmlwdGlvbjoNCg0KSSBoYXZlIGEgdGFibGUgdGhhdCBpcyBncm93aW5nIG91dCBv
ZiBjb250cm9sLg0KDQpBZnRlciBkb2luZyBhIHZhY3V1bSwgdGhlIGRlYWQgcm93cyBzdGlsbCBy
ZW1haW4uIFdoeSBjYW4ndCB0aGV5IGJlDQpyZW1vdmVkPw0KDQpERVRBSUw6ICAyMTQwNjM2MjIg
ZGVhZCByb3cgdmVyc2lvbnMgY2Fubm90IGJlIHJlbW92ZWQgeWV0Lg0KDQrigItPbmUgcG9zc2li
aWxpdHkgaXMgdGhhdCDigIsNCnlvdSBoYXZlIGEgbG9uZy1ydW5uaW5nIHRyYW5zYWN0aW9uIGhv
bGRpbmcgdXAgY2xlYW51cC4NCg0KVmlld2luZyAicGdfc3RhdF9hY3Rpdml0eSIgd2lsbCBiZSBp
bmZvcm1hdGl2ZS4NCg0K4oCLRGF2aWQgSi4NCg0K

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