The moment an analytics dashboard that used to load instantly starts taking ten, twenty, thirty seconds to render is usually the moment a team realizes their transactional database was never built for this job in the first place. Postgres and MySQL are excellent at what they’re designed for - fast reads and writes on individual records, transactional consistency. Aggregating billions of rows for a dashboard is a fundamentally different access pattern, and forcing a transactional database to do it is why analytics features quietly become the slowest, most complained-about part of a product.
Why row-oriented databases struggle at this specific job
Traditional databases store data row by row, which is efficient when you need a complete record - fetch this one order, this one user. Analytics queries typically need the opposite: aggregate one or two columns (revenue, event count) across millions or billions of rows, and don’t care about most of the other columns in each row at all. A row-oriented database still has to read entire rows to get at the columns you actually need, which becomes the exact bottleneck that makes analytical queries slow as data volume grows.
What ClickHouse does differently
ClickHouse is a column-oriented database, purpose-built for exactly this access pattern - it stores each column’s data together, so an aggregation query touching two columns out of fifty only reads those two columns’ data, not entire rows. Combined with aggressive compression (column-oriented storage compresses far better than row-oriented, since similar values are stored adjacently) and query execution genuinely designed for scanning and aggregating massive volumes, this is what makes billions-of-rows-in-milliseconds a realistic claim rather than marketing - for the specific class of query it’s built for.
Where this actually matters, concretely
- Product analytics and event tracking at real scale - user behavior events, page views, feature usage - where the query pattern is almost always “aggregate this metric across a time range and some dimensions,” exactly ClickHouse’s strength.
- Real-time dashboards that need to stay responsive as the underlying event volume grows into the billions, where a transactional database’s query time would keep degrading as data accumulates.
- Log and time-series analytics - application logs, IoT sensor data, financial market data - high-volume, append-heavy, aggregation-heavy workloads that are close to a textbook case for column-oriented storage.
What ClickHouse is genuinely bad at, and why that matters for the decision
This is not a general-purpose database replacement, and treating it as one is the most common mistake we see. Frequent updates to individual rows, complex multi-table transactional consistency requirements, and point lookups of single records are all significantly worse on ClickHouse than on a traditional row-oriented database. The right architecture almost always keeps Postgres or MySQL as the transactional source of truth for your core application data, with ClickHouse specifically as the analytics layer, fed by a pipeline (streaming or batch) from the transactional system - not a wholesale replacement of your primary database.
What we actually build
A typical architecture: application writes go to Postgres as normal, an event pipeline (Kafka or a simpler batch ETL, depending on scale and latency requirements) streams relevant events into ClickHouse, and the analytics dashboard queries ClickHouse exclusively, leaving the transactional database untouched by analytical load entirely. This separation is what actually fixes the “dashboard is slow and now it’s slowing down the whole app” problem, because the two workloads stop competing for the same database’s resources.
We architect this kind of data layer as part of our backend architecture work. If your analytics queries are dragging down application performance or just taking too long to be useful, get in touch and we’ll look at whether a dedicated analytics layer is the right fix.