ClickHouse for exception storage
Error telemetry is a wide-row, append-only, aggregate-read workload — which is close to the ideal case for a columnar engine, and a poor fit for a row store.
The shape of the workload
An exception event is wide — message, stack, route, method, user, release, environment, headers, body — and almost never read back by primary key. The questions asked of it are aggregates: how many errors per release, which routes fail most, what the p95 latency was for failing requests.
A row-oriented store reads whole rows off disk. Counting errors grouped by release means reading the stack trace and request body of every row to use two columns. A columnar store reads only the two columns the query names, which is the entire difference.
Order by what you filter by
ClickHouse has no B-tree secondary indexes in the sense a Postgres user expects. The `ORDER BY` of a MergeTree table is the primary index, and it determines which queries can skip data via sparse granule marks.
For exception data, ordering by project then timestamp is usually right: nearly every query is scoped to one project and a time window, so that ordering lets the engine skip most of the table. Partitioning by month then makes dropping old data an instant metadata operation rather than a mass delete.
CREATE TABLE exceptions (
projectId String,
timestamp DateTime64(3),
fingerprint FixedString(32),
name LowCardinality(String),
message String,
stack String,
route LowCardinality(String),
method LowCardinality(String),
severity LowCardinality(String),
release LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
-- Primary index: every query filters by project and time, so skip on both.
ORDER BY (projectId, timestamp)
TTL toDateTime(timestamp) + INTERVAL 90 DAY;LowCardinality is not cosmetic
Columns with a small set of repeated values — severity, method, route, release name — belong in `LowCardinality(String)`. ClickHouse then stores a dictionary and per-row integer references instead of repeating the string.
On error data this is a large win, because `GET`, `critical` and a release tag repeat across every row. Do not apply it to `message` or `stack`: high-cardinality columns under `LowCardinality` make things worse, not better.
What ClickHouse is bad at
Single-row updates and deletes. `ALTER TABLE ... UPDATE` is an asynchronous mutation that rewrites data parts; it is not an OLTP update and must not be used on a request path.
Point lookups of one row by id, high-frequency small inserts, and anything needing transactions across tables. This is why the metadata that genuinely mutates — projects, users, issue status, assignment — belongs in a row store, with ClickHouse holding only the append-only event stream. That split is the design VantaTrace uses: ClickHouse for occurrences, MongoDB for everything that changes.
Insert in batches, always
Every insert creates a data part that the merge process must later compact. Inserting one row per event produces enormous numbers of tiny parts and will eventually trigger "too many parts" errors and stall ingestion.
Batch instead — thousands of rows per insert, or one insert per queue job. This is why an ingestion pipeline wants a queue between the HTTP endpoint and the database: the endpoint can answer immediately while a worker accumulates and inserts batches.