Postgres in Production Special Series: Configuring pg_stat_statements to Reduce Deallocations (Part 5)

In Part 5 of this special Postgres in Production deep dive series, Ryan Booz turns to configuration. There are only a handful of settings that control how pg_stat_statements behaves, but they decide how much data you keep and how much you lose. This episode covers how to see when you’re losing data through deallocations, what each setting actually does, and the changes (in settings and in your application) that reduce data loss over time.

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

In the first four episodes we covered what pg_stat_statements is and the metrics it stores (Part 1), what makes a statement “unique” through normalization of the query text (Part 2), and where those query texts actually live on disk (Part 3). In Part 4 we walked through the entire process of storing new incoming metrics.

pg_stat_statements is our main view into what’s happening with completed query executions, and we’ve gotten a lot of value from it. But we also discussed an issue many people aren’t aware of when they analyze this data: deallocations. This episode is about the configuration that controls them.

Why deallocations matter

In order to store all the information coming in, pg_stat_statements sometimes has to remove previously stored data to make room for new information. One of the extension’s settings determines how often and when that happens.

If you aren’t tracking this, you have no idea how much data you could be losing over time. The good news is that the loss is measurable.

Seeing deallocations: the pg_stat_statements_info view

The pg_stat_statements_info view exposes a deallocation counter. In one example server, since the server started (or the stats were last reset), pg_stat_statements had deallocated 46 times. With the default pg_stat_statements.max of 5,000, each deallocation drops 5% of the table, or 250 rows. So 250 rows of metrics had been dropped 46 times since that server began.

If you aren’t tracking it yourself, your monitoring tool might be. In pganalyze we show this to you over a 10-minute window, with a counter for each server. In one example, over a 10-minute window a server (again at the default max of 5,000) deallocated twice, which means roughly every five minutes 250 rows of metrics were dropped to make room for new ones.

Sampling the deallocation counter (the 10-minute window)

Taking a sample of the deallocation counter is easy, and something you should do periodically. You query pg_stat_statements_info and read the deallocation counter. A 10-minute window is a good timeframe because it makes the per-second math easy to reason about.

If you query the view, wait 10 minutes, query it again, and the counter hasn’t moved, you’re in a good state. Most reasonable monitoring tools query pg_stat_statements at least once every 10 minutes, so you’re likely retaining as much data as you can.

What the deallocation numbers mean

Here’s how to read the counter over a 10-minute window. If it’s gone up by about 10, that means roughly every minute you’re losing 5% of the rows in the hash table. That’s a little concerning, especially if your monitoring tool isn’t querying often enough to catch the data before it’s dropped. Once you reach about 100 deallocations per 10 minutes, you’re losing 5% of your rows every six seconds or so, which is a lot of contention and really concerning. And believe it or not, I’ve seen numbers at 600 and above. At that point pg_stat_statements is trying to deallocate enough rows roughly every second to make room for new information, which is a lot of locking and effort on your metrics table, and you’re very likely losing information that would help you tune your database.

The configuration settings

The settings below come straight out of the Postgres documentation, and you can read about them there yourself.

pg_stat_statements.max

We’ve talked about this one in every episode, so I’ll keep it short, with two things worth repeating. The first is that a restart is required: if you decide to raise this value because you have a lot of deallocations, you have to plan downtime for it, and there’s no workaround, which is a real concern if you’re already having a problem. The second is that it reserves memory even if tracking is off. As long as the extension is loaded, it reserves enough shared memory for the maximum number of rows, because the schema is fixed and it knows how much it needs. That’s usually not much, a few to tens of megabytes, but it is reserved.

pg_stat_statements.track

This one can be changed with a configuration reload, and it has three options. By default it’s set to top, which tracks only top-level queries. Setting it to all also tracks queries inside functions and stored procedures, which will likely increase the number of rows, and remember there’s no correlation between those non-top-level queries and the top-level function that called them. Setting it to none turns off tracking while leaving the extension loaded. That last option is a handy shortcut: if pg_stat_statements is causing locking issues because of churn, you can reload with none to stop tracking for a while and get relief.

track_utility

This can also be changed with a reload, no restart required. It’s on by default, which means pg_stat_statements tracks essentially all statements, including utility statements like PREPARE, commits, and rollbacks. Those individual statements take up space in the table.

If your priority is tracking DML operations (SELECT, INSERT, UPDATE, DELETE, MERGE), you can turn track_utility off and reload to get instant relief, often saving a meaningful number of rows.

track_planning

This can be changed with a reload. It adds planning values to your statements. The default is off, because it incurs additional overhead from the timing work during the planning phase.

save

Like max, this one can only be changed with a restart. It saves the statistics to a file on a normal shutdown so they can be reloaded when the server restarts. The default is on, and that’s almost certainly what you want: after a restart, pg_stat_statements picks up where it left off instead of rebuilding all the statistics over time.

Reducing deallocations through settings

If you’re seeing a lot of deallocations, there are two settings-based levers. The first is to raise pg_stat_statements.max. We often recommend starting at 10,000 rather than the default 5,000, which is usually a good fit for one primary server with one application database; the more databases you have, the fewer slots there are per database, so the setting may need to go higher. Use the 10-minute window to gauge how often deallocations happen relative to your setting, and remember each one drops 5% of max. Once you think you need beyond roughly 50,000 to 100,000 entries, you likely need other mitigation too. The second lever is to turn off track_utility, a good, cheap way to reduce the number of entries while keeping the application-query data you actually want for tuning.

Reducing deallocations in your application

The fix for deallocations is almost never just tuning these values; it’s also about your application. The first thing to look at is query uniqueness, and the biggest culprit is WHERE IN clauses: on Postgres 17 and below, many IN lists of different lengths produce many individual entries in pg_stat_statements, so consider switching to an array value (your ORM may have a helper for this), and watch for queries built through string concatenation, which is another source of unnecessary uniqueness. Second, you can reduce the number of databases per server, since pg_stat_statements.max is server-wide and lots of activity across many databases means they all contend for the same slots; at some point you may need to break off into more servers with fewer databases. And finally, avoid querying with multiple roles unless necessary, since the same SQL statement executed by different roles produces separate entries in pg_stat_statements.

Key takeaways

• Deallocations are measurable. The deallocation counter in pg_stat_statements_info tells you how often pg_stat_statements is dropping 5% of the table to make room for new entries.
• Use a 10-minute sampling window. No change is healthy; ~10 per window means losing 5% per minute; ~100 means every six seconds; 600+ means roughly every second, with serious locking and data loss.
• Know the five settings. max (restart, reserves memory even when tracking is off), track (top/all/none, reload), track_utility (on by default, turn off to drop utility statements), track_planning (off by default, adds overhead), and save (restart, default on, persists stats across restarts).
• Settings buy relief, the application fixes the cause. Raising max to 10,000 and turning off track_utility help, but reducing query uniqueness (WHERE IN lists, concatenation), fewer databases per server, and avoiding redundant roles address the root cause.

What’s coming next

In the next episode we’ll dig into high-cardinality workloads specifically, and look at a couple of concrete ways to minimize deallocations and the loss of data over time. We’re getting close to finishing this series on pg_stat_statements, so I hope to see you back for it.

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
• pg_stat_statements configuration parameters
• The pg_stat_statements_info view
• 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