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


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

Best Solution

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.