Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 35 ยท Getting Started

What Is ClickHouse?

Learn what ClickHouse is, why it answers analytical queries over billions of rows in milliseconds, where it fits, and where it is the wrong tool.

ClickHouse is an open-source column-oriented database built for one job: answering analytical questions over very large tables, fast. "How many visitors per country did we have each hour last month?" "What is the 95th percentile of request time per endpoint?" Those queries read a few columns from billions of rows and boil them down to a small result. ClickHouse was created at Yandex to power web analytics and is now used for observability, product analytics, ad tech, finance and IoT.

This lesson explains what the database is, what makes it quick, and when you should (and should not) pick it. The next lessons go deeper: row vs column storage, your first table and queries, and the data types.

ClickHouse cannot run on this site. Everything marked ClickHouse is a read-only snippet. The runnable examples use SQLite as a clearly labelled stand-in: it speaks standard SQL, so it can show the idea of a query, but it does not behave like ClickHouse inside (no column storage, no MergeTree, different functions). Where a result matters, we only show it when we are certain of it.

What ClickHouse is good at

ClickHouse is an OLAP (online analytical processing) system. Its strengths:

  • Scans at speed. It reads only the columns a query names, compresses each column heavily, and processes data in blocks with vectorized code that uses every CPU core. Hundreds of millions to billions of rows per second per server is normal.
  • Fast inserts of big batches. It is built to ingest hundreds of thousands to millions of rows per second, as long as you insert in batches (thousands of rows or more per insert).
  • Real SQL. You write SELECT, JOIN, GROUP BY, window functions and CTEs, plus a very large function library (more than a thousand functions).
  • Scale out. You can shard a table across many servers and replicate each shard. See the later chapters on clusters.
  • Compact storage. Similar values stored together compress ten times or more, so billions of rows fit on modest disks.
SQL
-- ClickHouse: the kind of question it was made for
SELECT
    toStartOfHour(event_time) AS hour,
    count() AS views,
    uniq(user_id) AS visitors
FROM events
WHERE event_date >= today() - 7
GROUP BY hour
ORDER BY hour;

What ClickHouse is not

Every design choice that makes analytics fast makes something else slower. Know these limits before you commit:

Need ClickHouse Better fit
Many tiny transactions (orders, accounts) No row-level ACID transactions across tables PostgreSQL, MySQL
Update or delete single rows all day Updates are heavy "mutations"; a lightweight delete exists, but it is not a primary use PostgreSQL, MySQL
Fetch one row by key, thousands of times per second Possible but wasteful: it reads whole blocks Key-value store, OLTP database
Strict uniqueness and foreign keys None enforced An OLTP database
Tiny data (thousands of rows) Works, but a plain database is simpler SQLite, PostgreSQL

A common production shape is both: an OLTP database holds the live application data, and a copy of its changes streams into ClickHouse for dashboards and reports. See the roadmap in the track index for how later chapters connect them.

The main ideas in one picture

Why ClickHouse is fast

Columns Read only what you ask for
Compression Like values pack tightly
Vectorized Blocks, not single rows
Parallel Every core and server

Four ideas explain most of the speed.

1 / 2

A first taste with a stand-in

The example below is plain SQL on SQLite. It builds a tiny event table and asks an analytical question: views and distinct visitors per page. In ClickHouse the same shape of query would use count() and uniq().

SQL Stand-in: an analytics question (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Notice what the query touches: page, user_id and ms, but never event_time. A column store skips the column it does not need. A row store must read whole rows to get at those three fields. That difference is the subject of the next lesson.

SQL Stand-in: time buckets (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

How ClickHouse is usually deployed

You can run ClickHouse in several ways, covered in Install and run ClickHouse:

  1. Single server, from a package or Docker image. Fine for hundreds of millions of rows and often far more.
  2. Cluster with shards and replicas, coordinated by ClickHouse Keeper.
  3. ClickHouse Cloud, the managed service from the company behind the project.
  4. chDB / clickhouse-local, the engine as a library or a command-line tool for querying files without a server.
flowchart LR
    A["Apps, logs, devices"] --> B["Batch insert"]
    B --> C["ClickHouse tables<br/>(MergeTree)"]
    C --> D["Dashboards"]
    C --> E["Ad hoc SQL"]
    C --> F["Alerts and reports"]

Data flows in as batches, is stored as compressed columns, and is queried with SQL.

Common beginner mistakes

  • Treating it like PostgreSQL. Inserting one row at a time, updating rows often, or expecting joins to be free leads to slow, unhappy systems.
  • Skipping the sorting key. In ClickHouse the table's ORDER BY is the single most important design choice. The MergeTree chapter explains why.
  • Using the biggest types everywhere. A String where a LowCardinality(String) or a UInt8 would do wastes disk and time. See data types.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What kind of workload is ClickHouse designed for?

    ClickHouse is an OLAP, column-oriented database optimized for aggregating large amounts of data.
  2. 2. Why does a column store usually read less data for an analytical query?

    Each column is stored separately, so a query touching three of fifty columns reads about three fiftieths of the data.
  3. 3. Which pattern is a poor fit for ClickHouse?

    Updates and deletes are expensive mutations; ClickHouse prefers append-only batch writes.
  4. 4. A common architecture combines ClickHouse with what?

    OLTP databases handle transactions; ClickHouse receives a copy of the data for analytics.

Summary

  • ClickHouse is an open-source, column-oriented database for fast analytics on huge tables.
  • It is fast because it reads only needed columns, compresses well, processes data in vectorized blocks and uses all cores and servers.
  • It is a poor fit for frequent single-row updates, transactions and key-value lookups.
  • Insert in large batches; design the sorting key first.
  • On this site ClickHouse code is read-only; SQLite stand-ins show the SQL ideas.

Next: Row vs column storage