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