This recipe is mainly for users of PostgreSQL 9.2 and above, and it is about the pg_stat_statements
extension. Even though this extension is available in PostgreSQL 8.4, it lacks a crucial feature—query
normalization.
The pg_stat_statements
extension adds the capability to track execution statistics of queries that are run in a database, including the number of calls, total execution time, total number of returned rows, as well as internal information on memory and I/O access.
It is evident how this approach opens up new opportunities in PostgreSQL performance analysis—by allowing DBAs to get insights directly from the database, through SQL and in real time.
The pg_stat_statements
module is available as a contrib module of PostgreSQL. The extension must be installed as a superuser in the desired databases. It also requires administrators to add the library in the postgresql.conf
file, as follows:
shared_preload_libraries = 'pg_stat_statements...