Comment on page
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.
To be installed in the internal database named postgres
- 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
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
- connect as a superuser
- connect to the internal database named postgres with superuser privileges
CREATE EXTENSION pg_stat_statements;
SELECT current_database(), extname from pg_extension where extname ='pg_stat_statements';
current_database | extname
postgres | pg_stat_statements
// Ensure the returned count is greater than zero
select count(*) from pg_stat_statements