---
type: WebPage
title: How to Monitor Postg
description: ""
resource: https://share.google/c9hploc2LsaKbxn9r
tags: []
timestamp: 2026-07-02T22:21:12.883679Z
---

[Skip to main content](#main-content)

[OneUptime
![OneUptime logo](/img/3-transparent.svg)](/)

Open menu

Products

Enterprise

Enterprise

## Built for how you work

Scale your reliability operations with enterprise-grade tools.

[Enterprise Overview

Scale with confidence](/enterprise/overview)
[Request Demo

See it in action](/enterprise/demo)

[Contact Sales](/legal/contact)

Enterprise

[Enterprise Overview

Solutions for large organizations](/enterprise/overview)
[Request Demo

Schedule a personalized demo](/enterprise/demo)

Teams

[DevOps](/solutions/devops)
[SRE](/solutions/sre)
[Platform](/solutions/platform)
[Developers](/solutions/developers)

Industries

[FinTech](/industries/fintech)
[SaaS](/industries/saas)
[Healthcare](/industries/healthcare)
[E-Commerce](/industries/ecommerce)
[Media](/industries/media)
[Government](/industries/government)

[Documentation](/docs)
[Pricing](/pricing)
[Blog](/blog)

[Get Started Free](/accounts/register)

[Pricing](/pricing)

Resources

Resources

## Learn & Connect

Everything you need to get started and succeed.

[Documentation

Guides & tutorials](/docs)
[API Reference

REST API & SDKs](/reference)

[Star on GitHub](https://github.com/oneuptime/oneuptime)

Learn

[Blog

News & insights](/blog)
[Status

System status](https://status.oneuptime.com)
[Changelog

What's new](https://github.com/OneUptime/oneuptime/releases)
[Videos

Watch & learn](https://www.youtube.com/%40OneUptimehq)

Support

[Help Center](/support)
[Contact Us](/cdn-cgi/l/email-protection#daa9afaaaab5a8ae9ab5b4bfafaaaeb3b7bff4b9b5b7)

Company

[About Us](/about)
[Merch Store](https://shop.oneuptime.com)

[Legal](/legal)
[Privacy](/legal/privacy)
[Terms](/legal/terms)

100% Open Source

[Sign
in](/accounts)
[Sign up](/accounts/register)

![OneUptime](/img/3-transparent.svg)

Close menu

[Status Page](/product/status-page)

[Incidents](/product/incident-management)

[Monitoring](/product/monitoring)

[On-Call](/product/on-call)

[Maintenance](/product/scheduled-maintenance)

[Logs](/product/logs-management)

[Metrics](/product/metrics)

[Traces](/product/traces)

[Exceptions](/product/exceptions)

[Services](/product/services)

[Kubernetes](/product/kubernetes)

[Docker](/product/docker)

[Podman](/product/podman)

[Hosts](/product/host)

[Proxmox](/product/proxmox)

[AI / LLM Observability](/product/ai-observability)

[Ceph](/product/ceph)

[Docker Swarm](/product/docker-swarm)

[IoT Devices](/product/iot)

[Serverless](/product/serverless)

[Cloud](/product/cloud)

[Profiles](/product/profiles)

[RUM](/product/rum)

[Workflows](/product/workflows)

[Dashboards](/product/dashboards)

[AI Agent](/product/ai-agent)

Enterprise

[DevOps](/solutions/devops)
[SRE](/solutions/sre)
[Platform](/solutions/platform)

[Pricing](/pricing)
[Docs](/docs)
[Request Demo](/enterprise/demo)
[Support](/support)

[Sign
up](/accounts/register)

Existing customer?
[Sign in](/accounts)

Products

## Explore the OneUptime platform

One platform for monitoring, observability & incident response.

`⌘K`

[AI Agent

Auto-fix issues with AI-powered PRs — analyze incidents and open pull requests automatically.](/product/ai-agent)

### Essentials

[Monitoring

Uptime & synthetic checks](/product/monitoring)
[Status Page

Communicate incidents to users](/product/status-page)
[Incidents

Detect, manage & resolve](/product/incident-management)
[On-Call & Alerts

Smart routing & escalations](/product/on-call)
[Scheduled Maintenance

Plan & communicate downtime](/product/scheduled-maintenance)

### Observability

[Logs

Fastest log ingest & search](/product/logs-management)
[Metrics

Application & infra metrics](/product/metrics)
[Traces

Distributed request tracing](/product/traces)
[Exceptions

Error tracking & debugging](/product/exceptions)
[Profiles

CPU & memory profiling](/product/profiles)
[RUM

Real user monitoring](/product/rum)

### Infrastructure

[Services

Catalog every service you run](/product/services)
[Kubernetes

Cluster & pod observability](/product/kubernetes)
[Docker

Host & container observability](/product/docker)
[Podman

Host & container observability](/product/podman)
[Hosts

Auto-discovered server metrics](/product/host)
[Proxmox

VE clusters, VMs & backups](/product/proxmox)
[AI / LLM Observability

Tokens, cost, traces & prompts](/product/ai-observability)
[Ceph

Storage cluster health](/product/ceph)
[Docker Swarm

Nodes, services, tasks & stacks](/product/docker-swarm)
[IoT Devices

Fleets, sensors & gateways](/product/iot)
[Serverless

Functions & cold starts](/product/serverless)
[Cloud

AWS, GCP & Azure](/product/cloud)

### Automation & Analytics

[Workflows

No-code automation builder](/product/workflows)
[Runbooks

Auto-trigger response steps](/product/runbooks)
[Dashboards

Custom data visualizations](/product/dashboards)

No products found

[100% Open Source

Self-host or use our cloud](https://github.com/oneuptime/oneuptime)

`↑`
`↓`
`↵`
`esc`

![]()

# How to Monitor PostgreSQL with pg\_stat\_statements

A comprehensive guide to using pg\_stat\_statements for PostgreSQL query performance monitoring, covering installation, configuration, analysis queries, and integration with monitoring tools.

![Nawaz Dhandala](https://avatars.githubusercontent.com/nawazdhandala?s=64 "Nawaz Dhandala")

By @nawazdhandala

•
Jan 21, 2026
•

Reading time

[PostgreSQL](/blog/tag/postgresql)
[Pg\_stat\_statements](/blog/tag/pg_stat_statements)
[Monitoring](/blog/tag/monitoring)
[Performance](/blog/tag/performance)
[Query Analysis](/blog/tag/query-analysis)

## On this page

---

pg\_stat\_statements is the most essential extension for PostgreSQL performance monitoring. It tracks execution statistics for all SQL statements, helping you identify slow queries, resource-intensive operations, and optimization opportunities. This guide covers everything from setup to advanced analysis.

## Prerequisites

* PostgreSQL 13+ for the column names used in this guide (older versions use `total_time`, `mean_time`, and related columns)
* Superuser access for installation
* Understanding of query patterns

## Installation

### Enable the Extension

```
-- Create extension (requires superuser)
CREATE EXTENSION pg_stat_statements;

-- Verify installation
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
```

### Configure postgresql.conf

```
# Add to shared_preload_libraries (requires restart)

shared_preload_libraries = 'pg_stat_statements'

# PostgreSQL 14+: enable query identifiers if not already enabled
# compute_query_id = auto

# Configuration options
pg_stat_statements.max = 10000           # Max statements to track
pg_stat_statements.track = all           # all, top, or none
pg_stat_statements.track_utility = on    # Track non-DML statements
pg_stat_statements.track_planning = on   # Track planning time (v13+)
pg_stat_statements.save = on             # Save stats across restarts
```

Restart PostgreSQL:

```
sudo systemctl restart postgresql
```

## Basic Usage

### View All Statements

```
SELECT * FROM pg_stat_statements LIMIT 10;
```

### Key Columns

| Column | Description |
| --- | --- |
| `query` | Normalized query text |
| `calls` | Number of executions |
| `total_exec_time` | Total execution time (ms) |
| `mean_exec_time` | Average execution time (ms) |
| `rows` | Total rows returned |
| `shared_blks_hit` | Shared buffer hits |
| `shared_blks_read` | Shared blocks read from disk |
| `temp_blks_read` | Temp blocks read |
| `temp_blks_written` | Temp blocks written |

## Finding Problem Queries

### Slowest Queries by Total Time

```
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
```

### Slowest Queries by Average Time

```
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    round(min_exec_time::numeric, 2) AS min_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    query
FROM pg_stat_statements
WHERE calls > 100  -- Exclude rare queries
ORDER BY mean_exec_time DESC
LIMIT 20;
```

### Most Frequently Called Queries

```
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;
```

### Queries with High I/O

```
SELECT
    calls,
    shared_blks_hit,
    shared_blks_read,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_ratio,
    temp_blks_read + temp_blks_written AS temp_blks,
    query
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 20;
```

### Queries Using Temp Space

```
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    temp_blks_read,
    temp_blks_written,
    query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;
```

## Advanced Analysis

### Cache Hit Ratio by Query

```
SELECT
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    CASE
        WHEN shared_blks_hit + shared_blks_read = 0 THEN 0
        ELSE round(100.0 * shared_blks_hit / (shared_blks_hit + shared_blks_read), 2)
    END AS cache_hit_ratio,
    round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE calls > 10
ORDER BY shared_blks_read DESC
LIMIT 20;
```

### Planning vs Execution Time (PostgreSQL 13+)

```
SELECT
    query,
    calls,
    round(total_plan_time::numeric, 2) AS plan_ms,
    round(total_exec_time::numeric, 2) AS exec_ms,
    round((100.0 * total_plan_time / nullif(total_plan_time + total_exec_time, 0))::numeric, 2) AS plan_pct,
    round(mean_exec_time::numeric, 2) AS mean_exec_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_plan_time DESC
LIMIT 20;
```

### Calls per Execution Minute

```
-- Calls per minute of cumulative execution time (not wall-clock throughput)
SELECT
    query,
    calls,
    round((total_exec_time / 60000)::numeric, 2) AS total_exec_minutes,
    round((calls * 60000.0 / total_exec_time)::numeric, 2) AS calls_per_exec_minute
FROM pg_stat_statements
WHERE total_exec_time > 0
ORDER BY calls DESC
LIMIT 20;
```

### Find Similar Queries (Normalize Analysis)

```
-- Group similar queries
SELECT
    left(query, 50) AS query_prefix,
    count(*) AS variations,
    sum(calls) AS total_calls,
    sum(total_exec_time) AS total_time
FROM pg_stat_statements
GROUP BY left(query, 50)
HAVING count(*) > 1
ORDER BY sum(total_exec_time) DESC
LIMIT 20;
```

## Reset Statistics

### Reset All Statistics

```
SELECT pg_stat_statements_reset();
```

### Reset Specific User (PostgreSQL 13+)

```
-- Reset for specific user
SELECT pg_stat_statements_reset(
    userid => (SELECT oid FROM pg_roles WHERE rolname = 'app_user'),
    dbid => 0,
    queryid => 0
);
```

## Integration with Monitoring

### Prometheus Exporter Query

```
-- Create view for prometheus exporter
CREATE VIEW pg_stat_statements_metrics AS
SELECT
    queryid,
    dbid,
    userid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read,
    local_blks_hit,
    local_blks_read,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements;
```

### Periodic Snapshots

```
-- Create snapshot table
CREATE TABLE pg_stat_statements_history (
    snapshot_time TIMESTAMP DEFAULT NOW(),
    queryid BIGINT,
    dbid OID,
    userid OID,
    query TEXT,
    calls BIGINT,
    total_exec_time DOUBLE PRECISION,
    mean_exec_time DOUBLE PRECISION,
    rows BIGINT,
    shared_blks_hit BIGINT,
    shared_blks_read BIGINT
);

-- Take snapshot
INSERT INTO pg_stat_statements_history
SELECT
    NOW(),
    queryid, dbid, userid, query, calls,
    total_exec_time, mean_exec_time, rows,
    shared_blks_hit, shared_blks_read
FROM pg_stat_statements;

-- Compare snapshots
SELECT
    h1.query,
    h2.calls - h1.calls AS new_calls,
    h2.total_exec_time - h1.total_exec_time AS new_time_ms
FROM pg_stat_statements_history h1
JOIN pg_stat_statements_history h2
    ON h1.queryid = h2.queryid
   AND h1.dbid = h2.dbid
   AND h1.userid = h2.userid
WHERE h1.snapshot_time = '2026-01-20 00:00:00'
  AND h2.snapshot_time = '2026-01-21 00:00:00'
ORDER BY new_time_ms DESC
LIMIT 20;
```

## Best Practices

### Configuration Recommendations

```
# Track enough statements
pg_stat_statements.max = 10000

# Track all queries including nested
pg_stat_statements.track = all

# Track planning for optimization
pg_stat_statements.track_planning = on

# Save across restarts
pg_stat_statements.save = on
```

### Regular Analysis

```
-- Weekly: Top resource consumers
SELECT
    query,
    calls,
    round(total_exec_time::numeric / 1000, 2) AS total_seconds,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

-- Reset after analysis (optional)
-- SELECT pg_stat_statements_reset();
```

### Security Considerations

```
-- Limit access to statistics
REVOKE SELECT ON pg_stat_statements FROM PUBLIC;
GRANT SELECT ON pg_stat_statements TO monitoring_role;

-- Create sanitized view (hide query text)
CREATE VIEW pg_stat_statements_safe AS
SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements;
```

## Troubleshooting

### Extension Not Loading

```
-- Check shared_preload_libraries
SHOW shared_preload_libraries;

-- Verify module is available
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
```

### No Statistics Appearing

```
-- Check track setting
SHOW pg_stat_statements.track;

-- Verify extension is created in current database
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
```

### High Memory Usage

```
# Reduce max statements if needed
pg_stat_statements.max = 5000
```

## Conclusion

pg\_stat\_statements is essential for PostgreSQL performance monitoring:

1. **Enable it** on all production systems
2. **Review regularly** for problem queries
3. **Track trends** with periodic snapshots
4. **Integrate** with your monitoring stack
5. **Reset periodically** to see recent patterns

Combined with EXPLAIN ANALYZE, pg\_stat\_statements gives you complete visibility into query performance.

Share this article

![Nawaz Dhandala](https://avatars.githubusercontent.com/nawazdhandala)

### Nawaz Dhandala

Author

@nawazdhandala • Jan 21, 2026 •

Nawaz is building OneUptime with a passion for engineering reliable systems and improving observability.

[GitHub](https://github.com/nawazdhandala)

Technically validated
· Jun 21, 2026

View report

### Help improve this post

Every OneUptime blog post is open source. Found a typo, an inaccuracy, or have a clearer way to explain something? Anyone can contribute — your edits make this post better for everyone who reads it next.

[Edit this post on GitHub](https://github.com/oneuptime/blog/tree/master/posts/2026-01-21-postgresql-pg-stat-statements)
[Contributing guidelines](https://github.com/oneuptime/blog)

[Open source](https://github.com/oneuptime/oneuptime)

## OneUptime is the Open-Source Observability Platform

Your complete reliability stack unified: infrastructure monitoring, incident management, status pages, and APM. Open-source and self-hostable.

[Get started for free](/accounts/register)
[Request a demo](/enterprise/demo)

[Status Page

Real-time status updates](/product/status-page)

[Incidents

Detect and resolve fast](/product/incident-management)

[Monitoring

Monitor any resource](/product/monitoring)

[On-Call

Smart alert routing](/product/on-call)

[Maintenance

Plan & communicate downtime](/product/scheduled-maintenance)

[Logs

Fastest log ingest and search](/product/logs-management)

[Metrics

Performance insights](/product/metrics)

[Traces

End-to-end distributed tracing](/product/traces)

[Exceptions

Catch and fix bugs early](/product/exceptions)

[Workflows

Automate any process](/product/workflows)

[Dashboards

Visualize all your data](/product/dashboards)

[Kubernetes

Monitor K8s clusters](/product/kubernetes)

[Profiles

CPU & memory profiling](/product/profiles)

[AI Agent

Automatically detect, diagnose, and resolve incidents with AI-powered root cause analysis and code fixes.](/product/ai-agent)

We use cookies to enhance your browsing experience and provide
personalized content. By clicking "Accept," you consent to the use of cookies.

Our product uses both first-party and third-party cookies for session storage and for various other purposes.

Please note that disabling certain cookies may affect the functionality and performance of our product.

For more information about how we handle your data and cookies, please read our Privacy Policy.

By continuing to use our site without changing your cookie settings, you agree to our use of cookies as
described above. See our [terms](/legal/terms) and our [privacy policy](/legal/privacy)

Accept
all
Reject all

## Footer

Open Source Observability

### Build reliable systems with confidence

Join thousands of developers using OneUptime to monitor, debug, and optimize their infrastructure, stack, and apps.

[Read Blog](/blog)
[Star on GitHub](https://github.com/oneuptime/oneuptime)

[![OneUptime](/img/4-gray.svg)](/)

The complete open-source observability platform. Monitor, debug, and improve your entire stack in one place.

[GitHub](https://github.com/oneuptime/oneuptime)
[X](https://x.com/oneuptimehq)
[YouTube](https://www.youtube.com/%40OneUptimeHQ)
[Reddit](https://www.reddit.com/r/oneuptimehq/)
[LinkedIn](https://www.linkedin.com/company/oneuptime)

Trusted by thousands of teams worldwide - from Fortune 500 enterprises to fast-growing startups.

### Products

* [Status Page](/product/status-page)
* [Incidents](/product/incident-management)
* [Monitoring](/product/monitoring)
* [On-Call](/product/on-call)
* [Logs](/product/logs-management)
* [Metrics](/product/metrics)
* [Traces](/product/traces)
* [Exceptions](/product/exceptions)
* [Profiles](/product/profiles)
* [Real User Monitoring](/product/rum)
* [Kubernetes](/product/kubernetes)
* [Docker](/product/docker)
* [Podman](/product/podman)
* [Hosts](/product/host)
* [Proxmox](/product/proxmox)
* [AI / LLM Observability](/product/ai-observability)
* [Ceph](/product/ceph)
* [Docker Swarm](/product/docker-swarm)
* [IoT Devices](/product/iot)
* [Serverless](/product/serverless)
* [Cloud](/product/cloud)
* [Workflows](/product/workflows)
* [Dashboards](/product/dashboards)
* [AI Agent](/product/ai-agent)

### Solutions

* [Enterprise](/enterprise/overview)
* [Request Demo](/enterprise/demo)
* [Pricing](/pricing)
* [Data Residency](/legal/data-residency)

### Teams

* [DevOps](/solutions/devops)
* [SRE](/solutions/sre)
* [Platform](/solutions/platform)
* [Developers](/solutions/developers)

### Tools

* [MCP Server](/tool/mcp-server)
* [CLI](/tool/cli)

### Resources

* [Documentation](/docs)
* [API Reference](/reference)
* [Blog](/blog)
* [Help & Support](/support)
* [GitHub](https://github.com/oneuptime/oneuptime)
* [Changelog](https://github.com/oneuptime/oneuptime/releases)
* [Open Source Friends](/oss-friends)

### Industries

* [FinTech](/industries/fintech)
* [SaaS](/industries/saas)
* [Healthcare](/industries/healthcare)
* [E-Commerce](/industries/ecommerce)
* [Media](/industries/media)
* [Government](/industries/government)

### Company

* [About Us](/about)
* [Careers](https://github.com/OneUptime/interview)
* [Merch Store](https://shop.oneuptime.com)
* [Contact](/legal/contact)

### Legal

* [Trust Center](/trust)
* [Terms of Service](/legal/terms)
* [Privacy Policy](/legal/privacy)
* [SLA](/legal/sla)
* [Legal Center](/legal)

### Compare

* [vs PagerDuty](/compare/pagerduty)
* [vs Statuspage](/compare/statuspage.io)
* [vs Incident.io](/compare/incident.io)
* [vs Pingdom](/compare/pingdom)
* [vs Datadog](/compare/datadog)
* [vs New Relic](/compare/newrelic)
* [vs Better Stack](/compare/better-uptime)
* [vs Uptime Robot](/compare/uptime-robot)
* [vs Checkly](/compare/checkly)
* [vs SigNoz](/compare/signoz)

© 2026 HackerBay, Inc. All rights reserved.

[Open Source](https://github.com/oneuptime/oneuptime)
|
Made with care for developers worldwide

[SOC 2](/legal/soc-2)
[HIPAA](/legal/hipaa)
[GDPR](/legal/gdpr)
[ISO 27001](/legal/iso-27001)

## Validation report

Technically reviewed for accuracy • Jun 21, 2026

Loading validation report…

Automated technical review
Close