A column store for analytics
When your “run a report” query starts taking minutes against a normal database, the problem is not tuning — it is the wrong tool. A column store like ClickHouse answers analytical questions on billions of rows in the time a row store takes to think about it.
Normal databases store data row by row, which is perfect for “fetch this one order” and terrible for “sum this column across a billion orders”. A column store flips that — it reads only the columns a query touches, and it is built for aggregation at scale.
Right tool, wrong tool
- Good at: aggregations, time-series, logs and events, reports over huge tables
- Bad at: single-row lookups, frequent updates and deletes, transactions
- The pattern: keep your transactional database for live data, feed a column store for analytics
It loves cores and memory
ClickHouse parallelises a query across every core it can see, and holds a lot in memory while it works. A dedicated server with many cores and generous RAM is exactly its habitat — this is a workload where bare metal’s full CPU shows. See choosing a CPU.
Storage: fast, and less than you think
Column stores compress extremely well — often ten times smaller than the same data in a row store — so a billion rows may be surprisingly small on disk. Put it on NVMe for ingestion and query speed. See choosing a filesystem.
Ingest in batches
These engines want data in large batches, not a trickle of single-row inserts — insert one row at a time and you will make it miserable. Buffer events and insert them in bulk; this is the opposite instinct from a transactional database.
Back up what you cannot rebuild
If the analytics data is derived from a source you still have, a backup may be a re-import. If it is the only copy, back it up properly — see backups that actually restore.
Still stuck? Mail support@novogara.com — an engineer answers, at any hour. Back to the knowledge base