On Tue, Sep 21, 2010 at 9:10 PM, Christopher Gorge A. Marges
<gorge@apollo.com.ph> wrote:
> But how would the newer version prevent bloat and eliminate making the
> database unavailable while the *maintenance* goes on?
>
> The database is more than five years old, and we did not delete records
> until recently and when we do delete them, naturally the records are in
> front of the table and the lazy vacuum cannot reclaim the space. The full
> vacuum does since it consolidates all the unused space at the expense of
> locking the table. In our case our clients rely on our availability. The
> newer version(s) of postgres still has this problem of not reclaiming space
> unless we lock the table so we do not see any compelling reason to upgrade.
> But seeing 9.0 includes an improved vacuum makes it worth looking into.
There are a lot of good reasons to upgrade anyway, especially much
improved performance of newer versions of pg since 7.4 came out. Also
the fact that 7.4 is going out of support soon.
Note that the other suggestion about slony is a good idea as well, as
you could both upgrade AND remove bloat at the same time. Create a
new machine running 8.2, initiate replication, wait for it to catch
up, switch app over to new db which is now mostly bloat free.
Then, I think you might be able to get rid of the ongoing problem of
bloat if you were to partition your table. Create partitions, create
triggers, "insert into maintable select * from only maintable" will
then insert them into the child tables, which you can then just drop
or truncate without impacting the other partitions.