Second attempt, roll your own autovacuum

Glen Parker <glenebob@nwlink.com>

From: Glen Parker <glenebob@nwlink.com>
To: Postgres General <pgsql-general@postgresql.org>
Date: 2006-12-18T21:31:03Z
Lists: pgsql-hackers
Hi all,

I am still trying to roll my own auto vacuum thingy.  The goal is to 
vacuum on demand in one step just like the old days, but not hit the 
tables that never change (we have a lot).  The idea now is to use a 
combination of SQL and shell scripts to duplicate some of what auto 
vacuum does.  It actually doesn't seem that difficult.  I have some SQL 
that produces a list of tables that need vacuuming based on statistics 
found in pg_stat_user_tables, and reltuples from pg_class, using the 
same basic rules as auto vacuum per the documentation.  So far so good. 
  The SQL actually produces an SQL script containing VACUUM commands, 
which I can then feed back into psql.  The result is a HUGE savings in 
vacuum time at night.

The trouble now is, I don't see how to reset the statistics.  My 
assumption was that vacuum did it, but that appears to be false.  How 
does autovacuum do it?  Can I do it with SQL?

-Glen