Choose an analytical database

ClickHouse is a column-oriented analytical database. It scans, filters, and aggregates huge collections of events, fast. That’s what it’s built for, and that’s what you should use it for.

Databases split 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?

This query 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;

ClickHouse scans a billion events to answer that in seconds. The same query can bring a busy PostgreSQL server to its knees, because a row store has to read every column of every row it touches.

Why not use it for everything

ClickHouse gives up the things OLTP needs. Updating or deleting one row is an expensive background job. There are no classic multi-statement transactions to keep an order and its payment consistent.

So it’s not the home for a shopping cart or a profile update. Keep that state in PostgreSQL, MySQL, or another OLTP database. Send analytical events to ClickHouse when the workload justifies it.

Real products work 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 stream that powers every chart. I self-host Plausible for my own site, so I run this exact pair every day without thinking about it.

“When the workload justifies it” deserves attention. Tens of millions of rows with occasional reports? PostgreSQL handles that fine. ClickHouse earns its place when event volume or query latency makes the row store visibly struggle. My advice is to start with one database and add ClickHouse only when you feel the pain.

Classify before you build

Take four workloads and decide where each one 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, so ClickHouse. The dashboard reads aggregates over history, so ClickHouse again.

The session is the tricky one. The live session your app checks on every request is OLTP state. The record of past sessions you analyze for behavior is an event stream, and that belongs in 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.