Skip to article
Databases PostgreSQL

pg_stat_statements

pg_stat_statements is a PostgreSQL tool that shows which SQL statements are running often, taking time, or consuming resources. Instead of guessing from application logs, database teams can query a shared statistics view and see normalized statements with call counts, timing, rows, and other performance clues. In Azure Database for PostgreSQL flexible server, it is useful for tuning queries and capacity planning, but it is not free. Collecting query statistics adds overhead, so teams should enable and configure it deliberately.

Source: Microsoft Learn - Optimize query statistics collection on Azure Database for PostgreSQL Reviewed 2026-05-17

Exam trap
Enabling query statistics without considering overhead on an already CPU-bound server.
Production check
Is pg_stat_statements available, preloaded, and created in the database being investigated?
Practice this topic Microsoft Learn
Article details and learning context
Aliases
pg_stat_statements extension, PostgreSQL query statistics, query stats extension, Postgres statement statistics, Azure PostgreSQL pg_stat_statements
Difficulty
intermediate
CLI mappings
4
Last verified
2026-05-17
Compare related concepts Search related resources Browse the glossary

Understand the concept

Why it matters

pg_stat_statements matters because database performance problems are often caused by a small number of repeated queries. Without statement-level statistics, teams may scale compute, blame the network, or tune indexes blindly. The extension gives evidence for which normalized SQL patterns consume time and how often they run. That helps developers fix ORM regressions, missing indexes, N+1 query patterns, and expensive reports. It also supports capacity planning by showing workload shape over time. The caution is overhead and data sensitivity: query text and statistics must be collected, retained, and viewed responsibly. It turns tuning work into a prioritized queue instead of speculation.

Official wording and source

pg_stat_statements is a PostgreSQL extension that tracks execution statistics for normalized SQL statements. In Azure Database for PostgreSQL flexible server, it helps identify slow or frequently executed queries, but it must be enabled through extension and server-parameter configuration and carries measurable collection overhead.

Open Microsoft Learn

Technical context

In Azure architecture, pg_stat_statements sits inside the PostgreSQL database engine on Azure Database for PostgreSQL flexible server or compatible Postgres environments. It depends on extension availability, shared preload libraries, server parameters, database-level CREATE EXTENSION commands, and query access through SQL clients. Azure CLI helps operators inspect and change server parameters, while query analysis happens through psql, application tooling, Query Store, or monitoring dashboards. The extension complements Azure Monitor, Log Analytics, slow-query diagnostics, and application traces rather than replacing them.

Exam context

Compare with

Query Store Slow query PostgreSQL Query Store PostgreSQL log analytics

Where it is used

Where you see it

  1. In Azure Database for PostgreSQL server parameters, pg_stat_statements-related settings appear with shared preload libraries, tracking level, and query-size options during database tuning sessions and incident reviews.
  2. In SQL query output, the pg_stat_statements view lists normalized statements with calls, total time, mean time, rows, and identifiers during parameter audits, restart planning, and query investigations.
  3. In performance reviews, teams combine extension results with Azure Monitor metrics, slow-query logs, Query Store, and application traces after a release during performance baselining and capacity planning reviews.

Common situations

  • Find the SQL statements consuming the most total time after a release.
  • Identify high-call-count ORM queries that create unnecessary database load.
  • Compare query workload before and after index, schema, or application changes.
  • Support PostgreSQL capacity planning with evidence instead of guesswork.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Scenario 01 Factory planning query cleanup Scenario, objectives, solution, measured impact, and takeaway.
Scenario

Gearline Systems ran a production planning application on Azure Database for PostgreSQL flexible server. After a release, plant supervisors saw slow schedule pages during shift changes.

Goals
  • Identify the SQL statements responsible for the new latency spike.
  • Reduce schedule page P95 latency below 600 milliseconds.
  • Avoid increasing PostgreSQL vCores unless evidence proved capacity was the issue.
  • Limit query diagnostic access to database engineers and the owning developers.
Approach using pg_stat_statements

The database team enabled pg_stat_statements during a maintenance window after confirming server parameters with Azure CLI. They created the extension in the planning database and queried statements ordered by total execution time and calls. The top pattern was an ORM-generated lookup running thousands of times per page load. Developers added eager loading and an index, while operators compared Azure Monitor CPU, connection count, and statement statistics before and after the fix. Query text exports were restricted to a private engineering channel.

Potential outcomes
  • P95 schedule latency dropped from 2.4 seconds to 410 milliseconds after the ORM and index fix.
  • The team avoided a planned vCore increase, saving roughly 18 percent on monthly database compute.
  • The top statement’s call count fell by 87 percent during shift-change traffic.
  • Access review confirmed only approved engineers could view statement statistics and query text.
What to learn

pg_stat_statements turns PostgreSQL tuning from guesswork into evidence when teams pair query statistics with release timelines.

Scenario 02 Travel API regression review Scenario, objectives, solution, measured impact, and takeaway.
Scenario

SkyTrail Reservations used Azure Database for PostgreSQL flexible server for fare search metadata. A new promotion filter caused database CPU spikes every evening when partners refreshed offers.

Goals
  • Find whether the regression came from search SQL, partner batch jobs, or infrastructure limits.
  • Reduce evening CPU peaks below 70 percent without adding a read replica.
  • Preserve incident evidence before resetting query statistics.
  • Give developers enough statement detail to reproduce the issue safely.
Approach using pg_stat_statements

Operators inspected PostgreSQL parameters with Azure CLI, confirmed pg_stat_statements was active, and captured server state before the investigation. Database engineers queried statements by total time, mean time, and rows returned. The culprit was a promotion filter that prevented an existing index from being used. The team changed the predicate, added a partial index, and reset statistics only after saving a signed incident report. Azure Monitor metrics and application traces validated that partner batch jobs were not the source.

Potential outcomes
  • Evening CPU peaks dropped from 92 percent to 58 percent after the predicate and index change.
  • The company avoided adding a read replica that would have cost an estimated $2,800 per month.
  • Incident evidence was preserved before reset, reducing the postmortem timeline by two days.
  • Partner offer refreshes completed 34 percent faster with no API availability penalty.
What to learn

pg_stat_statements helps separate database design regressions from infrastructure capacity problems during high-pressure incidents.

Scenario 03 Climate data capacity planning Scenario, objectives, solution, measured impact, and takeaway.
Scenario

AerisLedger served climate-risk datasets to analysts through APIs and notebooks backed by PostgreSQL metadata tables. Customer growth made costs rise, but the team lacked query-level evidence for scaling.

Goals
  • Build a baseline of statement calls and execution time for the main metadata workload.
  • Identify queries that could be cached or rewritten before the next compute upgrade.
  • Create a capacity plan supported by metrics, not anecdotal user complaints.
  • Keep query diagnostics compliant with customer data-handling rules.
Approach using pg_stat_statements

The operations group enabled pg_stat_statements on a nonproduction clone first, then applied the same parameter configuration to production during the approved window. Azure CLI exports recorded server SKU, location, and parameter values for the capacity review. Engineers sampled the statistics weekly, joined results with notebook release dates, and found two metadata joins that dominated total execution time. They added cache keys, changed pagination, and documented which query text could appear in internal dashboards.

Potential outcomes
  • The next capacity upgrade was delayed by four months after query tuning reduced total database time.
  • Metadata API median latency improved 46 percent for notebook-heavy customers.
  • The capacity plan cited statement statistics, Azure Monitor trends, and workload growth projections.
  • Compliance review approved diagnostics because query text handling and dashboard access were documented.
What to learn

pg_stat_statements is valuable for planning when its statistics are governed as production telemetry, not casual debugging output.

Azure CLI

Azure CLI is useful for pg_stat_statements because enabling useful statistics often depends on flexible-server parameters. CLI commands make parameter state, pending restart requirements, server identity, and configuration drift visible before database administrators query the extension through SQL tools.

Useful for

  • Show shared_preload_libraries and pg_stat_statements parameters on a flexible server.
  • Set required server parameters in a controlled maintenance window after approval.
  • Restart a server only after confirming parameter changes and application readiness.
  • Export parameter settings and server metadata for performance-tuning evidence.

Before you run a command

  • Confirm tenant, subscription, resource group, server name, region, database owner, and maintenance window before parameter changes.
  • Check permissions because server-parameter updates and restarts affect all databases on the flexible server.
  • Review performance overhead, query-text exposure, compliance rules, and dashboard access before enabling tracking.
  • Use JSON output and capture previous parameter values so rollback and drift review are possible.

What the output tells you

  • Parameter values show whether pg_stat_statements is loaded, tracked, and sized as expected.
  • Restart-required flags explain whether changes are active or waiting for a planned server restart.
  • Server state, version, and location confirm the exact PostgreSQL environment being tuned.
  • SQL view columns such as calls, total time, mean time, and rows reveal query workload hotspots.

Mapped commands

PostgreSQL parameter inspection commands

adjacent 
az postgres flexible-server parameter show --resource-group <resource-group> --server-name <server-name> --name shared_preload_libraries --output json
az postgres flexible-server parameterdiscoverDatabases
read-only Microsoft Learn 
az postgres flexible-server parameter show --resource-group <resource-group> --server-name <server-name> --name pg_stat_statements.track --output json
az postgres flexible-server parameterdiscoverDatabases
read-only Microsoft Learn 
az postgres flexible-server parameter set --resource-group <resource-group> --server-name <server-name> --name shared_preload_libraries --value pg_stat_statements
az postgres flexible-server parameterconfigureDatabases
mutating Microsoft Learn 
az postgres flexible-server restart --resource-group <resource-group> --name <server-name>
az postgres flexible-serverremoveDatabases
destructive Microsoft Learn

Architecture context

pg_stat_statements lives inside the PostgreSQL engine used by Azure Database for PostgreSQL flexible server, so I place it in the database observability layer rather than the platform metrics layer. It captures normalized query fingerprints, call counts, timing, rows, and buffer-related evidence when the extension is enabled correctly. Architects use it to connect application symptoms to SQL behavior without relying only on CPU graphs or slow-query anecdotes. It must be planned with server parameters, extension creation, role access, Query Store settings, Log Analytics integration, and retention expectations. In real operations, pg_stat_statements becomes the bridge between developers tuning ORM-generated SQL and platform teams deciding whether the server needs indexing, pooling, memory, or compute changes.

Security
Security impact is indirect but meaningful. pg_stat_statements does not grant Azure access, but it can expose SQL text, table names, function names, and sometimes sensitive literals depending on normalization and application behavior. Access to the statistics view should be limited to trusted database roles. Enabling the extension may require elevated database privileges and server-parameter changes controlled by Azure administrators. Operators should avoid publishing raw query samples in tickets or dashboards without review. Combine least privilege, auditing, private connectivity, managed identities where applicable, and careful log handling to reduce diagnostic data exposure. Redact query examples before sharing outside trusted database channels.
Cost
Cost impact is mostly indirect. Better query visibility can avoid unnecessary vCore, memory, storage, or read-replica upgrades by showing which statements need tuning. It can also justify scaling when the workload is genuinely outgrowing the server. The extension itself adds overhead through statistics collection and query text handling, which may slightly increase CPU or disk pressure. Misusing it can lead to more diagnostics noise, larger logs, and operator effort. FinOps reviews should connect expensive PostgreSQL tiers to evidence from pg_stat_statements, Query Store, slow queries, and actual workload demand. Tie savings claims to measured runtime, not only lower resource peaks evidence.
Reliability
Reliability impact is indirect through performance observability. The extension helps find queries that threaten reliability by saturating CPU, I/O, memory, locks, or connection pools. However, enabling or changing related parameters may require planning, and collection overhead can worsen an already stressed server if configured carelessly. Reliable teams enable pg_stat_statements in nonproduction first, document restart requirements, baseline overhead, and define who can reset or query statistics. During incidents, the data helps distinguish a broad platform issue from one application query pattern, improving rollback and mitigation decisions. Set alert notes that reference the active statistics window and reset timestamp during triage.
Performance
Performance impact is both diagnostic and operational. pg_stat_statements helps improve performance by identifying high-total-time, high-call-count, or slow-average queries that deserve indexing, rewriting, caching, or pagination. At the same time, the extension hooks into query execution and adds collection overhead, so settings such as tracking level and query text size should be chosen carefully. Operators should compare CPU, latency, disk I/O, and query throughput before and after enabling it. The best results come from pairing statement statistics with execution plans, indexes, and release timelines. Retest after changes so improvements are not confused with cache effects or plan-cache artifacts and replays.
Operations
Operators use pg_stat_statements to investigate slow requests, compare releases, tune indexes, and create performance baselines. Azure CLI handles server-parameter inspection, configuration drift review, restarts when needed, and evidence export for flexible servers. SQL tools then query the pg_stat_statements view to sort by total time, mean time, calls, rows, or I/O-related columns. Runbooks should say when to enable tracking, how long to retain statistics, who can reset them, and how results are shared with developers. Changes should be paired with Azure Monitor metrics and application traces. Keep reset times and parameter values in every tuning report and release review note.

Common mistakes

  • Enabling query statistics without considering overhead on an already CPU-bound server.
  • Giving broad users access to query text and performance statistics that may reveal sensitive behavior.
  • Reading only average time and missing high-call-count queries that dominate total load.
  • Changing parameters but forgetting restart requirements or extension creation inside each database.