ClickHouse Analytics

​

Overview

​

Why ClickHouse?

​

Tables and Schema

​

run_events

• Engine: MergeTree()
• Partition key: toDate(inserted_at) (daily partitions)
• Order key: (project_id, run_id, created_at)
• TTL: 90 days from inserted_at

​

run_analytics

• Engine: MergeTree()
• Partition key: toDate(inserted_at) (daily partitions)
• Order key: (project_id, job_id, created_at)
• TTL: 365 days from inserted_at

​

compute_usage

• Engine: MergeTree()
• Partition key: toDate(inserted_at) (daily partitions)
• Order key: (project_id, started_at)
• TTL: 365 days from inserted_at

​

Data Pipeline

​

Export flow

Engine (Postgres writes) --> Exporter.Enqueue() --> In-memory buffer --> Flush --> ClickHouse batch INSERT

1. Enqueue. When the engine completes a state transition (run started, run finished, cost recorded), it calls Exporter.Enqueue() to place the record in an in-memory buffer. This call is non-blocking and safe for concurrent use.
2. Buffer accumulation. Records accumulate in memory until one of two flush triggers fires:
   • Batch size threshold — the buffer reaches CLICKHOUSE_BATCH_SIZE records (default: 1000).
   • Flush interval — CLICKHOUSE_FLUSH_INTERVAL elapses since the last flush (default: 5 seconds).
3. Batch flush. The exporter swaps the buffer, releasing the lock immediately, then writes the batch to ClickHouse via a single INSERT statement.
4. Graceful shutdown. On process termination, the exporter performs a final flush to drain any remaining buffered records before closing.

​

Backpressure

​

Record types

​

Query Patterns

​

Time-windowed aggregations

SELECT
    toStartOfHour(created_at) AS hour,
    count() AS total_runs,
    countIf(status = 'failed') AS failures,
    avg(duration_ms) AS avg_duration_ms
FROM run_analytics
WHERE project_id = :project_id
  AND created_at >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour

​

Cost analysis by preset

SELECT
    machine_preset,
    sum(cost_microusd) / 1000000 AS total_cost_usd,
    sum(duration_secs) / 3600 AS total_hours
FROM compute_usage
WHERE project_id = :project_id
  AND started_at >= now() - INTERVAL 30 DAY
GROUP BY machine_preset
ORDER BY total_cost_usd DESC

​

Event drill-down for a single run

SELECT event_type, level, message, metadata, created_at
FROM run_events
WHERE run_id = :run_id
ORDER BY created_at ASC

​

Queue wait percentiles

SELECT
    quantile(0.50)(queue_wait_ms) AS p50,
    quantile(0.95)(queue_wait_ms) AS p95,
    quantile(0.99)(queue_wait_ms) AS p99
FROM run_analytics
WHERE project_id = :project_id
  AND created_at >= now() - INTERVAL 24 HOUR

​

Configuration

​

Operational Considerations

​

ClickHouse is optional

• The Client constructor returns nil, and all methods on a nil client are safe no-ops.
• The Exporter constructor returns nil, and Enqueue() silently returns false.
• No ClickHouse driver is loaded, no connections are opened, and no goroutines are spawned.

​

Data retention and TTL

• run_events: 90-day retention. High-volume event data is expired first to manage storage.
• run_analytics: 365-day retention. Run summaries are kept for year-over-year reporting.
• compute_usage: 365-day retention. Billing records are retained for annual cost analysis.

​

Connection pool

• Max open connections: 10
• Max idle connections: 5
• Connection max lifetime: 30 minutes

​

Performance characteristics

• Write path: Fully asynchronous. The engine never blocks on ClickHouse writes. Failed flushes are logged and the batch is discarded to prevent memory growth.
• Read path: Queries benefit from ClickHouse columnar compression and the MergeTree primary index. Project-scoped queries with time-range filters are the most efficient access pattern.
• LowCardinality optimization: Columns with a small set of distinct values (event_type, status, machine_preset, triggered_by, level, execution_mode) use LowCardinality(String), which applies dictionary encoding for reduced storage and faster GROUP BY.