Step 2: Enable pg_stat_statements

In this step we set up pg_stat_statements, which is used by pganalyze to collect Postgres query statistics.

Enable pg_stat_statements

Connect to the primary database you will be monitoring as avnadmin and run the following SQL statement to enable the pg_stat_statements extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify that pg_stat_statements returns data

As avnadmin, run the following statement:

SELECT calls, query FROM pg_stat_statements WHERE userid = current_user::regrole LIMIT 1;

If you have configured your database correctly, this will return a result like this:

 calls | query
-------+-------
     8 | SELECT * FROM t WHERE field = ?
(1 row)

If you get an error or get stuck, feel free to reach out to us: we're happy to help.

Next, we add helper functions to collect data from pg_stat_statements: