Module 01

ClickHouse

ClickHouse is a column-oriented database built for OLAP: aggregating and scanning huge tables fast, not updating single rows fast. This module covers the setup you're running locally and the internals that make it behave the way it does.

33 lessons across 10 sections

Why column-oriented, in one picture

SELECT

Row-orientede.g. Postgres/MySQL

1IN42.1010:01

2US18.5010:02

3DE91.0010:02

4IN12.7510:03

avg(amount) still touches every full row on disk — all four columns come along for free whether you asked for them or not.

Column-orientedClickHouse

avg(amount) reads and decompresses only the amount column — the other three never leave disk.

01

Why ClickHouse

— What problem it solves, and when it's the wrong choice.

OLAP vs OLTP, history, when not to use it

02

Foundations

— Get it running and understand what kind of database this is.

Running ClickHouse locally

MergeTree Engine

The columnar storage engine

03

Data Types

— Choosing the right shape for your data, including nested structures.

Numeric, string, LowCardinality, Nullable

Arrays, Maps & JSON

Array, Tuple, Map, Nested, JSON

04

Storage Engine

— How ClickHouse actually stores rows on disk.

Parts & Background Merges

How writes become storage

ORDER BY & Primary Keys

The sparse index

Splitting data on disk

Codecs & column encoding

05

SQL & Querying

— Joins, windows, and the SQL features that behave differently here.

Algorithms, GLOBAL JOIN, pitfalls

Window Functions

OVER, frames, and SAMPLE

Aggregate Combinators

-If, -State, -Merge, -Distinct

Mutations (UPDATE / DELETE)

Why they're heavyweight, async rewrites

06

Query Speed

— The tricks that make scans over billions of rows fast.

Data-skipping indexes

Pre-sorted alternate layouts

Materialized Views

Push-based pre-aggregation

Fast in-memory lookups

Query Optimization

Vectorized execution

07

Ingestion & Other Engines

— Getting data in, and the engines beyond MergeTree.

Insert & Export Formats

Native, JSONEachRow, CSV, Parquet

Async Inserts & Batching

Why tiny inserts hurt, how to fix it

Other Table Engines

Log, Memory, Merge, View, Null

Streaming ingestion into MergeTree

Integration Engines

S3, MySQL, PostgreSQL, URL, File

08

Security & Backup

— Users, permissions, and not losing data.

RBAC, GRANT, default user

Row Policies, Quotas & TLS

Row-level security, limits, encryption

Backup & Restore

BACKUP/RESTORE, S3, disaster recovery

09

Scale & Ops

— Running it across machines, safely, over time.

Automatic data lifecycle

Distributed Tables

Sharding across nodes

Replication & Keeper

High availability

10

Monitoring & Operations

— Watching it run, tuning it, and what breaks in production.

system.parts, query_log, merges

Prometheus & Grafana integration

Memory limits, caches, thread settings

Production Failure Scenarios

Too many parts, OOM, replication lag