pg_stat_statements

pg_stat_statements
Name	pg_stat_statements
Author	PostgreSQL Global Development Group
Released	2008
Language	SQL, C
License	PostgreSQL License

pg_stat_statements pg_stat_statements is a PostgreSQL extension that provides a means to track execution statistics of SQL statements. It aggregates performance metrics for query text signatures to aid database administrators and developers in diagnosing workload characteristics and optimizing resource use. Widely used in production deployments, it integrates with monitoring stacks and observability tools.

Overview

pg_stat_statements operates as a shared library extension that hooks into the PostgreSQL executor to collect cumulative statistics per normalized statement fingerprint. It is comparable in purpose to tools and projects such as Prometheus (software), Grafana, New Relic, Datadog and complements system-level telemetry from platforms like Kubernetes and Amazon Web Services services. The extension influences workflows for teams using technologies such as GitHub, GitLab, Jenkins (software), Travis CI, and cloud databases provided by Google Cloud Platform, Microsoft Azure, and Oracle Corporation.

Installation and configuration

Installation requires building or enabling the extension in a PostgreSQL instance and adjusting server parameters. Typical operations are performed with administrators familiar with Debian, Red Hat Enterprise Linux, Ubuntu, Amazon Linux, or FreeBSD packaging systems and orchestration tools like Ansible, Chef (software), Puppet (software), or Terraform. Configuration chiefly involves setting shared_preload_libraries to include the extension and tuning track_activity_query_size and pg_stat_statements.max. Changes often require a restart of clusters managed by teams using Patroni, pgpool-II, Pgbouncer, or vendor offerings such as Amazon RDS and Heroku. Integration with observability often uses exporters for Prometheus (software) or connectors for InfluxDB and Elasticsearch.

Data collected and view columns

The extension exposes a view that reports aggregated metrics such as calls, total_time, rows, shared_blks_hit, shared_blks_read, temp_blks_written, and local_blks_hit. Administrators and developers map these columns to performance indicators familiar from analyses in environments influenced by OLTP (industry) deployments at companies such as Facebook, Twitter, Airbnb, Uber Technologies and research from institutions like MIT, Stanford University, University of California, Berkeley and Carnegie Mellon University. The view includes a normalized query text fingerprinting mechanism that groups statements across parametrized variants, enabling correlation with tools used by teams at Netflix, Spotify, LinkedIn, and Pinterest.

Usage and examples

Common queries against the extension's view identify top-time-consuming statements, highest-call-count statements, and statements with the most I/O. DBAs writing maintenance scripts may craft SQL similar to queries used by contributors active on Stack Overflow, inside monitoring dashboards built with Grafana panels, or alerting rules in Prometheus (software). Examples in operational runbooks often reference incident response patterns practiced by teams from Google LLC, Microsoft Corporation, IBM, or Oracle Corporation when responding to performance regressions during deployments orchestrated by Kubernetes or CI pipelines from Jenkins (software) and GitLab.

Performance impact and limitations

While the collected statistics are invaluable, they incur overhead because instrumentation executes inside the server process and increases shared memory usage. For very high-concurrency workloads common in architectures designed by Amazon.com, Alibaba Group, Tencent, and high-frequency environments at Goldman Sachs or JPMorgan Chase additional tuning may be required. The extension has limits on the number of distinct statement fingerprints and may aggregate dissimilar statements once capacity is reached; similar concerns have been documented in benchmarking studies from TPC (Transaction Processing Performance Council)-style workloads and academic papers from ACM and IEEE conferences.

Security and access control

Access to the extension's view should be governed by PostgreSQL role-based permissions and monitored under organizational policies aligned with frameworks like ISO/IEC 27001 and regulations such as General Data Protection Regulation and Sarbanes–Oxley Act. Typical practice restricts SELECT on the view to DBAs and trusted monitoring roles, and integrates with identity systems like LDAP and Active Directory and auditing solutions such as OSSEC or Splunk. Care should be taken because query text may contain sensitive literals; teams following guidance from NIST and OWASP often sanitize or redact sensitive information before long-term storage.

Category:PostgreSQL extensions