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

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