pgsentinel – sampling active session history

PostgreSQL provides session activity. However, in order to gather activity behavior, users have to sample the pg_stat_activity view multiple times. pgsentinel is an extension to record active session history and link the activity with query statistics (pg_stat_statements).

The module must be loaded by adding pgsentinel to shared_preload_libraries in postgresql.conf, which means a server restart is needed.

When pgsentinel is enabled, it collects the history of session activity:

• It's implemented as an in-memory ring buffer where samples are written with a given (configurable) period. Therefore, the user can see some number of recent samples depending on the history size (configurable).

In combination with pg_stat_statements, this extension can link the session activity with query statistics.

To get more granular queries statistics, pgsentinel samples the pg_stat_statements view:

• at the same time it is sampling the active sessions
• only for the queryid associated to an active session (if any) during the sampling

pgsentinel launches a special background worker for gathering session activities.

pgsentinel is implemented as an extension and not available in the default PostgreSQL installation. It is available from github under the same license as PostgreSQL License and supports PostgreSQL 9.6+.

pgsentinel is a PostgreSQL extension which requires PostgreSQL 9.6 or higher. Before the build and install steps, you should ensure the following:

• PostgreSQL version is 9.6 or higher.
• You have the development package of PostgreSQL installed or you built PostgreSQL from source.
• Your PATH variable configuration includes pg_config, or you've set a value for PG_CONFIG.

The typical installation procedure may look like:

As pgsentinel uses the pg_stat_statements extension (officially bundled with PostgreSQL) for tracking which queries get executed in your database, add the following entries to your postgres.conf:

$ shared_preload_libraries = 'pg_stat_statements,pgsentinel'
$ # Increase the max size of the query strings Postgres records
$ track_activity_query_size = 2048
$ # Track statements generated by stored procedures as well
$ pg_stat_statements.track = all

restart the postgresql daemon and create the extension:

$ git clone https://github.com/pgsentinel/pgsentinel.git
$ cd pgsentinel/src
$ make
$ sudo make install
$ psql DB -c "CREATE EXTENSION pgsentinel;"

pgsentinel reports the active session history activity through the pg_active_session_history view:

You can see it as samplings of pg_stat_activity providing more information:

• ash_time: the sampling time
• top_level_query: the top-level statement from pg_stat_activity.query (in case PL/pgSQL is used)
• query: the statement currently being executed, from get_parsedinfo() (not normalised, so parameter values are visible)
• cmdtype: the statement type (SELECT,UPDATE,INSERT,DELETE,UTILITY,UNKNOWN,NOTHING)
• queryid: the top-level query ID from pg_stat_activity.query_id¹, which links to pg_stat_statements
• nested_queryid: the query ID returned by get_parsedinfo() for the statement currently being executed
• blockers: the number of blockers
• blockerpid: the pid of the blocker (if blockers = 1), the pid of one blocker (if blockers > 1)
• blocker_state: state of the blocker (state of the blockerpid)

pgsentinel also reports query statistics history through the pg_stat_statements_history view:

The field descriptions are the same as for pg_stat_statements (except for the ash_time one, which is the time of the active session history sampling).

The worker is controlled by the following GUCs:

• Some fields may be NULL depending on the version (for example, leader_pid is NULL for version <= 13.0...)
• Since 1.5.1, PUBLIC can't execute get_parsedinfo(), which returns the raw query text of every backend. An existing install gets this only after ALTER EXTENSION pgsentinel UPDATE, run in each database that has the extension. A role granted EXECUTE sees the query text of all sessions.

If you're lacking some functionality in pgsentinel then you're welcome to make pull requests.

• Bertrand Drouvot bdrouvot@gmail.com, France, Twitter