Linking Slow Queries to Traces with SQLCommenter

This guide is part of OpenTelemetry Connection Pool Tracing. A slow statement in pg_stat_activity or the slow query log is anonymous: it shows the SQL, the duration and the backend, but not which request issued it, which endpoint, or which trace to open. SQLCommenter closes that gap by appending key–value metadata to the statement as a SQL comment, including the W3C traceparent:

SELECT id, total FROM orders WHERE customer_id = $1
/*traceparent='00-4bf92f3577b34da6a3ce929d0e0e4736-00f067aa0ba902b7-01',
  controller='OrdersController',action='list',db_driver='psycopg',framework='django'*/

The benefit is real and so is the cost: comments change the statement text, which affects prepared statement reuse and statement statistics aggregation. The objective is to enable it where the link is worth having, with the settings that limit the damage, and to know when the cost is too high.

Rapid incident diagnosis

Check whether comments are already being added. They are visible in the database’s view of running statements:

SELECT pid, left(query, 200) FROM pg_stat_activity
WHERE query LIKE '%traceparent%' LIMIT 5;

Measure the effect on statement statistics. pg_stat_statements normalises literals but not comments, so a distinct comment per request can create a distinct entry per request:

SELECT count(*) AS distinct_statements FROM pg_stat_statements;
SHOW pg_stat_statements.max;

A count at the configured maximum, with many near-identical entries, means the view is being churned and its usefulness is gone.

Check prepared statement reuse. Drivers key their statement cache on the SQL text, so a per-request comment defeats it:

-- PostgreSQL: statements prepared by the current session
SELECT count(*) FROM pg_prepared_statements;

On a driver with automatic preparation, a cache that never hits shows up as parse and plan time in pg_stat_statements and as more CPU on the database.

Decide whether the comment is per request or per shape. Some implementations include only static metadata — framework, controller, action — which keeps the text stable across requests; adding traceparent makes it unique per request. The choice determines every cost on this page.

Mathematical sizing or parameter formula

Statement cache impact. A unique comment per execution makes every statement text unique:

distinct_texts_with_traceparent ≈ executions
distinct_texts_static_only      ≈ distinct_shapes × distinct_controllers

A service issuing 40 distinct shapes from 20 controllers produces up to 800 distinct texts with static metadata — manageable — against millions with a per-request traceparent.

pg_stat_statements capacity. The view holds pg_stat_statements.max entries, evicting the least used:

churn_rate ≈ executions_per_second ÷ pg_stat_statements.max

At 2,000 executions per second and a maximum of 5,000, the whole view turns over every 2.5 seconds, which destroys its value for finding expensive statements.

Parse and plan cost. Without statement reuse, every execution is parsed and planned:

added_cpu ≈ executions_per_second × parse_plan_time

At 2,000 executions per second and 0.3 ms of parse and plan time, that is 0.6 CPU-seconds per second — more than half a core spent on planning.

Where the cost is acceptable. Two situations:

sampled tracing        : add traceparent only to sampled requests
static metadata only   : always safe; loses the per-request link

With 5 % sampling, the churn and the cache impact fall by a factor of twenty while the slow statements that matter — which are usually captured by tail sampling anyway — still carry their context.

Exact remediation & configuration

Django has it built in and off by default:

# settings.py
INSTALLED_APPS = [..., "google.cloud.sqlcommenter.django.middleware"]  # or the OTel integration
MIDDLEWARE = ["google.cloud.sqlcommenter.django.middleware.SqlCommenter", *MIDDLEWARE]

SQLCOMMENTER_WITH_FRAMEWORK = True
SQLCOMMENTER_WITH_CONTROLLER = True
SQLCOMMENTER_WITH_ROUTE = True
SQLCOMMENTER_WITH_OPENTELEMETRY = True     # adds traceparent

SQLAlchemy through OpenTelemetry instrumentation:

SQLAlchemyInstrumentor().instrument(engine=engine, enable_commenter=True,
                                    commenter_options={"db_driver": True, "opentelemetry_values": True})

Spring Boot with Hibernate can add a static comment per query, which is the safe variant:

spring:
  jpa:
    properties:
      hibernate:
        use_sql_comments: true

For trace context, the OpenTelemetry Java agent’s JDBC instrumentation can add it where the version supports it; check the agent’s documentation for the property name in the version you run.

Node.js with pg and the OpenTelemetry instrumentation:

new PgInstrumentation({
  addSqlCommenterCommentToQueries: true,
});

Sample it. The safest configuration adds the trace identifier only when the trace is sampled, so unsampled traffic keeps stable statement text. Where the integration does not offer that directly, a wrapper can:

from opentelemetry import trace

def maybe_comment(sql: str) -> str:
    span = trace.get_current_span().get_span_context()
    if not span.trace_flags.sampled:
        return sql
    return f"{sql} /*traceparent='00-{format(span.trace_id, '032x')}-{format(span.span_id, '016x')}-01'*/"

Protect the statistics. Raise pg_stat_statements.max if the churn is acceptable, and check whether the extension can be configured to ignore comments in your version:

ALTER SYSTEM SET pg_stat_statements.max = 10000;
SELECT pg_reload_conf();

Keep prepared statements working where they matter. For the handful of very hot statements, exclude them from commenting, or accept that the comment disables reuse for them and measure the CPU cost before deciding.

Behind a transaction pooler, comments interact with prepared statement tracking: PgBouncer’s tracking keys on the statement, so a unique comment defeats it in the same way it defeats the driver’s cache. That is an additional reason to sample.

Using the link during an incident

The value appears when a slow statement needs to become a trace.

From the database to the trace. Capture the running statement with its comment, extract the trace id, and open it:

SELECT pid,
       substring(query from 'traceparent=''00-([0-9a-f]{32})') AS trace_id,
       now() - query_start AS running_for,
       left(query, 80) AS sql
FROM pg_stat_activity
WHERE state = 'active' AND query LIKE '%traceparent%'
ORDER BY query_start LIMIT 10;

From the slow query log. With log_min_duration_statement set, the comment is in the log line, so a log search by trace id joins the two systems:

grep -o "traceparent='00-[0-9a-f]\{32\}" /var/log/postgresql/postgresql.log | sort | uniq -c | sort -rn | head

From an application trace to the database. The reverse direction needs no comment — the trace already contains the statement span — which is why the comment’s value is specifically for investigations that start at the database.

Combine with pool telemetry. A slow statement’s trace shows whether the request also waited for a connection, which distinguishes a database problem from a pool problem in one view. That is the pairing described in Tracing Connection Acquisition Latency with OpenTelemetry Spans.

Know when not to use it. Three cases where the cost usually exceeds the benefit: very high statement rates where planning cost matters, estates that depend on pg_stat_statements for capacity work, and deployments behind a transaction pooler relying on prepared statement tracking.

Validation & verification

The comment reaches the database. The pg_stat_activity query above must return rows with a trace id during traced traffic.

The link works end to end. Take one trace id from the database, open it in the tracing backend, and confirm it contains the statement.

Statement statistics survive. After enabling, watch the distinct entry count and the eviction rate:

SELECT count(*) FROM pg_stat_statements;

A count pinned at the maximum with constant turnover means the sampling rate is too high or static metadata should be used instead.

Prepared statement reuse is unaffected for hot statements. Compare parse and plan time before and after on the busiest statements; a significant rise means the comment is defeating the cache where it matters.

Sampling behaves. With 5 % sampling, roughly one statement in twenty should carry a trace id; a much higher proportion means the sampling decision is not being consulted.

Frequently Asked Questions

Related

• OpenTelemetry Connection Pool Tracing — the parent guide to pool instrumentation and traces.
• Tracing Connection Acquisition Latency with OpenTelemetry Spans — the pool-side half of the same investigation.
• Interpreting pg_stat_activity Wait Events for Pool Issues — reading the database view the comment enriches.
• Prepared Statement Caching Under Transaction Pooling — the caching the comment can defeat.