LogisterDocumentationClickHouse
Documentation / ClickHouse

Move high-volume dashboard analytics to ClickHouse safely.

ClickHouse is optional. Use it when cross-app analytics should stop competing with normal PostgreSQL work. The performance benefit begins only after coverage passes and the activation mode changes from temporary dual_write to read_preferred.

When to enable it

Stay on PostgreSQL first, then complete the ClickHouse cutover when analytics volume justifies it.

You do not need ClickHouse to ingest events, group errors, show activity, track monitors, or use project pages at modest volume. Add it when cross-app overview graphs become slow, analytics queries compete with normal app requests, or you need a dedicated store for heavier event and span analysis.

Keep ClickHouse off when...Complete the ClickHouse cutover when...
You are evaluating Logister or monitoring a small number of services.Overview graphs take seconds or minutes because PostgreSQL is aggregating a large event window.
You only need the inbox, activity feed, performance summaries, and monitor pages at modest volume.Analytics queries compete with ingestion, project pages, or other product database work.
You do not have an operator ready to own ClickHouse backups, schema, and health checks.You already operate ClickHouse or can use managed ClickHouse Cloud.

Activation modes

Choose the mode by where supported dashboards should read analytics.

Logister keeps PostgreSQL as the durable product database in every mode. The mode controls the secondary ClickHouse copy and the read path for supported analytics such as the cross-app dashboard explorer.

ModeClickHouse writesSupported analytics readsUse it for
disabledOffPostgreSQLEvaluation and modest event volume.
dual_writeOnPostgreSQLTemporary schema setup, backfill, and coverage verification.
read_preferredOnClickHouse, with PostgreSQL fallback on a ClickHouse query failureNormal operation after the coverage gate passes.

Dual write does not speed up dashboards

It proves the secondary copy without trusting it for reads. If ClickHouse is healthy but overview graphs are still slow, inspect the active mode. A deployment left on dual_write will continue running those aggregates in PostgreSQL.

Do not skip the coverage gate

A reachable ClickHouse server can still be incomplete. Keep dual write active until the idempotent event backfill finishes and Admin → Installation → ClickHouse reports matching stable-window coverage, then switch to read_preferred.

Insights

Project Insights remains PostgreSQL-first; the cross-app overview can use ClickHouse.

The project Insights workspace uses PostgreSQL indexes, bounded catalog sampling, and short Redis cache windows for its live UI. You can use Insights without ClickHouse, including time-window charts, environment and release filters, custom attribute filters, metric catalog selection, and recent matching events.

The cross-app dashboard explorer is a separate supported ClickHouse read path. In read_preferred, its event totals, timeline, type, project, and environment aggregates query ClickHouse. ClickHouse also remains useful for longer-range raw analytics, high-volume custom reporting, and materialized rollups such as events_1m and request_spans_1m.

Configuration

Use the admin page or set equivalent environment overrides.

Open Admin → Installation → ClickHouse to save encrypted connection settings, run the readiness check, and control the activation mode. If a nonblank environment variable is present, the page names it and keeps the UI value as an editable fallback. See the onboarding guide's ClickHouse walkthrough for the matching admin screen and the full first-run sequence.

shell
# Begin with dual_write only for setup and backfill.
LOGISTER_CLICKHOUSE_MODE=dual_write
LOGISTER_CLICKHOUSE_URL=http://127.0.0.1:8123
LOGISTER_CLICKHOUSE_DATABASE=logister
LOGISTER_CLICKHOUSE_EVENTS_TABLE=events_raw
LOGISTER_CLICKHOUSE_SPANS_TABLE=spans_raw
LOGISTER_CLICKHOUSE_USERNAME=default
LOGISTER_CLICKHOUSE_PASSWORD=
# Optional release-only DDL credentials
LOGISTER_CLICKHOUSE_MIGRATION_USERNAME=logister_schema
LOGISTER_CLICKHOUSE_MIGRATION_PASSWORD=
LOGISTER_CLICKHOUSE_FAILURE_THROTTLE_SECONDS=60
REDIS_URL=redis://127.0.0.1:6379/0

LOGISTER_CLICKHOUSE_URL can point at either a native ClickHouse HTTP endpoint or the ClickHouse Cloud Query API endpoint. Redis is included because ClickHouse writes are queued through the background worker. The runtime user needs insert and query access. The optional migration user needs CREATE, ALTER, and DROP VIEW access and is used only for schema repair.

Remove the legacy boolean before cutover

LOGISTER_CLICKHOUSE_ENABLED=true maps only to dual_write; it cannot enable ClickHouse reads. Replace it with LOGISTER_CLICKHOUSE_MODE=read_preferred after coverage passes. Environment variables override values saved in the admin UI, so an old provider secret can silently keep dashboards on PostgreSQL.

Schema setup

Create missing objects and repair compatible schema drift.

  • Canonical raw event/span tables and materialized views: docs/clickhouse_schema.sql
  • Starter dashboard queries: docs/clickhouse_dashboard_queries.sql
shell
bin/rails logister:clickhouse:schema:status
bin/rails logister:clickhouse:schema:repair
bin/rails logister:clickhouse:schema:status

The repair task is idempotent: it creates missing objects, checks the event-type enum on the raw table, rollup table, and materialized view, repairs compatible drift, then verifies readiness. The existing logister:clickhouse:schema:load command runs the same create-and-repair path. bin/release always invokes repair; it is a no-op while ClickHouse is disabled, including when the activation mode is stored in PostgreSQL.

A failed repair stops the release

Keep the previous application release active, correct ClickHouse permissions or incompatible schema changes, and rerun the release. Logister does not mark a partial analytics schema ready.

Manual SQL option

If you operate ClickHouse outside the Rails host, you can still apply docs/clickhouse_schema.sql directly with your ClickHouse client. Afterward, run the repair and status tasks or call /health/clickhouse to verify the app can see the same schema.

Backfill and cutover

Copy retained events, pass coverage, and finish the read cutover.

The backfill checks event IDs already present in ClickHouse before each bounded insert batch, so interrupted runs can be repeated. It does not delete PostgreSQL data or backfill trace spans.

shell
# Optional ISO-8601 from/to bounds are task arguments.
CONFIRM=backfill bin/rails 'logister:clickhouse:events:backfill[2026-01-01,2026-07-01]'
  1. Save dual_write and restart web and worker processes.
  2. Run schema repair and keep Sidekiq draining new writes.
  3. Run the retained-event backfill. Repeat the same command if it is interrupted.
  4. In Admin → Installation → ClickHouse, run Test candidate values. The check excludes the newest five minutes, compares distinct event IDs over the preceding 24 hours, and records a redacted fingerprint.
  5. Save read_preferred. Logister rejects that mode if the current connection fingerprint has not passed.

If the admin page owns the setting, select and save read_preferred. If your deployment provider owns it, set LOGISTER_CLICKHOUSE_MODE=read_preferred, remove any legacy LOGISTER_CLICKHOUSE_ENABLED override, and restart web and worker processes. Then call /health/clickhouse and confirm the response reports "clickhouse_mode":"read_preferred".

Worker path

Keep Sidekiq running before you rely on ClickHouse.

Accepted events and spans queue background writes. If the web app can reach ClickHouse but the worker is not running, new telemetry can still appear in the normal Logister UI while ClickHouse stays empty. When self-observability is configured with LOGISTER_API_KEY and LOGISTER_ENDPOINT, ClickHouse write failures are reported through logister-ruby as throttled logs and count metrics so one outage does not create an internal event storm.

shell
bundle exec sidekiq -C config/sidekiq.yml

Health and active mode

Verify connectivity, schema readiness, and the read mode separately.

  • GET /health/clickhouse returns 200 when ClickHouse is disabled.
  • GET /health/clickhouse returns 200 when ClickHouse is enabled, reachable, and the required schema is present.
  • GET /health/clickhouse returns 503 when ClickHouse is enabled but unreachable, missing required objects, or using an outdated event-type enum. The response identifies missing tables and schema issues when Logister can determine them.

A healthy response does not prove reads moved

Read the clickhouse_mode field. dual_write means connectivity and writes may be healthy while supported dashboards still query PostgreSQL. Expect read_preferred after a completed cutover.

After the health check passes, send a test event and span, confirm the worker writes them, run the stable-window coverage check, and verify the active mode before relying on ClickHouse for reporting.

What to query

Use ClickHouse for raw event analytics, root span rollups, and custom reporting.

The starter SQL creates events_raw, spans_raw, events_1m, request_spans_1m, and matching materialized views. The most useful query dimensions usually match the context your integrations send: project, event type, environment, release, service, exception class, transaction name, span kind, route, tags, and the original context JSON.

QuestionUseful fields
Which services are producing the most errors?Project, service, event type, exception class, release.
Which releases changed event volume?Release, event type, occurred time, environment.
Which transactions are slowest?Transaction name, duration, status, route, service.
Which request or page-load roots are slowest?spans_raw or request_spans_1m, trace ID, route, span kind, duration, service.
Which custom tags matter most?Tags plus the full context JSON for app-specific dimensions.