Re: Postgresqlism & Vacuum? - Mailing list pgsql-general

From Haroldo Stenger
Subject Re: Postgresqlism & Vacuum?
Date
Msg-id 38F7BC27.A0E34126@adinet.com.uy
Whole thread Raw
In response to RE: Postgresqlism & Vacuum?  ("Andrew Snow" <als@fl.net.au>)
List pgsql-general
Andrew Snow wrote:
>
> > Okay, this *used* to be a problem way way back, but I definitely don't
> > vacuum my databases nightly ... most times I don't do it until something
> > odd comes up that I figure that I may as well vacuum first to see if it
> > makes a differnece ...
> > vacuum'ng once a week, unless you one helluva insert/update/delete
> > intensive table ...
>
> Well this is where the problem is.  The whole Vacuum thing is not bad for
> most people as it allows doing things like reclaiming space and gathering
> some statistics, during off-peak times, and also to run it less frequently
> on databases that don't change often.  The problem is for those who have
> 24/7 continuous use of the database, combined with a high frequency of
> insert/update/delete, so that vacuuming regularly becomes a necessity.
>

How about:

VaccumAnalyzeDualProcedure
1. Use 2 Mirror Databases D1 and D2, every time there is an
insert/update/delete, apply it twice, one on each database.
2. Make D1 offline at a moment when no transactions are open.
3. Log transactions from now on, which apply on D2 (just
insert/update/delete).
4. Run VACUUM ANALYZE on D1.
5. When finished, apply the log on D1.
6. Make D2 offline at a moment when no transactions are open.
7. Make D1 online.
7. Log transactions from now on, which apply on D1 (just
insert/update/delete).
8. Run VACUUM ANALYZE on D2.
9. When finished, apply the log on D2.
10. Make D2 online.

Cheers,
Haroldo.

pgsql-general by date:

Previous
From: The Hermit Hacker
Date:
Subject: RE: Postgresqlism & Vacuum?
Next
From: Tatsuo Ishii
Date:
Subject: Re: [Fwd: [HACKERS] Porting reports (cont'd)]