Clickhouse Developer Notes

Data Storage

There are many ways of storing and retrieving data in clickhouse. Each table defined must specify a table engine, which will determine how the data is stored.

Most common engine to use is MergeTree or one of the other engines in the MergeTree family. -> Most universal and functional for high-load tasks

Sort Order is a big thing in Clickhouse

The primary key of the table determines how the data is stored and searched. If no PRIMARY KEY is given, then the ORDER BY clause will be used

In Clickhouse the PRIMARY KEY:

• Does not need to be unique for each column
• Should be composed by columns that are frequently used for searching

Insertions

• Done in bulk -> inserting one row at a time will create too many folders and would be slow

  

• Can use async insert to create buffer of inserts

• Each insert creates a part (a part is stored into its own folder) -> part == folder

• Coluns are sorted by primary key and each part has an inmutable file with the columns data

  • In screenshot, primary key sorts columns A,B,C. First it comes all (A,1) rows, then (B,2) rows and so on

  

• Clickhouse merges parts behind the scenes to avoid having too many (MergeTree table ..)

  • After merging parts, those are deleted
  • Max 150GB per merge part, so we would still have many data

  

Primary indexes

There is a file called primary.idx that has one key per granule (each granule has 8192 rows or 10MB of data) . In this file, there would be a key for every granule. In this fashion, we can skip granules that do not match the rows we are looking for based on the key.

ex: for reading (A,2) granules of keys 1-2 would be skipped as well as granules up from (B,1)

• A granule is a logical breakdown of rows inside an uncompressed block.
• primary key is the sort order of the table
  • Should be the columns from where we filter out the most
• primary index is an in-memory index containing the values of the PK of the first row of each granule

A granule is the smallest dataset ClickHouse works with

Select * selects granule from each column in something called stripes , each stripe is processed by a thread IN PARALLEL

DATA IS STORED within GRANULES and the PK of each granule is how Clickhouse looks logically at data .