Migrate Observability from TimescaleDB to ClickHouse

Temps stores all of its observability and analytics data in PostgreSQL/TimescaleDB by default — no extra services, single binary. When your traces, metrics, proxy logs, or analytics events outgrow TimescaleDB, you can route them to a ClickHouse cluster at runtime by setting a few environment variables on temps serve. There is no rebuild, no cargo feature flag, and no data-loss window: the ClickHouse backends are always compiled in and stay dormant until you configure them.

When to migrate

TimescaleDB is the right default for the vast majority of self-hosted deployments and needs no tuning. Reach for ClickHouse when you hit columnar-scale workloads that a row store struggles with:

• Millions of traces where the list view's per-request GROUP BY trace_id and duration sort over the spans hypertable becomes slow.
• High-cardinality metrics with long retention and heavy dashboard fan-out.
• High-volume proxy/request logs (hundreds of thousands to millions of rows) queried with wide time windows and many filters.
• Large analytics-event volumes where uniq()-based approximate counts and columnar scans beat exact PostgreSQL aggregation.

If you are not hitting these limits, stay on TimescaleDB — it is one less service to run.

What changes

Four independent domains can be backed by ClickHouse. They all activate from the same four TEMPS_CLICKHOUSE_* variables and share one ClickHouse database (temps by default), but each owns its own tables and migration-tracking table so they never collide.

The write model is the most important distinction for planning your migration, because it determines whether your existing data comes along automatically — see Historical data & backfill.

Prerequisites

• A reachable ClickHouse instance or cluster (self-hosted or managed) speaking the HTTP protocol (default port 8123).
• A ClickHouse user with permission to CREATE DATABASE, CREATE TABLE, and read/write within the target database. Temps bootstraps its own schema on first connect.
• Network reachability from the host running temps serve to the ClickHouse HTTP endpoint.
• Your existing PostgreSQL/TimescaleDB instance, untouched — it remains the system of record for analytics and the fallback for all four domains.

Step 1 — Stand up ClickHouse

Run a ClickHouse server reachable over HTTP. A minimal Docker example for evaluation:

docker run -d --name temps-clickhouse \
  -p 8123:8123 -p 9000:9000 \
  -e CLICKHOUSE_USER=temps \
  -e CLICKHOUSE_PASSWORD=your-clickhouse-password \
  -e CLICKHOUSE_DB=temps \
  clickhouse/clickhouse-server:latest

You do not need to create any tables by hand. On first connect, each Temps backend issues CREATE DATABASE IF NOT EXISTS for the target database and applies its own idempotent ClickHouse schema migrations — tracked per-domain in its own _temps_ch_*_migrations table.

Step 2 — Configure Temps

Set the ClickHouse variables on the process running temps serve:

export TEMPS_CLICKHOUSE_URL=http://clickhouse:8123
export TEMPS_CLICKHOUSE_USER=temps
export TEMPS_CLICKHOUSE_PASSWORD=your-clickhouse-password
# Optional — defaults to "temps" when TEMPS_CLICKHOUSE_URL is set:
export TEMPS_CLICKHOUSE_DATABASE=temps

Fail-closed activation

The ClickHouse backends activate only when the URL, user, and password are all present and non-empty (the database name fills in automatically). A variable that is set but empty counts as unset, so a half-configured operator never silently loses data — Temps simply stays in the default TimescaleDB-only mode. There is no partial-ClickHouse state.

Step 3 — Restart and verify

Restart temps serve so it reads the new environment. On boot, for each enabled domain Temps will:

1. CREATE DATABASE IF NOT EXISTS for the target database (run against the always-present default database so the session never depends on a database that doesn't exist yet).
2. Apply the embedded ClickHouse schema migrations, tracked per-domain in its own _temps_ch_*_migrations table.
3. Swap the read (and, for the swap-model domains, write) path to the ClickHouse backend.

Migrations run on background tasks so plugin init does not block on remote calls. If a migration fails, the error surfaces on the first query for that domain rather than crashing startup.

Confirm it's live

Check the server logs for the activation lines, for example:

ClickHouse OTel backend enabled (ADR-016) — applying migrations url=http://clickhouse:8123 database=temps
ClickHouse OTel migrations applied applied=["0001_spans"] skipped_count=0
Proxy/request logs: ClickHouse backend enabled (TEMPS_CLICKHOUSE_* configured)

Then query ClickHouse directly to confirm the schema and that new data is flowing:

# Tables exist in the shared database
clickhouse-client -q "SHOW TABLES FROM temps"
# Expect: events, events_5m_mv, sessions, spans, service_metrics, proxy_logs,
#         and the four _temps_ch_*_migrations tracking tables.

# New telemetry is landing (run after generating some traffic)
clickhouse-client -q "SELECT count() FROM temps.spans"
clickhouse-client -q "SELECT count() FROM temps.proxy_logs"
clickhouse-client -q "SELECT count() FROM temps.service_metrics"
clickhouse-client -q "SELECT count() FROM temps.events FINAL"

Finally, open the console — the Observe, Analytics, Monitoring, and traces views now read from ClickHouse. They should render identically; the data just comes from the columnar store.

Historical data & backfill

This is where the two write models diverge. Read this section before you migrate so you know what comes along.

Analytics events — history replicates automatically

Analytics events dual-write: record_event always writes to PostgreSQL synchronously (the system of record), then enqueues the event id into an events_ch_outbox table (INSERT ... ON CONFLICT (event_id) DO NOTHING). A background fan-out worker drains that outbox into ClickHouse, claiming batches with FOR UPDATE SKIP LOCKED.

Because the outbox is a queue, existing analytics events replicate to ClickHouse as the worker catches up after you enable the backend. Retries are safe — the events table uses ReplacingMergeTree(_version) and dedupes over its sort key, so re-delivering an event collapses to one row.

Traces, metrics, and proxy logs — new data only

These three domains do not dual-write. When ClickHouse is enabled, the backend is chosen at construction time: new writes go to ClickHouse instead of TimescaleDB. There is no automatic backfill of the spans, service_metrics, or proxy_logs rows already sitting in TimescaleDB.

In practice this means:

• After you switch on ClickHouse, the traces / metrics / proxy-log views show data ingested from that point forward.
• Your historical TimescaleDB data is not deleted — it stays in the hypertables and ages out under its existing 90-day retention policy. It is simply no longer the backend the console reads from for those domains.

TimescaleDB trace summaries

Even after the ClickHouse trace backend is enabled, Temps continues to write trace summary rows — root-span or per-trace aggregates — into TimescaleDB alongside the full span payload going to ClickHouse. This is intentional and serves two purposes:

• Rollback continuity. If you revert to the TimescaleDB backend, the summary rows give the Observe page enough anchor data to render trace correlation columns without a cold-start gap.
• Observe page correlation. The unified Observe page joins events, errors, and traces using summary-level columns (trace ID, duration, root service, status). During the mixed-backend period, those join columns are sourced from TimescaleDB summaries regardless of where the full span payload lives.

The summary rows are lightweight — they do not duplicate full span attributes or events. Full-detail trace inspection always queries whichever backend holds the span payload (ClickHouse when enabled). You can safely ignore the TimescaleDB summary rows in terms of storage cost; they are pruned under the same 90-day retention policy as the rest of the TimescaleDB hypertables.

Settings page

The Temps console exposes a Settings surface (under Settings → Observability or your instance's equivalent path) that shows the effective storage backend for each observability domain at a glance. It is the canonical place to confirm that your environment-variable configuration was picked up correctly after a restart.

The Settings page surfaces:

• Effective backend per domain — whether each of the four domains (analytics events, traces, resource metrics, proxy logs) is currently reading and writing from TimescaleDB or ClickHouse.
• Misconfiguration warnings — if one or more of the required ClickHouse variables (TEMPS_CLICKHOUSE_URL, _USER, _PASSWORD) are present but incomplete, the Settings page shows a warning banner explaining which variable is missing. This surfaces the fail-closed activation state described in Step 2 without requiring you to read server logs.
• Mixed-backend indicator — during the window where trace summaries are being sourced from TimescaleDB while full spans land in ClickHouse, the Settings page marks the traces domain as "mixed" so operators know the split is expected and intentional, not a configuration error.

Data-model hardening

The ClickHouse backends shipped with several schema hardening changes that were applied alongside the initial implementation. You do not need to run these manually — they are idempotent migrations applied by temps serve on boot — but knowing about them is useful when inspecting the ClickHouse tables directly or when troubleshooting query anomalies.

Nullable column fixes

Several columns that were initially declared NOT NULL in the ClickHouse DDL carried implicit zero/empty-string defaults that masked missing telemetry fields. Those columns were corrected to be nullable where the originating OTLP schema permits absent values. Affected examples include optional span attributes and resource label columns in the spans table. If you are querying these columns directly, use IS NOT NULL guards rather than checking for empty strings.

Index additions

The spans, service_metrics, and proxy_logs tables received additional secondary indexes (ClickHouse INDEX ... TYPE bloom_filter or minmax where appropriate) on high-cardinality filter columns such as service name, HTTP status code, and deployment ID. These indexes are picked up automatically by the query planner and reduce scan sizes for the filtered queries issued by the console.

ReplacingMergeTree key corrections

The events and spans tables use ReplacingMergeTree for idempotent re-delivery. An earlier schema revision had an incorrect sort-key ordering that caused the FINAL keyword (used in read queries to collapse duplicates) to be less effective than intended. The corrected sort key ensures that duplicate rows introduced by fan-out retries (analytics) or at-least-once OTLP delivery (traces) collapse efficiently. Existing data in a table with the old sort key is unaffected at the data level — duplicates are still resolved by FINAL — but you may see a one-time compaction spike after the migration applies as ClickHouse re-sorts the existing parts.

Behavior differences

The ClickHouse backends are designed to be drop-in, but a few behaviors differ by design:

• Approximate unique counts. The analytics-events backend uses uniq() (HyperLogLog), accurate to within ~1% at scale, instead of the exact COUNT(DISTINCT) used by PostgreSQL.
• Self-referral filter is PostgreSQL-only. The referrer-hostname self-referral filter checks the project_custom_domains table, which is not replicated to ClickHouse, so a referrer breakdown on the ClickHouse backend may include your own domain.
• Visitor/session analytics stay on PostgreSQL. Only the analytics events read path is backed by ClickHouse; the separate visitor/session path always uses PostgreSQL.
• Geolocation is denormalized. Country/region/city are written onto each ClickHouse event row at fan-out time, so breakdowns group on plain columns with no cross-database join.
• Deduplication semantics. ClickHouse tables use ReplacingMergeTree; reads that must be exact use FINAL to collapse duplicates before aggregating.
• Trace summary rows remain in TimescaleDB. The Observe page correlation columns (trace ID, root service, duration, status) are sourced from TimescaleDB summary rows even while full spans live in ClickHouse. This is expected — see TimescaleDB trace summaries. The Settings page marks the traces domain as "mixed" during this window so the split is visible to operators.
• Settings page warning states. If the Settings page shows a "misconfiguration" warning for one domain but not others, the most common cause is that the relevant TEMPS_CLICKHOUSE_* variable is present but empty. A warning state does not indicate data loss — it means that domain is still running on TimescaleDB (fail-closed) while Temps waits for a complete configuration.

Rollback

Rolling back is symmetric with enabling and loses no system-of-record data:

1. Unset TEMPS_CLICKHOUSE_URL, TEMPS_CLICKHOUSE_USER, and TEMPS_CLICKHOUSE_PASSWORD (and TEMPS_CLICKHOUSE_DATABASE if you set it).
2. Restart temps serve.

Temps reverts every domain to the PostgreSQL/TimescaleDB path with no other changes. For analytics events, PostgreSQL was the system of record the entire time, so nothing is lost. For traces / metrics / proxy logs, the console reads from TimescaleDB again — the data ingested while ClickHouse was enabled lives in ClickHouse and will not appear in the TimescaleDB-backed views (mirror of the forward-cutover gap).

Troubleshooting

Related pages

• Observability storage and retention — measure telemetry growth and review retention, compression, sampling, and quota controls.
• Environment Variables Reference — the full TEMPS_CLICKHOUSE_* reference and fan-out internals.
• OpenTelemetry (OTLP) Ingest & Query — how traces, metrics, and logs are ingested and queried.
• Analytics — the analytics feature reference.
• Observe — the unified observability view that reads from whichever backend is configured.
• Monitoring — resource metrics, backed by service_metrics.