Set Up Database Monitoring for PostgreSQL

pg_stat_statements provides the per-query statistics and explain plan candidates used by Top Query Collection.

Add these settings to postgresql.conf (for example, /etc/postgresql/16/main/postgresql.conf):

1shared_preload_libraries = 'pg_stat_statements'
2pg_stat_statements.track = all
3pg_stat_statements.max = 10000
4track_io_timing = on

track_io_timing records block read and write time. Without it, the Avg Block Read Time and Avg Block Write Time charts stay at zero.

Restart PostgreSQL so the library loads:

1sudo systemctl restart postgresql

Then create the extension in every database you want to monitor:

1CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify that statistics are being recorded:

1SELECT calls, query FROM pg_stat_statements LIMIT 1;

Create a dedicated user for the agent. The pg_monitor role lets the agent read every session in pg_stat_activity, which query samples require.

1CREATE USER middleware_user WITH PASSWORD '<strong-password>';
2GRANT pg_monitor TO middleware_user;
3
4-- Repeat for each database you monitor
5GRANT CONNECT ON DATABASE <database_name> TO middleware_user;

To collect explain plans, the user must also be able to read the tables your queries use. Run this in each monitored database, for each schema:

1GRANT USAGE ON SCHEMA public TO middleware_user;
2GRANT SELECT ON ALL TABLES IN SCHEMA public TO middleware_user;
3ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO middleware_user;

The agent runs EXPLAIN, not EXPLAIN ANALYZE, so queries are planned but never executed. Plans for INSERT, UPDATE, and DELETE statements need the matching privilege on the table. Without it, those queries have no plan, and every other feature keeps working.

In Middleware, go to Installation → All Integrations → PostgreSQL. Enter the endpoint, database, and the middleware_user credentials as described in the PostgreSQL Integration guide, then turn on:

• Query Sample Collection: samples active sessions from pg_stat_activity.
• Top Query Collection: collects statistics from pg_stat_statements and explain plans for the top queries. Use Collect Top N Queries to limit how many queries are tracked.
• Schema Collection: collects table and index definitions. Use Exclude Schemas to skip schemas you do not need.

Optionally, use Databases to monitor and Databases to exclude to limit which databases on the server are collected. Leave both empty to monitor every database.

Click Save. The agent picks up the new configuration on its next configuration check.

Generate some database traffic, then open APM → Database:

1. The instance appears on the Databases tab under PostgreSQL.
2. Query Metrics and Samples list your queries.
3. Opening a query shows a plan in Explain Plans.
4. Schemas lists your tables. Schema data is refreshed periodically, so the first tables can take a few minutes to appear.