TopolearnKnowing what ClickHouse is designed for tells you when to pick it, and why its rules differ from PostgreSQL's.
ClickHouse is an OLAP (analytical) database. A transactional database such as PostgreSQL stores each row together and is great at reading or updating one order by id. ClickHouse stores each column separately, compressed, so a query like "revenue per day for the last year" reads only the two columns it needs, decompresses them with vectorized code and uses every CPU core. That is how a single server aggregates billions of rows per second.
The trade-offs follow from the design:
- Inserts should arrive in batches (thousands of rows), not one row at a time.
- Updates and deletes exist but are heavier than in an OLTP database; data is mostly appended.
- There are no enforced foreign keys and the primary key is not unique.
You can run it several ways. clickhouse server plus clickhouse client is the classic setup; ClickHouse Cloud is the managed service; clickhouse-local runs the full engine over local files with no server at all, which is handy for exploring CSV or Parquet. All of them are the same single clickhouse binary.
The SQL is familiar: SELECT, WHERE, GROUP BY, JOIN, CTEs and window functions all work. What changes is table design and a large library of extra functions, which the next lessons cover.
Example
-- clickhouse-local: query a file directly, no server needed
-- $ clickhouse local
SELECT version(); -- e.g. 25.8.x
-- Generate 10 million rows and aggregate them in well under a second
SELECT number % 10 AS bucket,
count() AS n,
avg(number) AS mean
FROM numbers(10000000)
GROUP BY bucket
ORDER BY bucket
LIMIT 3;
-- 0 1000000 4999995
-- 1 1000000 4999996
-- 2 1000000 4999997Remember
- Columnar storage + vectorized execution = fast scans and aggregations
- Batch your inserts; treat data as mostly append-only
- clickhouse-local runs the engine on files without a server
Watch out for
- Using ClickHouse as the primary database for an app that does many single-row updates.