Collect regular statistics from pg_stat* views

This recipe describes how to collect the statistics needed to understand what is going on in the database system on a regular basis so that they can be used to further optimize the queries that are slow or are becoming slow as the database changes.

We have included an example extension, called pgstatslog. It can be used to track these changes. The extension works on PostgreSQL 9.1+.

Look at the Using an installed module and managing installed extensions recipes from Chapter 3, Configuration, for an overview of the extensions infrastructure in PostgreSQL.

Getting ready

Find the pgstatslog directory in the set of files distributed along with this book.

Find out the directory to place shared files using pg_config --sharedir and then copy the files to the extension subdirectory of that directory.

Now that the extension has been installed in your PostgreSQL server, it is time to install it in each database that you want to monitor:

postgres=# CREATE EXTENSION...