Skip to content

Replace ClickHouse with embedded DuckDB - #1

Closed
SrihariLegend wants to merge 5 commits into
mainfrom
feat/duckdb-port
Closed

Replace ClickHouse with embedded DuckDB#1
SrihariLegend wants to merge 5 commits into
mainfrom
feat/duckdb-port

Conversation

@SrihariLegend

@SrihariLegend SrihariLegend commented Aug 29, 2026

Copy link
Copy Markdown
Owner

Purpose / Description

Replace ClickHouse with embedded DuckDB as the analytics store (session telemetry, audit/security logs, webhook deliveries, layer snapshots, session rollups). Motivation: remove the ClickHouse dependency (Yandex lineage), shrink the deployment profile (one less container, no 2GB RAM budget, no extra image), and improve query latency for the dashboard/trace workloads.

DuckDB is embedded in the API process: no server, no HTTP hop, no credentials. Backup becomes "copy one file".

Fixes

  • N/A — no tracking issue (dependency replacement / supply-chain decision)

Approach

  • observal-server/services/duckdb/ — the real implementation:
    • client.py: process-wide connection (RLock + asyncio.to_thread), _query() returning an httpx-shaped QueryResult, and a compatibility layer that auto-translates legacy ClickHouse syntax ({name:Type} placeholders, Array(...) params, FINAL, FORMAT JSON, trailing SETTINGS, count(), now64(3), toUInt64, INTERVAL {n:Type} UNIT). Unused named params are dropped (DuckDB rejects them; legacy callers pass them).
    • insert.py: batch inserts via Arrow relations — DuckDB's executemany and row-at-a-time INSERT OR REPLACE against ART primary keys are pathologically slow (~200–2k rows/s measured); the Arrow scan + set merge path sustains ~30k rows/s. ReplacingMergeTree semantics preserved via PRIMARY KEY + INSERT OR REPLACE (write-time dedup — no FINAL anywhere).
    • migrations.py, schema.py: versioned SQL runner (duckdb/migrations/001_baseline.sql consolidates legacy CH migrations 001–004), runtime init, resource pragmas (memory_limit, threads).
  • services/clickhouse/ — deprecated shim re-exporting the DuckDB implementation under legacy names; all ~31 callers work unchanged.
  • Retention: DuckDB has no table TTL, so the retention cron now explicitly applies the legacy DDL TTLs (730d audit/security logs, 30d raw_line scrub).
  • Topology: DuckDB is single-process read-write → API_WORKERS=1 and the arq worker runs embedded in the API process (EMBEDDED_WORKER=true, default); the separate worker service and the ClickHouse service are removed from compose.
  • Migration jobs: ClickHouse export/import scopes are rejected with a clear error (DuckDB archive export/import is follow-up work).
  • Deferred (documented in docs/duckdb-analytics.md): observal_cli installer, server-package compose, Grafana dashboards.

How Has This Been Tested?

  • Unit/integration: new live-engine suites tests/test_duckdb_analytics.py (19 tests: migrations idempotency, write-time dedup, checkpoint rewind, rollup correctness, retention TTLs, concurrency, compat layer) and tests/test_duckdb_resource_tuning.py (11 tests, live pragma verification). Root suite: 8,235 passed, 0 failed; server-suite failure set byte-identical to main (12 pre-existing failures).
  • Live E2E: booted Postgres 18 + Redis 8 + API; login → session ingest (5 JSONL lines) → replay/summary/dashboard endpoints → re-ingest dedup (skipped: 5, still 1 session) → live audit trail → embedded arq cron firing. One real integration bug found and fixed (arq.create_worker import + signal handling).
  • Full docker compose stack of this branch ran healthy for hours with live WAL activity; Playwright E2E suite run against it — all failures traced to pre-existing spec drift (missing /telemetry/hooks endpoint, missing fixture users, nonexistent DELETE routes), zero failures attributable to the port.
  • Benchmark (scripts/bench_duckdb_vs_clickhouse.py, reproducible): 1M session events + 500k audit rows, vs a real ClickHouse 26.6 container:
Metric ClickHouse DuckDB Δ
Ingest 19.9k rows/s 30.2k rows/s 1.5× faster
Session replay p50 5.2ms 3.0ms 1.7× faster
Dedup range scan p50 5.1ms 2.4ms 2.1× faster
Dashboard rollups p50 9.6–10.8ms 1.3–1.8ms 7–8× faster
Recent activity p50 9.7ms 0.9ms 10× faster
Audit 7d scan p50 4.8ms 3.5ms 1.4× faster
Storage (1M events + 500k audit) 110MB 347MB CH 3.2× smaller (ZSTD vs FSST)

To reproduce: docker run -p 18123:8123 -e CLICKHOUSE_PASSWORD=clickhouse clickhouse/clickhouse-server:26.6, then from observal-server/: DUCKDB_PATH=/tmp/bench.duckdb python ../scripts/bench_duckdb_vs_clickhouse.py --sessions 2000 --events 500 --audit-rows 500000.

Screenshots (live stack on this branch, real ingested session — a Pi coding session with 1.2M input tokens / 361 tool calls):

Executive dashboard off DuckDB rollups:
dashboard

Traces list with populated token/tool columns:
traces

Trace detail — session replay with dedup ("953 events, deduped from 954"):
trace detail

Audit log writing to and reading from DuckDB:
audit log

Learning (optional, can help others)

  • DuckDB's Python executemany is a per-row prepared-statement loop (~2k rows/s) and row-at-a-time INSERT OR REPLACE on ART-indexed tables is ~200 rows/s; staging via pyarrow relations + one set-merge is the fast path (30k+ rows/s).
  • ClickHouse FINAL (merge-on-read for ReplacingMergeTree) is replaced by PRIMARY KEY + write-time dedup; readers get exact ART point-lookups instead of bloom-filter-assisted granule scans.
  • DuckDB FSST string compression is ~3–4× on repetitive JSON vs ClickHouse ZSTD(3) at ~18× — the one regression; acceptable at this workload's scale.
  • DuckDB rejects unused named parameters and binds Array params differently than ClickHouse HTTP; the compat layer handles both.
  • DuckDB is single-process read-write, which is why the arq worker moved in-process (EMBEDDED_WORKER).

Checklist

  • You have a descriptive commit message with a short title (first line, max 50 chars).
  • You have commented your code, particularly in hard-to-understand areas
  • You have performed a self-review of your own code
  • UI changes: include screenshots of all affected screens (in particular showing any new or changed strings)

Licenses

Library Description License
duckdb Embedded analytical database (Python client) MIT License
pytz Required by duckdb for TIMESTAMPTZ→datetime conversion MIT License

AI Assistance

Was generative AI tooling used to co-author this PR?

  • Yes(Please Specify the tool): Pi coding agent — drafted the port, tests, and benchmark under human direction
  • Was the generated code manually reviewed and tested?

Discord username (optional)

Discord username:

@SrihariLegend
feat(analytics): replace ClickHouse with embedded DuckDB
a2eae64 
The analytics store (session telemetry, audit/security logs, webhooks,
layer snapshots, session rollups) moves from ClickHouse to embedded
DuckDB. No container, no HTTP hop, no Yandex lineage.

- services/duckdb/: connection+query client (httpx-shaped QueryResult,
  legacy-syntax compat layer), Arrow batch inserts with INSERT OR REPLACE
  write-time dedup (replaces ReplacingMergeTree + FINAL), versioned SQL
  migrations, runtime init + resource pragmas
- services/clickhouse/: deprecated shim re-exporting the DuckDB
  implementation; all existing callers work unchanged
- duckdb/migrations/001_baseline.sql: consolidated final schema
- retention cron now owns the former DDL TTLs (730d audit/security,
  30d raw_line scrub); maintain job becomes CHECKPOINT
- deploy: API_WORKERS=1, arq worker embedded in the API process
  (EMBEDDED_WORKER=true), ClickHouse service removed from compose
- migration jobs: ClickHouse scopes rejected with a clear error
  (DuckDB archive export/import is follow-up)
- tests: new live-engine suites (test_duckdb_analytics,
  test_duckdb_resource_tuning); 8232 root tests pass, server suite
  failure set identical to main (all pre-existing)
- scripts/bench_duckdb_vs_clickhouse.py: reproducible benchmark

Benchmark (1M session events + 500k audit rows, this machine):
  ingest:   DuckDB 30.2k rows/s vs ClickHouse 19.9k rows/s (1.5x)
  queries:  DuckDB p50 0.9-3.5ms vs CH p50 4.8-10.8ms (1.4x-10x)
  storage:  CH 110MB vs DuckDB 347MB (CH 3.2x smaller, FSST vs ZSTD)

Follow-ups: observal_cli installer, server-package compose, Grafana
dashboards, DuckDB archive export/import for migration jobs.
@SrihariLegend
fix(analytics): handle NULL aggregates and DuckDB type differences
348ba1d
@SrihariLegend
fix(analytics): repair DuckDB Array placeholder binding and alert/pol…
bec0f53 
…icy regressions

Three product bugs found in the ClickHouse→DuckDB port, none caught by the
test suite (it mocked _query or used the same broken translation):

- Array(String) placeholders returned empty results: legacy callers bind
  stringified literals ("['s1','s2']") which DuckDB treated as one scalar
  VARCHAR, so IN ($ids) silently matched nothing. The compat layer now
  parses such literals back into real Python LIST params and rewrites
  IN ($name) to = ANY($name) (NOT IN -> <> ALL). Fixes silently-empty
  user profiles (tools/languages), version-impact "why" analysis, and
  insight transcript fetches. Real-list params keep working too.
- alert_evaluator._query_token_usage: DuckDB sum() over an empty window is
  NULL (ClickHouse returned 0), so float(None) aborted the query and
  below-threshold token_usage alerts never fired on zero usage. The sum is
  now wrapped in coalesce(..., 0).
- QueryResult.json() now emits the ClickHouse `rows` key alongside `data`,
  so api/routes/admin/policy.py security-events `total` is no longer
  always 0.

Also declare pytz: duckdb's Python client requires it to convert
TIMESTAMP WITH TIME ZONE values (bare now()/current_timestamp) back to
Python datetimes; without it every such query raised "Required module
'pytz' failed to import".

Verified end-to-end against a live DuckDB file (migrations → inserts →
refresh → user_profile/version_impact/session_meta_extractor queries →
token_usage alerts → json() rows → bare now()), plus 4 new regression
tests in tests/test_duckdb_analytics.py. Root suite: 8540 passed.
@SrihariLegend
fix(e2e): clean up Grafana observability config and repair E2E test s…
5dfc5dc 
…pecs

- Remove ClickHouse plugin and dependency from docker-compose.observability.yml
- Fix syntax error in review diff screenshots E2E test payload
- Fix doctor CLI command invocation in Kiro E2E test
- Add .dirac-cache to .gitignore
@SrihariLegend
fix(analytics): residual NULL-aggregate guards and init-container DUC…
6bfc4b3 
…KDB_PATH

- version_impact: guard avg_* float() conversions against NULL (DuckDB
  aggregates are NULL over NULL-only groups, e.g. all-rendered=0 sessions;
  ClickHouse returned 0)
- compose: observal-init runs the DuckDB migrations and must share the
  API's DUCKDB_PATH explicitly - it has SKIP_DDL_ON_STARTUP=true, so a
  missing/mismatched path would boot the API against an unmigrated file
@github-actions github-actions Bot added new contributor Pull request from a first-time contributor env-changed PR modifies .env.example; check dependent config files server Pull request touches server code tests Pull request adds or modifies tests labels Aug 29, 2026
@github-actions

github-actions Bot commented Aug 29, 2026

Copy link
Copy Markdown

⚠️ .env.example was modified

New or changed environment variables detected. Please verify the following are updated accordingly:

This is an automated reminder. If none of the above apply to your change, you can safely ignore this comment.

@SrihariLegend

SrihariLegend commented Aug 29, 2026

Copy link
Copy Markdown
Owner Author

Superseded by upstream PR: Observal#1706

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

env-changed PR modifies .env.example; check dependent config files new contributor Pull request from a first-time contributor server Pull request touches server code tests Pull request adds or modifies tests

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

1 participant

@SrihariLegend