Campaign Classic V7 - What is db module pg_stat_statements? What is it for?

Description

  1. What is db module pg_stat_statements ?

  2. Why it is requested to enable by clients?

  3. How it is activated?

Resolution

1.  The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server.

The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module.

Link : https://www.postgresql.org/docs/9.4/pgstatstatements.html

2. To implement a view type schema on this table which allows us to monitor queries that take long time. This way clients will be able to tune those and optimize performance.

This extension provides metrics on running queries : which are the most frequently run queries and how long each takes.

3.  pg_stat_statements 

  1. Verify that official PostgreSQL contrib package for the postgres version is installed.

$ dpkg -l | grep postgres
postgresql-contrib-9.3

If not installed, install it.

$ apt-get install postgresql-contrib-9.3

  1. Change postgresql.conf to preload the module on startup

Default : shared_preload_libraries = ‘’
New : shared_preload_libraries = ‘pg_stat_statements’

– if using both pg_stat_statements and auto_explain, use ONE record and separate with commas
shared_preload_libraries = ‘auto_explain,pg_stat_statements’

  1. In the database that you want to monitor, execute CREATE query :

– no extension creation required for auto_explain, only for pg_stat_statements
psql
postgres=# \c mydatabase
mydatabase=# CREATE EXTENSION pg_stat_statements;

  1. In the CUSTOMIZED OPTIONS section of postgresql.conf, add the lines:

pg_stat_statements.max = 5000
pg_stat_statements.track = all
pg_stat_statements.save = on

  1. Restart postgresql service
    Example : Get top 5 most costly queries in the databases

SELECT query, calls, total_time, rows, 100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) AS hit_percent
from pg_stat_statements as s inner join pg_database as d on d.oid = s.dbid
where d.datname = ‘Database_Name’
order by total_time desc limit 5;

On this page