ClickHouse

ClickHouse was built at Yandex to power Metrica, its web analytics product — so pageview-scale event data is not a use case it adapted to, it is the origin story. The MergeTree engine family stores sorted, compressed columns and answers aggregations over billions of rows in milliseconds; materialized views pre-aggregate on insert, and approximate functions (uniq, quantiles) trade exactness for speed where analytics can afford it. Plausible and PostHog both run on it, which says plenty about its fit for tracking workloads.

How it models data. A MergeTree table is a set of immutable parts, each sorted by the table’s sorting key, merged together in the background. The primary key is a sparse index over granules (8,192 rows by default), not a row-level index — it exists to skip data, and it does not enforce uniqueness. That means your sorting key IS your performance model: an events table ordered by (site_id, toDate(timestamp)) will answer per-site date-range queries by reading a sliver of the data, and the same query against a badly ordered table will scan everything. Optional partitioning (commonly by month via toYYYYMM) adds coarse pruning, and table-level TTLs handle retention by deleting or tiering expired rows during merges — useful for GDPR-driven retention windows.

Implementation notes. Because every insert creates a new part, ClickHouse wants large batched inserts, not row-at-a-time writes — put a buffer (Kafka, or ClickHouse Cloud’s ClickPipes) in front of your event stream. Duplicates are your problem, not the database’s: at-least-once pipelines usually pair with ReplacingMergeTree, and you must write queries that tolerate not-yet-merged duplicates. Point updates and deletes are asynchronous mutations that rewrite parts, so identity stitching and consent-deletion workflows need designing up front rather than bolting on. Wide multi-table joins take more care than in a general-purpose warehouse — schema design does the work that a query optimizer does elsewhere, and denormalizing event context at ingest is the norm.

Ecosystem and operations. The integration surface is broad and documented: dbt, Airflow, Kafka, Grafana, Metabase, Superset, Airbyte, Fivetran, plus Segment and RudderStack on the event side. It is a database you operate (or rent as ClickHouse Cloud, where compute is metered per-minute in 8 GB RAM increments and storage is billed on compressed size), not a serverless endpoint. Self-hosting a single node is genuinely easy; running a replicated cluster with Keeper, backups, and schema migrations is a real operational commitment.

Use it when you need real-time, user-facing analytics on raw events, self-hosted infrastructure for cost or residency reasons, or query latency that BigQuery and Snowflake cannot hit. Look past it when your workload is classic batch ELT with heavy joins and mutations, or your team wants a fully managed warehouse with zero schema-tuning obligations.