BUG #8058: CLUSTER and VACUUM FULL fail to free space

On 2013-04-12 08:34:24 +0000, daniel@heroku.com wrote:

The following bug has been logged on the website:

Bug reference: 8058
Logged by: Daniel Farina
Email address: daniel@heroku.com
PostgreSQL version: 9.0.13
Operating system: Ubuntu 10.04
Description:

We have a somewhat high-churn table acting as a queue, and over time it's
grown to be something like a gigabyte. I surmised it might be vanilla
bloat, but the truth seems somewhat more exotic because both VACUUM FULL and
CLUSTER generated absolutely no new free space.

In the end, ALTER TABLE and CREATE TABLE ... (LIKE) ran nearly instantly and
got the table size down to a few hundred K from 900M.

This caused quite a few problems because would normally be cheap index scan
over a mere 100 tuples were taking a few seconds.

There are TOASTed fields on this table, ranging in a few hundred bytes of
text per attribute.

We have retained the old bloated table so we can poke at it.

Could it be that you have old transactions around? That would explain
the issue since CLUSTER et al. will preserve rows that are still visible
to some existing transaction while CREATE TABLE ... LIKE won't.

Typical suspects would be longrunning (idle in) transactions or prepared
transactions.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

daniel@heroku.com writes:

We have a somewhat high-churn table acting as a queue, and over time it's
grown to be something like a gigabyte. I surmised it might be vanilla
bloat, but the truth seems somewhat more exotic because both VACUUM FULL and
CLUSTER generated absolutely no new free space.

In the end, ALTER TABLE and CREATE TABLE ... (LIKE) ran nearly instantly and
got the table size down to a few hundred K from 900M.

My money is on there being old idle transactions somewhere that kept
recently-dead rows from being reclaimable. If memory serves, VACUUM
FULL and CLUSTER will faithfully retain such rows, but of course a
manual data transfer like that wouldn't.

We have retained the old bloated table so we can poke at it.

I think contrib/pgstattuple could tell you about dead tuples.

regards, tom lane

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

On Fri, Apr 12, 2013 at 7:07 AM, Andres Freund <andres@2ndquadrant.com> wrote:

On 2013-04-12 08:34:24 +0000, daniel@heroku.com wrote:

The following bug has been logged on the website:

Bug reference: 8058
Logged by: Daniel Farina
Email address: daniel@heroku.com
PostgreSQL version: 9.0.13
Operating system: Ubuntu 10.04
Description:

We have a somewhat high-churn table acting as a queue, and over time it's
grown to be something like a gigabyte. I surmised it might be vanilla
bloat, but the truth seems somewhat more exotic because both VACUUM FULL and
CLUSTER generated absolutely no new free space.

In the end, ALTER TABLE and CREATE TABLE ... (LIKE) ran nearly instantly and
got the table size down to a few hundred K from 900M.

This caused quite a few problems because would normally be cheap index scan
over a mere 100 tuples were taking a few seconds.

There are TOASTed fields on this table, ranging in a few hundred bytes of
text per attribute.

We have retained the old bloated table so we can poke at it.

Could it be that you have old transactions around? That would explain
the issue since CLUSTER et al. will preserve rows that are still visible
to some existing transaction while CREATE TABLE ... LIKE won't.

Typical suspects would be longrunning (idle in) transactions or prepared
transactions.

You are right, except it's once-removed: at some point we've turned on
hot standby feedback to try to assuage some complaints about follower
lag knowing that this general class of symptom was possible, and this
is almost certainly the cause. Clearly, I didn't remember that or
think to check this time.

Sorry about the mis-report.

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

On Fri, Apr 12, 2013 at 10:46 AM, Daniel Farina <daniel@heroku.com> wrote:

On Fri, Apr 12, 2013 at 7:07 AM, Andres Freund <andres@2ndquadrant.com> wrote:

On 2013-04-12 08:34:24 +0000, daniel@heroku.com wrote:

The following bug has been logged on the website:

Bug reference: 8058
Logged by: Daniel Farina
Email address: daniel@heroku.com
PostgreSQL version: 9.0.13
Operating system: Ubuntu 10.04
Description:

We have a somewhat high-churn table acting as a queue, and over time it's
grown to be something like a gigabyte. I surmised it might be vanilla
bloat, but the truth seems somewhat more exotic because both VACUUM FULL and
CLUSTER generated absolutely no new free space.

In the end, ALTER TABLE and CREATE TABLE ... (LIKE) ran nearly instantly and
got the table size down to a few hundred K from 900M.

This caused quite a few problems because would normally be cheap index scan
over a mere 100 tuples were taking a few seconds.

There are TOASTed fields on this table, ranging in a few hundred bytes of
text per attribute.

We have retained the old bloated table so we can poke at it.

Could it be that you have old transactions around? That would explain
the issue since CLUSTER et al. will preserve rows that are still visible
to some existing transaction while CREATE TABLE ... LIKE won't.

Typical suspects would be longrunning (idle in) transactions or prepared
transactions.

You are right, except it's once-removed: at some point we've turned on
hot standby feedback to try to assuage some complaints about follower
lag knowing that this general class of symptom was possible, and this
is almost certainly the cause. Clearly, I didn't remember that or
think to check this time.

Sorry about the mis-report.

Oh yeah, and one more thing: somehow I was under the unsubstantiated
impression that VACUUM FULL/CLUSTER would wait for xmin to pass (like
DDL) when in operation, but in retrospect there's no reason why that
need be the case.

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

On Fri, Apr 12, 2013 at 7:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

daniel@heroku.com writes:

We have a somewhat high-churn table acting as a queue, and over time it's
grown to be something like a gigabyte. I surmised it might be vanilla
bloat, but the truth seems somewhat more exotic because both VACUUM FULL and
CLUSTER generated absolutely no new free space.

In the end, ALTER TABLE and CREATE TABLE ... (LIKE) ran nearly instantly and
got the table size down to a few hundred K from 900M.

My money is on there being old idle transactions somewhere that kept
recently-dead rows from being reclaimable. If memory serves, VACUUM
FULL and CLUSTER will faithfully retain such rows, but of course a
manual data transfer like that wouldn't.

We have retained the old bloated table so we can poke at it.

I think contrib/pgstattuple could tell you about dead tuples.

Yeah, you and Andres are on the mark, although the cause is
potentially a bit less visible: hot standby feedback. Also, my
misunderstanding of VACUUM FULL/CLUSTER's interaction with snapshots,
which I thought more similar to DDL for no reason in particular.

Sorry about the noise.

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