Query-level PostgreSQL evidence with OpenTelemetry: a privilege decision, not a config flag

Stages 1 and 2 of part one give you useful views of the same incident sitting next to each other: what the PostgreSQL server was doing, and how long the call took. Neither can name the query shape that got slower, and neither can tell you that a statement was stuck behind another transaction.

The evidence for both exists inside PostgreSQL, and the OpenTelemetry Collector’s PostgreSQL receiver can collect it but it has some potential impact on privileges and some query text leaving the database.

Version and prerequisites As in part one: OpenTelemetry Collector Contrib v0.158.0 (released 2026-08-04).

Stage 3: query samples, blocking evidence, and a bridge you have to build

Query samples add server-side evidence about individual long-running sessions. Trace correlation can connect that evidence to an application trace, but that correlation is advanced and requires custom work.

What a query sample is, and what it is not

The receiver emits query samples from pg_stat_activity as logs. A query sample is a snapshot of a PostgreSQL backend that happened to be running when the receiver scraped it. Rather than describe the shape, here is one, copied out of the sandbox with nothing removed:

db.system.name              = postgresql
db.namespace                = app
db.query.text               = SELECT id, customer, status, amount FROM orders WHERE id = ?
user.name                   = app
postgresql.state            = idle
postgresql.pid              = 417
postgresql.application_name =
network.peer.address        = 172.20.0.3/32
network.peer.port           = 45422
postgresql.client_hostname  =
postgresql.query_start      = 2026-08-11 06:28:00.739932+00
postgresql.wait_event       = ClientRead
postgresql.wait_event_type  = Client
postgresql.query_id         =
postgresql.total_exec_time  = 0.022

Read that as an answer to “what was backend 417 doing when I looked”. It ran a 0.022 ms indexed read, it is now idle, and the wait event says it is in ClientRead, waiting on the application to send the next thing, not on the database. Four details in that record are worth flagging before the interesting cases:

• The literal is gone. The receiver replaces inline literals: a hand-typed WHERE id = 1 arrives as WHERE id = ?. Values are obfuscated in sampled text. Table and column names are not.
• postgresql.query_id is empty here. When it is empty you have no key to join this sample to the workload statistics in stage 4. You get the text, not the identity.
• postgresql.application_name is empty because nothing set it. That field is the hook the correlation bridge later hijacks.
• A sample is not evidence of slowness. Most of what you collect looks like this: fast, idle, uninteresting. The value is in the minority of records below.

Sampling

These are samples, not a query history. pg_stat_activity shows what PostgreSQL backends are doing right now, and the receiver only looks at it on each collection interval. A 5 ms query that starts and finishes between two scrapes will never be seen.

That skew toward whatever is running when the receiver looks is useful for investigating slow queries and blocking, and it means query samples can never tell you everything PostgreSQL executed. Stage 4 complements them with pg_stat_statements, which aggregates query activity over time and can reveal a fast query that is expensive simply because it runs millions of times.

For long-running query be mindful of sampling, they may still be missed. Duration improves your odds but does not guarantee capture. If a statement matters enough that you need certainty it was counted, that is stage 4’s job, but query samples will never do it.

The grant this asks for

The receiver documentation for v0.158.0 specifies pg_monitor for query sampling. Treat that grant as part of the rollout. I ran my monitoring role with more privilege than that, so I cannot tell you if it can be run with less permissions. If a narrower grant matters to your database owner, test it and let me know so I can update this part.

Turning it on, and what it costs

Query samples are off by default and arrive on the logs pipeline from stage 1, not the metrics one:

receivers:
  postgresql:
    # ... the stage 1 settings, unchanged
    events:
      db.server.query_sample:
        enabled: true             # off by default
    query_sample_collection:
      max_rows_per_query: 100     # default 1000, per collection

Side note: PostgreSQL may truncate long queries before the receiver sees them. pg_stat_activity.query is limited by track_activity_query_size, which defaults to 1024 bytes. You can increase the limit, but doing so requires a PostgreSQL restart, uses more memory, and allows more query text to reach your telemetry backend. Increase it only if truncated queries are actually limiting your investigations.

The part that answers “is it blocked?”

Since v0.156.0, query samples can carry blocking and lock evidence. This is the evidence that lets you distinguish a query that is slow because it is doing work from one that is slow because another transaction is preventing it from making progress.

The generated schema documents attributes including:

Attribute	What it gives you	Value in a real row-lock wait
postgresql.blocking.pids	PostgreSQL process IDs reported as blocking the sampled session	{713}
postgresql.blocking.wait_duration	How long the sampled session has been waiting on the lock	25
postgresql.blocking.start_time	When the lock wait began	2026-08-11T06:30:24Z
postgresql.blocking.lock.mode and .lock.type	The requested lock mode and lockable-object type	ShareLock, transactionid
postgresql.blocking.lock.relation	The relation associated with the wait	empty
postgresql.blocking.transaction.start_time	When the blocked transaction started	2026-08-11T06:30:24Z

That right-hand column comes from one captured sample.

What a blocked query looks like

Here is the whole record, a plain UPDATE contending for a row another transaction was holding:

db.system.name                             = postgresql
db.namespace                               = app
db.query.text                              = UPDATE orders SET status = ? WHERE id = ?
user.name                                  = app
postgresql.state                           = active
postgresql.pid                             = 727
postgresql.wait_event                      = transactionid
postgresql.wait_event_type                 = Lock
postgresql.total_exec_time                 = 4788.563
postgresql.blocking.pids                   = {713}
postgresql.blocking.wait_duration          = 5
postgresql.blocking.start_time             = 2026-08-11T06:30:24Z
postgresql.blocking.lock.mode              = ShareLock
postgresql.blocking.lock.type              = transactionid
postgresql.blocking.lock.relation          =
postgresql.blocking.transaction.start_time = 2026-08-11T06:30:24Z

Compare that with the idle sample above and the diagnosis reads almost directly from the record. PID 727 is active, but its wait_event_type is Lock: PostgreSQL considers the backend active, but it cannot make progress. It has been waiting five seconds on a transactionid lock, and PID 713 is blocking it.

Its total_exec_time is about 4.8 seconds, almost all of which is lock wait rather than useful query work. From the application’s side this can simply look like a slow database call. The query sample is what tells you that the statement itself is not necessarily slow: it is stuck behind another transaction.

Read the blocking fields together

Three fields are particularly useful together.

postgresql.blocking.pids tells you who is blocking the sampled backend. The value uses PostgreSQL’s array syntax: {713} means PID 713 is blocking it, while {713,721} would mean two backends are blocking it. This is not JSON, so downstream parsing should not expect [713,721].

postgresql.wait_event_type and postgresql.wait_event tell you what the backend is waiting for. Here, Lock and transactionid mean that the statement is waiting for another transaction to finish.

Finally, postgresql.blocking.lock.relation can tell you which relation is involved, but only when the lock PostgreSQL reports is associated with a relation. That is why it is empty in this example. During ordinary row contention PostgreSQL commonly reports a wait on the other transaction’s ID, and a transaction ID has no relation attached to it.

The query text fills that gap:

db.query.text = UPDATE orders SET status = ? WHERE id = ?

So the useful reading of this sample is: PID 727 is waiting for a transaction held by PID 713 to finish, and the blocked statement is operating on orders****.

Filter on the value, not the presence of the attribute

That means filtering for the existence of postgresql.blocking.pids does not find blocked queries; it finds every query sample. Filter on a non-empty value instead:

postgresql.blocking.pids != '{}'

Or in Tsuga:

postgresql.blocking.pids:*

This is also why downstream parsing needs to understand the representation. blocking.pids is a PostgreSQL array literal such as {713}, not a JSON array or a comma-separated list.

Sampling still applies to lock waits

Blocking evidence does not turn query sampling into an event stream. The receiver still sees only what exists in pg_stat_activity when it scrapes.

Wait-start information requires PostgreSQL 14 or later; I did not test older versions because the sandbox ran PostgreSQL 16.4 throughout. Short lock waits can also begin and end between two receiver scrapes and never appear in the telemetry.

The longer a query remains blocked, the more opportunities the receiver has to observe it, but capture is never guaranteed.

The receiver also samples itself

There is another source of noise that was bigger than I expected: the receiver’s own connections appear in pg_stat_activity, so they can become query samples too.

On my sandbox, with two receiver instances scraping every ten seconds and one application generating traffic, 85 sampled sessions belonged to the monitoring roles and only 10 belonged to the application. Roughly nine in ten samples were the observer watching itself.

Filter the monitoring role out before putting query samples on a dashboard. A busier application will change that ratio, but it does not remove the underlying problem.

Useful without trace correlation

None of this requires the trace-correlation bridge in the next section.

A query sample can already tell you that a slow-looking statement was waiting rather than executing, what kind of wait it encountered, and which PostgreSQL backend was blocking it. That is enough to answer an important incident question: is this query slow, or is something else preventing it from running?

There is still a trade-off. Query samples require additional privileges, produce more logs, and send query information to your telemetry backend. The receiver masks literal values with ?, so values written directly in the sampled query are not included. The query structure, including table and column names, remains visible.

In other words, the values are masked, but the structure of your database is not.

Optional: correlate a query sample with a trace

Query samples can tell you what PostgreSQL was doing, but by default they cannot tell you which application request caused it.

The receiver has an unusual mechanism for bridging that gap: if the PostgreSQL connection’s application_name contains a valid W3C traceparent, the receiver can extract its trace and span IDs and attach them to the query-sample log.

This is not automatic instrumentation. Your application has to put the current trace context into application_name before running the query.

How the bridge works

A W3C traceparent looks like this:

00-7a54b0006244f5fa047ef6384e28ac50-9f115c95e55c806b-01

Its four parts are:

version - trace ID                         - span ID          - flags
00      - 7a54b0006244f5fa047ef6384e28ac50 - 9f115c95e55c806b - 01

A version-00 traceparent is 55 characters, so it fits inside PostgreSQL’s 63-byte application_name limit.

The application does roughly this:

borrow connection
      |
      v
get current trace + span IDs
      |
      v
SET application_name = '<traceparent>'
      |
      v
run database work
      |
      v
RESET application_name
      |
      v
return connection to pool

If the receiver samples that PostgreSQL backend while the query is running, it can turn the traceparent back into OpenTelemetry trace context.

I tested that on the sandbox and received:

traceId                     = 7a54b0006244f5fa047ef6384e28ac50
spanId                      = 9f115c95e55c806b
db.system.name              = postgresql
db.namespace                = app
db.query.text               = SELECT pg_sleep ( ? )
user.name                   = app
postgresql.state            = active
postgresql.pid              = 809
postgresql.application_name = 00-7a54b0006244f5fa047ef6384e28ac50-9f115c95e55c806b-01
postgresql.wait_event       = PgSleep
postgresql.wait_event_type  = Timeout
postgresql.query_id         =
postgresql.total_exec_time  = 40.5

The important result is at the top: traceId and spanId are now fields on the query-sample log record. Your backend can use them to navigate from this PostgreSQL evidence back to the application trace.

postgresql.application_name remains in the sample too, so you can see both the mechanism and the context the receiver extracted from it.

Node: set it on the borrowed

With pg, do this on a client borrowed from the pool, not on the pool as a whole:

import { context, trace } from '@opentelemetry/api';
import pg from 'pg';

const { Pool } = pg;
const pool = new Pool();

function currentTraceparent() {
  const span = trace.getSpan(context.active());
  if (!span) return null;

  const ctx = span.spanContext();
  if (!ctx.traceId || !ctx.spanId) return null;

  const flags = (ctx.traceFlags & 0xff)
    .toString(16)
    .padStart(2, '0');

  return `00-${ctx.traceId}-${ctx.spanId}-${flags}`;
}

async function queryWithTrace(sql, params = []) {
  const client = await pool.connect();
  const traceparent = currentTraceparent();

  try {
    if (traceparent) {
      await client.query(
        `SELECT set_config('application_name', $1, false)`,
        [traceparent],
      );
    }

    return await client.query(sql, params);
  } finally {
    try {
      await client.query('RESET application_name');
    } finally {
      client.release();
    }
  }
}

The important part is the ownership boundary:

const client = await pool.connect();

try {
  // set trace context
  // use this connection
} finally {
  // clear trace context
  client.release();
}

The trace context belongs to the borrowed PostgreSQL session, so it must be removed before that session goes back into the pool.

Python: the same pattern with Psycopg

The Python version is conceptually identical. OpenTelemetry exposes the current span with trace.get_current_span(), from which you can obtain its span context.

For a Psycopg 3 connection:

from opentelemetry import trace

def current_traceparent():
    span = trace.get_current_span()
    ctx = span.get_span_context()

    if not ctx.is_valid:
        return None

    trace_id = f"{ctx.trace_id:032x}"
    span_id = f"{ctx.span_id:016x}"
    flags = f"{int(ctx.trace_flags):02x}"

    return f"00-{trace_id}-{span_id}-{flags}"

def query_with_trace(conn, sql, params=None):
    traceparent = current_traceparent()

    try:
        if traceparent:
            conn.execute(
                "SELECT set_config('application_name', %s, false)",
                (traceparent,),
            )

        return conn.execute(sql, params or ())
    finally:
        conn.execute("RESET application_name")

With a connection pool, the same rule applies: the try/finally must surround the whole period during which that physical connection belongs to the request.

For example, schematically:

with pool.connection() as conn:
    traceparent = current_traceparent()

    try:
        if traceparent:
            conn.execute(
                "SELECT set_config('application_name', %s, false)",
                (traceparent,),
            )

        result = conn.execute(
            "SELECT id, status FROM orders WHERE id = %s",
            (order_id,),
        ).fetchone()

    finally:
        conn.execute("RESET application_name")

Do not hide the reset in unrelated request middleware unless that middleware also owns the PostgreSQL connection. The thing that sets session state should be responsible for clearing it.

Transactions make cleanup more subtle

The examples above show the mechanism, but production code needs to account for transaction handling too.

If the application starts a transaction and a statement fails, PostgreSQL can leave that transaction in an aborted state. A subsequent:

RESET application_name;

may then fail until the application rolls the transaction back.

So the safe ordering for explicitly managed transactions is:

borrow connection
set application_name

try
    BEGIN
    database work
    COMMIT
catch
    ROLLBACK
finally
    RESET application_name
    return connection

The exact implementation depends on the driver and pool you use.

Never return a pooled connection while it might still contain another request’s traceparent.

If your pool has its own reset or cleanup hook, that is a useful second line of defence, but I would still clear the value explicitly at the same layer that set it.

What goes wrong if you forget the reset

This is the dangerous part.

application_name belongs to the PostgreSQL session, not to an HTTP request, span, or query. A connection pool keeps that session alive and gives it to another request later.

I deliberately skipped the reset in the sandbox and then sent six unrelated requests through the pool:

 pid | state |                     application_name                    | last_query
-----+-------+---------------------------------------------------------+---------------------
 869 | idle  | 00-509e98b0953fcb9a2e5c55d78f13f268-004a495208fe1def-01 | SELECT id, customer…
 877 | idle  | (empty)                                                 | SELECT id, customer…

PID 869 still carries the traceparent from an earlier request even though its latest query belongs to somebody else.

If the receiver samples the connection now, it can associate that query with the wrong trace.

That is worse than having no correlation at all. Missing evidence is visible as a gap; incorrect correlation looks authoritative.

What correlation gives you, and what it does not

The bridge answers one narrow question:

Which application trace was using this PostgreSQL session when this sample was captured?

It does not suddenly turn the query sample into complete query diagnostics.

In the captured record above:

traceId             = 7a54...
spanId              = 9f11...
postgresql.query_id =

The trace fields let you correlate upward to the application request.

But postgresql.query_id is empty, so this particular sample cannot correlate sideways to the workload-level pg_stat_statements evidence from stage 4.

You also give up the normal meaning of application_name

There is another trade-off that is easy to miss.

application_name is normally useful precisely because it contains a stable human-readable identity such as:

orders-api
billing-worker
nightly-reconciliation

Replacing that with:

00-7a54b0006244f5fa047ef6384e28ac50-9f115c95e55c806b-01

means PostgreSQL operators no longer get that service or job name from the field while the traceparent is installed.

Recommendation: Start without it

Query samples do not require trace correlation to be useful.

Without this bridge they can still tell you:

• which query was sampled,
• whether it was waiting,
• what kind of wait it encountered,
• which backend was blocking it,
• and how long that wait had lasted.

That is where I would start.

Add the application_name bridge only when real incidents show that the remaining question “which exact application request did this sample belong to?” is valuable enough to justify custom application code, loss of the normal application_name, and the risk of incorrect attribution if pool cleanup ever fails.

Stage 4: top queries and plans

Everything so far explains evidence around one slow request: how long it took, whether it queued, what the server was doing, and whether it sat behind a lock. Stage 4 changes the unit of analysis from a request to a workload, which is how you find out what to fix rather than only what happened.

What top queries and plans answer

A span describes one execution. pg_stat_statements groups executions by normalised query shape and keeps call counts, total time and rows against each shape:

• Which query shape costs the most total time? The shape that consumes the database may be individually fast but extremely frequent.
• Did this shape get slower, or is it just running more often? Those are different problems with different fixes.
• Is the statement in my slow span the cause, or a bystander? The workload saturating the server may belong to another service.
• Did the fix work? After the change ships, the workload-level cost should move.

Plans answer the next question down: why is this shape slow. A sequential scan where you expected an index, a row estimate off by orders of magnitude, or a join order that changed as the data grew.

Three levels of query analysis

Those two questions are answered at three different depths:

1. Top queries. pg_stat_statements tells you which query patterns consume the most time, how often they run, and how many rows they process. No query plan is involved.
2. Receiver-generated plans. The receiver can collect plans for top queries. This adds work to the database, so control it with limits such as top_n_query and max_explain_each_interval. It does not execute the original queries the way EXPLAIN ANALYZE does.
3. Manual plans. When you need to understand why a specific query is slow, an operator runs EXPLAIN or EXPLAIN ANALYZE deliberately.

The difference between those last two commands matters: EXPLAIN estimates how PostgreSQL would execute the query, while EXPLAIN ANALYZE actually runs it and measures what happened.

Server setup: the module and its settings

Top-query collection needs the pg_stat_statements module loaded through shared_preload_libraries (require server restart).

# postgresql.conf. The first line is why this needs a restart
shared_preload_libraries = 'pg_stat_statements'

# Optional, and each one has a price. Defaults in comments.
pg_stat_statements.max = 10000     # 5000. Shapes kept before the least-used are evicted
pg_stat_statements.track = top     # top. 'all' also tracks statements nested in functions
pg_stat_statements.track_utility = off  # on. Off drops VACUUM, SET, DDL and friends
track_io_timing = on               # off. Adds block read/write timing; measure the overhead
compute_query_id = auto            # auto. Already on when pg_stat_statements asks for it

compute_query_id is worth understanding rather than copying: at auto the identifier is computed because pg_stat_statements requests it, which is how postgresql.queryid reaches your telemetry. Set it to on only if something else needs query IDs when that module is not loaded.

Where to create the extension

pg_stat_statements collects statistics across the PostgreSQL cluster, but its view is created separately in each database.

The receiver reads top-query data through its own connection, which uses the postgres database by default. That means the extension must exist there:

-- In the database the receiver connects to, which is `postgres` by default.
-- Not once per entry in the receiver's `databases:` list.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The receiver’s databases: setting does not change this. It controls which databases are included in the collected telemetry, not where the receiver queries pg_stat_statements.

I verified this on Collector v0.158.0 with PostgreSQL 16.4: creating the extension only in the application database caused top-query collection to fail, while creating it only in postgres worked.

You only need the extension in an application database as well if you want to query pg_stat_statements directly while connected to that database.

The grant, and its silent failure

The failure mode here is worth knowing because nothing reports it. Once the extension is installed in a database, an ordinary user can already see the statistics. What they cannot see is the identity: PostgreSQL documents that “only superusers and roles with privileges of the pg_read_all_stats role are allowed to see the SQL text and queryid of queries executed by other users”. So a monitoring account without it gets rows of timings it cannot attribute to any query: a populated view that answers nothing. That is the privilege stage 4 actually asks for:

-- Without this the receiver collects timings with no query text and no queryid
GRANT pg_read_all_stats TO otel_monitor;

Verify as the monitoring role, in the database the receiver connects to, before the Collector does it for you. A missing extension errors; a missing privilege returns rows with the identity stripped, so check for both in one query:

psql "host=postgres.internal user=otel_monitor dbname=postgres" -Atc \
  "SELECT count(*), count(queryid) FROM pg_stat_statements" \
  || echo "extension missing, fix before rollout"

On the sandbox I used for this article the two numbers were 156 and 0 without the grant, and 157 and 157 with it.

The receiver config

Top queries are a second opt-in event, with their own collection interval and their own limits. The defaults are generous, which is the argument for setting them explicitly:

receivers:
  postgresql:
    # ... the stage 1 settings, unchanged
    databases: [orders, billing]
    events:
      db.server.query_sample:
        enabled: true
      db.server.top_query:
        enabled: true               # off by default
    top_query_collection:
      collection_interval: 60s      # default 60s, independent of metric scraping
      top_n_query: 100              # default 200, ranked per interval
      max_rows_per_query: 100       # default 1000
      max_explain_each_interval: 50 # default 1000, the plan-generation budget
      query_plan_cache_size: 1000   # default 1000
      query_plan_cache_ttl: 1h      # default 1h

What this exposes

That pg_read_all_stats grant is what makes other users’ SQL readable, so on a shared cluster it is also what lets another team’s query text leave PostgreSQL and land in your telemetry backend. The grant and the data-exposure decision are the same decision.

Custom SQL is a separate collection layer

The PostgreSQL receiver leaves some questions deliberately unanswered. It does not estimate table bloat, report maintenance age per table, follow a running vacuum, count idle-in-transaction sessions, or inventory WAL files and replication slots.

Tsuga’s Helm example adds those answers with an otel schema, otel.* SQL functions, and sqlquery receivers that call them. It emits the results as metrics beside the native receiver data. It collects: lock and connection queries run every 30 seconds, vacuum and WAL checks every minute, table-maintenance checks every five minutes, and bloat once an hour. The SQL also caps its output: 50 table-stat rows, 20 bloat candidates, and 20 query shapes.

That is a custom collection layer, not more receiver configuration. Each function becomes a permanent query against your database, with its own cost, failure mode, and permission requirement. Add one only when you can make sure it can help you troubleshoot and don’t forget to then measure the load.

The privilege setup deserves the same scrutiny. This setup grants otel_monitor the broader pg_monitor role, and grants it EXECUTE on the functions. Make sure adapt the privilege to your needs.

The queries an operator actually runs

The receiver ships the workload view to your backend on an interval. During an incident you often want the same view from a psql prompt, right now, and it is the same three questions in order.

Which shape is expensive? Total time, not mean time, because the shape that consumes the database is frequently an individually fast one:

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 100) AS shape
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 10;

Two rows of real output from that query, taken mid-load on the sandbox, are the whole argument for stage 4 in one screenful:

       queryid        | calls | total_ms | mean_ms | rows |  shape
----------------------+-------+----------+---------+------+------------------------------
 -6257661822689736760 |   501 |  65896.4 |  131.53 | 5010 | SELECT o1.customer, count(*)…
 -4221548598437380883 |  1352 |     42.5 |    0.03 | 1352 | SELECT id, customer, status,…

The second shape ran nearly three times as often and cost forty-two milliseconds in total. The first cost sixty-six seconds. A trace showing you either one in isolation cannot tell you that.

PostgreSQL 13 is where planning time became trackable separately from execution time, and where these columns took their current names; on 12 and earlier you are looking for total_time. The dbid filter matters on a shared cluster: the module tracks statistics across every database on the server, so without it you are ranking other databases’ work alongside your own.

Did it get slower, or is it just running more often? These counters are cumulative since the last reset, so a single snapshot cannot answer that. Take two, a few minutes apart, and subtract per queryid: if calls grew and total_exec_time / calls held steady, the shape did not get slower; something upstream started calling it more.

There is a reset function, and it is a trap in this context:

SELECT pg_stat_statements_reset();  -- discards all statistics, for everyone

It is superuser-only by default, which is a small mercy. The receiver reports incremental values from this view, so a manual reset lands in its output as an artefact, and every other tool reading the same shared statistics loses its baseline at the same moment. Diff two snapshots instead. The narrow form, pg_stat_statements_reset(userid, dbid, queryid), exists if you genuinely need to clear one shape.

Why is this shape slow? The statement in pg_stat_statements is normalised, so it carries $1 placeholders and will not plan directly. PostgreSQL 16 added the option built for exactly that: GENERIC_PLAN, which allows the placeholders and produces a plan that does not depend on their values:

-- PostgreSQL 16+: plan the shape as stored, placeholders and all
EXPLAIN (GENERIC_PLAN, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = $1 AND status = $2;

It cannot be combined with ANALYZE, so what you get is the planner’s intent, not a measurement. Before 16 you substitute representative values yourself, and then the plan you get back is the plan for those values, which is sometimes the answer and sometimes the reason you were confused.

And if you do reach for EXPLAIN ANALYZE on anything that writes, PostgreSQL’s own documentation gives the wrapper:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 12345;
ROLLBACK;

If plan access has to be handed to an operator or a tool without granting broad SELECT on the data, the pattern is a SECURITY DEFINER function that only ever plans. Datadog’s agent uses one, and the shape is worth borrowing:

CREATE SCHEMA IF NOT EXISTS otel_diag;

CREATE OR REPLACE FUNCTION otel_diag.explain_statement(l_query text, OUT explain json)
RETURNS SETOF json AS $$
DECLARE
  curs REFCURSOR;
  plan json;
BEGIN
  SET TRANSACTION READ ONLY;
  OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
  FETCH curs INTO plan;
  CLOSE curs;
  RETURN QUERY SELECT plan;
END;
$$ LANGUAGE plpgsql RETURNS NULL ON NULL INPUT SECURITY DEFINER;

-- PostgreSQL grants EXECUTE to PUBLIC by default. Undo that.
REVOKE EXECUTE ON FUNCTION otel_diag.explain_statement(text) FROM PUBLIC;
GRANT USAGE ON SCHEMA otel_diag TO otel_monitor;
GRANT EXECUTE ON FUNCTION otel_diag.explain_statement(text) TO otel_monitor;

Be careful with this function. Because it uses SECURITY DEFINER, it runs EXPLAIN with the function owner’s permissions rather than the caller’s.

EXPLAIN without ANALYZE does not execute the query, so the main risk is not modifying data. The risk is information exposure: anyone allowed to call the function can see plans for tables the function owner can access.

The example limits access in two places:

REVOKE EXECUTE ON FUNCTION otel_diag.explain_statement(text) FROM PUBLIC;
GRANT USAGE ON SCHEMA otel_diag TO otel_monitor;
GRANT EXECUTE ON FUNCTION otel_diag.explain_statement(text) TO otel_monitor;

Keeping the function in its own schema is important. In my test, an unauthorized role was rejected at the schema boundary first with permission denied for schema otel_diag. If you put the function in public, you lose that extra boundary and have to rely on the function’s EXECUTE permissions alone.

If you do not want to expose this capability to the monitoring role, skip the function and keep EXPLAIN as a manual operator step.

The receiver also emits postgresql.queryid, which is useful for grouping observations of the same query shape. However, PostgreSQL does not guarantee that a queryid will remain stable forever, particularly across major PostgreSQL versions.

Six things to agree with the database owner before stage 4 goes on:

• Grants. Which roles this adds, on which cluster, and who signed off.
• Query text. Whether SQL text and schema names may leave the database, and who can read them downstream.
• Destination and retention. Where these logs land and for how long.
• Limits. Row, plan and cache limits set explicitly rather than left at defaults.
• Load. Measured on a representative workload, not on an idle instance.
• Off switch. A named owner who can turn it off, and a rollback somebody has run once.

If an item has no answer yet, stage 4 waits. The SQL Query receiver is not the shortcut around those decisions either: it runs whatever SQL you give it on an interval, making that query a permanent part of the database’s load.

The rollout order, continued

Part one’s steps 1 and 2 (receiver metrics, then client spans and pool metrics) come first, and the numbering carries on from them.

1. Query samples, if their diagnostic value justifies the documented privilege, query-text exposure, and logs ingestion. Use them for blocking and wait evidence even if you never add trace correlation.
2. Correlation, tested in a pool, as an optional custom engineering project. Prove isolation between borrowers and measure its cost. Skip it for workloads where the trade is poor.
3. Top queries after the six agreements are settled, one database at a time. The shared_preload_libraries restart comes first, then the extension and the pg_read_all_stats grant verified as the monitoring role, then the event enabled with top_n_query and max_explain_each_interval set explicitly in the same change.
4. Manual plans on request, inside an incident procedure, with the EXPLAIN mode chosen by the operator.

Each step is useful without requiring the next one. That is what makes the rollout safe to stop in the middle, including stopping at the end of part one.

If you only remember one thing from part two

Every capability in this post is a yes to something other than a config flag.

• Query samples buy you blocking evidence for the price of a documented privilege and query text in your telemetry backend. That is frequently a good trade, and the one I would reach for first.
• The trace-correlation bridge buys request-level attribution for the price of a custom application change you have to test in a real pool.
• Top queries buy workload-level analysis for the price of a server restart, pg_read_all_stats, and plan work the receiver causes.

Take the six agreements to the database owner before the change.

And the boundary worth keeping: query samples answer what was this backend waiting on, top queries answer which shape costs the most.