Re: possible vacuum improvement?

On 3 Sep 2002 at 9:49, Tom Lane wrote:

> In terms of what might happen for 7.4 or beyond, what I'd personally
> like to see is some "auto vacuum" facility that would launch background
> vacuums automatically every so often. This could (eventually) be made
> self-tuning so that it would vacuum heavily-updated tables more often
> than seldom-updated ones --- while not forgetting the
> every-billion-transactions rule...

OK, I plan to work on this. Here is my brief idea

1)Create a table (vacuum_info) that stores table name and auto vacuum defaults.
Since I am planning this in contrib, I would not touch pg_class.

The table will store
- table names
- number of transactions to trigger vacuum analyze(default 1K)
- number of transactions to trigger full vacuum(default 10K)

A trigger on pg_class i.e. table creation should add a row in this table as
well.

2)Write a trigger on tables that updates statistics on table activity. I see

-pg_stat_all_tables
-pg_stat_sys_tables
-pg_stat_user_tables.

The columns are

-n_tup_ins
-n_tup_upd
-n_tup_del

Of course it will ignore it's own updates and inserts to avoid infinite loops.
This will update the pseudo statistics in vacuum_info table

Another trigger on vacuum_info will trigger vacuum if required. Ideally I would
write it in external multithreaded library to trigger vacuum in background
without blocking operations on vacuum_info table.

I need to know the following..

1)Is this sounds like a workable solution?

2)Is this as simple as I have put here or am I missing some vital components?

3)Is there some kind of rework involved?

4)Is use of threads sounds portable enough? I just need to trigger a thread in
background and return. No locking, nothing is required. Will there be any
problem for postgres invoking such an external trigger?

5)When I create a function in a .so, is it possible to invoke init/startup
routines? I can create and destroy thread in these routine to avoid thread
creation overhead. If postgres is using dlopen, I can use _init, _fini.

6)such a 'daemon' would be on per back-end basis if I am guessing correctly.
Would locking things in transactions for vacuum_info be sufficient?

I hope I am making a sensible proposal/design(My first attempt to contribute to
postgres). Please let me know your comments.

Bye
Shridhar

--
Blast medicine anyway! We've learned to tie into every organ in thehuman body
but one. The brain! The brain is what life is all about. -- McCoy, "The
Menagerie", stardate 3012.4