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

On Fri, Apr 12, 2013 at 7:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> daniel(at)heroku(dot)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.