Tracing ClickHouse queries with OpenTelemetry: when your team needs it
Application traces usually stop at the database client call, so when an API endpoint is slow and ClickHouse is the suspect, the span for the query shows only total wall time. It does not show which stage of the query consumed that time. OpenTelemetry is the vendor-neutral framework for generating and exporting traces, metrics and logs. It is not a backend, so you still choose where spans are stored and visualized. Trace context propagation means the application passes its trace identifiers into the ClickHouse request so server-side work can be linked to the caller's span. Span storage means ClickHouse records its own spans in a table you can query alongside application data. Confirm the exact tracing behavior, settings and span table layout for the ClickHouse version you run, because these details change between releases and are documented in the ClickHouse GitHub releases.
Teams usually reach for this when the question is about where time goes across services, not only which query was slow. system.query_log answers which queries were slow, failed or expensive on the server side, and Prometheus shows aggregate trends in load, merges and memory. Neither ties a single user request to the queries it triggered across several services. If your problem is already visible in the query log or in cluster metrics, tracing adds a cost in instrumentation, collector capacity and storage that may not be justified. If incidents repeatedly start in application code and end in a database call that looks normal in aggregate, tracing is the next step.
Before committing, ask the team who will own the pipeline, whether the applications already emit OpenTelemetry, and how long spans must be kept. Then run a test on a non-production cluster with representative queries and check that the trace identifiers reach the server and that the span table fills as expected. The OpenTelemetry Collector documentation explains the agent and gateway patterns you will need to choose between.
Which tracing approach fits your situation
Your applications already create spans and export them over OTLP. The data path runs from each service through its OpenTelemetry SDK to a Collector agent or gateway, then to the trace store you operate. When the service calls ClickHouse, the trace context from the incoming request should carry into the query, so the database work appears as child spans under the request that caused it. Confirm that this link exists, because it is not guaranteed by default in every client library.
- Client propagation: Confirm that your ClickHouse driver or HTTP client sends the W3C
traceparentheader on each query, and check it with a test request. - Server span recording: Confirm the trace settings for your version, including
opentelemetry_start_trace_probabilityand whetheropentelemetry_span_logis enabled. - Sampling policy: Agree on one sampling decision across the application and ClickHouse so that each trace is recorded end to end.
- Collector placement: Decide whether an agent runs beside each service or a gateway receives data from many sources, and set batching and memory limits on it.
- Export target: Confirm the exporter endpoint and the storage behind it before you size the pipeline.
Check that one request produces linked application and query spans in your trace view before you expand sampling.
If you only need to find where time goes inside ClickHouse, you can skip application instrumentation and any external backend. ClickHouse records its own spans for sampled queries into a system table, and you read them with SQL. The data path stays inside your cluster: the server writes spans, you join them to query logs by trace identifier, and you examine the timing of each stage. This approach answers questions about the query itself, such as which stage waits longest, but it cannot show calls made by the application around the query.
- Sampling control: Set the probability for traced queries deliberately, since tracing every query adds overhead and table growth.
- Span table retention: Apply a TTL or regular cleanup to the span table so that it does not grow without limit.
- Join keys: Confirm the trace identifier columns in your version, and test a join against
system.query_logon a sample query. - Access control: Restrict who can read the span table, because span attributes can reveal query structure and parameters.
- Forwarding option: Decide now whether you will later forward spans to a Collector, since that changes how you manage the table.
Run one traced query, find its span rows, and confirm the timings add up to the query duration before you rely on this view for diagnosis.
If a vendor or hosted service stores and visualizes your traces, the data leaves your network. The data path runs from applications and ClickHouse through a Collector gateway, which applies processors and then sends spans to the backend over OTLP or a vendor-specific exporter. Authenticator extensions can add credentials to outgoing requests, such as an OAuth2 client flow, and can validate incoming connections. The main decision here is what you allow to leave, because query text and span attributes can contain values from your data.
- Attribute filtering: Use Collector processors to remove or mask attributes that carry sensitive values before export.
- Egress and credentials: Confirm the endpoint, the authentication method and the network rules needed to reach it.
- Component stability: Check the stability level of each Collector component you use in its README, since levels differ.
- Data classification: Record which telemetry categories leave your environment and get approval from your data owners.
- Cost model: Ask the vendor how charges scale with span volume, retention and sampling, and get the terms in writing.
Send a test trace through the full path, then check the received span for removed attributes before you enable production sampling.
Configuration options and when each fits
This table compares the main choices for tracing ClickHouse queries: where spans are stored, how the Collector is deployed, how traces are sampled, and how connections are authenticated. Read each row against your trace volume, retention needs and the team that will query the data. The main takeaway is that keeping spans in a separate trace backend and storing them in ClickHouse both work, but each moves cost, retention and query speed in a different direction.
| Decision | Option | Trade-off | When it fits |
|---|---|---|---|
| Span storage | ClickHouse tables | Spans sit next to query_log data, so joins are fast, but the cluster carries trace storage and retention cost | Teams that already run ClickHouse and want to correlate spans with system tables in SQL |
| Span storage | Separate trace backend | Adds another system to operate and license, but keeps trace load and retention off the analytics cluster | Organizations with a dedicated observability platform or strict separation between analytics and telemetry |
| Collector deployment | Agent next to services | Each host runs a Collector, which adds per-node resource use and configuration to maintain, but limits data loss from a single failed hop | Workloads that need local buffering and per-host attributes such as node or pod metadata |
| Collector deployment | Gateway | Centralizes processing and export credentials in fewer places, but becomes a bottleneck that must be scaled and monitored | Smaller footprints or teams that want one place to sample, redact and route spans |
| Sampling | Head sampling in the SDK | Cheapest to run and keeps volume low, but slow or failing queries may be dropped before they are seen | Stable, high-volume paths where aggregate latency matters more than every individual trace |
| Sampling | Tail sampling in the Collector | Keeps slow and error traces by policy, but needs memory and a gateway that sees all spans for a trace | Investigations where errors and slow queries must be kept reliably |
| Receiver authentication | Authenticator extension such as OIDC | Stops unknown senders from writing spans, but adds an identity dependency that must be available for ingest | Shared gateways accepting telemetry from several teams or networks |
| Exporter authentication | oauth2client or static credentials | oauth2client rotates tokens for outgoing requests with more setup, while static credentials are simpler but harder to rotate | Exports to a backend or ClickHouse endpoint that requires credentials the Collector must send |
| Exporter and receiver transport | TLS with certificate checks | Protects spans in transit and adds certificate lifecycle work for every hop | Any path that crosses a network boundary or carries query text that may contain sensitive values |
How to set up query tracing in order
This sequence connects an application request to the ClickHouse query it triggers, so one trace ID shows the full path from the service call to query execution. Each step ends with a check that confirms the link is working before you move on.
Confirm the ClickHouse version and tracing settings
The platform owner checks that the cluster runs a version that still receives security updates. The security policy lists 26.9, 26.8, 26.7 and 26.3 as supported, so confirm the exact build with a version query on each node. Then read the tracing settings in the documentation for that release, since names and defaults can change between lines. Record the settings you plan to use. Done when every node reports the same supported version and the settings read back the values you expect from a test session.
Instrument the application and propagate trace context
The application team adds OpenTelemetry instrumentation to the service that calls ClickHouse and wraps each database call in a client span. Confirm with your driver whether it carries the trace context into the query. If it does not, you can still record the query ID on the application span so the two records can be matched later. Send one test request through the service. Done when the application span shows a trace ID and the query ID it issued, and you can find that query ID in the server logs.
Enable server-side span recording on a test node
On one non-production node, turn on the server setting that records spans for queries, using the exact name from the documentation for your release. Confirm which system table receives those spans and whether sampling applies, because sampling decides which queries leave a record at all. Run a query that carries the test trace ID and check the table. Done when the span table contains a row with the expected trace ID and a duration close to the query's own timing in the query log. If no row appears, check the sampling setting before changing anything else.
Deploy the Collector with a receiver and exporter
The platform team deploys the OpenTelemetry Collector as an agent beside the service or as a gateway for several sources. Use the contrib distribution from the official releases, verify its signed artifacts and SHA256 checksums, and pin the version you tested. Configure an OTLP receiver for the application spans and an exporter to your span store, and add authentication if the receiver is reachable from outside its host. Follow the configuration best practices before exposing any endpoint. Done when the Collector logs show both pipelines started without errors and a test span passes through it.
Choose and configure the span store
The Collector does not store telemetry, so the team must choose where spans live and how long they are kept. Options include a ClickHouse table fed by the Collector, a dedicated tracing backend, or an existing observability platform. Decide who will query spans, how many days they need to keep, and who may read traces that include query text. Confirm that the chosen backend accepts the Collector exporter you configured. Done when a test span sent through the Collector can be retrieved from the store by its trace ID.
Verify the path with one known slow query
Run a deliberately slow query, such as a large aggregation on a non-production table, from the instrumented service with a known trace ID. Then follow that trace from the application span through the Collector into the span store, and compare the query span's duration with the query log entry for the same query ID. Check that the time spent inside ClickHouse accounts for most of the request time. Done when one trace shows the service call, the database call and the server-side query work, with matching durations. Keep this query as a repeatable test after each change.
How to correlate application traces with query execution
The correlation starts with a join key that exists on both sides. Have the application set a query_id for each ClickHouse request and record that value on the application span, or derive it from the trace ID so the two can be matched later. On the ClickHouse side, look up the same identifier in system.query_log for the query's statistics and errors, and in system.opentelemetry_span_log for spans, if the server is recording them for that request. Whether the server also reads trace context from a traceparent header, and which fields it fills for HTTP and native clients, varies by release. Confirm both on your chosen version by sending one tagged request end to end and checking that the rows appear before you rely on the join.
Once the rows match, split the elapsed time into segments and assign each one to a source. The gap between the client span start and the server's query start approximates network and connection time. Queue time shows up when a query waits for a concurrency slot, and parse and analysis time sit before the read phase. Reads, merges the query waited on, and final result formatting follow. The client span then covers time spent consuming the result stream. query_log gives total duration and profile event counters, while the span log gives nested timings for the stages it instruments. Confirm which counter or span maps to each stage on your version, using a query with a known shape, because names and coverage change between releases.
The two tables answer different questions and should be kept together. system.query_log holds one row per finished query, including failed ones, so it is the record to query for slow or expensive statements. The span log only covers requests that carried trace context, so set sampling deliberately and check that the traces you need are actually recorded. Background merges and mutations belong to no application trace, so investigate them through system tables such as system.part_log instead. Both tables grow with traffic, so decide retention for each, and document which applications set the join key, because a missing identifier is the most common reason a trace stops at the database boundary.
Security and operational considerations for trace data
Traces move through three places: the application, the Collector, and the ClickHouse trace store. Each one can leak data, accept untrusted input, or fall behind on support, so the controls below should be designed together rather than added after the first query runs.
Mask personal data in span text
Database spans often carry the SQL text and bound parameters of each query, and those parameters can hold names, account numbers or email addresses. Decide which attributes are recorded at all, and strip or hash parameter values in the Collector before export. Ask the team to review sample spans from production-like traffic before the trace store is opened to anyone else.
Set retention for trace data
Traces are kept for debugging, not as a record of what users did, so their retention should be shorter than the business need that produced them. Set a TTL on the trace tables and confirm that backups of the trace store follow the same retention rule. Ask your compliance lead whether any span attribute falls under an erasure obligation.
Authenticate Collector receivers
A Collector receiver that accepts spans from any network host can be flooded with fake data or used to inject misleading traces. Require authentication on each receiver, for example with an OIDC authenticator extension, and limit which networks can reach the port. Confirm the authentication method with the component README for your Collector version.
Protect Collector exporter credentials
When the Collector sends spans to ClickHouse or another backend, it holds credentials that grant write access to the trace store. Use a dedicated ClickHouse user with insert-only rights to the trace tables, and supply the password from a secret store rather than the Collector file. The oauth2client extension can add credentials to outgoing requests if your backend uses token-based access.
Restrict who can read the trace store
Span tables can reveal query text, service topology and internal hostnames, which is useful to attackers as well as engineers. Grant read access through roles and row policies rather than the default user, and review who can open trace dashboards. Access rights should follow the same model as the rest of your ClickHouse access control.
Keep Collector and ClickHouse supported
Trace pipelines break quietly when a component falls out of support, and a ClickHouse version outside its security window exposes the same store. Track the Collector release cadence and the ClickHouse supported version list, and plan upgrades in a non-production environment first. Confirm the supported versions with the vendor before setting a date.
Questions teams ask before tracing ClickHouse
How much effort does tracing ClickHouse queries with OpenTelemetry take?
Effort depends on how much of the application estate needs to propagate trace context, how many services and clients send queries, how the Collector is laid out, and which backend stores the spans. Instrumenting one service that calls ClickHouse through a single driver is a small task. Covering dozens of services in several languages, plus agent and gateway Collector tiers, is a larger one. Ask any partner to break the quote into instrumentation coverage, Collector topology, ClickHouse server configuration, backend setup and validation, so each driver of cost is visible.
Can our own team do this without a partner?
Yes, if the team has engineers who know their application frameworks, can edit ClickHouse server configuration, and can operate a Collector. The skills needed are OpenTelemetry instrumentation for each language in use, Collector configuration, basic ClickHouse administration, and enough SQL to query the span data and join it to system tables. OpenTelemetry trace APIs and SDKs are stable in most languages, including Go, Java, Python and .NET, but log SDK maturity varies, so check the status of each language your teams use. A partner is most useful when the team lacks capacity or wants a tested pattern delivered.
How long does a tracing project take, and what does the timeline depend on?
No duration can be stated here without a written scope. The timeline depends on the number of services to instrument, the languages involved, whether the ClickHouse cluster needs configuration changes, the Collector layout, the storage backend, and how many approvals and test windows the organization requires. Some teams trace one application end to end first, then extend coverage. Ask for milestones that separate a proof of concept on a test environment from production rollout, and for the dependencies each milestone has on your change calendar.
What do we need to provide for the work to go well?
The work needs access to the applications that will be instrumented, including source repositories or build pipelines, and to the ClickHouse servers and their configuration files. It also needs a test environment with realistic traffic, so trace volume and query patterns can be checked before production. Collector hosts or Kubernetes namespaces, network paths from applications to the Collector and from the Collector to the trace store, and named owners who can approve configuration changes are also required. Confirm credential handling for the Collector with your security team early, since the Collector can authenticate incoming connections and add credentials to exporter requests.
Do stored spans become expensive at scale?
Spans can add significant storage and query load, and the cost grows with request volume, the number of spans per request, the attributes attached to each span, and retention length. Sampling, attribute discipline and retention policy are the main levers. Before choosing them, measure the span rate in a test environment, estimate the size of each stored row on your own data, and decide which spans must be kept in full for incident investigation. Ask any partner to show the assumptions behind a storage estimate, because no general figure applies to every workload.
Which Collector topology should we choose, agent or gateway?
An agent runs next to each service and forwards telemetry, while a gateway accepts data from many sources and sends it on, so the choice is a trade-off between local buffering and central control. Many deployments combine both. The right layout depends on how many services exist, where they run, how sampling decisions are made, and whether one team must control exports for everyone. The Collector is vendor-agnostic and is released with signed artifacts and SBOM files, which helps when comparing distributions for your environment.
Which backend stores the spans, and how does it connect to ClickHouse?
OpenTelemetry is not a backend, so you must choose a system that stores and visualizes traces. ClickHouse can be that store, or it can be one part of a broader observability stack such as ClickStack. Decide this before instrumenting, because the schema, retention and query patterns follow from it. Confirm with the backend vendor or project which ClickHouse versions it supports, and plan its upgrades alongside the ClickHouse upgrade path, since both affect how trace data is queried and kept.
How DeployOpen scopes and delivers ClickHouse tracing
DeployOpen plans, implements, documents and supports self-hosted open-source platforms inside your own infrastructure, including the OpenTelemetry Collector pipeline and ClickHouse trace storage that connect application traces to query execution. Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.
How we scope
- We map which services emit spans, which ClickHouse clusters receive queries, and where trace context is lost today.
- The written scope names the Collector deployment model, trace storage schema, retention period and the dashboards or queries to be delivered.
- We confirm with your team which ClickHouse version and OpenTelemetry Collector release line your environment will run before build starts.
How we build
- We configure ClickHouse trace logging and span storage in a pre-production environment that carries representative query volume.
- We wire Collector receivers, processors and exporters in your cluster, and check that application spans link to ClickHouse query spans.
- We test sampling, ingestion overhead and trace completeness against the acceptance criteria written into the scope.
How we hand over
- You receive configuration as code, runbooks for Collector and ClickHouse trace tables, and a record of the tests that were run.
- We walk your platform and on-call engineers through how to find a slow request from an application span into its ClickHouse query.
- Documentation covers version pins, upgrade notes and the known limits of each OpenTelemetry component as stated in its README.
How we support
- Ongoing support, Collector and ClickHouse upgrades, and continuous testing are available as retainers after handover.
- Retainer scope is agreed in writing and can cover changes to sampling, retention and dashboards as your services change.
- Ask for a quote built from the written scope, which sets out one-time build work separately from recurring support.
