ClickHouse

What ClickHouse is

ClickHouse is a database management system built for one job: answering analytical questions over large amounts of data. It uses SQL, so the interface is familiar, but almost everything behind that interface is arranged differently from a transactional database.

Rows against columns

A transactional database stores a row together. That is the right choice when an application asks for one customer record and wants every field of it.

Analytical queries behave differently. They read millions of rows but usually only a handful of columns. Storing a table by column means those queries read only what they need, and the columns they ignore are never touched.

There is a second effect. Values within a column are similar to each other, and similar values compress well. Stored data ends up considerably smaller than the raw input, which reduces both storage cost and the amount that has to be read.

The ordering key does the heavy lifting

When you create a table with the MergeTree engine you specify an ordering key. This is the single most consequential decision in ClickHouse table design.

Rows are physically stored in that order, and the engine keeps a sparse index over it. When a query filters on the leading columns of the ordering key, whole ranges of data can be skipped without being read.

Choose it to match how the table will be queried and large scans become small ones. Choose it badly and the same query reads everything.

How writes work

An insert does not modify existing files. It creates a new part: a self contained piece of the table on disk.

Parts accumulate, so the engine merges them in the background, combining smaller parts into larger ones in the correct order. This is where the MergeTree name comes from, and it is why ClickHouse handles continuous inserts well while updates to existing rows are a different matter.

Updates and deletes

Changing rows that already exist is possible but is not the normal path. The documentation describes these operations as mutations, which rewrite the affected data rather than editing rows in place.

The practical consequence is that ClickHouse tables are usually designed so data is appended. Where a current value is needed from a stream of changes, engines within the MergeTree family exist that collapse or replace rows during merges.

Keeping aggregates current

Materialised views in ClickHouse are maintained as data is inserted. A view defined over a table receives new rows as they arrive and updates its own stored result.

This gives a way to keep summary tables current without scheduling a rebuild, which matters when the underlying table is large enough that recomputing it regularly would be impractical.

Who ClickHouse suits

ClickHouse suits teams querying very large event, log or metric tables, and product teams building analytics features that their own users query directly. It is used by engineers and analysts writing SQL, and operated by whoever owns the deployment.

What to be aware of

It is not a transactional database and does not try to be. Applications needing frequent small updates and strict transactional behaviour should use something built for that.

Table design also carries more weight here than in a general purpose database. The engine choice and the ordering key determine query performance to a degree that indexes on a conventional database do not, so those decisions are worth making carefully before loading data.

Getting started

The documentation includes a quick start that installs ClickHouse locally, creates a table and loads a sample data set. Separate sections cover table engines, the MergeTree family, materialised views, integrations and deployment.