how to automatically determine which tables need a vacuum / reindex in postgresql

Question

i've written a maintenance script for our database and would like to run that script on whichever tables most need vacuuming/reindexing during our down time each day. is there any way to determine that within postgres?

i would classify tables needing attention like this:

• tables that need vacuuming
• tables that need reindexing (we find this makes a huge difference to performance)

i see something roughly promising here

Answer 1

It sounds like you are trying to re-invent auto-vacuum. Any reason you can't just enable that and let it's do it's job?

For the actual information you want, look at *pg_stat_all_tables* and *pg_stat_all_indexes*.

For a good example of how to use the data in it, look at the source for auto-vacuum. It doesn't query the views directly, but it uses that information.

Answer 2

I think you really should consider auto-vacuum.

However, if i did understood right your needs, that's what i'll do:

• For every table (how many tables do you have?) define the criterias;

For example, talbe 'foo' need to be reindex every X new records and vacuum every X update, delete or insert

• Write out your own application to do that.

Every day it check the tables status, save it in a log (to compare the rows difference over the time), and then reindex/vacuum the tables whose match yours criterias.

Sounds a little hacking, but i think is a good way to do an custom-autovacuum-with-custom-'triggers'-criteria

Answer 3

How about adding the same trigger function that runs after any CRUD action to all the tables.

The function will receives table name, checks the status of the table, and then run vacuum or reindex on that table.

Should be a "simple" pl/sql trigger, but then those are never simple...

Also, if your DB machine is strong enough, and your downtime long enough, just run a script every night to reindex it all, and vacuum it all... that way even if your criteria was not met at test time (night) but the was close to it (few records less than your criteria), it will not pose an issue the next day when it does reach the criteria...