Fw: Postgresql backend to perform vacuum automatically

----- Original Message -----
From: "Nicolas Bazin" <nbazin@ingenico.com.au>
To: "mlw" <markw@mohawksoft.com>
Sent: Wednesday, March 06, 2002 10:21 AM
Subject: Re: [HACKERS] Postgresql backend to perform vacuum automatically

Also I think that each database should be vaccuumed sequentially and also
vacuum should check whether another instance is already at work.

I don't know whether a time delay should be used anyway. From the tests in
the ORACLE vs POSTGRESQL thread it appears that 5 sec is the best delay.
Vacuum should be smart enough to assess the performance gain / processing
penalty ratio and decide to go forth. So may be the number of update
threshold should be refined.
The idea would be to assess how much the vacuum will penalize access to a
specific database and then what performance improvement we can expect. The
DBA can then set a ratio to start vacuum.
The statistic should compute the ratio and set a flag when a vacuum is
needed. A trigger can be set on the flag field update to start vacuuming.

----- Original Message -----
From: "mlw" <markw@mohawksoft.com>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "Neil Padgett" <npadgett@redhat.com>; "Tom Lane" <tgl@sss.pgh.pa.us>;
"Nicolas Bazin" <nbazin@ingenico.com.au>; <pgsql-hackers@postgresql.org>
Sent: Wednesday, March 06, 2002 8:56 AM
Subject: Re: [HACKERS] Postgresql backend to perform vacuum automatically

Bruce Momjian wrote:

Neil Padgett wrote:

On Tue, 2002-03-05 at 15:59, Bruce Momjian wrote:

If they do not affect performance, then why have them off?

I think Jan said 2-3%. If we can get autovacuum from it, it

would be a

win to keep it on all the time, perhaps.

Assuming that the statistics get updated:

How often should the sats table be queried?
What sort of configurability would be needed?

You could wake up every few minutes and see how the values have

changed.

I don't remember if there is a way to clear that stats so you can

see

just the changes in the past five minutes. Vacuum the table that

had

activity.

Ick -- polling. The statistics process should be able to wake

somebody

up / notify the postmaster when the statistics change such that a

vacuum

is required.

Yes, that would tie that stats collector closer to auto-vacuum, but it
certainly could be done.

Using an alert can be done, but polling is easier for a proof of

concept.