Skip to content

Configuration

Use the following command to view available configuration parameters in the pg_stat_monitor_settings view:

SELECT *

FROM pg_settings

WHERE name like 'pg_stat_monitor.%';

To amend the pg_stat_monitor configuration, use the General Configuration Unit (GCU) system.

GUC variable types

There are two types of GUC variables:

The first type can only be set in the postgresql.conf configuration file and comes into an effect on the start of the postgres server. You can set the same variable using the ALTER SYSTEM command. In this case the value of this variable is written into the postgresql.auto.conf which has preference over postgresql.conf. You also must restart the postgres server to apply the values from the postgresql.auto.conf file. The user can change the variable using the SET command without any error, but that does not affect the variable settings.

The second type of GUC variables can be set by the user from the client (psql) using the SET command. These variables are session-based, and their values can only be visible on that sessions. These variables can also be set with the or ALTER SYSTEM command and in the configuration file, but in that case, the effect of these variables is on all new sessions.

Parameters

The following table shows setup options for each configuration parameter and whether the server restart is required to apply the parameter’s value:

Parameter name SET ALTER SYSTEM SET server restart configuration reload
pg_stat_monitor.pgsm_max ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_query_max_len ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_track_utility ✅ ✅ ❌ ✅
pg_stat_monitor.pgsm_normalized_query ✅ ✅ ❌ ✅
pg_stat_monitor.pgsm_max_buckets ❌ ❌ ✅ ✅
pg_stat_monitor.pgsm_bucket_time ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_histogram_min ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_histogram_max ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_histogram_buckets ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_query_shared_buffer ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_enable_overflow ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_overflow_target ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_enable_pgsm_query_id ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_enable_query_plan ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_track ❌ ❌ ❌ ✅
pg_stat_monitor.pgsm_extract_comments ❌ ❌ ❌ ✅
pg_stat_monitor.pgsm_track_planning ❌ ❌ ✅ ❌
pg_stat_monitor.pgsm_track_application_names ✅ ✅ ❌ ❌

Parameters description

pg_stat_monitor.pgsm_max

Values:

  • Min: 10
  • Max: 10240
  • Default: 256

Server restart - YES

This parameter defines the limit of shared memory (in MB) for pg_stat_monitor. This memory is used by buckets in a circular manner. The memory is divided between the buckets equally, at the start of the PostgreSQL.

pg_stat_monitor.pgsm_query_max_len

Values:

  • Min: 1024
  • Max: 2147483647
  • Default: 2048

Server restart - YES

Sets the maximum size of the query. This parameter can only be set at the start of PostgreSQL. For long queries, the query is truncated to that particular length. This is to avoid unnecessary usage of shared memory.

pg_stat_monitor.pgsm_track_utility

Type: boolean (YES / NO). Default: YES

Server restart - NO

This parameter controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE, and DELETE. Starting with version 1.1.0, the values are changed from 1 / 0 to YES / NO.

pg_stat_monitor.pgsm_normalized_query

Type: boolean. Default: NO

Server restart - NO

Starting with version 1.1.0, the data type changed to boolean and the query shows the actual parameter instead of the placeholder by default. It is quite useful when users want to use that query and try to run that query to check the abnormalities. Users should be aware, however, that running queries with disabled normalization can expose some sensitive data.

But in most cases users like the queries with a placeholder. This parameter is used to toggle between the two said options.

In version 1.0.0 and earlier, the parameter type is “integer” and the default value is 1. This means that the query shows placeholders instead of the actual data

pg_stat_monitor.pgsm_max_buckets

Values:

  • Min: 1
  • Max: 20000
  • Default: 10

Server restart - YES

pg_stat_monitor accumulates the information in the form of buckets. All the aggregated information is bucket based. This parameter is used to set the number of buckets the system can have. For example, if this parameter is set to 2, then the system will create two buckets. First, the system will add all the information into the first bucket. After its lifetime (defined in the pg_stat_monitor.pgsm_bucket_time parameter) expires, it will switch to the second bucket, reset all the counters and repeat the process.

pg_stat_monitor.pgsm_bucket_time

Values:

  • Min: 1
  • Max: 2147483647
  • Default: 60

Server restart - YES

This parameter is used to set the lifetime of the bucket. System switches between buckets on the basis of pg_stat_monitor.pgsm_bucket_time.

pg_stat_monitor.pgsm_histogram_min

Values:

  • Min: 0
  • Max: 50000000
  • Default: 1

Server restart - YES

The minimum execution time for a query to appear in histogram output (in ms). Starting with version 2.0.0, this parameter can be set as a decimal value, allowing to output queries with the execution time less than 1 ms.

pg_stat_monitor.pgsm_histogram_max

Values:

  • Min: 10
  • Max: 50000000
  • Default: 10000

Server restart - YES

This parameter sets the upper bound of the query execution time for the histogram output (in ms). Starting with version 2.0.0, you can set a decimal value for this parameter thus fine-tuning the output to more precision.

pg_stat_monitor.pgsm_histogram_buckets

Values:

  • Min: 2
  • Max: 50
  • Default: 20

Server restart - YES

This parameter sets the maximum number of histogram buckets. Starting with version 1.1.0, the maximum value is changed to 50.

Starting with version 2.0.0, on server startup pg_stat_monitor calculates the maximum number of buckets that can be created for the time range specified in pgsm_histogram_min/pgsm_histogram_max and compares it with the number of buckets specified by the user. If the calculated number falls below the user configuration, the warning is written to the server log.

pg_stat_monitor.pgsm_query_shared_buffer

Values:

  • Min: 1
  • Max: 10000
  • Default: 20

Server restart - YES

This parameter defines the shared memory limit (in MB) allocated for a query tracked by pg_stat_monitor.

pg_stat_monitor.pgsm_enable_overflow

Type: boolean. Default: YES

Server restart - NO

Controls whether pg_stat_monitor can grow beyond shared memory into swap space.

pg_stat_monitor.pgsm_overflow_target

Type: boolean (YES / NO). Default: NO

Server restart - NO

Starting with version 2.0.0, this option is deprecated. Use the pg_stat_monitor.pgsm_enable_overflow instead.

Sets the overflow target for the pg_stat_monitor. Starting with version 1.1.1, the default value is NO. In version 1.0.0 and earlier, the default value was 1 (YES).

pg_stat_monitor.pgsm_enable_pgsm_query_id

Type: boolean. Default: YES

Server restart - NO

Controls the generation of a unique hash code that identifies the query. This hash code is independent of PostgreSQL server version, constants within the query, database, user or schema. Its usage allows getting insights into how the query is being planned and executed across PostgreSQL versions, database, users or schemas. Enabling this parameter results in additional load on the database.

pg_stat_monitor.pgsm_enable_query_plan

Type: boolean. Default: NO

Server restart - NO

Enables or disables query plan monitoring. When the pgsm_enable_query_plan is disabled (no), the query plan will not be captured by pg_stat_monitor. Enabling it may adversely affect the database performance.

pg_stat_monitor.pgsm_track

Default: top

Server restart - NO

This parameter controls which statements are tracked by pg_stat_monitor.

Values:

  • top: Default, track only top level queries (those issued directly by clients) and excludes listing nested statements (those called within a function).
  • all: Track top along with sub/nested queries. As a result, some SELECT statement may be shown as duplicates.
  • none: Disable query monitoring. The module is still loaded and is using shared memory, etc. It only silently ignores the capturing of data.

pg_stat_monitor.pgsm_extract_comments

Type: boolean (YES/NO). Default: NO

Server restart - NO

This parameter controls whether to enable or disable extracting comments from queries.

pg_stat_monitor.pgsm_track_planning

Type: boolean. Default: NO

Server restart - YES

Available for PostgreSQL 14 and later versions.

This parameter instructs pg_stat_monitor to monitor query planning statistics.

pg_stat_monitor.pgsm_track_application_names

Type: boolean. Default: YES

Server restart - NO

Controls if to record or not the name of the application that executes the query. When enabled, the application name is a part of the entry key. However, the application name tracking is an expensive operation and you can face perfomance degradation correlated to the number of connections.

Disabling this parameter results in statistics cumulation for the same query issued by different applications in the same entry. For the sake of query execution performance, it’s recommeded to disable this feature.

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.