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