🔌pg_stat_statements

The pg_stat_statements extension allows to trace the execution statistics of all queries. It is an essential extension in understanding the activity of a PostgreSQL cluster.

This extension is a prerequisite when using Datasentinel.

Once the settings are configured, there is no further action required. Resetting pg_stat_statements is unnecessary.

Datasentinel collects activity metrics for all queries at regular intervals to allow detailed analyzes on the graphical interface.

Installation

This extension is part of the official PostgreSQL contrib package. Refer to the documentation for more information

To be installed in the internal database named postgres

Modify postgresql.conf

Add pg_stat_statements to shared_preload_libraries parameter

• Add the following lines

pg_stat_statements.track = all
# Optional, not necessary for Datasentinel to save pg_stat_statements.
pg_stat_statements.save = false
# The extension's maximum number of tracked statements should be reduced from its default 5000 value 
# (e.g. 2000) as Datasentinel collects metrics at regular intervals.
pg_stat_statements.max = 2000
# For the pg_stat_activity.query field (optional, needs reboot)
track_activity_query_size = 65536
# Since postgresql 14
compute_query_id = on
# log IO timing
track_io_timing=on

A new parameter compute_query_id has been added since PostgreSQL 14. This is a configuration parameter enabling computation of a query identifier, which can be displayed via pg_stat_activity , in EXPLAIN output or in log output if configured in log_line_prefix .

For optimal utilization of Datasentinel, we advise configuring this parameter accordingly.

compute_query_id = on

The Agent version 3.2, which was released in February 2023, introduces the Collection level feature allowing you to filter metrics collected from the extension

You see lots of utility commands (other than SELECT, INSERT, UPDATE, DELETE) in pg_stat_statements,

Especially if you are using XA type distributed transactions, PREPARE TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION queries are not normalized by pg_stat_statements

You can disable their collect by setting this parameter:

pg_stat_statements.track_utility = off

Reload Conf

• connect as a superuser

SELECT pg_reload_conf();

Create Extension

• connect to the internal database named postgres with superuser privileges

CREATE EXTENSION pg_stat_statements;

Check Extension

SELECT current_database(), extname from pg_extension where extname ='pg_stat_statements';
current_database |      extname
------------------+--------------------
postgres         | pg_stat_statements
(1 row)

// Ensure the returned count is greater than zero
select count(*) from pg_stat_statements