On Fri, Apr 12, 2013 at 10:46 AM, Daniel Farina <dan...@heroku.com> wrote: > On Fri, Apr 12, 2013 at 7:07 AM, Andres Freund <and...@2ndquadrant.com> wrote: >> On 2013-04-12 08:34:24 +0000, dan...@heroku.com wrote: >>> The following bug has been logged on the website: >>> >>> Bug reference: 8058 >>> Logged by: Daniel Farina >>> Email address: dan...@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
