To a developer coming from MySQL, ClickHouse looks deceptively familiar. It uses SQL, with tables, columns, SELECT, WHERE, and GROUP BY, and a client connects and runs queries immediately. But the first UPDATE of a single row breaks the illusion. ClickHouse isn’t MySQL with a faster engine — it’s a database built for a different job entirely.
The previous post covers what ClickHouse is and when to use it; this one covers what a MySQL developer needs to know before using it.
The core idea: columns, not rows
MySQL stores data row by row. A users row — id, name, email, created_at — sits together on disk. That’s perfect for OLTP: “fetch user 42,” “update user 42’s email.” One record is touched at a time.
ClickHouse stores data column by column. Every id is stored together, every name together, every created_at together.
That single design choice explains almost everything else:
SELECT count(*), avg(amount) FROM orders WHERE year = 2025only reads theamountandyearcolumns — it never touches any other columns in the table. On a billion-row table, that’s the difference between seconds and minutes.- Columns compress well. A column of country codes or timestamps has a lot of repetition, so ClickHouse compresses it 10-30x. Less disk read means faster queries.
- Reading a single full row is slow — ClickHouse has to jump across every column file to reassemble it. This is why “give me user 42” is the wrong question to ask ClickHouse.
Columnar storage is only half the story. ClickHouse also uses vectorized execution — it processes thousands of values from a column at once, using SIMD instructions on the CPU, rather than evaluating a query row by row. MySQL walks rows one at a time; ClickHouse operates on whole columns in bulk. Columnar layout, heavy compression, and vectorized execution together are what make it fast.
MySQL is for transactions — many small reads and writes of individual rows. ClickHouse is for analytics — scanning huge ranges to aggregate. The two are complementary and are typically run side by side.
What’s different from MySQL
1. No PRIMARY KEY in the MySQL sense
In MySQL, the primary key enforces uniqueness and builds a B-tree index. In ClickHouse, the closest concept is the ORDER BY clause of the table — and it does not enforce uniqueness at all.
CREATE TABLE events
(
event_date Date,
user_id UInt64,
event_type String,
amount Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY (event_date, user_id);Code language: JavaScript (javascript)ORDER BY sorts the data on disk and builds a sparse index — by default ClickHouse stores one index entry per 8,192 rows, not one per row. That’s why the index is tiny and why ClickHouse is built to scan ranges, not pluck single rows.
ORDER BY doesn’t just sort — it defines how ClickHouse physically organizes data into parts, which makes it the main lever for query speed. It should match how the table is filtered (here: by event_date, then user_id), not be chosen for uniqueness.
Two identical rows can be inserted and ClickHouse will keep both. Enforcing uniqueness is the application’s responsibility, not the engine’s.
2. UPDATE and DELETE are not everyday operations
In MySQL, rows are updated constantly. In ClickHouse, the data is designed to be append-only.
Updates and deletes exist (ALTER TABLE ... UPDATE, plus a lightweight DELETE — and, on recent versions, a lightweight UPDATE), but historically they’re “mutations” — asynchronous, heavy operations that rewrite large chunks of data. They’re meant for occasional corrections (GDPR deletes, backfills), not for an application’s normal write path.
Even with lightweight DELETE/UPDATE, ClickHouse still behaves like an append-only system. Mutations mark rows for removal and clean them up during merges. They’re not row-store updates — they’re background transformations of columnar data.
A design that needs frequent row updates is either modeled wrong for ClickHouse, or calls for a special engine like ReplacingMergeTree (which deduplicates rows with the same ORDER BY key — eventually, during background merges).
The pattern is to append events rather than update state, then compute the current state at query time.
3. Insert in big batches, not row by row
MySQL is fine with thousands of single-row INSERT statements. ClickHouse is the opposite: every insert creates a small “part” (a set of files on disk) that must later be merged in the background. Thousands of tiny inserts become thousands of tiny parts, overwhelming the background merges; past a threshold ClickHouse stops accepting inserts and returns a Too many parts error.
Inserts should go in big batches — tens of thousands of rows at a time, ideally. For a genuine stream of single rows, asynchronous inserts (async_insert = 1, with wait_for_async_insert = 0) let ClickHouse buffer them server-side.
4. There are no transactions
There is no BEGIN/COMMIT/ROLLBACK wrapping multiple statements as in MySQL. ClickHouse trades transactional guarantees for raw analytical throughput. Data that needs ACID transactions belongs in Postgres or MySQL, and is then streamed into ClickHouse for analysis.
5. Joins work, but denormalize first
Joins are supported and the SQL looks familiar, but large joins are memory-hungry because ClickHouse loads the right-hand table into memory. The idiomatic approach is to denormalize — store a wide, flat table — because that aligns with columnar physics: fewer lookups, fewer random accesses, more sequential scans. It is an analytics schema, not an OLTP schema.
For small lookup tables, dictionaries are a fast, in-memory key-value replacement for a join.
6. SELECT * is the most expensive query
In MySQL, SELECT * costs little — the whole row already sits together on disk, so reading every column is barely more work than reading one. In ClickHouse it forces the engine to read every column file and reconstruct full rows — the opposite of what a columnar system is built for. Columnar databases reward precision: name only the columns a query actually needs.
Data types: mostly familiar, a few new habits
The behavioral differences above are the real adjustment. The type system is mostly familiar — most MySQL types map directly to a ClickHouse equivalent:
| MySQL | ClickHouse | Note |
|---|---|---|
VARCHAR(255) / TEXT | String | No length limit, no separate type |
INT, BIGINT | Int32, Int64 (or UInt32, UInt64) | Pick signed/unsigned and width explicitly |
DATETIME | DateTime / DateTime64 | DateTime64 for sub-second precision |
DECIMAL(10,2) | Decimal(10, 2) | Same idea |
NULL columns | Nullable(String) | Nullability is a wrapper — and it costs performance, so avoid it where possible |
A few ClickHouse-specific types worth knowing on day one:
LowCardinality(String)— wrap any column with a smallish set of distinct values (statuses, country codes, event types). It dictionary-encodes the column and dramatically speeds up filtering and grouping.Enum8/Enum16— like MySQL enums, stored as tiny integers.Date32,IPv4,IPv6— analytics-oriented types for long historical date ranges and for compact, fast IP storage and operations.
Try it in Docker: build a MergeTree table
No install, no cluster. Just Docker:
docker run -d --name clickhouse-playground \
-p 8123:8123 -p 9000:9000 \
--ulimit nofile=262144:262144 \
clickhouse/clickhouse-serverOpen a SQL client inside the container:
docker exec -it clickhouse-playground clickhouse-clientNow create a table, load a million rows with a built-in generator, and run an aggregation:
CREATE TABLE events
(
event_date Date,
user_id UInt64,
event_type LowCardinality(String),
amount Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY (event_date, user_id);
INSERT INTO events
SELECT
today() - rand() % 365,
rand() % 100000,
-- arrays are 1-indexed in ClickHouse, hence the leading 1 +
['click', 'view', 'purchase'][1 + rand() % 3],
(rand() % 10000) / 100
FROM numbers(1000000);
-- Aggregate a million rows in milliseconds
SELECT
event_type,
count() AS events,
round(sum(amount), 2) AS total
FROM events
GROUP BY event_type
ORDER BY total DESC;Code language: JavaScript (javascript)That GROUP BY over a million rows returns almost instantly — and it scales to billions because it only reads the columns the query names.
Remove the container when finished:
docker rm -f clickhouse-playgroundClickHouse in one paragraph
ClickHouse is a columnar, append-only, analytics database. It reads only the columns a query names, compresses them hard, and is built to scan enormous ranges to compute aggregates — not to fetch or update individual rows. Data is inserted in big batches, updates are rare, the data is sorted with ORDER BY rather than indexed row by row, and the transactional source of truth stays in MySQL or Postgres. ClickHouse serves as the reporting half of the stack.
Everything else — engines, materialized views, distributed tables — builds on this foundation.