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

indexingoptimizationpostgresqlvacuum

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.

Related Solutions

PostgreSQL: Show tables in PostgreSQL

From the psql command line interface,

First, choose your database

\c database_name

Then, this shows all tables in the current schema:

\dt

Programmatically (or from the psql interface too, of course):

SELECT * FROM pg_catalog.pg_tables;

The system tables live in the pg_catalog database.

PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

You can use the REASSIGN OWNED command.

Synopsis:

REASSIGN OWNED BY old_role [, ...] TO new_role

This changes all objects owned by old_role to the new role. You don't have to think about what kind of objects that the user has, they will all be changed. Note that it only applies to objects inside a single database. It does not alter the owner of the database itself either.

It is available back to at least 8.2. Their online documentation only goes that far back.

Best Solution

Related Solutions

PostgreSQL: Show tables in PostgreSQL

PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

Synopsis:

Related Question