ClickHouse

ClickHouse is an open source, column oriented SQL database management system built for online analytical processing (OLAP). It keeps the values of a column together, so filters and aggregations read only the columns they touch.

At CyberElectro ClickHouse is the analytics store on Docker: events, clicks and performance data land here, while transactions run on PostgreSQL.

Columnar storage and the sparse index

MergeTree tables are stored as sorted, immutable parts. The primary index is sparse: one mark per granule of 8192 rows instead of one entry per row, so the index of a table with billions of rows still fits in memory.

• ORDER BY sets the sort order inside each part and doubles as the primary key, and partition pruning skips unneeded partitions; the documentation recommends monthly or coarser partitioning.
• The sparse index is not a unique key, and single row point lookups are not its strength.
• Data skipping indexes (minmax, set, bloom_filter) help with filters outside the sorting key; columns are compressed individually and execution is vectorised.

The MergeTree family

The base engine is MergeTree, the most robust and most commonly used engine. Every insert creates an immutable part sorted by the primary key, and a background process merges small parts into larger ones, much like an LSM tree. Parts from different partitions are never merged, and rows with the same key need not share a part.

• ReplacingMergeTree keeps the most recent row per key, SummingMergeTree sums numeric columns of a key, AggregatingMergeTree stores partial aggregate states, and CollapsingMergeTree cancels rows with a sign column (+1 and -1).

Because resolution happens at merge time, a plain SELECT can still see unmerged duplicates; an exact answer needs FINAL or a GROUP BY.

SQL and materialised views

Its declarative SQL dialect matches ANSI SQL in many cases, including GROUP BY, ORDER BY, JOIN, IN and window functions.

A materialised view is not a stored snapshot: it is a trigger that runs a query over the blocks inserted into a table and writes the result into a target table, so it acts as a continuously maintained index. Its grouping has to match the target table’s ORDER BY, or merges cannot combine partial states.

Ingestion: batches, not single rows

Every insert creates at least one part, so row by row writes leave too many small parts for the background merges to handle; the documentation calls this the typical route to the Too many parts error. Batched inserts are preferred.

Asynchronous inserts (async_insert) batch server side: rows are buffered in memory and written to disk when the buffer reaches a configured size (100 MiB by default) or a timeout expires. The documentation recommends wait_for_async_insert = 1, so the acknowledgement arrives only after the data reaches disk.

What the documentation lists as limits

• Not OLTP and not transactional: an insert into one partition of one MergeTree table is ACID when packed as a single block, but multi statement transactions are experimental, inserts into a Buffer table are neither atomic nor isolated nor durable, and transactional workloads belong to a row store.
• Memory can be the bottleneck: aggregations build hash tables in memory and a join loads the right hand table, so large joins and high cardinality GROUP BY exhaust memory.
• Merges are a matter of settings: inactive parts are removed after a configurable interval (8 minutes by default) and large merges grow to about 150 GB compressed.
• Replication consistency is separate: the ACID terms do not cover the consistency semantics of distributed systems.

Further reading

• MergeTree table engine
• Table parts and part merges
• Asynchronous inserts

At CyberElectro ClickHouse is the containerised analytics layer for events and performance data, transactional state stays in PostgreSQL, and processing runs on n8n.