Links
Comment on page
🔌

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
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
If you see lots of utility commands (other than SELECT, INSERT, UPDATE, DELETE).
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