How to Query ClickHouse for the First Time

November 16, 2026

ClickHouse is a column-oriented database built for scanning billions of rows and aggregating them fast. It is not a general-purpose transactional store. If your first instinct is to model it like Postgres, it will fight you.

This walkthrough gets you from install to a real aggregation query, and covers the two design choices that decide whether ClickHouse is fast or mysteriously slow.

How do you get ClickHouse running?

Docker is the shortest path:

docker run -d --name ch -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server

HTTP on 8123, native protocol on 9000. Then docker exec -it ch clickhouse-client and you have a SQL prompt.

There is also ClickHouse Cloud with a free trial tier, and the single-binary install via curl https://clickhouse.com/ | sh. For local exploration the Docker image is enough and costs nothing.

One gotcha: the default user is default with no password, and the server binds all interfaces. Do not expose 8123 to the open internet in that state.

How do you design a table?

ClickHouse uses MergeTree engines. The sort key is the single most important decision, because it controls what the sparse primary index can skip. Data is physically ordered by that key on disk.

CREATE TABLE events (
  ts DateTime,
  user_id UInt64,
  country LowCardinality(String),
  event String,
  value Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts);

Three things to notice. LowCardinality(String) for low-uniqueness columns like country often halves the storage of that column. The partition key is monthly, which keeps individual partitions manageable — do not partition by day unless you have a lot of data, because too many partitions slows queries. And ORDER BY starts with user_id, so per-user lookups can skip most of the table.

If your dominant query is “totals per day,” then ORDER BY (toDate(ts), country) is better. There is no universal answer; the sort key should match the queries you run most.

How do you load data?

For a CSV, use INSERT ... FORMAT CSVWithNames from the client with input redirection, or the clickhouse-client --query "INSERT ... FORMAT CSV" < file.csv form. Do not insert row by row — ClickHouse wants large batches, ideally 10,000 rows or more per insert. Thousands of tiny inserts create thousands of parts on disk and the background merge process will not keep up.

The column order in the CSV must match the table definition. There is no automatic mapping by header name in FORMAT CSV.

A simple alternative for exploration: clickhouse-local reads a CSV without any server at all and lets you run SQL over it directly. Handy for checking a file before loading it.

How do you write queries it is good at?

Aggregate over lots of rows, filter on the sort key, and avoid SELECT *.

SELECT country, count() AS events, round(avg(value), 2) AS avg_value
FROM events
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY country
ORDER BY events DESC
LIMIT 10;

That runs in well under a second on hundreds of millions of rows if the table is sorted sensibly, because ClickHouse reads only the three columns involved.

Joins are the weak spot. A large-to-large join can exhaust memory. Prefer denormalising, or use Dictionary tables for small lookup tables. If you must join, put the smaller table on the right side and watch the memory limit — the default max_memory_usage for queries can be surprisingly low on a modest box.

What will annoy you?

No real updates or deletes. ALTER TABLE ... UPDATE is a mutation that rewrites whole parts asynchronously; it is not an OLTP update. If you need frequent row updates, you picked the wrong database.

Second, no transactions across statements. Third, count(DISTINCT x) on a high-cardinality column is expensive; use uniq() or uniqCombined() for approximate counts, which is usually fine for dashboards and vastly cheaper.

If you are coming from Postgres, the mental shift is: model your table around your queries, not around normalisation.

Quick Summary

  • ClickHouse is a column store for fast aggregation over huge row counts, not an OLTP database.
  • Choose ORDER BY based on your most frequent query; it decides what can be skipped.
  • Use LowCardinality(String) for country, status, and similar columns.
  • Insert in batches of 10,000+ rows; tiny frequent inserts break the merge process.
  • No row-level updates or transactions; use uniq() for approximate distinct counts.