How can I tell if autovacuum is running on Windows Server 2003 for my PGSQL database?

Question

in the pre-8.x days I have run numerous PGSQL databases on Linux, and did the database vacuuming explicitly via maintenance script.

When 8.x came along I did not move to autovacuum on those systems on the basis that it was working fine so no need to change anything. So I am not an autovacuum expert.

Now in the post-8.x era I find myself with some PGSQL databases running on Windows Server 2003 or newer.

It looks like autovacuum should be running because we have followed the instructions and left it turned on in the .conf file.

However connecting up PGAdmin to the database prompts us that we should run VACUUM.

How can you tell if the autovacuum daemon is running and looking after your database(s), and vacuuming them correctly?

Answer 1

There are columns in pg_stat_user_tables that list when the last autovacuum and autoanalyze was run on a table - that's a good starting point. And of course, the logfile will contain information about what autovacuum is doing.

You can look specifically at the postgres.exe processes with a tool like Process Explorer. There will be a mutex specifically named to emulate the ps commandline information available on Unix. It should be easy to find. And you can just search for "autovacuum" to make sure it's running.