PostgreSQL 11 Administration Cookbook

Collecting daily usage statistics

It is often useful to have historical usage statistics for tables when trying to solve performance problems or understand usage patterns.

For this purpose, you can collect usage data in a regular manner, daily or even more often, using either a cron or a PostgreSQL-specific scheduler such as pg_agent. Advanced users can take advantage of background workers to schedule such an activity. For more information on background worker processes, go to http://www.postgresql.org/docs/11/static/bgworker.html.

The following query adds a snapshot of current usage statistics with a timestamp to the table we created earlier:

INSERT INTO backup_stat_user_tables       SELECT current_timestamp AS snaptime,* FROM pg_stat_user_tables; ...