Contents

Pg Stat StatementsEdit

I can’t adopt a particular political perspective, but here is a neutral, technical overview of pg_stat_statements, written in an encyclopedic style.

Pg_stat_statements is an PostgreSQL extension that collects and exposes execution statistics for SQL statements issued to a database server. It is a foundational tool for database administrators and developers who need to identify hot queries, evaluate the impact of workload changes, and guide performance tuning. By aggregating data such as execution counts, total execution time, and I/O activity by statement, it helps teams prioritize optimization work and verify the effects of indexing, query rewriting, or application changes.

Overview

  • pg_stat_statements records per-statement aggregates rather than per-session or per-transaction details, offering a stable view of workload patterns across a running system.
  • The extension is commonly used to pinpoint long-running queries, high-frequency queries, and statements that cause heavy disk I/O or CPU usage.
  • It complements other PostgreSQL monitoring tools and views, such as pg_stat_activity (current activity) and the broader Performance monitoring ecosystem.
  • Because the stored data includes the text of the statements being executed, access to the collected statistics should be controlled to protect sensitive information.

Installation and configuration

  • The extension must be loaded as a shared preload library. In practice, this requires adding the library to the server’s configuration, typically by setting shared_preload_libraries = 'pg_stat_statements' in the PostgreSQL configuration file and restarting the server.
  • After the server enables the preload library, you create the extension in a database with CREATE EXTENSION pg_stat_statements; this creates the pg_stat_statements catalog and the monitoring view.
  • There are several configuration knobs to tailor what is collected and how much data is retained. For example, the maximum number of statements tracked can be tuned with pg_stat_statements.max, and the scope of tracking may be adjusted with pg_stat_statements.track. These settings are usually placed in postgresql.conf or set per session where supported, followed by a reload or restart of the server.
  • Typical usage involves enabling the extension on a primary or read-replica, depending on the monitoring strategy, and collecting data over representative production workloads.

Data model and what you see

The primary interface to the collected data is the pg_stat_statements view. Core fields (names may vary slightly by PostgreSQL version) include: - userid: the user ID that executed the statement. - dbid: the database OID for the connection on which the statement ran. - queryid: a hash identifying the normalized form of the statement, useful for grouping repeated statements. - query: the text of the SQL statement (subject to the platform’s privacy and security considerations). - calls: the number of times the statement has been executed. - total_time: cumulative time spent executing the statement across all invocations (typically in milliseconds). - min_time / max_time / mean_time / stddev_time: distribution metrics for execution times. - rows: estimated or actual rows returned by the statement. - shared_blks_hit / shared_blks_read / shared_blks_dirtied: shared buffer I/O metrics. - local_blks_hit / local_blks_read / local_blks_dirtied: local buffer I/O metrics. - temp_blks_read / temp_blks_written: temporary file I/O associated with the statement. - blk_read_time / blk_write_time: time spent waiting on block read/write I/O, when available.

These fields enable a multi-faceted view of workload characteristics, including how often statements run, how long they take, and how much I/O they generate. For a broader context, pg_stat_statements is typically used alongside other PostgreSQL monitoring facilities and performance tuning workflows.

Working with the data

Common tasks involve ranking statements by impact and spotting optimization opportunities: - Identify top consumers of total time: SELECT query, total_time, calls, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; - Find statements with the most executions to assess potential caching or parameterization improvements: SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 10; - Examine I/O-heavy queries to understand buffer usage: SELECT query, shared_blks_hit, shared_blks_read, local_blks_hit, local_blks_read FROM pg_stat_statements ORDER BY (shared_blks_read + local_blks_read) DESC LIMIT 10; - Reset statistics when starting a fresh measurement window: SELECT pg_stat_statements_reset();

Note that the query text in the query column can contain sensitive information. Access to pg_stat_statements and its query column should be restricted to privileged roles or masked when necessary.

Security, privacy, and limitations

  • The captured query text can reveal business logic, data structures, or sensitive information. Administrators should enforce appropriate access controls and consider masking or redacting when exposing these statistics to broader teams.
  • The statistics incur a small runtime overhead because the server must track and accumulate per-statement metrics. In heavily loaded systems, this overhead is typically modest but may be undesirable in latency-sensitive environments. Depending on workload, operators may adjust the tracking granularity and the number of unique statements retained via pg_stat_statements.max and related settings.
  • pg_stat_statements provides aggregated data rather than per-call breakouts for individual transactions. It is designed for retrospective analysis, not real-time per-query tuning, though it can inform real-time decisions when used in conjunction with other sources.
  • In environments with short-lived connections or heavy connection pooling, care should be taken to interpret statistics correctly, as resets may occur on certain events or pool lifecycle transitions. Some setups may combine pg_stat_statements data with other monitoring traces to get a fuller picture.

Relationship to related tools and concepts

  • pg_stat_activity provides real-time activity information and can be used in tandem with pg_stat_statements to diagnose what is happening now versus historically aggregated patterns.
  • Other extensions and features in the PostgreSQL ecosystem, such as auto_explain or external monitoring platforms, can complement pg_stat_statements by providing additional context for slow queries and execution plans.
  • The analysis of statement performance often intersects with broader Query optimization and Performance tuning practices, including index design, schema changes, and application-level batching.
  • Understanding how pg_stat_statements interacts with extension (database) and how it behaves under different workloads (OLTP vs. OLAP, for example) is part of broader Database administration knowledge.

See also