ClickHouse
ClickHouse is an open-source columnar database built for analytical queries. It stores each column in its own compressed files, so a query that averages one column over a billion rows reads that column and nothing else.
Pick it when data arrives as an append-only stream (events, logs, metrics, telemetry or copied transactions) and people mostly ask questions like "average, count or sum of this, grouped by that, over this time range". Orders, carts and user profiles, where rows change one at a time, belong in PostgreSQL or MySQL. Teams usually copy that data into ClickHouse with change data capture or an ETL job.
How it works
Almost every production table uses the MergeTree engine. Its ORDER BY clause decides how rows are sorted on disk, and the primary key (by default the same columns) is a sparse index: one entry for every granule of 8,192 rows. Each INSERT writes a new immutable part, a directory of sorted, compressed column files. Background merges then combine small parts into bigger ones. In One2N's fleet telemetry work, where each vehicle sends up to 5,000 values a minute, one of the table designs was this:
Take 1,000 vehicles with 2,000 PIDs (sensor parameters) each, one reading a minute. That is 2.88 billion rows a day, about 351,600 granules, so the whole index fits in memory.
A query for one vehicle over two hours matches 2,000 x 120 = 240,000 rows. Once the day has merged into a single part, those rows sit next to each other, and ClickHouse reads about 30 granules out of 351,600. A filter on pid_name alone gets little help from the index, because pid_name is the third column in the sort order.
Parts are also where ClickHouse hurts. If each of those 1,000 vehicles inserted its own rows every minute, the server would get 1,000 new parts a minute, about 17 a second. Merges fall behind, inserts first slow down and then fail with a "Too many parts" error.
Send the same rows through one writer that flushes every 10 seconds and you get 6 parts a minute of about 333,000 rows each, which merges handle easily. Async inserts (async_insert = 1) do the batching on the server if you cannot batch in the client.
Why it matters in production
Most ClickHouse pain comes from using it like a transactional database. UPDATE and DELETE run as mutations that rewrite whole parts in the background. There are no multi-statement transactions. A join between two big tables loads the right-hand table into memory by default. Nullable columns keep an extra file of null flags per column, and the sparse-data post switched to plain default values to avoid them.
Get the sort key wrong and every query scans the table, and changing it later means creating a new table and copying the data across.
Done right, the savings are large. One2N moved a fintech client off AWS Glue and Athena onto a self-hosted ClickHouse cluster with 1 shard and 2 replicas. Monthly analytics spend fell from $13,000 to $4,000, and p95 query latency stayed at 4.2 seconds, about where it was on Athena.
See also
On the One2N blog
References
