Re: Reclaiming space

On 9/22/2010 12:18 PM, Scott Marlowe wrote:
> On Tue, Sep 21, 2010 at 9:10 PM, Christopher Gorge A. Marges
> <gorge(at)apollo(dot)com(dot)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./