ClickHouse Tutorial: Learn Columnar Analytics Database from Scratch (2026)
I have used ClickHouse to power real-time analytics dashboards querying billions of rows in sub-second time. ClickHouse is a columnar database designed for OLAP workloads.
We will cover installation, the MergeTree engine, query optimization, data ingestion, JOINs, and cluster deployment.
Installing ClickHouse and Understanding Columnar Storage
ClickHouse is available as binary, Docker container, or cloud service. Server on port 8123 (HTTP) and 9000 (native TCP). Use clickhouse-client for interactive queries.
Columnar storage: each column in a separate file. Only queried columns are read. Column-by-column compression with algorithm choice per column.
curl https://clickhouse.com/ | sh
sudo ./clickhouse install
sudo systemctl start clickhouse-server
clickhouse-client --host localhost --port 9000 --user default
CREATE TABLE events (event_time DateTime, user_id UInt64, event_type String, amount Float64) ENGINE = MergeTree() ORDER BY (event_time, user_id);The MergeTree Engine and Data Partitioning
Data stored in parts (directories of column files). Background merge combines smaller parts into larger ones. ORDER BY defines sort key and primary index.
PARTITION BY for data grouping, typically by month. Sparse index stores one entry per 8192 rows (granularity), memory-efficient for terabytes.
CREATE TABLE events (event_time DateTime, user_id UInt64, event_type String, amount Float64) ENGINE = MergeTree() PARTITION BY toYYYYMM(event_time) ORDER BY (event_time, user_id) SETTINGS index_granularity = 8192;
INSERT INTO events VALUES (now(), 1, 'purchase', 29.99), (now(), 2, 'click', 0.0);
ALTER TABLE events DROP PARTITION 202501;Querying at Speed: Aggregations, Filtering, Window Functions
Excels at aggregations over large datasets. Vectorized query engine processes batches using CPU SIMD, achieving billions of rows/sec/core.
Window functions supported. argMax/argMin for latest values. uniq() for approximate distinct counts via HyperLogLog.
SELECT event_type, count() AS cnt, sum(amount) AS revenue FROM events WHERE event_time > now() - INTERVAL 1 DAY GROUP BY event_type ORDER BY revenue DESC;
SELECT event_time, user_id, amount, sum(amount) OVER (PARTITION BY user_id ORDER BY event_time) AS running_total FROM events;
SELECT uniq(user_id) AS unique_users FROM events;Data Ingestion: Kafka, S3, and Batch Inserts
Kafka engine and materialized views for streaming. s3 function for batch from S3. Native format via clickhouse-client or HTTP.
Batch inserts in chunks of 100k-1M rows. Each INSERT creates a part; too many small parts degrade merge. Use async_insert for non-blocking inserts.
CREATE TABLE events_queue (event_time DateTime, user_id UInt64, event_type String) ENGINE = Kafka SETTINGS kafka_broker_list = 'localhost:9092', kafka_topic_list = 'events', kafka_group_name = 'clickhouse', kafka_format = 'JSONEachRow';
CREATE MATERIALIZED VIEW events_mv TO events AS SELECT * FROM events_queue;
curl -X POST http://localhost:8123/?query=INSERT%20INTO%20events%20FORMAT%20TSV --data-binary @events.tsvJOIN Optimization in ClickHouse
JOINs load the right table into memory. Right table should be small enough for RAM. Use ANY LEFT JOIN to avoid duplicate expansion.
Denormalization is preferred. Dictionary tables for reference data are fastest. Global JOIN distributes right table to all shards.
SELECT e.event_time, u.name FROM events e LEFT JOIN users u ON e.user_id = u.id;
SELECT e.event_time, u.name FROM events e GLOBAL LEFT JOIN users u ON e.user_id = u.id;
CREATE DICTIONARY user_dict (id UInt64, name String) PRIMARY KEY id SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 TABLE 'users')) LIFETIME(300);Distributed Tables and Cluster Deployment
Shared-nothing architecture. Shards with ReplicatedMergeTree via ClickHouse Keeper (ZooKeeper). Distributed table provides unified view.
Queries fan out to all shards, aggregate at initiator, return results. Ensure indexes and partition pruning for low latency.
CREATE TABLE events_local ON CLUSTER mycluster (event_time DateTime, user_id UInt64, event_type String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}') PARTITION BY toYYYYMM(event_time) ORDER BY (event_time, user_id);
CREATE TABLE events_distributed ON CLUSTER mycluster AS events_local ENGINE = Distributed(mycluster, default, events_local, rand());Frequently Asked Questions
Is ClickHouse free?
Open-source under Apache 2.0. ClickHouse Cloud is a managed service with usage-based pricing. Enterprise tier available.
When should I use ClickHouse versus PostgreSQL?
Use ClickHouse for analytical queries on billions of rows. Use PostgreSQL for transactional workloads (OLTP) and row-level operations.
Can ClickHouse do UPDATE and DELETE?
Supports ALTER UPDATE and ALTER DELETE, but they are async and bulk-oriented. ClickHouse is optimized for append-only workloads.
How does ClickHouse handle high-concurrency queries?
Handles hundreds of concurrent queries per node. For thousands, use a load-balanced cluster and connection poolers like pgCat.
Originally published on Ayodhyyya. Last updated June 1, 2026.