Re: reclaiming diskspace bloat w/near-zero downtime

On Friday December 3 2004 9:09, Tom Lane wrote:
> "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.

That sounds very possible; these systems are under heavy load most all the
time. Anytime vacuum starts, there are almost certainly other transactions
in process. Wondering how to confirm/report this? I know how to grab a
list of backends and their current queries, but is there a query to grab a
snapshot of open transactions for before and after comparisons?

I assume upgrading to 7.4.6 would not help this particular issue if that's
what's going on?

Sounds like 24x7x365 operations on a single cluster is maybe too tall of an
order under these loads. Maybe time for slony & friends for maintenance
switchovers.

Ed