"David Esposito" <pgsql-general@esposito.newnetco.com> writes:
> You're right that the index behavior is well-behaved with the cycle of
> INSERT / DELETE / VACUUM ... But while it was running, I started a second
> session to the database after the 60th iteration and did
> BEGIN;
> SELECT COUNT(*) FROM bigboy;
> ROLLBACK;
> During my transaction, I saw the relpages charge upwards steadily until I
> issued the ROLLBACK ..
Sure. VACUUM can't remove rows that might still be visible to any open
transaction, so sitting with an open transaction is going to effectively
disable VACUUM. Client apps that hold open transactions for long
intervals are bad news (but this is true for many databases not just PG).
> but even after the ROLLBACK (and even after closing
> the second DB connection), the pages weren't reclaimed on the next VACUUM
Plain VACUUM doesn't try very hard to shorten the table physically, so
that's not surprising either. But the internal free space should get
picked up at this point.
This does not strike me as an explanation for ongoing bloat. There
are always going to be a few tuples not immediately reclaimable, but
normally that just factors in as part of the steady-state overhead.
Your VACUUM VERBOSE traces showed
DETAIL: 2 dead row versions cannot be removed yet.
DETAIL: 1 dead row versions cannot be removed yet.
so you're not having any major problem with not-yet-removable rows.
So I'm still pretty baffled :-(
regards, tom lane