Tracing SQLAlchemy Async Queries with OpenTelemetry

Capturing database spans from create_async_engine requires instrumenting the synchronous engine that the async wrapper drives underneath, which is the single detail that trips up most asyncio services. This guide is part of the Distributed Tracing and OpenTelemetry in Python guide and the async tracing patterns guide, and it shows the exact SQLAlchemyInstrumentor configuration that produces correct query spans without inflating statement cardinality.

The async engine in SQLAlchemy is a thin coroutine-aware facade over a conventional synchronous Engine. OpenTelemetry's instrumentation listens to the Core execution events on that inner engine, so you reach it through the engine.sync_engine attribute. Once attached, every executed statement opens a span carrying the operation, the table, and optionally the full SQL text, while enable_commenter writes the active trace context back into the database query log for end-to-end correlation.

Prerequisites

Pin the instrumentation against a working SDK installation and an async driver such as asyncpg. The instrumentation versions track the unstable instrumentation channel, so a constrained range avoids surprise breakage.

pip install "opentelemetry-sdk>=1.30.0,<2.0.0" \
            "opentelemetry-instrumentation-sqlalchemy>=0.51b0,<1.0.0" \
            "opentelemetry-exporter-otlp-proto-grpc>=1.30.0,<2.0.0" \
            "sqlalchemy>=2.0.0,<3.0.0" \
            "asyncpg>=0.29.0,<0.31.0"

Set the exporter endpoint and a service name so the database spans land under the right service in your backend.

export OTEL_EXPORTER_OTLP_ENDPOINT="http://localhost:4317"
export OTEL_SERVICE_NAME="orders-api"

Implementation

First, configure the TracerProvider and a BatchSpanProcessor at import time, before any engine is created. Initializing the provider after the engine exists leaves the instrumentation bound to a NoOpTracer, and database spans silently disappear. This mirrors the SDK bootstrap described in setting up OpenTelemetry in FastAPI.

import os
from opentelemetry import trace
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import BatchSpanProcessor
from opentelemetry.exporter.otlp.proto.grpc.trace_exporter import OTLPSpanExporter

# 1. Provider first, so the instrumentation binds to a real tracer.
provider = TracerProvider()
provider.add_span_processor(
    BatchSpanProcessor(
        OTLPSpanExporter(endpoint=os.getenv("OTEL_EXPORTER_OTLP_ENDPOINT"))
    )
)
trace.set_tracer_provider(provider)

Second, create the async engine and instrument its synchronous counterpart. The create_async_engine call returns an AsyncEngine; its sync_engine attribute is the object SQLAlchemyInstrumentor knows how to patch. Pass it through instrument_engine, not a global instrument() call, so the binding is explicit and scoped.

from sqlalchemy.ext.asyncio import create_async_engine
from opentelemetry.instrumentation.sqlalchemy import SQLAlchemyInstrumentor

# 2. Build the async engine.
engine = create_async_engine(
    "postgresql+asyncpg://app:secret@localhost/orders",
    pool_size=10,
    max_overflow=5,
)

# 3. Instrument the underlying synchronous engine, not the async facade.
SQLAlchemyInstrumentor().instrument(
    engine=engine.sync_engine,   # the layer where Core events fire
    enable_commenter=True,       # append trace context to SQL
    commenter_options={"db_driver": True, "opentelemetry_values": True},
)

Third, run queries through the normal async session API. Each execute now opens a child span under whatever span is active in the current asyncio context, so a query issued inside a request handler nests beneath the request span automatically.

import asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

async def fetch_order(order_id: int) -> dict | None:
    async with AsyncSession(engine) as session:
        # Bound parameter keeps the captured statement parameterized.
        result = await session.execute(
            text("SELECT id, status FROM orders WHERE id = :oid"),
            {"oid": order_id},
        )
        row = result.first()
        return dict(row._mapping) if row else None

asyncio.run(fetch_order(42))

With enable_commenter active, the statement that reaches PostgreSQL carries the active trace context as a trailing comment, letting you pivot from a slow query in the database log straight to its span.

Expected Output: the SQL recorded by the database (for example in pg_stat_statements or the query log) includes the appended commenter block.

SELECT id, status FROM orders WHERE id = $1
/*db_driver='asyncpg',traceparent='00-4bf92f3577b34da6a3ce929d0e0e4736-00f067aa0ba902b7-01'*/

Why the async engine delegates to sync_engine

create_async_engine does not implement its own SQL execution. It builds a normal synchronous Engine configured with an async-capable DBAPI shim (the asyncpg or aiomysql adapter), then wraps it in an AsyncEngine facade. Every await session.execute(...) ultimately drives that inner engine through a greenlet bridge: SQLAlchemy runs the synchronous Core machinery inside a greenlet and yields to the event loop whenever the driver would block on the socket. The Core execution events — before_cursor_execute and after_cursor_execute, which the instrumentation subscribes to — fire on the inner synchronous engine inside that greenlet. That is the entire reason you instrument engine.sync_engine: it is the only object that actually emits the events, even though your code only ever touches the async facade. Passing the AsyncEngine to instrument(engine=...) registers listeners on an object that never fires them, which is why it silently produces no spans.

The span the instrumentation opens is created from the same contextvars-based active context your async code runs under, so a query issued inside a request handler's span nests beneath it without any manual propagation. The greenlet bridge preserves the context across the yield, so the span the database events open is a child of whatever was active at the await.

Connection-pool behavior in the span timeline

The query span wraps the cursor execution, but acquiring a pooled connection happens before execution begins, so checkout latency is not inside the query span by default. Under pool exhaustion — more concurrent execute calls than pool_size + max_overflow — a coroutine waits on pool_timeout for a free connection, and that wait shows up as a gap in the trace between the parent span and the first child query span rather than as inflated query duration. If you see request spans whose database children start late but run fast, the pool, not the database, is the constraint; raise pool_size or shorten the time connections are held. For visibility into checkout itself, you can open a thin application span around engine.connect() so the wait becomes a measurable span rather than dead time.

A second pool subtlety: pool_pre_ping issues a lightweight liveness check before handing out a connection. With instrumentation active that ping is itself a traced statement, so you may see small extra spans (a SELECT 1 or driver-specific ping) interleaved with your real queries. They are expected; filter them at query time rather than disabling pre-ping, which guards against stale connections after a database failover.

What enable_commenter writes, precisely

enable_commenter=True turns on SQLCommenter, which appends a structured, URL-encoded key-value comment to the end of each statement just before execution. The keys are controlled by commenter_options: db_driver adds the driver name and version, opentelemetry_values adds the W3C traceparent (and tracestate when present) drawn from the active span. The comment is appended to the statement text but is not part of the bound-parameter set, so it does not affect the query plan or the result. Two consequences follow. First, the appended comment changes the literal SQL string, which means tools that fingerprint queries by exact text — some query-cache or normalization layers — will see each statement as unique unless they strip comments; verify your pg_stat_statements normalization (it ignores comments by default) and any prepared-statement caching tolerate it. Second, set enable_attribute_commenter=True only when you want the fully commented statement mirrored onto the span's db.statement attribute; leaving it off keeps db.statement clean and parameterized while the database log still carries the correlation comment.

Configuration options

SQLAlchemyInstrumentor().instrument accepts the following arguments most relevant to async engines.

You can omit engine and call instrument() to patch every engine class-wide, but per-engine instrumentation is the supported, predictable path for asyncio services that build engines lazily.

Verification

Add a ConsoleSpanExporter during local development to confirm spans are emitted. A correctly instrumented query produces a span whose name is the operation plus table and whose attributes include db.system and a parameterized db.statement.

{
  "name": "SELECT orders",
  "kind": "SpanKind.CLIENT",
  "attributes": {
    "db.system": "postgresql",
    "db.name": "orders",
    "db.statement": "SELECT id, status FROM orders WHERE id = $1"
  },
  "parent_id": "00f067aa0ba902b7"
}

A non-null parent_id confirms the query span nested under the active request span, and a parameterized db.statement confirms cardinality is controlled. If parent_id is null, the query ran outside any active span context; if db.statement contains literal values, switch the offending call to bound parameters.

Detecting N+1 queries from spans

The most valuable thing async query spans reveal is the N+1 pattern: a request that issues one parent query, then fires a separate follow-up query per returned row instead of joining or batching. Because every execute opens a sibling span under the request, an N+1 renders as a long, flat run of near-identical query spans whose db.statement differs only in a bound parameter. A lazy relationship access on an ORM result inside an async for loop is the usual culprit. The trace makes the count explicit, which is far more legible than reading it from the code.

[
  {"name": "SELECT orders", "db.statement": "SELECT id, customer_id FROM orders LIMIT 50", "parent_id": "req"},
  {"name": "SELECT customers", "db.statement": "SELECT name FROM customers WHERE id = $1", "parent_id": "req"},
  {"name": "SELECT customers", "db.statement": "SELECT name FROM customers WHERE id = $1", "parent_id": "req"},
  {"name": "SELECT customers", "db.statement": "SELECT name FROM customers WHERE id = $1", "parent_id": "req"}
]

Fifty repeated SELECT customers spans under one request is the signature. The remedy is a single batched query (WHERE id = ANY(:ids)) or an eager-loading strategy such as selectinload, which collapses the fan-out into one or two spans. Counting siblings by db.statement in a backend query turns this into an alert rather than a manual hunt, the same span-aggregation thinking applied when managing span attributes for queryability.

Common mistakes

Instrumenting the async engine directly. Passing the AsyncEngine object to instrument(engine=...) patches nothing useful because the Core execution events fire on the inner synchronous engine. No spans appear and no error is raised. Always pass engine.sync_engine.

Interpolating values into SQL text. Building statements with f-strings folds literals into db.statement, so each distinct value produces a distinct span name and the trace backend's cardinality budget explodes. Use bound parameters so the captured statement and span name stay stable, the same discipline used when managing span attributes to keep traces queryable. For broader context flow across coroutines, see context propagation and baggage.

Blocking the loop with a synchronous engine. Mixing a plain create_engine and synchronous Session into an async service blocks the event loop for the duration of every query, so the query span records its real duration but every other coroutine stalls behind it and unrelated spans inflate. The trace shows healthy database spans alongside mysteriously slow neighbors. Use create_async_engine with an async driver throughout, and keep blocking database libraries off the loop, the same rule that governs all I/O in async tracing patterns.

Instrumenting after the engine is built without re-binding. Calling SQLAlchemyInstrumentor().instrument() after the engine already opened connections, or initializing the TracerProvider after engine creation, binds the listeners to a NoOpTracer so spans never appear. Always set the provider first, then create the engine, then instrument its sync_engine, in that order.

Frequently Asked Questions