Should we enable pgsm_normalized_query or not? — Missing QAN examples on PMM 3.8.1

Uncategorized
1

Hi team,

Following up on this earlier thread: No query examples in PMM QAN with pg_stat_monitor normalized queries enabled

We’re on PMM 3.8.1 monitoring PostgreSQL with pg_stat_monitor, and confirmed the same behavior: with pgsm_normalized_query = on, QAN’s Examples tab shows “Sorry, no examples found for this query” since the normalized query column only stores placeholders ($1, $2) and never the literal values.

This is tracked as PMM-13195, but we need to decide our config going forward, so a few questions:

  1. What’s Percona’s actual recommendation — should we run with pgsm_normalized_query = on or off in production? The install docs recommend on but that silently disables Examples, which seems like an important trade-off to flag.
  2. If we turn it off to get Examples back, are there known downsides beyond the Table/Plan behavior mentioned in the earlier thread — e.g. performance overhead of storing literal query text, memory/storage growth in pg_stat_monitor’s shared buffer, or security/PII exposure from capturing literal values?
  3. Is there a way to get both — normalized aggregation and example queries — short of waiting on pg_stat_monitor PR #350 (the stalled bind_variables column)?
  4. Any ETA or plan for PMM-13195, and would it help if we added our environment details/+1 to that ticket?

Environment:

  • PMM: 3.8.1
  • Database: PostgreSQL 17 version
  • Extension: pg_stat_monitor (via shared_preload_libraries)
  • Current: pgsm_normalized_query = on

Would really appreciate guidance on which setting to run with, since Examples are important for us to debug slow queries but we don’t want to break aggregation or hit hidden costs. Thanks!

2

Can someone help me on this issue?

3

Can someone help me on this issue?

4

Can someone help me on this issue?

5

Can someone help me on this issue?

6

Can someone help me on this issue?

7

Can someone help me on this issue?

8

Can someone help me on this issue?

9

Thanks for the detailed writeup — you’ve found a real gap in our docs. The short answer is that the right setting depends on how your app sends SQL, not on your PMM version.

If your queries arrive with literals inlined (simple protocol — psql, string-built SQL, ORMs not using prepared statements): set pgsm_normalized_query = off. You get Examples back and lose nothing on aggregation, because pmm-agent normalizes the query itself on the client side when the userset parameter is off.

If your queries use server-side prepared statements (JDBC and most drivers with prepared statements): the setting changes nothing. pg_stat_monitor never stores bind values, so the text stays WHERE id = $1 either way, and no configuration fixes that today.

  1. Our recommendation, and the docs bug. The pgsm_normalized_query = 1 line under “Recommended settings” in our install guide never got the caveat it needs. Upstream’s default has been off since pg_stat_monitor 1.1.0 — changed deliberately (PG-362) so examples work out of the box. Our “recommended” value is the one that suppresses them. We’re fixing that.

    A better pattern than the global switch: run pgsm_normalized_query = off and use PMM’s per-service Disable query examples (UI checkbox in 3.5+, or --disable-queryexamples) where query text is sensitive. pmm-agent still normalizes locally and just drops the example, so you keep clean fingerprints and full metrics, decided per service rather than server-wide. It’s a userset parameter, so ALTER SYSTEM SET … = off; SELECT pg_reload_conf(); is enough — no PostgreSQL or pmm-agent restart. Set it globally, though, not per role or database: pmm-agent reads the value from its own session and interprets the stored text accordingly.

  2. Downsides of off — verified on PostgreSQL 17.9 with pg_stat_monitor 2.3:

  • Aggregation: unaffected. The bucket hash key contains no query text; queryid is PostgreSQL’s own parse-tree hash and pgsm_query_id is always computed on the normalized text. Same statement with different literals gives identical IDs in both modes.

  • DB CPU: no change. PGSM normalizes anyway to compute pgsm_query_id, so off neither saves nor costs anything there.

  • Shared buffer: no entry explosion. One text per hash entry per bucket, first execution wins (two literal variants collapsed into one row with calls = 2). Literal text is just somewhat longer than the placeholder form.

  • Truncation is the one real downside. PGSM cuts stored text at pgsm_query_max_len (2048 default — a 2729-char statement came back as exactly 2048). pmm-agent can’t parse truncated SQL, so it falls back to using the truncated literal as the fingerprint. Long statements — big IN lists, multi-row INSERTs — will show literal values instead of a clean fingerprint. With on that can’t happen.

    Independent of all this: keep pgsm_enable_query_plan = off, as our docs advise. That setting — not normalization — is what multiplies entries, since plan text embeds literals and planid is part of the hash key (three literals produced three separate rows in my test). That’s the memory risk you were asking about.

  1. Getting both. For simple-protocol traffic, off already is both. For bind parameters, no — and don’t wait on PR #350: it was closed unmerged in March 2026, with the maintainer noting a bind_variables column adds nothing over pgsm_normalized_query = off.

    On memory, since you asked: unrelated to normalization, but if you use prepared statements heavily, note upstream issues #792 (~4 kB leaked per prepared-statement execution on PG 18.x) and #628 — both fixed on main, not yet in a release. The extension is under active rework including its memory management, but I have no date to give you.

  2. PMM-13195. Straight answer: Open, unassigned, no fixVersion; the “3.X” marker means no committed release. We reproduced it in 2024 and it stalled because the remaining part isn’t fixable in PMM alone — QAN can only show what pg_stat_monitor stores. Your ping on the ticket also went unanswered, which is on us. Please do add your environment, specifically: which driver and whether it uses server-side prepared statements; SELECT name, setting FROM pg_settings WHERE name LIKE 'pg_stat_monitor.%'; and whether Examples appear for some queries after switching to off. That’s what we need to split the ticket into its two real cases. What we can commit to now is the docs fix — the trade-off next to the recommendation, plus a PostgreSQL entry in QAN troubleshooting (today’s “no examples found” guidance is MySQL-only).

Which case are you in?

ALTER SYSTEM SET pg_stat_monitor.pgsm_normalized_query = off;SELECT pg_reload_conf();

-- run your workload for a bucket or two, then:
SELECT calls, left(query, 80) AS query FROM pg_stat_monitor WHERE query LIKE '%$1%' LIMIT 10;

Rows still containing $1 are extended-protocol statements — no examples possible. Rows with literals will appear in QAN’s Examples tab.