PostgreSQL performance monitoring using pg_stat_statements

To monitor the performance of one of our applications I asked to activate the pg_stat_statements module on PostgreSQL.

From the documentation:

The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server.

The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module.

So I asked the admins to enable the module and restart the server. Once I got confirmation that the restart was done I tried to call the stored procedure:

SELECT *
FROM
  pg_stat_statements
ORDER BY
  total_time DESC;

Unfortunately this resulted in an error message:

Query failed: ERROR: relation "pg_stat_statements" does not exist

Let’s check if the module is indeed installed:

SELECT * FROM pg_available_extensions

That seems OK. It turns out that you need to take one extra step before you can use this module. You have to execute following command:

CREATE EXTENSION pg_stat_statements; 

That’s it!