|
On 9/22/2010 12:18 PM, Scott Marlowe wrote: 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.On Tue, Sep 21, 2010 at 9:10 PM, Christopher Gorge A. Marges <gorge@xxxxxxxxxxxxx> 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. Christopher Gorge A. Marges Software Services Apollo Technologies, Inc. |
