How to discover what a Postgresql table's usage is?

Question

Our team is working on a Postgresql database with lots of tables and views, without any referential constraints. The project is undocumented and there appears to be a great number of unused/temporary/duplicate tables/views dirtying the schema.

We need to discover what database objects have real value and are actually used and accessed. My inital thoughts were to query the Catalog/'data-dictionary'.

Is it possible to query the Postgresql Catalog to find an object's last query time.

Any thoughts, alternative approaches and or tools ideas?

Answer 1

Not sure about the last query time but you can adjust your postgresql.conf to log all SQL:

log_min_duration_statement = 0

That will at least give you an idea of current activity.

Answer 2

Check the Statistics Collector

Answer 3

Reset the statistics, pg_stat_reset(), and then check the pg catalog tables like pg_stat_user_tables and such to see where activity looks like its showing up.