Re: Performance of autovacuum and full vacuum of database

This is also true in my situation, where I have some medium sized
tables, which have a always just a handful of rows heavily updated. The
amount of updates is not too big related to the size of the table, but
the repeated update of the same row will cause problems before
autovacuum will kick in, as I understood. In this case I would need a
far smaller threshold for this table as for others.

I wonder how hard would it be to implement a rule system for autovacuum,
maybe kept in it's dedicated "pg_" table ? I would even volunteer to
write this if somebody would point me in the right direction, even if
I'm not a C guy and not too familiar with postgres internals.

Another possibility would be to integrate the vacuum rules into the
creation of the table. The info would then go to the meta-data of the
table. It would also allow to dynamically change it from a SQL prompt
versus fiddling with config files.

Does this sound reasonable ? As I said, it's important enough for me to
allocate some time to implement it, but I need assistance, as I'm a Java
programmer and have no experience with C or postgres code (other than
occasionally hacking some already existing code to make it work as I
want, postgres included - for a long time we've run a patched version
where the foreign key checks did not lock the parent row exclusively,
now I can throw away that patch thanks to 8.1).

Cheers,
Csaba.

On Fri, 2005-11-11 at 17:04, Vivek Khera wrote:
> On Nov 10, 2005, at 3:43 PM, Matthew T. O'Connor wrote:
>
> > Yes exactly, and if you find that pg_autovacuum is never or not
> > often enough firing off vacuum comands, then you will need to play
> > with the threshold settings. The default thresholds for
> > pg_autovacuum are too conservative for most people, so you may very
> > well have to do this.
>
> Another issue with autovacuum (haven't investigated the 8.1 version
> yet) is that you can't make different threshhold settings for
> different tables. For example, I have some tables that are a handful
> of rows but are updated bazillions of times per day, and other tables
> with 10's of millions of rows updated perhaps 100k times per day.
> autovacuum would go several days without vacuuming some of the
> tables, which was just bad...
>
> I don't think it is suitable for all situations.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend