Re: possible vacuum improvement? - Mailing list pgsql-hackers

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
totrigger 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. 


ByeShridhar

--
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