G.2. pgpro_stats

The pgpro_stats extension provides a means for tracking planning and execution statistics of all SQL statements executed by a server. It is based on the pg_stat_statements module and provides the following additional functionality:

  • Storing query plans in addition to query statements.

  • Configuring sample rate for statistics collection to reduce overhead.

  • Calculating wait event statistics for executed queries.

  • Calculating resource usage statistics of statement planning and execution.

  • Calculating cache invalidation statistics.

  • Calculating additional archiver statistics.

  • Providing an interface to statistics about vacuuming databases, tables, and indexes collected by the core system.

  • Tracing of application sessions.

  • Creating views that emulate other extensions.

The background information, along with views and types, related to calculating cache invalidation statistics is provided in a separate section Section G.2.8.

G.2.1. Limitations

  • pgpro_stats can sometimes fail to match identical parameters in the query statement and the corresponding query plan.

  • Some SPI queries are not included into statistics.

  • Texts and plans of some SPI queries are not normalized.

  • pgpro_stats is incompatible with pg_stat_statements, as well as other extensions that use parser, planner, or executor hooks to modify parse and plan trees and execution of the queries. Note also that in order to dump the final versions of the queries and plans, pgpro_stats should be the last on the list of shared_preload_libraries, but some existing extensions, such as pg_pathman, will not work at all unless they are the last on this list.

  • pgpro_stats may not work correctly with third-party extensions that produce CustomScan and ForeignScan nodes.

G.2.2. Installation and Setup

pgpro_stats is provided with Postgres Pro Standard as a separate pre-built package pgpro-stats-std-14 (for the detailed installation instructions, see Chapter 16). Once you have pgpro_stats installed, complete the following steps to enable pgpro_stats:

  1. Add pgpro_stats to the shared_preload_libraries parameter in the postgresql.conf file:

    shared_preload_libraries = 'pgpro_stats'
    
  2. Restart the Postgres Pro Standard instance for the changes to take effect.

    Once the server is reloaded, pgpro_stats starts tracking statistics across all databases of the cluster. If required, you can change the scope of statistics collection or disable it altogether using pgpro_stats configuration parameters.

  3. To access the collected statistics, you have to create pgpro_stats extension:

    CREATE EXTENSION pgpro_stats;
    

In addition, query identifier calculation must be enabled in order for pgpro_stats to be active, which is done automatically if compute_query_id is set to auto or on, or any third-party module that calculates query identifiers is loaded.

G.2.3. Usage

G.2.3.1. Collecting Statistics on Query Statements and Plans

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

G.2.3.2. Monitoring Custom Metrics

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:

  1. For each metric, define all configuration parameters listed in Section G.2.7.2. You must specify a unique numeric identifier of each metric in the parameter names.

    For example, to monitor index bloating each 60 seconds, you can define a new metric by setting metrics-related parameters as follows:

    pgpro_stats.metric_1_name = index_bloat
    pgpro_stats.metric_1_query = 'select iname, ibloat, ipages from bloat'
    pgpro_stats.metric_1_db = 'postgres'
    pgpro_stats.metric_1_user = postgres
    pgpro_stats.metric_1_period = '60s'
    

  2. Restart the server.

    pgpro_stats starts collecting statistics on executed statements and saves it into the ring buffer, and the collected data appears in the pgpro_stats_metrics view:

    SELECT * FROM pgpro_stats_metrics;
    

    Once the new metric is added, its parameters can be changed without a server restart by simply reloading the postgresql.conf configuration file.

  3. If required, set up data export to a monitoring system of your choice.

G.2.4. Views

G.2.4.1. The pgpro_stats_statements View

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 G.71.

Table G.71. pgpro_stats_statements Columns

NameTypeReferencesDescription
useridoidpg_authid.oidOID of user who executed the statement
dbidoidpg_database.oidOID of database in which the statement was executed
toplevelbool True if the query was executed as a top-level statement (always true if pgpro_stats.track is set to top)
queryidbigint Internal hash code, computed from the statement's parse tree
planidbigint Internal hash code, computed from the statement's plan tree
querytext Text of a representative statement
plantext The text of the query plan, in the format defined by the pgpro_stats.plan_format configuration parameter
plansint8  Number of times the statement was planned (if pgpro_stats.track_planning is enabled, otherwise zero)
total_plan_timefloat8  Total time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero).
min_plan_timefloat8  Minimum time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
max_plan_timefloat8  Maximum time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
mean_plan_timefloat8  Mean time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
stddev_plan_timefloat8  Population standard deviation of time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero)
plan_rusagepgpro_stats_rusage  Resource usage statistics of the statement planning.
callsint8 Number of times the statement was executed
total_exec_timefloat8 Total time spent executing the statement, in milliseconds
min_exec_timefloat8 Minimum time spent executing the statement, in milliseconds
max_exec_timefloat8 Maximum time spent executing the statement, in milliseconds
mean_exec_timefloat8 Mean time spent executing the statement, in milliseconds
stddev_exec_timefloat8 Population standard deviation of time spent executing the statement, in milliseconds
exec_rusagepgpro_stats_rusage  Resource usage statistics of the statement execution.
rowsint8 Total number of rows retrieved or affected by the statement
shared_blks_hitint8 Total number of shared block cache hits by the statement
shared_blks_readint8 Total number of shared blocks read by the statement
shared_blks_dirtiedint8 Total number of shared blocks dirtied by the statement
shared_blks_writtenint8 Total number of shared blocks written by the statement
local_blks_hitint8 Total number of local block cache hits by the statement
local_blks_readint8 Total number of local blocks read by the statement
local_blks_dirtiedint8 Total number of local blocks dirtied by the statement
local_blks_writtenint8 Total number of local blocks written by the statement
temp_blks_readint8 Total number of temp blocks read by the statement
temp_blks_writtenint8 Total number of temp blocks written by the statement
shared_blk_read_timefloat8  Total time the statement spent reading shared blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
shared_blk_write_timefloat8  Total time the statement spent writing shared blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
local_blk_read_timefloat8  Total time the statement spent reading local blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 17, contains zero.
local_blk_write_timefloat8  Total time the statement spent writing local blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 17, contains zero.
temp_blk_read_timefloat8  Total time the statement spent reading temp blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 15, contains zero.
temp_blk_write_timefloat8  Total time the statement spent writing temp blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 15, contains zero.
wal_recordsint8 Total number of WAL records generated by the statement
wal_fpiint8 Total number of WAL full page images generated by the statement
wal_bytesnumeric Total amount of WAL bytes generated by the statement
jit_functionsint8 Total number of functions JIT-compiled by the statement. In Postgres Pro versions lower than 15, contains zero.
jit_generation_timefloat8 Total time spent by the statement on generating JIT code, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_inlining_countint8 Number of times functions used in the statement have been inlined. In Postgres Pro versions lower than 15, contains zero.
jit_inlining_timefloat8 Total time spent by the statement on inlining functions, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_optimization_countint8 Number of times the statement has been optimized. In Postgres Pro versions lower than 15, contains zero.
jit_optimization_timefloat8 Total time spent by the statement on optimizing, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_emission_countint8 Number of times code has been emitted by the statement. In Postgres Pro versions lower than 15, contains zero.
jit_emission_timefloat8 Total time spent by the statement on emitting code, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_deform_countint8 Total number of tuple deform functions JIT-compiled by the statement. In Postgres Pro versions lower than 17, contains zero.
jit_deform_timefloat8 Total time spent by the statement on JIT-compiling tuple deform functions, in milliseconds. In Postgres Pro versions lower than 17, contains zero.
wait_statsjsonb A jsonb object containing statistics on wait events, for each execution of the query that uses the corresponding plan. Each statistic is provided in milliseconds and is a multiple of the pgpro_stats.profile_period configuration parameter.
inval_msgspgpro_stats_inval_msgs  Number of cache invalidation messages by type generated by the statement (if this is supported by the server, otherwise zero).
stats_sincetimestamp with time zone Time at which statistics gathering started for this statement
minmax_stats_sincetimestamp with time zone Time at which min/max statistics gathering started for this statement (fields min_plan_time, max_plan_time, min_exec_time and max_exec_time)

Take into account that like pg_stat_statements, pgpro_stats normalizes into one record those DML queries (containing SELECT, INSERT, UPDATE, DELETE and MERGE commands) that have equivalent structures according to some internal hash value. Being compared this way, two queries are normally considered equal if they are semantically equivalent up to constants included in the queries. All the other commands are, however, compared strictly as query texts. When the value of a constant in a query is ignored for comparison with other queries, this constant is replaced in the pgpro_stats output with a symbol of a parameter, such as, $k, where k is a positive integer. If a query already contains parameters, the initial value of k equals the number following the last number of a $n parameter in the original query text. If there are no parameters, the initial value of k equals 1. Note that sometimes hidden parameter symbols affect this numbering. For example, PL/pgSQL uses such hidden symbols to insert values of function local variables into queries, so a PL/pgSQL statement SELECT i + 1 INTO j will be represented as SELECT i + $2 in the normalized query text.

pgpro_stats uses a similar technique to normalize plan texts. When doing so, an attempt is made to associate numbers of constants in the plan text with the corresponding numbers of constants in the query text. If such an attempt appears unsuccessful for a certain constant in the plan text, it is assigned the number following the maximum number of a constant replaced in the query text. For example, consider the query:

SELECT 1::int, 'abc'::VARCHAR(3), 2::int;

pgpro_stats will replace numbers of constants in the query text and in the text of the corresponding plan as follows:

postgres=# SELECT query, plan FROM pgpro_stats_statements;
                     query                      |                                plan
------------------------------------------------+--------------------------------------------------
SELECT $1::int, $2::VARCHAR(3), $3::int         | Result                                           +
                                                |   Output: $1, $4, $3                             +

In this plan text, it appeared possible to associate constants numbered 1 and 3 from the query text, but not the constant numbered 2, and the latter was replaced with the number following the maximum number in the query text, that is, number 4.

Replacement of numbers in plan texts has an exception for version numbers of XML documents. If in the original query such a number is represented with a constant, e.g., '1.0', it is retained as is in the plan text rather than replaced with $k. If the version number of an XML document is represented with an expression, replacement of constants follows usual rules.

G.2.4.2. The pgpro_stats_totals View

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 G.72.

Table G.72. pgpro_stats_totals Columns

NameTypeDescription
object_typetextType of the object for which aggregated statistics are collected: "cluster", "database", "user", "client_addr", "application", "backend", "session"
object_idbigintID of the object: oid for databases and users, pid for backends, sid for sessions, NULL for others
object_nametextTextual name of the object or NULL
queries_plannedint8Number of queries planned
total_plan_timefloat8Total time spent in the planning of statements, in milliseconds
total_plan_rusagepgpro_stats_rusageAggregate resource usage statistics of the statement planning
queries_executedint8Number of queries executed
total_exec_timefloat8Total time spent in the execution of statements, in milliseconds
total_exec_rusagepgpro_stats_rusageAggregate resource usage statistics of the statement execution
rowsint8Total number of rows retrieved or affected by the statements
shared_blks_hitint8Total number of shared block cache hits by the statements
shared_blks_readint8Total number of shared blocks read by the statements
shared_blks_dirtiedint8Total number of shared blocks dirtied by the statements
shared_blks_writtenint8Total number of shared blocks written by the statements
local_blks_hitint8Total number of local block cache hits by the statements
local_blks_readint8Total number of local blocks read by the statements
local_blks_dirtiedint8Total number of local blocks dirtied by the statements
local_blks_writtenint8Total number of local blocks written by the statements
temp_blks_readint8Total number of temp blocks read by the statements
temp_blks_writtenint8Total number of temp blocks written by the statements
shared_blk_read_timefloat8 Total time the statements spent reading shared blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
shared_blk_write_timefloat8 Total time the statements spent writing shared blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
local_blk_read_timefloat8 Total time the statements spent reading local blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 17, contains zero.
local_blk_write_timefloat8 Total time the statements spent writing local blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 17, contains zero.
temp_blk_read_timefloat8 Total time the statements spent reading temp blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 15, contains zero.
temp_blk_write_timefloat8 Total time the statements spent writing temp blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 15, contains zero.
wal_recordsint8Total number of WAL records generated by the statements
wal_fpiint8Total number of WAL full page images generated by the statements
wal_bytesnumericTotal amount of WAL bytes generated by the statements
jit_functionsint8Total number of functions JIT-compiled by the statements. In Postgres Pro versions lower than 15, contains zero.
jit_generation_timefloat8Total time spent by the statements on generating JIT code, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_inlining_countint8Number of times functions used in the statements have been inlined. In Postgres Pro versions lower than 15, contains zero.
jit_inlining_timefloat8Total time spent by the statements on inlining functions, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_optimization_countint8Number of times the statements have been optimized. In Postgres Pro versions lower than 15, contains zero.
jit_optimization_timefloat8Total time spent by the statements on optimizing, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_emission_countint8Number of times code has been emitted by the statements. In Postgres Pro versions lower than 15, contains zero.
jit_emission_timefloat8Total time spent by the statements on emitting code, in milliseconds. In Postgres Pro versions lower than 15, contains zero.
jit_deform_countint8Total number of tuple deform functions JIT-compiled by the statements. In Postgres Pro versions lower than 17, contains zero.
jit_deform_timefloat8Total time spent by the statements on JIT-compiling tuple deform functions, in milliseconds. In Postgres Pro versions lower than 17, contains zero.
wait_statsjsonbA jsonb object containing statistics on wait events for each execution of the queries. Each statistic is provided in milliseconds and is a multiple of the pgpro_stats.profile_period configuration parameter.
inval_msgspgpro_stats_inval_msgs Number of cache invalidation messages by type generated by the statements (if this is supported by the server, otherwise zero).
cache_resetsint4Number of shared cache resets (only for cluster, databases and backends). Gets incremented for a backend when it receives a full cache reset message.
stats_sincetimestamp with time zoneTime at which statistics gathering started for the statements

G.2.4.3. The pgpro_stats_info View

The statistics of the pgpro_stats module itself are tracked and made available via a view named pgpro_stats_info. This view contains only a single row. The columns of the view are shown in Table G.73.

Table G.73. pgpro_stats_info Columns

NameTypeDescription
deallocbigintTotal number of times pgpro_stats_statements entries about the least-executed statements were deallocated because more distinct statements than pgpro_stats.max were observed
stats_resettimestamp with time zoneTime at which all statistics in the pgpro_stats_statements view were last reset

G.2.4.4. The pgpro_stats_metrics View

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

Table G.74. pgpro_stats_metrics Columns

NameTypeDescription
metric_numberint4A unique ID of the collected metric assigned by user. This ID is included into parameter names that define the metric.
metric_nametextThe name of the metric defined by the pgpro_stats.metric_N_name parameter
db_nametextThe name of the database for which a particular metric was collected
tstimestamptzThe time when the metric value got calculated
valuejsonbThe result of the query used for metric measurement. It is serialized in jsonb as an array of objects received via to_jsonb(resulting_row). If an error occurs, a single object is returned that contains code, message, detail, and hint fields.

G.2.4.5. The pgpro_stats_archiver View

The pgpro_stats_archiver view will contain one row showing data about the archiver process of the cluster.

Table G.75. pgpro_stats_archiver Columns

ColumnTypeDescription
archived_countbigintNumber of WAL files that have been successfully archived
last_archived_waltextName of the last WAL file successfully archived
last_archived_timetimestamp with time zoneTime of the last successful archive operation
failed_countbigintNumber of failed attempts for archiving WAL files
last_failed_waltextName of the WAL file of the last failed archival operation
last_failed_timetimestamp with time zoneTime of the last failed archival operation
active_timeint8Overall time that the archiver process was active
archive_command_timeint8Overall execution time of the archive command
stats_resettimestamp with time zoneTime at which these statistics were last reset

G.2.4.6. The pgpro_stats_vacuum_database View

Important

Starting with Postgres Pro 16, this view contains no data because the statistics to be displayed are available through the catalog view pg_stats_vacuum_database (see System Views for details).

The pgpro_stats_vacuum_database view will contain one row for each database in the current cluster, showing statistics about vacuuming that database. These statistics are collected by the core system as explained in Section 26.2. The table below describes the columns of the view.

Table G.76. pgpro_stats_vacuum_database Columns

ColumnTypeDescription
dbidoidOID of a database
total_blks_readint8Number of database blocks read by vacuum operations performed on this database
total_blks_hitint8Number of times database blocks were found in the buffer cache by vacuum operations performed on this database
total_blks_dirtiedint8Number of database blocks dirtied by vacuum operations performed on this database
total_blks_writtenint8Number of database blocks written by vacuum operations performed on this database
wal_recordsint8Total number of WAL records generated by vacuum operations performed on this database
wal_fpiint8Total number of WAL full page images generated by vacuum operations performed on this database
wal_bytesnumericTotal amount of WAL bytes generated by vacuum operations performed on this database
blk_read_timefloat8Time spent reading database blocks by vacuum operations performed on this database, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timefloat8Time spent writing database blocks by vacuum operations performed on this database, in milliseconds (if track_io_timing is enabled, otherwise zero)
delay_timefloat8Time spent sleeping in a vacuum delay point by vacuum operations performed on this database, in milliseconds (see Section 18.4.4 for details)
system_timefloat8System CPU time of vacuuming this database, in milliseconds
user_timefloat8User CPU time of vacuuming this database, in milliseconds
total_timefloat8Total time of vacuuming this database, in milliseconds
interruptsint4Number of times vacuum operations performed on this database were interrupted on any errors

G.2.4.7. The pgpro_stats_vacuum_tables View

Important

Starting with Postgres Pro 16, this view contains no data because the statistics to be displayed are available through the catalog view pg_stats_vacuum_tables (see System Views for details).

The pgpro_stats_vacuum_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about vacuuming that specific table. These statistics are collected by the core system as explained in Section 26.2. The table below describes the columns of the view.

Table G.77. pgpro_stats_vacuum_tables Columns

ColumnTypeDescription
relidoidOID of a table
schemanameName of the schema this table is in
relnamenameName of this table
total_blks_readint8Number of database blocks read by vacuum operations performed on this table
total_blks_hitint8Number of times database blocks were found in the buffer cache by vacuum operations performed on this table
total_blks_dirtiedint8Number of database blocks dirtied by vacuum operations performed on this table
total_blks_writtenint8Number of database blocks written by vacuum operations performed on this table
rel_blks_readint8Number of blocks vacuum operations read from this table
rel_blks_hitint8Number of times blocks of this table were already found in the buffer cache by vacuum operations, so that a read was not necessary (this only includes hits in the Postgres Pro buffer cache, not the operating system's file system cache)
pages_scannedint8Number of pages examined by vacuum operations performed on this table
pages_removedint8Number of pages removed from the physical storage by vacuum operations performed on this table
pages_frozenint8Number of times vacuum operations marked pages of this table as all-frozen in the visibility map
pages_all_visibleint8Number of times vacuum operations marked pages of this table as all-visible in the visibility map
tuples_deletedint8Number of dead tuples vacuum operations deleted from this table
tuples_frozenint8Number of tuples of this table that vacuum operations marked as frozen
dead_tuplesint8Number of dead tuples vacuum operations left in this table due to their visibility in transactions
index_vacuum_countint8Number of times indexes on this table were vacuumed
rev_all_frozen_pagesint8Number of times the all-frozen mark in the visibility map was removed for pages of this table
rev_all_visible_pagesint8Number of times the all-visible mark in the visibility map was removed for pages of this table
wal_recordsint8Total number of WAL records generated by vacuum operations performed on this table
wal_fpiint8Total number of WAL full page images generated by vacuum operations performed on this table
wal_bytesnumericTotal amount of WAL bytes generated by vacuum operations performed on this table
blk_read_timefloat8Time spent reading database blocks by vacuum operations performed on this table, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timefloat8Time spent writing database blocks by vacuum operations performed on this table, in milliseconds (if track_io_timing is enabled, otherwise zero)
delay_timefloat8Time spent sleeping in a vacuum delay point by vacuum operations performed on this table, in milliseconds (see Section 18.4.4 for details)
system_timefloat8System CPU time of vacuuming this table, in milliseconds
user_timefloat8User CPU time of vacuuming this table, in milliseconds
total_timefloat8Total time of vacuuming this table, in milliseconds
interruptsintegerNumber of times vacuum operations performed on this table were interrupted on any errors

Columns total_*, wal_* and blk_* include data on vacuuming indexes on this table, while columns system_time and user_time only include data on vacuuming the heap.

G.2.4.8. The pgpro_stats_vacuum_indexes View

Important

Starting with Postgres Pro 16, this view contains no data because the statistics to be displayed are available through the catalog view pg_stats_vacuum_indexes (see System Views for details).

The pgpro_stats_vacuum_indexes view will contain one row for each index in the current database (including TOAST table indexes), showing statistics about vacuuming that specific index. These statistics are collected by the core system as explained in Section 26.2. The table below describes the columns of the view.

Table G.78. pgpro_stats_vacuum_indexes Columns

ColumnTypeDescription
relidoidOID of an index
schemanameName of the schema this index is in
relnamenameName of this index
total_blks_readint8Number of database blocks read by vacuum operations performed on this index
total_blks_hitint8Number of times database blocks were found in the buffer cache by vacuum operations performed on this index
total_blks_dirtiedint8Number of database blocks dirtied by vacuum operations performed on this index
total_blks_writtenint8Number of database blocks written by vacuum operations performed on this index
rel_blks_readint8Number of blocks vacuum operations read from this index
rel_blks_hitint8Number of times blocks of this index were already found in the buffer cache by vacuum operations, so that a read was not necessary (this only includes hits in the Postgres Pro buffer cache, not the operating system's file system cache)
pages_deletedint8Number of pages deleted by vacuum operations performed on this index
tuples_deletedint8Number of dead tuples vacuum operations deleted from this index
wal_recordsint8Total number of WAL records generated by vacuum operations performed on this index
wal_fpiint8Total number of WAL full page images generated by vacuum operations performed on this index
wal_bytesnumericTotal amount of WAL bytes generated by vacuum operations performed on this index
blk_read_timefloat8Time spent reading database blocks by vacuum operations performed on this index, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timefloat8Time spent writing database blocks by vacuum operations performed on this index, in milliseconds (if track_io_timing is enabled, otherwise zero)
delay_timefloat8Time spent sleeping in a vacuum delay point by vacuum operations performed on this index, in milliseconds (see Section 18.4.4 for details)
system_timefloat8System CPU time of vacuuming this index, in milliseconds
user_timefloat8User CPU time of vacuuming this index, in milliseconds
total_timefloat8Total time of vacuuming this index, in milliseconds
interruptsintegerNumber of times vacuum operations performed on this index were interrupted on any errors

G.2.5. Data Types

G.2.5.1. The pgpro_stats_rusage Type

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 G.79.

Table G.79. pgpro_stats_rusage Fields

NameTypeDescription
readsbigintNumber of bytes read by the filesystem layer
writesbigintNumber of bytes written by the filesystem layer
user_timedouble precisionUser CPU time used
system_timedouble precisionSystem CPU time used
minfltsbigintNumber of page reclaims (soft page faults)
majfltsbigintNumber of page faults (hard page faults)
nswapsbigintNumber of swaps
msgsndsbigintNumber of IPC messages sent
msgrcvsbigintNumber of IPC messages received
nsignalsbigintNumber of signals received
nvcswsbigintNumber of voluntary context switches
nivcswsbigintNumber of involuntary context switches

G.2.6. Functions

pgpro_stats_statements_reset(userid Oid, dbid Oid, queryid bigint, planid bigint, minmax_only boolean) returns timestamp with time zone

pgpro_stats_statements_reset discards statistics gathered so far by pgpro_stats corresponding to the specified userid, dbid, queryid, and planid. If any of the parameters are not specified, the default value 0(invalid) is used for each of them and the statistics that match with other parameters will be reset. If no parameter is specified or all the specified parameters are 0(invalid), it will discard all statistics. If all statistics in the pgpro_stats_statements view are discarded, it will also reset the statistics in the pgpro_stats_info view. When minmax_only is true only the values of minimum and maximum planning and execution time will be reset (i.e. min_plan_time, max_plan_time, min_exec_time and max_exec_time fields). The default value for minmax_only parameter is false. Time of last min/max reset performed is shown in minmax_stats_since field of the pgpro_stats_statements view. This function returns the time of a reset. This time is saved to stats_reset field of pgpro_stats_info view or to minmax_stats_since field of the pgpro_stats_statements view if the corresponding reset was actually performed. By default, this function can only be executed by superusers. Access may be granted to others using GRANT.

Note

As statistics in the pgpro_stats_vacuum_database, pgpro_stats_vacuum_tables, and pgpro_stats_vacuum_indexes views are collected by the core system, to reset them, call the pg_stat_reset() function (see Section 26.2.22 for details).

pgpro_stats_statements(showtext boolean) returns setof record

The pgpro_stats_statements view is defined in terms of a function also named pgpro_stats_statements. Users can also call the pgpro_stats_statements function directly, and by specifying showtext := false make query text be omitted (that is, the OUT argument that corresponds to the view's query column will return nulls). This feature is intended to support external tools that might wish to avoid the overhead of repeatedly retrieving query texts of indeterminate length. Such tools can instead cache the first query text observed for each entry themselves, since that is all pgpro_stats itself does, and then retrieve query texts only as needed. Since the server stores query texts in a file, this approach may reduce physical I/O for repeated examination of the pgpro_stats_statements data.

pgpro_stats_info() returns record

pgpro_stats_info view is defined in terms of a function also named pgpro_stats_info. Users can also call the pgpro_stats_info function directly.

pgpro_stats_totals_reset(type text, id bigint) returns timestamp with timezone

pgpro_stats_totals_reset discards statistics gathered so far by pgpro_stats corresponding to the specified object type and id. If no parameter is specified or the type parameter is set to 0, all statistics will be discarded. If type is set to a valid object type, then if id is specified, then statistics will be discarded only for the specified object, else, statistics will be discarded for all objects of the specified type. Otherwise, no statistics will be discarded. This function returns the time of a reset. By default, this function can only be executed by superusers. Access may be granted to others using GRANT.

pgpro_stats_totals() returns setof record

The pgpro_stats_totals view is defined in terms of a function also named pgpro_stats_totals. Users can also call the pgpro_stats_totals function directly.

pgpro_stats_metrics() returns setof record

Defines the pgpro_stats_metrics view, which is described in detail in Table G.74.

pgpro_stats_get_archiver() returns setof record

Defines the pgpro_stats_archiver view, which is described in detail in Table G.75.

pgpro_stats_wal_sender_crc_errors() returns bigint

In Postgres Pro Enterprise, returns the number of errors detected by the WAL sender process when the wal_sender_check_crc parameter is on. In Postgres Pro Standard, returns zero. Note that this counter is reset to zero when the cluster restarts.

pgpro_stats_vacuum_database(dboid oid) returns setof record

Defines the row of the pgpro_stats_vacuum_database view, which is described in detail in Table G.76, for the database specified by dboid.

pgpro_stats_vacuum_tables(dboid oid, relid oid) returns setof record

Defines the row of the pgpro_stats_vacuum_tables view, which is described in detail in Table G.77, for the database specified by dboid and table specified by reloid. If reloid = 0, the statistics for each table in the specified database are returned.

pgpro_stats_vacuum_indexes(dboid oid, relid oid) returns setof record

Defines the row of the pgpro_stats_vacuum_indexes view, which is described in detail in Table G.78, for the database specified by dboid and index specified by reloid. If reloid = 0, the statistics for each index in the specified database are returned.

G.2.6.1. Session-Tracing Functions

In pgpro_stats, tracing of application sessions is implemented. It is based on filters, which trigger logging the execution of queries that match filtering conditions. Queries and their plans are logged in so called trace files specified by the user or in the system log file (if the trace file is not specified). Filters are stored in a table located in the shared memory. The rows of this table are filters, and the columns contain filtering conditions. You should fill this table with filters to start tracing queries.

Once a database administrator adds a filter in any session, all subsequent executions of queries that match the filter conditions will be traced by all sessions of the instance without a need in the server restart. In other words, filters can be added, deleted or updated on the fly, and tracing with these filters immediately starts for existing and future sessions.

Each filter includes:

  • Identification fields, such as username, client_addr, database_name, pid or application_name (see Table G.80 for details). Execution of a statement will be traced if its characteristics in the current session are the same as the values of the respective filter identification fields.

  • Resource fields, from duration to total_inval_msgs in Table G.80. Execution of a statement will be traced if the resource statistics of the statement execution in the current session are not less than the limits specified in the respective filter resource fields.

  • Fields specifying EXPLAIN options. They allow you to control the EXPLAIN output to the trace file or system log file.

Warning

Although when specifying a filter, you can assign values to any combination of filter fields, bear in mind that a too general filter will lead to an excessive size of the trace file and will affect the performance more than desired as the main performance overhead is associated with writing to the trace file rather than with checking the filter conditions.

Specialized functions enable creation, update and deletion of query filters:

pgpro_stats_trace_insert(VARIADIC "any") returns integer

Adds a filter to the list of session-tracing filters. A filter must be passed as a sequence of alternating key and value pairs. For example:

pgpro_stats_trace_insert('pid', 42, 'database', 'main', 'explain_analyze', true)

See Table G.80 for the list of available filters. NULL values are not allowed, so just omit fields that can take any value. Returns → filter_id of the added filter.

pgpro_stats_trace_update(filter_id integer, VARIADIC "any") returns boolean

Updates a session-tracing filter defined by filter_id. Filter fields to update must be passed as a sequence of alternating key and value pairs. NULL values are accepted. See Table G.80 for the list of available filters. Returns true on success, false otherwise.

pgpro_stats_trace_delete(filter_id integer) returns boolean

Deletes a session-tracing filter defined by filter_id. Returns true on success, false otherwise.

pgpro_stats_trace_reset() returns integer

Removes all session-tracing filters. Returns the number of removed filters. By default, this function can only be executed by superusers. Access may be granted to others using GRANT.

pgpro_stats_trace_show() returns setof record

Displays all the query filters that the user added for tracing. These filters are shown in Table G.80.

Table G.80. pgpro_stats_trace_show() Output

NameTypeDescription
filter_idintegerFilter ID, numbered from 1.
activebooleanTrue if the filter is active. Default: true.
aliasnameFilter name
tracefilenameName of the trace file. Trace files are created in PGDATA/pg_stat directory and have trace extensions.
pidintegerProcess ID of the backend that executes a particular statement
database_namenameName of the database where a particular statement is executed
client_addrnameIP address of the client connected to this backend
application_namenameName of the application that invoked execution of the statement
usernamenameName of the user who executes the statement
queryidbigintInternal hash code, computed from the statement's parse tree
planidbigintInternal hash code, computed from the statement's plan tree
durationfloat8Time spent in the planning and execution of the statement, in milliseconds
plan_timefloat8Time spent in the planning of the statement, in milliseconds
exec_timefloat8Time spent in the execution of the statement, in milliseconds
user_timefloat8User CPU time used in planning and execution of the statement
system_timefloat8System CPU time used in planning and execution of the statement
rowsint8Total number of rows retrieved or affected by the statement
shared_blks_hitint8Total number of shared block cache hits by the statement
shared_blks_readint8Total number of shared blocks read by the statement
shared_blks_fetchedint8Total number of shared blocks fetched from buffers by the statement
shared_blks_dirtiedint8Total number of shared blocks dirtied by the statement
shared_blks_writtenint8Total number of shared blocks written by the statement
local_blks_hitint8Total number of local block cache hits by the statement
local_blks_readint8Total number of local blocks read by the statement
local_blks_fetchedint8Total number of local blocks fetched from buffers by the statement
local_blks_dirtiedint8Total number of local blocks dirtied by the statement
local_blks_writtenint8Total number of local blocks written by the statement
temp_blks_readint8Total number of temp blocks read by the statement
temp_blks_writtenint8Total number of temp blocks written by the statement
wal_bytesnumericTotal amount of WAL bytes generated by the statement
total_wait_timefloat8Total time execution of this statement spent waiting
total_inval_msgsbigintTotal number of cache invalidation messages generated by the statement (if this is supported by the server)
explain_analyzebooleanIf true, EXPLAIN output will be logged with ANALYZE parameter. Default: false.
explain_verbosebooleanIf true, EXPLAIN output will be logged with VERBOSE parameter. Default: false.
explain_costsbooleanIf true, EXPLAIN output will be logged with COSTS parameter. Default: true.
explain_settingsbooleanIf true, EXPLAIN output will be logged with SETTINGS parameter. Default: false.
explain_buffersbooleanIf true, EXPLAIN output will be logged with BUFFERS parameter. Default: false.
explain_walbooleanIf true, EXPLAIN output will be logged with WAL parameter. Default: false.
explain_timingbooleanIf true, EXPLAIN output will be logged with TIMING parameter. Default: false.
explain_formattextThe value of FORMAT parameter of EXPLAIN to be logged, which can be TEXT, XML, JSON, or YAML. Default: TEXT


Example G.1. Usage of Session-Tracing Functions

Let's add the filter first:

SELECT pgpro_stats_trace_insert('alias', 'first', 'pid', pg_backend_pid(), 'explain_analyze', true);

Let's add the filter second and specify logging to the trace file second_tf.trace:

SELECT pgpro_stats_trace_insert('alias', 'second', 'database_name', current_database(), 'explain_costs', false, 'tracefile', 'second_tf');

You can view the table with filters as follows:

\x auto
SELECT * from pgpro_stats_trace_show();

 -[ RECORD 1 ]-------+----------
 filter_id           | 1
 active              | t
 alias               | first
 tracefile           | 
 pid                 | 243183
 database_name       | 
 client_addr         | 
 application_name    | 
 username            | 
 queryid             | 
 planid              | 
 duration            | 
 plan_time           | 
 exec_time           | 
 user_time           | 
 system_time         | 
 rows                | 
 shared_blks_hit     | 
 shared_blks_read    | 
 shared_blks_fetched | 
 shared_blks_dirtied | 
 shared_blks_written | 
 local_blks_hit      | 
 local_blks_read     | 
 local_blks_fetched  | 
 local_blks_dirtied  | 
 local_blks_written  | 
 temp_blks_read      | 
 temp_blks_written   | 
 wal_bytes           | 
 total_wait_time     | 
 total_inval_msgs    | 
 explain_analyze     | t
 explain_verbose     | f
 explain_costs       | t
 explain_settings    | f
 explain_buffers     | f
 explain_wal         | f
 explain_timing      | t
 explain_format      | text
 -[ RECORD 2 ]-------+----------
 filter_id           | 2
 active              | t
 alias               | second
 tracefile           | second_tf
 pid                 | 
 database_name       | postgres
 client_addr         | 
 application_name    | 
 username            | 
 queryid             | 
 planid              | 
 duration            | 
 plan_time           | 
 exec_time           | 
 user_time           | 
 system_time         | 
 rows                | 
 shared_blks_hit     | 
 shared_blks_read    | 
 shared_blks_fetched | 
 shared_blks_dirtied | 
 shared_blks_written | 
 local_blks_hit      | 
 local_blks_read     | 
 local_blks_fetched  | 
 local_blks_dirtied  | 
 local_blks_written  | 
 temp_blks_read      | 
 temp_blks_written   | 
 wal_bytes           | 
 total_wait_time     | 
 total_inval_msgs    | 
 explain_analyze     | f
 explain_verbose     | f
 explain_costs       | f
 explain_settings    | f
 explain_buffers     | f
 explain_wal         | f
 explain_timing      | f
 explain_format      | text

The following query matches the conditions of both filters, so it must be logged in the system log file and in the specified trace file:

SELECT 1 as result;

The following is the output to the system log file:

2023-04-18 04:52:53.242 MSK [63112] LOG:  Filter 1 triggered explain of the plan:
Query Text: SELECT 1 as result;
Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

And the following is the output to the second_tf.trace trace file:

Query Text: SELECT 1 as result;
Result

Let's delete the first filter:

SELECT pgpro_stats_trace_delete(1);

Let's also change pid to 2 for the second filter

SELECT pgpro_stats_trace_update(2, 'pid', 2);

When you execute the query

SELECT 2 as result;

it does not get logged to second_tf.trace.

Now let's remove all filters from the table:

SELECT pgpro_stats_trace_reset();


G.2.6.2. Functions for Creating Views that Emulate Other Extensions

pgpro_stats can create views similar to those available in pg_stat_statements and pg_stat_kcache extensions. Specifically, pg_stat_statements, pg_stat_statements_info, pg_stat_kcache and pg_stat_kcache_detail views can be created. Each view is only created in a Postgres Pro version if it is available in pg_stat_statements/pg_stat_kcache extension for the same version of Postgres Pro/PostgreSQL. For example, the pg_stat_statements_info view is only created in Postgres Pro versions starting with 14. The following functions enable creating these views:

pgpro_stats_create_pg_stat_statements_compatible_views() returns void

Creates pg_stat_statements and pg_stat_statements_info views.

pgpro_stats_create_pg_stat_kcache_compatible_views() returns void

Creates pg_stat_kcache and pg_stat_kcache_detail views.

By default, these functions can only be executed by superusers. Access may be granted to others using GRANT.

To create pg_stat_statements* views, drop the pg_stat_statements extension if it was previously installed and call the function:

select pgpro_stats_create_pg_stat_statements_compatible_views();

To create pg_stat_kcache* views, drop the pg_stat_kcache extension if it was previously installed and call the function:

select pgpro_stats_create_pg_stat_kcache_compatible_views();

Once the views are created, you can work with them as if the pg_stat_statements/pg_stat_kcache extension is installed.

If you need to remove the views created earlier, do it in a regular way:

drop view pg_stat_statements;
drop view pg_stat_statements_info;
drop view pg_stat_kcache;
drop view pg_stat_kcache_detail;

G.2.7. Configuration Parameters

G.2.7.1. General Settings

pgpro_stats.max (integer)

pgpro_stats.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pgpro_stats_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 5000. This parameter can only be set at server start.

pgpro_stats.max_totals (integer)

pgpro_stats.max_totals is the maximum number of objects tracked by the module (i.e., the maximum number of rows in the pgpro_stats_totals view). If more distinct objects than that are observed, information about least-used objects is discarded. The default value is 1000. This parameter can only be set at server start.

pgpro_stats.track (enum)

pgpro_stats.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions) with nesting level not greater than 100, or none to disable statement statistics collection. The default value is top. Only superusers can change this setting.

pgpro_stats.track_utility (boolean)

pgpro_stats.track_utility controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE. The default value is on. Only superusers can change this setting.

pgpro_stats.track_planning (boolean)

pgpro_stats.track_planning controls whether planning operations and duration are tracked by the module. Enabling this parameter may incur a noticeable performance penalty, especially when statements with identical query structure are executed by many concurrent connections which compete to update a small number of pg_stat_statements entries. The default value is off. Only superusers can change this setting.

pgpro_stats.track_totals (boolean)

pgpro_stats.track_totals controls whether aggregate statistics for objects (cluster, users, databases etc.) are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_cluster (boolean)

pgpro_stats.track_cluster controls whether aggregate statistics for the cluster are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_databases (boolean)

pgpro_stats.track_databases controls whether aggregate statistics for the databases are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_users (boolean)

pgpro_stats.track_users controls whether aggregate statistics for the users are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_applications (boolean)

pgpro_stats.track_applications controls whether aggregate statistics for the applications (whose names are set by application_name) are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_client_addr (boolean)

pgpro_stats.track_client_addr controls whether aggregate statistics for the client IP addresses are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_backends (boolean)

pgpro_stats.track_backends controls whether aggregate statistics for the backends are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.track_sessions (boolean)

pgpro_stats.track_sessions controls whether aggregate statistics for the sessions are tracked by the module. The default value is on. Only superusers can change this setting.

pgpro_stats.save (boolean)

pgpro_stats.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are neither saved at shutdown nor reloaded at server start. The default value is on. This parameter can only be set in the postgresql.conf file or on the server command line.

pgpro_stats.plan_format (text)

pgpro_stats.plan_format selects the EXPLAIN format for the query plan. Possible values are text, xml, json, and yaml. The default value is text. Changing this parameter requires a server restart.

pgpro_stats.enable_profile (boolean)

pgpro_stats.enable_profile enables sampling of wait events for separate statements. The default value is true. Changing this parameter requires a server restart.

pgpro_stats.query_sample_rate (float)

pgpro_stats.query_sample_rate specifies the fraction of queries that are randomly selected for statistics calculation. Possible values lie between 0.0 (no queries) and 1.0 (all queries). The default value is 1.0. Changing this parameter requires a server restart.

pgpro_stats.profile_period (integer)

pgpro_stats.profile_period specifies the period, in milliseconds, during which to sample wait events. The default value is 10. Only superusers can change this setting.

pgpro_stats.metrics_buffer_size (integer)

pgpro_stats.metrics_buffer_size specifies the size of the ring buffer used for collecting statistical metrics. The default value is 16kB. Changing this parameter requires a server restart.

pgpro_stats.metrics_workers (integer)

pgpro_stats.metrics_workers specifies the number of workers used to collect statistical metrics. If this parameter is set to 2 or higher, one of the workers serves as the primary worker distributing queries to other workers. If only one worker is available, it gets reloaded to connect to different databases. Setting this parameter to 0 disables metrics collection. The default value is 2. Changing this parameter requires a server restart.

pgpro_stats.stats_temp_directory (string)

pgpro_stats.stats_temp_directory specifies the directory with the external file to store query texts. This can be a path relative to the data directory or an absolute path. Changing this parameter requires a server restart.

G.2.7.2. Metrics Settings

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.

pgpro_stats.metric_N_name (text)

The name of metric N. This name will be displayed in the metric_name column of the pgpro_stats_metrics view.

pgpro_stats.metric_N_query (text)

The query statement that defines the metric to collect.

pgpro_stats.metric_N_period (integer)

The time interval at which to collect metric N, in milliseconds. Default: 60000 ms

pgpro_stats.metric_N_db (text)

The list of databases for which to collect metric N. Database names must be separated by commas. You can specify the * character to select all databases in the cluster except the template databases. If you need to analyze the template databases as well, you have to specify them explicitly.

pgpro_stats.metric_N_user (text)

The name of the user on behalf of which to collect metric N. This user must have access to the database for which the metric is collected.

G.2.8. Cache Invalidation Metrics

Among the rest, pgpro_stats can collect cache invalidation statistics. This section provides some background information needed to better understand related metrics.

Each backend has its local cache, which allows you to minimize accesses for meta information on tables, for example, to the system catalogs. If a backend changes the meta information, this information must be updated in other backends' caches. This is implemented by sending invalidation messages through a queue: the backend that changed the meta information on some object sends an appropriate message to the queue.

All backends get invalidation messages from the queue. Depending on whether the object for which the invalidation message was received is cached, the backend either ignores the message (when the object is not cached) or updates its cache (when the object is cached). In pgpro_stats, most invalidation message counters, unless explicitly stated otherwise for certain counters, are incremented when backends just generate messages, which will only be sent to the queue upon commit of the appropriate transaction. Note that the counters will remain incremented if the transaction is rolled back, although the message will not be sent to the queue.

When a backend that is adding messages to the queue figures out that the queue size reached a certain limit, it starts a cleanup by deleting messages already processed by all backends, and if backends are found that heavily fall behind and thus delay the cleanup, they get a reset signal, which forces them to reset all their caches.

G.2.8.1. The pgpro_stats_inval_status View

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

Table G.81. pgpro_stats_inval_status Columns

NameTypeDescription
num_inval_messagesint8Current number of invalidation messages in the queue
num_inval_queue_cleanupsint8Number of invalidation queue cleanups done to prevent its overflow
num_inval_queue_resetsint4Number of cache resets for backends that fail to process messages fast enough

In a working system, num_inval_messages usually approximately equals 4000, which means that the queue is pretty full. The speed of the num_inval_queue_cleanups growth is determined by how fast invalidation messages are generated. Growth of num_inval_queue_resets is normally zero, and non-zero growth indicates either too fast generation of messages or delays in processing messages by backends. Monitoring num_inval_queue_cleanups and num_inval_queue_resets may in some cases allow you to detect problematic backend/backeds as described below.

If for a certain time interval, num_inval_queue_cleanups considerably increased, while num_inval_queue_resets did not, this indicates that invalidation messages are generated faster and/or backends process them more slowly, but backends still manage to process messages before the queue overflows.

If for a time interval, num_inval_queue_cleanups did not considerably increase, while num_inval_queue_resets did, this definitely indicates a delay in processing messages by backend(s), and the cache_resets column of the pgpro_stats_totals view allows you to figure out which backend(s) to blame.

If for a time interval, both counters considerably increased, this also indicates that invalidation messages are generated faster and/or backends process them more slowly, but this time backends fail to process messages before the queue overflows. The cache_resets column of the pgpro_stats_totals view allows you detect which backend(s) delay message processing. In this case, it is not possible to definitely conclude whether too fast generation of messages or a delay in message processing accounts for the growth of num_inval_queue_resets. However, the totals counter of the pgpro_stats_inval_msgs view may help here. If the change of this counter for that interval is pretty the same as for a previous interval of the same length, you can definitely conclude that the growth is caused by backend delays.

The pgpro_stats_inval_status view can be defined in terms of a function:

pgpro_stats_inval_status() returns record

Defines the pgpro_stats_inval_status view, which is described in detail in Table G.81.

G.2.8.2. The pgpro_stats_inval_msgs Type

The pgpro_stats_statements and pgpro_stats_totals views for each corresponding object, show a record of the pgpro_stats_inval_msgs record type containing counters for cache invalidation messages. The fields of the type are shown in Table G.82.

Table G.82. pgpro_stats_inval_msgs Fields

NameTypeDescription
totalbigintTotal number of invalidation messages
catcachebigintNumber of selective catalog cache invalidation messages
catalogbigintNumber of whole catalog cache invalidation messages
relcachebigintNumber of selective relation cache invalidation messages
relcache_allbigintNumber of whole relation cache invalidation messages
smgrbigintNumber of invalidation messages of open relation files. Gets incremented when the messages are sent to the queue.
relmapbigintNumber of relation map cache invalidation messages. Gets incremented when the messages are sent to the queue.
snapshotbigintNumber of catalog snapshot invalidation messages

G.2.9. Authors

Postgres Professional, Moscow, Russia