Postgres in Production, Special Series: Deep Dive into pg_stat_statements (Part 1)

In this special series of "Postgres in Production", we take a deep dive into pg_stat_statements -- the essential Postgres extension for tracking query performance. In Part 1, Ryan Booz covers what pg_stat_statements is, how to enable it, what it tracks, and critically, what it doesn't track and why that matters for your monitoring setup.

Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel.

---

Transcript

What is pg_stat_statements?

Have you ever found yourself asking questions like: "My database is slow right now -- which query is using all the resources?" Or maybe, "Over the last 24 hours, which queries on average take more than a second to execute?" What about, "Some of these webpages are loading slowly -- can you show me which queries are called a lot, but take a couple hundred milliseconds on average?"

These are common questions for database engineers, DBAs, and developers trying to make their applications work well with Postgres. And the answer has almost always been: go use pg_stat_statements.

pg_stat_statements is an included extension, part of the contrib modules that ship with the Postgres open source project. It's available wherever you install Postgres -- whether that's a cloud provider, a Docker container, or a bare metal install. It's there, it just needs to be activated.

How pg_stat_statements stores data

The metrics that pg_stat_statements holds are stored in an in-memory hash table per Postgres instance -- not per database. There's one instance of this hash table across all databases within a Postgres instance, and it tracks per-execution query metrics.

Enabling pg_stat_statements

To enable pg_stat_statements, two steps are required:

1. Configure it within the shared_preload_libraries setting in postgresql.conf. If you're using a cloud environment, some providers enable this by default, and all give you a way to edit this setting.
2. Install the extension on at least one database using the CREATE EXTENSION command.

Once that's done, you can begin querying the data.

What does it track?

As of Postgres 18, there are at least 45 columns in this view, including:

• A unique query ID for every normalized query text
• The actual query text
• Call counts, total time, and mean run time
• Rows selected
• Lock and block information
• Planning metrics

There's a lot of data, and it can be incredibly helpful for finding queries that need your attention.

The cumulative metrics problem

One of the key things to understand is that all metrics in pg_stat_statements are cumulative -- but only since the last time a query was tracked.

Up until recently, the common understanding was that metrics persist until the server restarts or until you explicitly reset them with pg_stat_statements_reset(). For years, the workflow looked like this:

1. Server is slow? Run pg_stat_statements_reset() to clear all metrics
2. Wait a few minutes, then query the view to find which queries are using the most resources
3. Identify and optimize the problematic query
4. Reset metrics again and continue monitoring

pg_stat_statements wasn't originally intended to be sampled over and over for days, weeks, or months to track long-term query metric performance.

This was an aha moment -- many of us assumed that if a query has run at any point during the lifetime of a Postgres instance, there would be data for it in pg_stat_statements. That's just not true.

What it doesn't track

At a high level, pg_stat_statements does not provide:

• Per-execution history -- There's no record saying "this query was executed two months ago and here are the metrics for that specific execution"
• Time-windowed data -- It's not a per-second or per-minute view. Monitoring tools have to calculate deltas from the cumulative metrics themselves
• Every query forever -- At any one time, it can only track up to pg_stat_statements.max unique query texts (default: 5,000)

The real issue is what happens when it reaches that 5,000 unique query limit -- something we'll explore in depth throughout this series.

Why this matters for monitoring

When we treat pg_stat_statements as the single source of truth for query performance history, we run into real problems:

• Every monitoring tool that tracks query performance over time samples from pg_stat_statements -- it's the only query metrics view available everywhere in Postgres
• If you don't understand when pg_stat_statements can fail or when settings need to be modified, you could be flying blind when it matters most
• You might not be able to find a problematic query or its historical data in either pg_stat_statements or your monitoring tool

What's coming in this series

Over the next episodes in this series, we'll cover:

• What makes a "unique" statement -- How normalized query texts work, and what "normalize" really means
• Source code deep dive -- Uncovering misconceptions by looking at the actual Postgres source
• Query text truncation -- Why you can't always find the full query text
• Configuration tuning -- How to better configure pg_stat_statements for your workload
• High cardinality workloads -- Why they're problematic and what you can do about it
• Long-term strategies -- Better ways to utilize pg_stat_statements as your Postgres environment grows

Stay tuned for Part 2, where we'll dig into what makes a statement "unique" in pg_stat_statements and look at the source code to uncover some longstanding misconceptions.

I hope this series helps you better understand one of the most essential tools in the Postgres ecosystem. Feel free to subscribe to our YouTube channel, sign up for our newsletter or follow us on LinkedIn to get updates about new episodes!

What we discussed in this episode

• pg_stat_statements documentation
• PostgreSQL contrib modules
• shared_preload_libraries configuration