Re: Postgresql backend to perform vacuum automatically

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Tuesday, March 05, 2002 12:59 PM
To: mlw
Cc: Tom Lane; Nicolas Bazin; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Postgresql backend to perform vacuum
automatically


> > > 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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>
How long does it take to vacuum a table with 12 indexes and 100 million
rows in it?  This idea is making me very nervous.  Suppose (for
instance)
that we have regular updates to some table, and it is constantly getting

vacuum attempts thrown at it.

Now imagine a large systems with many large tables which are frequently
receiving updates.  Would 100 simultaneous vacuum operations be a good
thing when .0001% of the table has changed [on average] for each of
them?

I know for sure "update statistics" at regular intervals on some of the
SQL systems I have used would be sheer suicide.

Better make it configurable, that's for sure.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
<<<<