May 2018
Intermediate to advanced
576 pages
30h 25m
English
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/current/static/bgworker.html.
The following query adds a snapshot of current usage statistics with a timestamp to the table created earlier:
INSERT INTO backup_stat_user_tables SELECT current_timestamp AS snaptime, * FROM pg_stat_user_tables; ...