Capturing statements that require tuning using pg_stat_statements

pg_stat_statements is an extension that enables us to see the history of SQL statements. It is part of the contrib module of PostgreSQL and is thus maintained by the community. In this recipe, we shall see how this extension can be installed and created to identify statements that require some tuning.

Getting ready

pg_stat_statements is an extension that is available with the contrib module. So, it requires the postgresql-contrib-13 package on Ubuntu or the postgresql13-contrib package on CentOS/Red Hat to be installed.

How to do it...

The following steps should be taken:

1. Use the following command to create the extension in the database that needs tuning:

$ psql -d migops -c "CREATE EXTENSION pg_stat_statements"

2. Set the share_preload_libraries parameter to pg_stat_statements using the following command. Please note that a change to shared_preload_libraries requires a restart of the PostgreSQL cluster:

$ psql...