Propagating Trace Context Through PostGIS Functions

A spatial trace usually stops at the database boundary. The application span says “spatial join, 4.2 seconds” and nothing inside it is visible: which predicate ran, whether the index was used, how many features the planner actually touched, whether a trigger fired a validity check that cost half the time. On a pipeline where the expensive and failure-prone work lives inside PostGIS, that boundary is exactly where the trace stops being useful. This guide covers how to carry trace context into the database, attach it to the work PostGIS does, and get spans back out — without adding per-row overhead to a query that already touches millions of features. It belongs to OpenTelemetry integration for GIS pipelines under geospatial observability architecture fundamentals.

Trace context crossing the database boundary and returning as child spans An application span issues a query. The trace and span identifiers are carried across the database boundary in a session setting and as a SQL comment. Inside the database, a statement-level record captures the identifiers alongside plan statistics and any validity-trigger work. An exporter reads those records and emits them as child spans that attach beneath the application span in the trace. Without the propagation, the same region of the trace is shown as an opaque block labelled four point two seconds with no detail. Without propagation the database is one opaque span app: enrich_parcels db query · 4.2 s · no detail with propagation app: enrich_parcels db: spatial join · 4.2 s index scan · 0.9 s ST_Intersects · 2.1 s trigger · 1.1 s SET app.trace_id session-scoped, per transaction statement record ids + plan stats + timings exporter emits child spans one trace app and database joined

Problem framing: three things have to survive the boundary

Getting useful database spans needs three separate mechanisms, and most partial implementations have one of them.

The identifiers. The trace and parent span identifiers must reach the database session so that anything recorded there can be attached to the right trace. A SQL comment carrying the context is the portable form; a session setting is the queryable one. Both have a place.

The context. Identifiers alone produce a span with no meaning. The database record needs the operation’s spatial context — which layer, which predicate, how many candidate features — or you end up with a correctly-parented span labelled “SELECT”.

The cost breakdown. The reason to instrument inside the database is to see where the time went: index scan versus predicate evaluation versus trigger work. That comes from plan statistics, not from wall-clock timing of the whole statement.

A fourth consideration governs the whole design: the instrumentation must not scale with row count. Spatial statements routinely touch millions of features, so anything evaluated per row — a trigger writing a telemetry row, an attribute constructed per feature, a timestamp captured per iteration — becomes the dominant cost of the operation rather than a measurement of it. Every technique below is deliberately statement-scoped or sampled, and that constraint is what makes database tracing affordable on this workload rather than a well-intentioned way to double the cost of a bulk load.

Implementation: carry the context, record the statement

Set the context at the start of the transaction and label the statement so it can be correlated even where session settings are unavailable.

# db_trace.py — carry trace context into PostGIS.
from contextlib import contextmanager
from opentelemetry import trace

tracer = trace.get_tracer("gis.etl")

@contextmanager
def traced_transaction(conn, layer: str, operation: str):
    with tracer.start_as_current_span(f"db.{operation}") as span:
        ctx = span.get_span_context()
        trace_id = format(ctx.trace_id, "032x")
        span_id = format(ctx.span_id, "016x")
        with conn.cursor() as cur:
            # Session settings are queryable from inside functions and triggers,
            # which a SQL comment is not.
            cur.execute("SELECT set_config('app.trace_id', %s, true)", (trace_id,))
            cur.execute("SELECT set_config('app.span_id',  %s, true)", (span_id,))
            cur.execute("SELECT set_config('app.layer',    %s, true)", (layer,))
        span.set_attribute("gis.layer", layer)
        yield conn

Inside the database, record statement-level work against those identifiers. A lightweight table plus an explicit call at the end of each significant operation is far cheaper and more predictable than a per-row trigger.

-- Statement-level span records, written once per operation, never per row.
CREATE TABLE IF NOT EXISTS telemetry.db_span (
  trace_id     text        NOT NULL,
  parent_span  text        NOT NULL,
  name         text        NOT NULL,
  layer        text,
  started_at   timestamptz NOT NULL,
  ended_at     timestamptz NOT NULL,
  attributes   jsonb       NOT NULL DEFAULT '{}'::jsonb
);

CREATE OR REPLACE FUNCTION telemetry.record_span(
  p_name text, p_started timestamptz, p_attrs jsonb DEFAULT '{}'::jsonb)
RETURNS void LANGUAGE sql AS $$
  INSERT INTO telemetry.db_span (trace_id, parent_span, name, layer,
                                 started_at, ended_at, attributes)
  VALUES (current_setting('app.trace_id', true),
          current_setting('app.span_id',  true),
          p_name,
          current_setting('app.layer',    true),
          p_started, clock_timestamp(), p_attrs);
$$;

-- Usage inside a spatial procedure: one record per meaningful stage.
DO $$
DECLARE t0 timestamptz := clock_timestamp(); n bigint;
BEGIN
  CREATE TEMP TABLE hits AS
  SELECT a.address_id, d.district_id
  FROM curated.addresses a
  JOIN curated.districts d ON ST_Intersects(d.geom, a.geom);

  GET DIAGNOSTICS n = ROW_COUNT;
  PERFORM telemetry.record_span(
    'postgis.point_in_polygon', t0,
    jsonb_build_object('gis.predicate', 'ST_Intersects',
                       'gis.matched_features', n));
END $$;

The exporter then reads unexported rows and emits them as child spans with the recorded parent, which is what makes them attach beneath the application span rather than floating as orphans.

The cost breakdown comes from the planner rather than from manual timing. Capturing it once per operation, on a sampled basis, gives the index-versus-predicate split without instrumenting anything per row.

-- Sampled plan capture: run for a small fraction of operations only.
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT a.address_id, d.district_id
FROM curated.addresses a
JOIN curated.districts d ON ST_Intersects(d.geom, a.geom);
-- Store the JSON in the span's attributes; the node types reveal whether the
-- GiST index was used and how many rows the recheck actually evaluated.
Cost of three propagation approaches against the detail each one yields Three approaches are compared. A SQL comment carrying trace context costs nothing and yields correlation only, letting logs be joined to traces but producing no spans. Session settings plus statement-level span records cost a single insert per operation and yield properly parented child spans with spatial attributes. Per-row triggers recording each feature cost proportionally to the row count and yield detail nobody queries, and are marked as the approach to avoid on spatial workloads where row counts reach millions. Statement-level records are the sweet spot; per-row triggers never are SQL comment only cost: zero · yields: correlation between database logs and traces, no spans good as a supplement, insufficient on its own session settings + statement records cost: one insert per operation · yields: parented child spans with spatial attributes plan statistics captured on a sample give the index-versus-predicate split per-row triggers cost: proportional to row count · yields: detail nobody queries on a twelve-million-feature load this is the dominant cost of the load itself Connection pooling leaking trace context between two requests Two requests share a pooled connection in sequence. In the broken arrangement the trace identifier is set at session scope, so the second request's queries inherit the first request's identifier and its spans attach to the wrong trace. In the correct arrangement the identifier is set at transaction scope and resets at commit, so the second request sets its own and nothing leaks. The difference is a single boolean argument, highlighted in both paths. One boolean argument decides whether traces leak between requests session scope — leaks request A · trace_id = a1b2 request B · still reads trace_id = a1b2 transaction scope — resets request A · trace_id = a1b2 request B · trace_id = c3d4 commit resets set_config(‘app.trace_id’, id, true) — the third argument is the whole difference.

Verification

Run one traced operation and confirm the resulting trace contains the database child spans attached beneath the application span, with the correct layer attribute. An orphaned span usually means the parent identifier was captured from the wrong span — commonly the tracer’s current span at export time rather than at execution time.

Then confirm the overhead. Time the same operation with and without recording enabled; a statement-level record should be unmeasurable against a query touching millions of rows. If it is measurable, something is recording per row.

Finally, confirm the session settings are transaction-scoped. The third argument to set_config being true is what makes them reset at commit; without it, a pooled connection carries one request’s trace identifier into the next request’s queries, silently mixing traces.

Gotchas

Connection pooling leaking context. The most common defect. Use transaction-scoped settings and set them at the start of every transaction, never once per connection.

Recording per row. Turns observability into the dominant cost of a bulk load.

Capturing plans on every statement. EXPLAIN ANALYZE runs the query; capturing it always doubles the work. Sample it.

Spans exported but never parented. Records written without a valid parent identifier produce a separate, useless trace per statement. Assert the parent is present before export.

Attributes that repeat the SQL. Storing the full statement text in every span is expensive and rarely what you need; store the operation name, layer and predicate, and correlate to the statement through the database’s own logging.

FAQ

Does this work with connection poolers in transaction mode?

Yes, and it is the reason for transaction-scoped settings. In transaction pooling mode a session can serve different clients between transactions, so anything set at session scope is unsafe. Setting inside the transaction is correct in both pooling modes.

Should every statement produce a span?

No. Instrument the operations whose cost or failure you actually need to see — the spatial joins, the validity sweeps, the bulk promotions. A span per statement produces a trace nobody can read and an export volume that competes with the collector budget described in budgeting collector CPU for vector telemetry.

How do I attribute time spent in triggers?

Record a span inside the trigger function itself, using the same session settings. Trigger cost is frequently a surprising share of a bulk load — a per-row validity check on twelve million features is not free — and it is invisible from the application side.

What about work done by autovacuum or background jobs?

Those have no trace context and should not be forced into one. Instrument them as metrics instead, which is what the index-maintenance signals in spatial index health monitoring are for.

How long should database span records be retained before export?

Minutes, not hours. The record table is a queue, and treating it as one — export, then delete — keeps it small enough that the insert stays cheap and a failed exporter is visible as a growing backlog rather than as silent unbounded growth. Alert on the unexported row count exceeding a few thousand; on a healthy platform it sits near zero, and a rising value means the exporter has stopped while the pipeline keeps writing.

Can the plan statistics be captured without EXPLAIN ANALYZE?

Partly. Cumulative statistics views give index-versus-sequential scan counts per table over time, which answers the “is the index being used” question at low cost. Use those continuously and reserve plan capture for sampled deep dives.

One organisational note: the database spans are most useful to the people who own the queries, not to whoever set up the tracing. Making the span names match the procedure names engineers already use, rather than inventing an instrumentation vocabulary, is what determines whether anybody looks at them after the first week.