Re: reclaiming diskspace bloat w/near-zero downtime

"Ed L." <pgsql(at)bluepolka(dot)net> writes:
> [SIDEBAR: Vacuum + fsm is not working as
> I expected; it is clearly not reclaiming space resulting from the UPDATEs.
> If I UPDATE 10000 rows and then run vacuum, I was expecting/hoping that a
> subsequent UPDATE of 9000 rows would largely reuse the space reclaimed from
> the 10000-row UPDATE.]

VACUUM can't reclaim rows until the last transaction that could
potentially see those rows is gone. I'd venture that your above
disappointment occurs because there is some other transaction staying
open across the update/vacuum sequence.

As for the problem at hand, I think it's insoluble under the constraints
you've set yourself. Bite the bullet and schedule some downtime. It'd
probably be worth trying CLUSTER rather than VACUUM FULL to compact the
table (I *think* CLUSTER is okay in 7.3.* but don't recall for sure...)

regards, tom lane