# ClickHouse

> Production-grade OLAP AnalyticsSink over ClickHouse — self-hosted or ClickHouse Cloud — for billions of events with millisecond aggregates.



---

<!-- source: en/plugins/clickhouse.md -->
## ClickHouse

_Production-grade OLAP AnalyticsSink over ClickHouse — self-hosted or ClickHouse Cloud — for billions of events with millisecond aggregates._

`@voltro/plugin-clickhouse` is the production `AnalyticsSink` for analytics at
scale — native HTTP ingest into a ClickHouse cluster (self-hosted **or**
ClickHouse Cloud, both via the official `@clickhouse/client`). The sink owns an
`events` table inside the configured database (a MergeTree engine created on
first boot) and implements all four contract methods. Reach for it when
[postgres-lite](/docs/plugins/analytics-postgres) hits its ~10M events/day
ceiling and you need millisecond aggregate queries over billions of rows. For
the shared `AnalyticsSink` API and the `useAnalytics()` read methods, see
[Analytics & warehouse sinks](/docs/plugins/analytics).

## Install

```
pnpm add @voltro/plugin-clickhouse
```

## Wiring

```ts
// app.config.ts
import { clickhouseAnalytics } from '@voltro/plugin-clickhouse'

export default {
  type:      'api' as const,
  name:      'myApi',
  store:     'postgres' as const,
  analytics: clickhouseAnalytics({
    url:      process.env.CLICKHOUSE_URL!,   // https://my-cluster.clickhouse.cloud
    username: process.env.CLICKHOUSE_USER,
    password: process.env.CLICKHOUSE_PASSWORD,
    database: 'voltro_events',               // optional, default 'default'
  }),
}
```

At boot the plugin opens the client, pings the cluster (fail-fast on bad
config), and creates the events table + any mirror tables. The client is closed
on graceful shutdown.

## Options

`clickhouseAnalytics(options)`:

| Option | Type | Default | Notes |
|---|---|---|---|
| `url` | `string` | — (**required**) | HTTP URL of the ClickHouse server, incl. protocol + port. |
| `username` | `string` | — | |
| `password` | `string` | — | |
| `database` | `string` | `'default'` | The plugin creates the events table inside it on first boot. |
| `table` | `string` | `'events'` | Override the events-table name. |
| `mirrorTables` | `ReadonlyArray<string>` | `[]` (events-only) | Reactive tables to CDC-mirror into `voltro_mirror_<table>` (a `ReplacingMergeTree(version)` over `{ id, data, version, is_deleted }`) so analytical queries JOIN events against live user data. The version is the framework's commit-order stamp, so a change that arrives late loses the collapse. |
| `mirrorPrimaryKey` | `string` | `'id'` | Primary-key column on the mirrored source rows. |
| `batch` | `{ maxSize?, flushIntervalMs? } \| false` | **on** — `{ maxSize: 20, flushIntervalMs: 5000 }` | Client-side batching of `track()` inserts. `false` opts out (one immediate, confirmed insert per event). |

## Batching is on by default

One HTTP insert per event is how a MergeTree gets **part-exploded** under load —
every single-row insert creates a data part the server must merge away. So
`track()` **batches by default**: rows buffer in the process and flush in ONE
multi-row insert once 20 events are pending, 5 seconds after the first buffered
event (whichever comes first), and on graceful shutdown (the `dispose` hook
drains the buffer before the client closes).

What changes observably with batching on:

- A successful `track()` means **"buffered"**, not "ClickHouse accepted the
  row" — the insert happens later, off the call path.
- Events become visible to reads up to `flushIntervalMs` later than they were
  tracked.
- A flush failure is logged and that batch **dropped** (best-effort analytics —
  re-queuing a partially-applied insert would risk duplicates). A hard crash
  loses whatever is still buffered; a graceful shutdown loses nothing.

If you need per-event delivery confirmation, opt out explicitly:

```ts
analytics: clickhouseAnalytics({
  url: process.env.CLICKHOUSE_URL!,
  batch: false,   // one immediate insert per track(); success = row accepted
})
```

…or tune the window: `batch: { maxSize: 500, flushIntervalMs: 2000 }`.

`database`, `table`, and each `mirrorTables` entry are validated as SQL
identifiers at boot (they're interpolated into DDL) — a bad name fails loudly at
`app.config` eval, not on the first query.

> **Connection details come from `options`, not env.** The plugin reads no
> `CLICKHOUSE_*` variables itself — the example above wires `process.env` into
> the options. Use `@voltro/env`'s `defineEnv` to declare them as secrets.

## No raw-client escape hatch

ClickHouse-specific features outside the cross-provider contract
(HyperLogLog, dictionaries, materialised views) are not reachable through
the plugin — it exposes no raw `@clickhouse/client` handle; the
`AnalyticsSink` contract is the extension seam. Where you need them, query
ClickHouse with your own client instance against the same tables.

Using the Tag binds your handler to ClickHouse — stay on `useAnalytics()` for
provider-portable code. The plugin also exports `EVENTS_TABLE_DDL` and
`MIRROR_TABLE_DDL` for advanced schema work.

## See also

- [Analytics & warehouse sinks](/docs/plugins/analytics) — the shared
  `AnalyticsSink` contract, the `useAnalytics()` read API, `composeAnalytics`,
  and the CDC-mirror details.