Instrument database sql With OpenTelemetry Traces in Go

A request that spends 400 milliseconds in "the database" is not a diagnosis. Which of the eleven statements was it, were they sequential, did one wait for a connection, did the commit take the time? Distributed tracing answers those if every database call is a span under the request's span. For database/sql the usual way is otelsql, a wrapper that instruments the driver, and this article uses it on Go 1.25.6 with pgx and PostgreSQL 18.6. It collects the spans in memory and prints them, so you can see exactly what a request produces.

It belongs to the database cornerstone and is the tracing counterpart of slow query logging. Spans are also how an N plus one becomes obvious in a trace viewer.

Wiring it in

Two changes: open the database through otelsql, and make sure every call receives the request's context, because that is how a span knows its parent.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"sort"

	"github.com/XSAM/otelsql"
	_ "github.com/jackc/pgx/v5/stdlib"
	"go.opentelemetry.io/otel"
	"go.opentelemetry.io/otel/attribute"
	sdktrace "go.opentelemetry.io/otel/sdk/trace"
	"go.opentelemetry.io/otel/sdk/trace/tracetest"
	semconv "go.opentelemetry.io/otel/semconv/v1.26.0"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func main() {
	ctx := context.Background()
	exporter := tracetest.NewInMemoryExporter()
	tp := sdktrace.NewTracerProvider(sdktrace.WithSyncer(exporter))
	otel.SetTracerProvider(tp)

	db, err := otelsql.Open("pgx", dsn,
		otelsql.WithAttributes(semconv.DBSystemPostgreSQL),
		otelsql.WithSpanOptions(otelsql.SpanOptions{Ping: true, DisableErrSkip: true}),
	)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	if err := otelsql.RegisterDBStatsMetrics(db, otelsql.WithAttributes(semconv.DBSystemPostgreSQL)); err != nil {
		log.Fatal(err)
	}

	// A request: a parent span, one query, one transaction with two statements, and one failing statement.
	tracer := otel.Tracer("lab628")
	ctx, request := tracer.Start(ctx, "GET /orders/7")
	var n int
	if err := db.QueryRowContext(ctx, "select count(*) from generate_series(1, $1)", 1000).Scan(&n); err != nil {
		log.Fatal(err)
	}
	tx, _ := db.BeginTx(ctx, &sql.TxOptions{})
	_, _ = tx.ExecContext(ctx, "create temp table t628 (id int)")
	_, _ = tx.ExecContext(ctx, "insert into t628 values (1), (2)")
	_ = tx.Commit()
	_, failed := db.ExecContext(ctx, "select * from no_such_table628")
	request.End()
	_ = tp.ForceFlush(ctx)

	fmt.Println("failing statement:", failed != nil)
	spans := exporter.GetSpans()
	sort.Slice(spans, func(i, j int) bool { return spans[i].StartTime.Before(spans[j].StartTime) })
	byID := map[string]string{}
	for _, s := range spans {
		byID[s.SpanContext.SpanID().String()] = s.Name
	}
	for _, s := range spans {
		parent := "(root)"
		if p, ok := byID[s.Parent.SpanID().String()]; ok {
			parent = p
		}
		var stmt string
		for _, a := range s.Attributes {
			if a.Key == attribute.Key("db.statement") || a.Key == attribute.Key("db.query.text") {
				stmt = a.Value.AsString()
			}
		}
		status := s.Status.Code.String()
		fmt.Printf("%-24s parent=%-14s status=%-5s kind=%s stmt=%q\n", s.Name, parent, status, s.SpanKind, stmt)
	}
}

failing statement: true
GET /orders/7            parent=(root)         status=Unset kind=internal stmt=""
sql.connector.connect    parent=GET /orders/7  status=Unset kind=client stmt=""
sql.conn.query           parent=GET /orders/7  status=Unset kind=client stmt="select count(*) from generate_series(1, $1)"
sql.rows                 parent=GET /orders/7  status=Unset kind=client stmt=""
sql.conn.reset_session   parent=GET /orders/7  status=Unset kind=client stmt=""
sql.conn.begin_tx        parent=GET /orders/7  status=Unset kind=client stmt=""
sql.conn.exec            parent=GET /orders/7  status=Unset kind=client stmt="create temp table t628 (id int)"
sql.conn.exec            parent=GET /orders/7  status=Unset kind=client stmt="insert into t628 values (1), (2)"
sql.tx.commit            parent=GET /orders/7  status=Unset kind=client stmt=""
sql.conn.reset_session   parent=GET /orders/7  status=Unset kind=client stmt=""
sql.conn.exec            parent=GET /orders/7  status=Error kind=client stmt="select * from no_such_table628"

otelsql.Open("pgx", dsn, ...) takes the same arguments as sql.Open and returns a *sql.DB. Everything else in the program is ordinary database/sql. The options set attributes common to every span (db.system), and span behaviour: here pings are traced and DisableErrSkip stops the wrapper from reporting the driver's internal "skip this interface" signal as an error. The program installs a tracer provider that records spans in memory so they can be printed. In a service it would be an OTLP exporter, as described in the OpenTelemetry Go documentation.

One version note. The latest otelsql and OpenTelemetry releases require Go 1.26, so this lab pins the versions that build on Go 1.25 (otelsql v0.39.0 and the SDK at v1.37.0). Pin to what your toolchain supports and upgrade with it.

What a request produced

The program starts a span GET /orders/7, runs a query, a transaction of two statements, and one statement that fails. The printed spans, in start order:

Every span is client kind and every one has the request span as its parent, because the program passed the context that held it. Reading the list as a story: the request connected, ran a query, read its rows, ran a transaction and failed on the last statement.

A few details matter in practice.

The SQL keeps its placeholders. The first statement's attribute is select count(*) from generate_series(1, $1), with the 1000 argument not recorded. That is what you want. Argument values are personal data more often than not, and a trace backend is a place with a long retention and a wide audience. Do not enable options that add arguments to spans in production, and think twice in staging.

Failures are visible. The failed statement's span has status Error while the rest are Unset, the default for success, so a trace viewer highlights it, and the error message is recorded as an event.

Transactions show their structure. begin_tx, the statements and tx.commit are separate spans, so a transaction that holds a connection for two seconds is visible as a long gap between begin_tx and commit, and a slow commit, usually the disk, is visible on its own. This is the view that makes the advice in unit of work about short transactions measurable.

There is noise. reset_session spans, sql.rows and the connect span are accurate and rarely interesting. The library has options to omit them (SpanOptions fields such as OmitConnResetSession, OmitRows and OmitConnPrepare), and in a high traffic service you will want to, since a trace with 400 spans is a trace nobody reads.

Metrics for the pool

otelsql.RegisterDBStatsMetrics(db, ...) registers instruments that read db.Stats(): open connections, connections in use and idle, how many requests waited for a connection and for how long. Those numbers are the early warning of a pool that is too small or a connection leak, the subject of connection leaks. Chart connections in use against the maximum, and alert on wait time rising.

What the trace is good for, and what it is not

Traces answer where did the time in this request go. They show sequential versus concurrent calls, the one statement that dominates, and the gap before a query where the request waited for a pooled connection (the connect span or an unexplained space before the query span). They are poor at answering which query is the most expensive overall, which is a question about aggregates; use pg_stat_statements or the slow log for that. Use traces to investigate a slow request and the other tools to find what to optimise.

Sampling matters. Tracing every request at full detail is expensive in CPU, in the size of what you export and in the bill. A sample of one in a hundred, plus every request over a latency threshold or with an error (tail based sampling in the collector), keeps the signal and drops the cost.

Using pgx natively instead

If you use pgx directly rather than through database/sql, the same job is done by a QueryTracer on the pool configuration, and the community package otelpgx provides one that emits spans and metrics. The tracer interface is what the slow query logger uses. Either route gives you a span per statement; choose the one that matches how the service already talks to the database.

Checklist

1. Use otelsql.Open (or the pgx tracer) once, where the pool is created.
2. Use the Context variants of every call and pass the request context, so spans have a parent. A call with context.Background() makes an orphan span or none.
3. Leave argument values out of spans.
4. Omit noisy span types and sample.
5. Register the pool metrics and alert on wait time.
6. Name spans after the operation in your own code too, such as repo.OrdersByCustomer, so the database spans sit under something a person recognises.

The library is documented in the otelsql repository, and the conventions for database spans are in the OpenTelemetry database semantic conventions.