Why pg_stat_statements Shows Queries from All Databases in the PostgreSQL agent (4379662)

Foglight users may notice SQL statements from multiple databases, even ones where pg_stat_statements wasn’t explicitly created. This article explains why that happens and when you need to run CREATE EXTENSION in each database.

PostgreSQL’s pg_stat_statements extension uses shared memory at the cluster level, not per-database. Here's how it works:

• When pg_stat_statements is listed in shared_preload_libraries (in postgresql.conf), PostgreSQL begins collecting query statistics globally across all databases.
• Each database contributes its query activity to the same shared memory structure.
• The CREATE EXTENSION pg_stat_statements; command is only needed to expose the view in a specific database — it does not control whether stats are collected.
• Foglight queries the pg_stat_statements view from the database it connects to (e.g., postgres), and that view pulls data from shared memory — which includes queries from all databases.

Step 1: Enable the Extension in at Least One Database (required for Foglight)

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

This exposes the view needed to query the shared memory collector.

Step 2: (Optional) Enable in Other Databases

If you want to query stats directly from other databases (e.g., for dashboards or local monitoring), run the same command in each: 

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

This is not required for Foglight, unless it’s configured to connect to those specific databases.

Step 3: Grant Access to Monitoring Users

Ensure the Foglight agent user has access

GRANT pg_monitor TO foglight;

This role includes access to pg_stat_statements and other monitoring views.

Summary

Example

If pg_stat_statements is enabled only in mydb, running:

SELECT datname, query FROM pg_stat_statements;

…will still show queries from postgres, template1, and other databases because the stats are pulled from shared memory.

Agent Statement Tracking Configuration

DB w/ pg_stat_statements view is a configurable field under the Agent Status Property's Statement Tracking group. Only one database can be specified in this field.

This setting determines where the agent queries the pg_stat_statements view. However, the results will reflect SQL activity across all databases on the server, because pg_stat_statements collects statistics globally.

The field is configurable to provide flexibility, for example, if the client prefers to create the view in a database other than the default postgres database.