Choose an analytical database

ClickHouse is a column-oriented analytical database. It is designed to scan, filter, and aggregate large collections of events quickly.

The database world splits along a workload line. OLTP (online transaction processing) means many small reads and writes that must be exact right now: create an order, update a balance, load one user’s profile. OLAP (online analytical processing) means questions over lots of history: how many requests failed per hour last month, which pages grew fastest this quarter.

A query like this is ClickHouse’s home turf:

SELECT toStartOfDay(ts) AS day, count() AS pageviews
FROM events
WHERE ts >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;

Scanning a billion events to answer that takes ClickHouse seconds. The same query can bring a busy PostgreSQL instance to its knees, because a row store must read every column of every row it touches.

Why not use it for everything

It is not the default home for a shopping cart transaction or one user profile update. ClickHouse trades away the things OLTP needs: updating or deleting individual rows is an expensive background operation, and there are no classic multi-statement transactions to keep an order and its payment consistent.

Keep transactional invariants in PostgreSQL, MySQL, or another OLTP database, then send analytical events to ClickHouse when the workload justifies it.

Real products are built exactly this way. Plausible Analytics, the privacy-friendly web analytics tool, runs both databases side by side: PostgreSQL holds user accounts and site settings, ClickHouse holds the pageview event stream that powers every dashboard chart.

“When the workload justifies it” is worth taking seriously. Tens of millions of rows with occasional reporting queries? PostgreSQL handles that fine. ClickHouse earns its place when event volume or query latency makes the row store visibly struggle.

Classify before you build

Take these four workloads and decide where each lives: an account balance, an application log stream, a product dashboard, and a user session.

The balance is transactional state — OLTP, no discussion. The log stream is append-only events at high volume — ClickHouse. The dashboard reads aggregates over history — ClickHouse. The session is the interesting one: the live session your app checks on every request is OLTP state, while the record of past sessions you analyze for behavior is an event stream for ClickHouse.

For each dataset, name the system of record and, separately, the analytical copy. Events flow one way, from the source of truth into ClickHouse — never back.