How a column
store works

Reading two fields out of six

If we store each record as a row, its six fields sit next to each other. The database reads chunks of the file called pages. A page containing the shop and country will also contain fields we don't need, and reading that page brings them all into memory.

A column store rearranges the values: all the shop values together, all the timestamps together, all the countries together, and so on. Now the query can read the shop and country columns without reading the others.

The saving comes from the fields we leave behind. Counting page views by shop and country requires those two columns, however many other fields each record contains. If a report needs all six fields, storing them in columns won't let us skip any of them. For a request that fetches one complete customer record, a row store with a suitable index may be a better fit.

Columns also put similar values together, which gives compression something to work with. Our 16 page views contain just three country names. Instead of writing “Sweden” into every record that needs it, we can store the name once in a dictionary and put a short code in those records. For example, if 0 means Sweden, the blue-mug visit can store 0 as its country. Looking up that code gives us the original name.

The dictionary takes space too. Reusing its entries across many records can save space; if every record has a different country name, there's little repetition to remove and the extra codes may make things larger. This is one encoding idea, rather than a description of every ClickHouse column's on-disk format.

ClickHouse can also process a batch of column values together, instead of repeating the same bookkeeping for one record at a time. Compression and batch processing are further reasons column stores suit large analytical queries. These savings come on top of avoiding unused fields.

Looking at one shop

We've counted page views for all the shops. When the owner of shop B opens their dashboard, though, they only need B's counts. We could save more work by skipping records from the other shops. Storing the values in columns doesn't help us find B: its values are still mixed in with A, C, and D.

SELECT shop, country,
       count(*) AS views
FROM page_views
WHERE shop = 'B'
GROUP BY shop, country
ORDER BY shop, country;

Sorting by shop puts B's records next to each other. This moves whole records: the blue-mug visit's country, time, and payload must move with its shop value. We still store the fields in separate columns, but a given position in every column must refer to the same visit. Sorting each column independently would scramble the records.

We can divide that shared record order into groups called blocks. Each block covers corresponding positions across the columns. That gives us two ways to avoid reading data: leave out entire fields, or skip a range of records within the fields we need.

For each block, keep its smallest and largest shop key. A block whose keys run from C to D can't contain B. We can skip those positions in both the shop and country columns, while continuing to leave the other four columns unread.

When every block mixes records from all four shops, its range runs from A to D and can't rule out B. Sorting brings B's records together, so blocks containing only the other shops can be skipped. The report still counts two visits from Sweden and two from the UK; the sort order changes the work needed to find them.

ClickHouse uses a sparse primary index for this job. It records the key at the start of each group of sorted rows, called a granule. For a key such as shop, the query can search these ordered entries to find candidate granules, then check their rows.

The minimum-and-maximum method keeps each block's last key as well as its first. A sparse index has less information about each granule's contents. The diagram shows candidate ranges, not every optimisation in ClickHouse's search. Once granules are selected, separate mark files locate their data in the requested columns. Real granules are much larger than four rows.

This is why the sorting key matters. Putting each shop's events together is useful for our dashboard. It won't help as much if the next query asks for one user across all shops: that user's events could be scattered through the table. We could keep another arrangement for that query, but we'd have to store it and update it too.

Adding events to sorted data

So far we've rearranged data that was already there. Visitors keep opening pages, so the shops keep sending new records, and each one needs to end up in the right place in the sort order. Rewriting a large sorted file whenever an event arrives would mean doing a lot of work for a small addition.

ClickHouse's MergeTree storage engines handle this by sorting incoming batches separately. A batch is written into one or more parts. Each part contains sorted rows stored in columns, along with an index. Once written, the part is immutable: it won't be edited in place.

Illustration · Inside a partOne part, several columns

Index Data part

A query can read matching rows from several parts, so new events don't have to wait for one big sorted file. But lots of small parts give each query more places to look. In the background, ClickHouse merges parts: it reads their contents and writes a new, larger sorted part to replace them.

For a small example, imagine four page views arriving as four separate batches, each creating one part. Combining them into one part requires reading and rewriting those records. If the same four views arrive in one batch, we can write one part immediately and avoid those merges. Both cases store the same records, but the separate batches cause extra work.

Of course, collecting a larger batch takes time. If the dashboard needs new events quickly, you may not want to wait. ClickHouse's asynchronous inserts can collect batches on the server. Their acknowledgement settings control whether the sender waits for that batch to be written before receiving success.

What if an event was wrong? With ReplacingMergeTree, you can insert a corrected version and let a later merge reconcile the versions. Until that merge happens, both versions may still be present. A query that needs the latest version must account for that. And if you've already used the old event to update a daily total, correcting the event doesn't by itself fix the total.

A write can succeed before it reaches the second copy

We've followed a page view into a sorted part. But what if the machine storing that part fails? This is a deployment question shared by many databases. To finish our ClickHouse example, we'll look at a setup that keeps a second copy.

A new page view arrives at the receiving replica, which stores it and sends a copy to the second replica. Sending takes time. Until it arrives, only the receiving replica has this record. We're adding a new event here, so there are no old and corrected versions to reconcile.

When should we tell the analytics service its write succeeded? We could reply as soon as the receiving replica stores it, or wait for the second copy.

If the receiving replica loses its storage before the second copy arrives, the page view is lost under either policy. Waiting doesn't copy the data any faster. But an immediate reply would have told the service its write succeeded, even though the only copy was then lost. Waiting avoids giving that success response before a second copy exists.

Once the second replica has the page view, losing the receiving replica leaves a copy available. Waiting therefore delays success until the write can survive that particular failure.

Self-managed ReplicatedMergeTree copies data asynchronously. A write can succeed before another replica has it. Insert-quorum settings let you require more replicas to confirm receipt first. That still doesn't mean every replica you might read from has caught up.

ClickHouse Cloud uses a different arrangement. With SharedMergeTree, compute nodes read parts from shared object storage, and metadata coordinates which parts are available. The storage service keeps the durable data, so adding a compute node doesn't mean giving it a separate full copy. Readers still need to learn that a new part exists.

Illustration · Compute and storageWhere do the copies live?

Compute Compute Replica copies Shared data

Separate storageEach replica holds a copy.

Shared storageCompute reads the same store.

When would you use this?

For our dashboard, this arrangement makes sense. Most events are added once and read many times. Queries count and group values from a few columns, and a shop filter can rule out much of the data. I'd consider a column store for that workload.

Now consider the checkout in an online shop. Two people try to buy the last item. The database needs to update inventory and create an order without selling that item twice. Reading fewer columns won't establish that rule; we need a transaction that keeps those changes together. I'd start by looking at a transactional relational database for that part of the application.

You can recognise some of the same ideas in other analytical databases. DuckDB runs inside your process, but also benefits from column layouts and avoiding unnecessary reads. Analytical warehouses may use similar techniques while arranging storage and compute differently. The part handling and replication behaviour we've looked at here are specific to the ClickHouse engines we've named.

If you're comparing one of those systems with ClickHouse, start with an actual query from your application. Work out which columns it needs and whether the sorting order lets it skip records. Then check how new data and corrections reach that query. Those details will tell you more than the label "column store" alone.