On 9/22/2010 12:18 PM, Scott Marlowe wrote:
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.
Never thought of using slony that way. Also the partitioning stuff is *new* to me and it looks like a good idea. I admit I am not up to speed with the new features as I am a software developer by profession and far from a db expert. Will try this out. Thank you very much.
Christopher Gorge A. Marges
Software Services
Apollo Technologies, Inc.