ClickHouse
Column store that answers analytics queries over billions of rows in seconds
Absurdly fast for the one job it does, and a bad fit for everything else, which is how a good tool should be.
Pros
- Funnel and session queries over raw events come back in seconds
- Speaks SQL, so the learning curve is the dialect, not the language
- Self-hosting in a container takes ten minutes
- Compression makes a year of events fit on a small disk
Cons
- Updates and deletes are an afterthought; it wants append-only data
- Joins are slower and stranger than in a row database
- The SQL dialect has enough surprises that copied Postgres queries fail
Best forDevelopers with an event table that has outgrown Postgres and who want fast aggregates without a data team.
ClickHouse is a columnar database built for analytics. You pour events into it, millions a day if you like, and ask questions that touch all of them, and it answers while a row database would still be reading the disk. It is open source and there is a hosted Cloud version.
How I use it
I keep it for a tracking experiment. Raw page views, clicks and form events from a few sites land in one wide table, and the questions I ask are the classic ones: how many sessions reached the checkout, where did they drop, what did a cohort do in the first ten minutes. In Postgres those queries were a coffee break. Here they are a blink, on a single small instance in a container, with data compressed so well that a year fits where a month used to.
What surprised me is how little tuning it needed. A sensible sort key on the table and the rest took care of itself. What annoys me is everything outside the sweet spot. It does not want you to update or delete rows; you can, but it is slow and awkward, so the data has to be shaped as append-only from the start. Joins between big tables are slower than you expect and need to be written the ClickHouse way. And the SQL dialect has enough small differences that a query pasted from a Postgres project fails on a function name or a date cast, every time.
I run it self-hosted because the experiment is small. The Cloud version is priced on compute and storage, with a trial credit that is generous enough to find out whether you need it.
Who it is for
Developers who have an events table that Postgres has started to choke on and who are comfortable with SQL. If your largest table has a few million rows, you do not need this yet. Skip it as a primary application database; it is the wrong shape for transactions, and the people who try end up running two databases anyway.
