How to do it…

You can collect information by executing the following query for each database that you intend to monitor:

SELECT collect_deltas();

This will collect the changes in the pg_stat_user_* and pg_statio_user_* tables that have occurred since the last invocation.

You should probably set up a cron job to run on a regular basis so that you have good coverage of what happens at what time of the day and week. Running it at an interval of 5 – 15 minutes should usually give you enough temporal resolution to understand what is going on with your tables.

For example, you can add this (or a similar variation) to the postgres user's cron table:

*/5 * * * * /usr/bin/psql -c 'SELECT collect_deltas()' mydbname