Once installed, the pgpro_stats extension starts collecting statistics on the executed statements. The collected data is similar to the one provided by pg_stat_statements, but also includes information on query plans and wait events for each query type. The statistics is saved into an in-memory ring buffer and is accessible through the pgpro_stats_statements view.

By default, pgpro_stats collects statistics on all the executed statements that satisfy the pgpro_stats.track and pgpro_stats.track_utility settings. If performance is a concern, you can set a sample rate for queries using the pgpro_stats.query_sample_rate parameter, and pgpro_stats will randomly select queries for statistics calculation at the specified rate.

To collect statistics on wait events, pgpro_stats uses time-based sampling. Wait events are sampled at the time interval specified by the pgpro_stats.profile_period parameter, which is set to 10ms by default. If the sampling shows that the process is waiting, the pgpro_stats.profile_period value is added to the wait event duration. Thus, time estimation for each wait event remains valid even if the pgpro_stats.profile_period parameter value has changed. If you are not interested in wait event statistics, you can disable wait event sampling by setting the pgpro_stats.enable_profile parameter to false.

pgpro_stats_statements.plans and pgpro_stats_statements.calls aren't always expected to match because planning and execution statistics are updated at their respective end phase, and only for successful operations. For example, if a statement is successfully planned but fails during the execution phase, only its planning statistics will be updated. If planning is skipped because a cached plan is used, only its execution statistics will be updated.

As an example, let's create a table with some random data and build an index on this table:

CREATE TABLE test AS (SELECT i, random() x FROM generate_series(1,1000000) i);
CREATE INDEX test_x_idx ON test (x);

Now run the following query several times using different values for :x_min and :x_max:

SELECT * FROM test WHERE x >= :x_min AND x <= :x_max;

The collected statistics should appear in the pgpro_stats_statements view:

SELECT queryid, query, planid, plan, wait_stats FROM pgpro_stats_statements WHERE query LIKE 'select * from test where%';
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | 8287793242828473388
plan       | Gather
           |   Output: i, x
           |   Workers Planned: 2
           |   ->  Parallel Seq Scan on public.test
           |         Output: i, x
           |         Filter: ((test.x >= $3) AND (test.x <= $4))
           |
wait_stats | {"IO": {"DataFileRead": 10}, "IPC": {"BgWorkerShutdown": 10}, "Total": {"IO": 10, "IPC": 10, "Total": 20}}
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | -9045072158333552619
plan       | Bitmap Heap Scan on public.test
           |   Output: i, x
           |   Recheck Cond: ((test.x >= $3) AND (test.x <= $4))
           |   ->  Bitmap Index Scan on test_x_idx
           |         Index Cond: ((test.x >= $5) AND (test.x <= $6))
           |
wait_stats | {"IO": {"DataFileRead": 40}, "Total": {"IO": 40, "Total": 40}}
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | -1062789671372193287
plan       | Seq Scan on public.test
           |   Output: i, x
           |   Filter: ((test.x >= $3) AND (test.x <= $4))
           |
wait_stats | NULL
-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------
queryid    | 1109491335754870054
query      | select * from test where x >= $1 and x <= $2
planid     | -1748292253893834280
plan       | Index Scan using test_x_idx on public.test
           |   Output: i, x
           |   Index Cond: ((test.x >= $3) AND (test.x <= $4))
           |
wait_stats | NULL

With pgpro_stats, you can define custom metrics to be monitored. The collected data will be saved into an in-memory ring buffer and then sent to a monitoring system. Unlike direct polling of a database by a monitoring system that can lose some data if the connection is interrupted, this approach allows to get all the collected data regardless of connection issues, as long as this data is still available in the ring buffer.

To set up a custom metric to collect, do the following:

The statistics gathered by the module are made available via a view named pgpro_stats_statements. This view contains one row for each distinct database ID, user ID and query ID (up to the maximum number of distinct statements that the module can track). The columns of the view are shown in Table F.63.

The aggregate statistics gathered by the module are made available via a view named pgpro_stats_totals. This view contains one row for each distinct object (up to the maximum number of distinct objects that the module can track). The columns of the view are shown in Table F.64.

The pgpro_stats_inval_status view shows one row with the current status of the global cache invalidation queue. The columns of the view are shown in Table F.65.

In a working system, num_inval_messages usually approximately equals 4000, which means that the queue is pretty full. Growing num_inval_queue_cleanups indicates an active generation of messages. Growth of num_inval_queue_resets is normally zero, and a non-zero growth indicates either a too fast generation of messages or delays in processing messages by backends. You can see reset counters for each backend in the cache_resets column of the pgpro_stats_totals view.

The metrics gathered by pgpro_stats are displayed in the pgpro_stats_metrics view. The table below describes the columns of the view.

pgpro_stats_rusage is a record type that contains resource usage statistics of statement planning/execution. The fields of this type are shown in Table F.67.

pgpro_stats_inval_msgs is a record type containing counters for cache invalidation messages. The fields of the type are shown in Table F.68.

The following parameters can be used to define a custom metric to collect. The N placeholder in the parameter name serves as a unique identifier of the metric to which this setting should apply; it must be set to a non-negative integer for each metric.

When you add these parameters for a new metric, you have to restart the server for the changes to take effect. Once the new metric is added, its parameters can be changed without a server restart by simply reloading the postgresql.conf configuration file.