Postgres in Production Special Series: Diagnosing High Cardinality Workloads in pg_stat_statements (Part 6)

In Part 6 of this special Postgres in Production deep dive series, Ryan Booz asks a question that determines how useful pg_stat_statements can be for you at all: do you have a high cardinality workload? This episode covers what that actually means, why ORMs, dynamic SQL, and AI-assisted development tools generate more unique queries than you might expect, a side by side demo of the same workload on Postgres 17 and Postgres 18, and the concrete checks that tell you whether pg_stat_statements is losing the data you need for query tuning.

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

A quick recap

Over the first five episodes we covered what pg_stat_statements is and the metrics it stores (Part 1), what makes a statement “unique” through normalization (Part 2), where the query texts live on disk (Part 3), how new metrics get stored and old ones get deallocated (Part 4), and the configuration settings that control all of it (Part 5).

Through all of that, I have probably mentioned the pg_stat_statements.max setting at least 100 times, because it’s so crucial to understanding how effective the data is that you have. This episode is about the workloads that consistently outrun that setting, and being able to identify whether you’re in that situation is really helpful to determining if pg_stat_statements can help you do the query tuning and optimization that you need it to.

What a high cardinality workload means

When I say high cardinality, what does it really mean? To me, it means something like this: if you have a workload that’s consistently generating more unique normalized queries than the pg_stat_statements.max capacity, then you really have a high cardinality workload from a pg_stat_statements perspective. The extension isn’t able to consistently retain the metrics that can help you find the queries that are most in need of being optimized.

Now, I can hear a lot of you say, and quite honestly, I’ve said this myself: “My application isn’t that complex. There’s no way I’m suffering from something like this. The data must be there somewhere, I just can’t find it.” Well, that’s not always the case.

It’s often the tooling we’re using that hides the SQL that’s being generated behind the scenes. We know that the tool is abstracting the SQL for us, but we might not fully understand the effect it’s having on our query metrics.

Where the unique queries come from

It might be the ORM and the way it generates unique SQL statements for different clients, customers, and tenants. Maybe it’s custom dynamic SQL that you yourself have written. I’ve done this often in stored procedures, and if I’m running with pg_stat_statements.track = all, that dynamic SQL produces unique statements over and over again.

Maybe it’s ad hoc reporting. All of us are connecting more and more tools to our database, including AI systems, and those tools generate a plethora of queries as they attempt to navigate the information needed to satisfy the request. Lots of ad hoc querying can fill up pg_stat_statements very quickly.

And we’ve talked often in this series about variable length IN lists in Postgres 17 and below, not to mention dynamic column select lists with otherwise similar queries.

A demo: overwhelming pg_stat_statements with Bluebox

I want to show you a brief demo of exactly how easy it is to overwhelm pg_stat_statements, with a tool you could actually test this with yourself. I’ll use the load generation tool included with the Bluebox sample database, a sample database I’ve created and made available open source to the community for testing and demonstrations like this.

About an hour before recording this video, I started this load generation with the exact same settings on a Postgres 17 database and a Postgres 18 database. The load testing project has about 30 different scenarios, each created for very specific kinds of query shapes and data requests. Most of them have one or two SQL parameterized statements included, executed directly on the database without the use of an ORM. In total, there are fewer than 50 actually unique text statements in the entire project.

But a couple of them, like batch_film_lookup, take a list of anywhere from one to 200 film IDs and look them up. On Postgres 17 or below, that becomes really easy to turn into a lot of unique load: all it takes is a WHERE IN clause and lots of unique lists of film IDs.

After about an hour of running, Postgres 17 has 671 unique statements inside of pg_stat_statements. The exact same workload on Postgres 18 has 120 unique statements. That is one of the improvements we’ve talked about a number of times through this series: in Postgres 18 and above, those IN lists are normalized down to one statement.

Another way to visualize this is to look at the queries with those IN lists. One example load query looks up data based on lists of customer IDs. On Postgres 17, we can see dozens of the same query except for the WHERE IN clause that shows a unique list each time: one has many customer IDs, another has four or five, and so on. On Postgres 18, the same query produces only two entries.

You might ask why two, if Postgres 18 collapses all of this. Some of it has to do with the tooling: the first time it sends a parameterized query, it also has to send the value types, and it often sends a generic type like numeric when it isn’t 100% sure what the type is. Once it gets a response back, the tooling can sometimes adjust and from that point forward sends the correct data type, for instance an integer instead of numeric. This is often why you’ll see a few extra entries for a query text even on Postgres 18.

The sample load test is a really easy way to see the impact of a seemingly simple query workload on pg_stat_statements, and you can use the tool to test it yourself. Grab Bluebox, read the instructions on how to start the load testing, and configure it against one of your sample Bluebox databases.

Our databases are hidden behind our tools

More than ever, I think we’re experiencing higher and higher cardinality query workloads because our databases are hidden and abstracted from our day to day developer work. The reason is staring us right in the face in this modern age: ORMs and data access tools let us use our own objects and object notation to get the data we want, letting the framework write the queries, hopefully as effectively as possible. But we’ve all experienced time and again how inefficient they can be as our applications and databases get more complex.

Additionally, we now have dozens of AI-assisted development tools that freely query the database based on a schema, in ways they decide are best. Even in my own development sessions using a number of these tools, I see significant numbers of pg_stat_statements data being created, because the tooling combs for specific bits of information with unique one-off queries. If it can’t find what it needs, it will keep iterating and changing the queries to get to the next step of its task as quickly as possible.

How to tell if you have a high cardinality workload

So how do you diagnose it? First, you have to know what pg_stat_statements.max is set to. I’m sorry to mention it yet again, but it really is the most essential setting in pg_stat_statements for how effectively you can find the data you need, dependent on your workload.

Second, how close are you to actually hitting that limit? If your statement count is always at the 95% threshold, it’s a pretty sure bet that deallocations have been going on. You could try resetting pg_stat_statements and watching whether it fills back up quickly. I’ve worked with clients that have it set to 5,000 or even 10,000, and when they reset the metrics, the hash table refills in less than 30 minutes. That’s a really good indicator of how large your workload is and how quickly you can use that information before it’s gone again.

Third, is the deallocation counter climbing? When you query pg_stat_statements_info, do you see that number going up every so often, even once or twice a day, let alone once or twice an hour?

Fourth, how often do you search for query text that doesn’t exist in pg_stat_statements? I often hear people ask: “I know I have a query that’s running in my code. Why isn’t it in pg_stat_statements?” The short and simple answer is, “It’s probably getting lost”. If you consistently try to find a statement you know the text of, querying by the first few lines, and you can’t find it, it’s being deallocated more quickly than you can get the metrics for it. That is a high cardinality workload.

And one last test: your top queries. What you consider a top query might change depending on what you’re trying to achieve, but often we start with total execution time, how much total time a query has taken in execution since pg_stat_statements started tracking it. If you order by that and the top 10 changes pretty frequently, while you believe your workload is pretty consistent, then you probably have a high cardinality workload.

Taking action

If you do have a high cardinality workload, it really is time to take action. You can’t just hope this is going to fix itself.

First, go back and watch episode five, where we reviewed all of the settings in pg_stat_statements, the ones you can change on the fly and the ones that require a restart. Next, consider upgrading to Postgres 18, which was one of the suggestions I gave at the end of that episode.

Then there are the things to look at in your ORM and AI tooling. Talk about it with your development teams: help them understand that when a query suggestion comes out of these tools, there are patterns to look for that might be problematic.

And honestly, I suggest you keep notes of when pg_stat_statements seemed to lack the information you needed. Is there any consistency to it? Maybe every Monday morning you go to find queries and you can’t, because big data transformation jobs ran over the weekend and flooded pg_stat_statements, pushing out information from the week before that you wanted for identifying problems.

What happens if you don’t

Ultimately, if you don’t take action, tuning just becomes a lot harder in Postgres. You don’t have all the information you need to find problematic queries. Your history becomes shorter, so even if you think you’ve fixed something, it’s hard to identify whether the fix had the intended outcome. And the optimization decisions you make become a little less trustworthy over time, because you’re never sure they really had the intended impact.

Key takeaways

• A high cardinality workload means your queries outrun your capacity. If your workload consistently generates more unique normalized queries than pg_stat_statements.max can hold, pg_stat_statements can’t retain the metrics you need for tuning.
• The uniqueness usually comes from tooling. ORMs generating per-tenant statements, custom dynamic SQL (especially with track = all), ad hoc reporting, AI-assisted development tools, and IN lists on Postgres 17 and below.
• Postgres 18 makes a measurable difference. The same one hour Bluebox workload produced 671 unique statements on Postgres 17 and 120 on Postgres 18, thanks to IN list normalization.
• Five checks diagnose it. Know your max; watch how fast the table refills after a reset; watch the deallocation counter in pg_stat_statements_info; check whether queries you know are running go missing; and watch whether your top 10 by total execution time churns.
• Settings buy relief, but the cause lives in your application. Revisit the episode 5 settings, consider Postgres 18, and work with your development teams on the query patterns your tools produce.

What’s coming next

In our last episode we’ll talk about a couple of ways to use pg_stat_statements, including some of the queries we recommend, so that you can more effectively find the queries that are most impacting your workload. Please join us next time for episode seven of this deep dive into pg_stat_statements.

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

• pg_stat_statements documentation
• The pg_stat_statements_info view
• Bluebox sample database

Earlier parts in the pg_stat_statements deep dive series:

• Part 1: What it is and what it isn’t
• Part 2: What makes a statement unique
• Part 3: Where Your Query Text Actually Lives
• Part 4: How pg_stat_statements Decides What to Keep
• Part 5: Configuring pg_stat_statements to Reduce Deallocations