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

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.

regards, tom lane