Why ClickHouse Powers Fast Product Analytics

# Why ClickHouse Powers Fast Product Analytics

If you have ever run SELECT count(*) FROM events WHERE ... against a few billion rows and watched ClickHouse return in under a second, you have felt why it has become the default analytical database for product analytics, observability, and real-time dashboards. This article explains *why* it is fast — the architecture, not just the marketing.

Row stores vs column stores

The single biggest idea is storage orientation. A traditional OLTP database like PostgreSQL stores data row by row on disk. That is ideal for transactional work: "fetch this one user's full record."

Analytics workloads are the opposite. A typical query touches a few columns across millions of rows: "average session duration by country last month." In a row store, you read entire rows off disk just to extract one or two fields — enormous waste.

ClickHouse is a columnar store. Each column is stored contiguously:

Row store:    [id,ts,country,dur][id,ts,country,dur]...
Column store: [id,id,id...][ts,ts,ts...][country...][dur,dur...]

To compute avg(dur) GROUP BY country, ClickHouse reads only the country and dur columns. The rest never leaves disk. For wide event tables with dozens of columns, this is a 10–50x reduction in I/O before any other trick.

Compression: the columnar bonus

Storing a column together means the values are the same type and often highly similar. That compresses brilliantly. Timestamps are nearly sequential (delta encoding), country codes have low cardinality (dictionary encoding), and runs of identical values collapse (RLE). ClickHouse layers codecs like Delta, DoubleDelta, Gorilla, and general-purpose LZ4/ZSTD:

CREATE TABLE events (
    timestamp DateTime CODEC(DoubleDelta, ZSTD),
    user_id   UInt64   CODEC(ZSTD),
    country    LowCardinality(String),
    duration  Float32  CODEC(Gorilla, LZ4)
)
ENGINE = MergeTree
ORDER BY (country, timestamp);

Better compression means less data on disk *and* less data to read into memory — speed and storage cost win together.

Vectorized execution

Classic databases process data one row at a time through a chain of function calls. ClickHouse processes data in blocks of columns (thousands of values at once), running tight loops that the CPU can vectorize with SIMD instructions. One instruction operates on many values simultaneously. Combined with cache-friendly contiguous columns, this keeps the CPU pipelines full instead of stalling on per-row overhead.

The MergeTree engine

The workhorse storage engine is MergeTree. Its key ideas:

• Data is written in sorted parts based on the ORDER BY key.
• A sparse primary index marks every Nth row (default granularity 8192), so the engine can skip straight to relevant data without a per-row index.
• Background merges combine small parts into larger sorted ones (the "Log-Structured Merge" lineage).
• Partitioning (e.g. by month) lets queries prune whole date ranges.

Choosing a good ORDER BY is the most important schema decision — it determines how much data queries can skip.

Materialized views and aggregation

For dashboards that ask the same questions repeatedly, ClickHouse offers materialized views that pre-aggregate at insert time. An AggregatingMergeTree view can roll raw events into hourly summaries on the fly:

CREATE MATERIALIZED VIEW events_hourly
ENGINE = AggregatingMergeTree
ORDER BY (country, hour)
AS SELECT
    country,
    toStartOfHour(timestamp) AS hour,
    count() AS events,
    uniqState(user_id) AS uniq_users
FROM events
GROUP BY country, hour;

Now the dashboard queries a tiny pre-aggregated table instead of scanning raw events.

The tradeoffs (be honest)

ClickHouse is not a general-purpose database. Its speed comes from choices that make it bad at other things:

• No real transactions / weak single-row updates. It is built for append-heavy event data, not OLTP.
• Updates and deletes are expensive — they are implemented as asynchronous "mutations," not in-place edits.
• Eventual consistency in clustered setups; deduplication may lag.
• Joins work but are less natural than in a row store; denormalization is often preferred.

The rule: use ClickHouse for analytical, append-only, read-heavy workloads. Keep PostgreSQL or MySQL for your transactional core.

Why it fits product analytics

Product analytics is the canonical fit: huge volumes of immutable events (page views, clicks, API calls), queried by aggregation across time and dimensions, with dashboards that demand sub-second responses. That is ClickHouse's home turf.

How PandaStack uses ClickHouse

PandaStack's metrics and analytics pipeline is built on ClickHouse. Rather than shipping a client-side SDK, PandaStack captures requests server-side at the Kong ingress and proxy layer and writes them to a ClickHouse event log. That means analytics with no JavaScript snippet to install, no ad-blocker gaps, and the query speed described above powering your dashboards. The columnar engine is exactly why those dashboards stay fast as your event volume grows.

References

• [ClickHouse documentation](https://clickhouse.com/docs)
• [MergeTree engine reference](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree)
• [ClickHouse: column-oriented database concepts](https://clickhouse.com/docs/en/intro)
• [The Design and Implementation of Modern Column-Oriented Database Systems (Abadi et al.)](https://stratos.seas.harvard.edu/files/stratos/files/columnstoresfntdbs.pdf)

---

ClickHouse's columnar storage, compression, and vectorized execution are why fast analytics feel effortless. PandaStack gives you server-side analytics powered by ClickHouse with zero SDK setup — try it on the free tier at [dashboard.pandastack.io](https://dashboard.pandastack.io).