What ClickHouse Is: Features and Step-by-Step Installation (2026 Guide)

A hands-on ClickHouse guide for makers: what it is, why it's so fast, its key features and how to install it in 5 minutes with curl or Docker.

What ClickHouse is (without the marketing)
- Why it's so fast: the four real reasons
Key features you should know about
My opinion: when ClickHouse is the right call and when it isn't
Installing ClickHouse step by step
Your first queries: from zero to real data
Mistakes you're going to make (I made them)
Conclusion
Frequently asked questions

What ClickHouse is (without the marketing)

ClickHouse is an open-source columnar analytical database, written in C++, designed to run aggregation queries over large volumes of data at speeds that look like a measurement error.

The key word is analytical. In database jargon, this means OLAP (Online Analytical Processing), as opposed to OLTP (Online Transaction Processing), which is what PostgreSQL or MySQL do.

The difference matters more than it seems:

OLTP (Postgres, MySQL, SQLite) is optimized to read and write individual rows very fast. "Give me user 4821". "Update the status of this order". Thousands of small operations per second, with transactions and strong consistency.

OLAP (ClickHouse, DuckDB, BigQuery) is optimized to read many rows but few columns. "Give me the sum of revenue by country over the last 90 days". It doesn't care about a specific row; it cares about sweeping millions of rows and reducing them to a number.

If you want the full context for this category —history, use cases and the vendor map— I cover it in what OLAP databases are.

The technical key that makes it possible is columnar storage. A row-oriented database stores data on disk like this:

[id:1, país:ES, ingresos:42, timestamp:...][id:2, país:MX, ingresos:17, timestamp:...]

If you want to sum the ingresos column, you have to read everything, including the país and timestamp you don't care about. ClickHouse stores each column in its own file:

ingresos: [42, 17, 88, 12, ...]
país:     [ES, MX, ES, AR, ...]

To sum ingresos you only read that file. If your table has 40 columns and your query uses 2, you're reading 5% of the data. That's where the magic starts, and everything else amplifies it.

Why it's so fast: the four real reasons

1. Brutal compression. When you store values of the same type together and also sorted, patterns repeat and compression algorithms become very efficient. ClickHouse's documentation shows compression ratios ranging from 2x on free text to over 1,000x on well-sorted low-cardinality columns. Fewer bytes on disk = less I/O = faster queries.

2. Sparse primary index. ClickHouse doesn't build a B-tree index with one entry per row. It builds a sparse index: one entry every 8,192 rows (a "granule"). This means a few megabytes of index per terabyte of data, which fits in RAM without breaking a sweat. The trade-off is that ClickHouse never looks up an exact row, but the block where it lives. For analytics, that's exactly the right trade-off.

3. Vectorized and parallel engine. Operations run over blocks of columns using SIMD instructions, not row by row. And they parallelize aggressively across all available cores. On an 8-core laptop you already feel the difference; on a 64-core server it's another league.

4. Separation of reads and writes. Inserts create new "parts" that are merged in the background (an LSM-style architecture). Inserting doesn't block queries, and querying doesn't block inserts.

Key features you should know about

These are the ones that actually change how you design your project, not the full datasheet list.

A single binary, zero dependencies

This sounds like a minor detail and is probably the most important thing for a small project. ClickHouse is distributed as a single executable that contains server, client and local mode. No JVM, no mandatory ZooKeeper, no orchestrator. You download a file, run it, you have an analytical database.

Compare that to spinning up Apache Druid, which needs six different process types. For a maker working alone, this difference is what decides whether the project ships or dies in the docker-compose.yml.

`clickhouse-local`: SQL over files with no server

You can query CSV, Parquet or JSON straight from disk or from a URL, without importing anything or starting a server:

./clickhouse local --query "
  SELECT country, count() AS visitas
  FROM file('logs.csv', CSVWithNames)
  GROUP BY country
  ORDER BY visitas DESC
  LIMIT 10
"

This replaces pandas scripts for quick exploration and is noticeably faster on large files.

Incremental materialized views

This is the feature that has most changed how I build. A materialized view in ClickHouse isn't a cache that refreshes: it's an insert trigger. Every time you insert into the source table, the aggregation is computed over that block and written to the destination table.

The result is that you move the cost from query time to insert time. Your dashboard queries a table of 4,000 pre-aggregated rows instead of 400 million raw rows. I cover it in depth in the advanced guide, but hold on to the idea.

Full SQL support with real JOINs

For years the standard criticism of ClickHouse was "JOINs are bad". That no longer holds. It supports all standard JOIN types, has an optimizer that reorders joins using column statistics, and adds types that don't exist in standard SQL like ASOF JOIN (join by the closest temporal value, not exact), which is pure gold for time series and financial data.

It also extends SQL with hundreds of analytical functions: uniqExact, quantileTDigest, windowFunnel, sequenceMatch. Things that would be a 60-line CTE in Postgres are a function here.

Compatibility with 70+ formats and data lakes

It reads and writes Parquet, Iceberg, Delta Lake, Avro, Protobuf, JSON in all its variants. It can query files in S3 directly without ingesting them. This means it doesn't lock you in: if tomorrow you want to migrate, your data leaves as Parquet with one statement.

JSON without schema explosion

You can ingest semi-structured data with a native JSON type that internally stores it columnarly. Ingest events with varying shapes without designing the perfect schema up front, and you still get columnar-database performance.

My opinion: when ClickHouse is the right call and when it isn't

This is where most tutorials fail you, because they're written by people who want to sell you something.

ClickHouse is a great idea if:

You have an events table that grows without end —product analytics, logs, metrics, clicks, IoT telemetry— and your queries are aggregations over time ranges. This is the canonical case and where it'll feel like magic.

You're building user-facing analytics: a dashboard inside your product that many customers query simultaneously. ClickHouse handles high concurrency with millisecond latencies, which is exactly where warehouses like Snowflake get expensive.

Your BigQuery or Snowflake bill gives you anxiety. A dedicated 30-60 €/month server with ClickHouse handles workloads that cost an order of magnitude more on a consumption-based warehouse. For a bootstrapped project this isn't an optimization, it's the difference between viable and unviable.

ClickHouse is a bad idea if:

You need frequent per-row updates and deletes. ClickHouse has improved a lot here (lightweight mutations, ReplacingMergeTree, lightweight deletes), but it's still an engine designed to write once and read many times. If your workload is a CRUD app, use Postgres.

You need multi-table ACID transactions. Transactional support is limited and not the project's goal. Don't put your order state here.

You have less than a few million rows. With 500,000 rows Postgres with a decent index already gives you millisecond answers. Adding ClickHouse is operational complexity with no benefit. The right answer for a small dataset is not to change databases.

Your team is zero people and you already have three services to maintain. It's one more service to monitor, back up and update. It's worth it when the performance pain is real, not when it's anticipated.

My practical rule: if a GROUP BY over your biggest table takes more than 5 seconds in Postgres and that query is central to your product, it's time to look at ClickHouse. Before that, no.

One additional nuance: the pattern that has worked best for me isn't replacing Postgres, but putting them together. Postgres stays as the transactional source of truth —users, orders, configuration— and ClickHouse receives a copy of the events table via CDC or a replication job. Each one does what it knows how to do.

Installing ClickHouse step by step

Let's get practical. Three paths depending on what you need.

Option 1: quick install with curl (macOS, Linux, FreeBSD)

The most direct way. Download the right binary for your system:

curl https://clickhouse.com/ | sh

On Linux and macOS this also installs clickhousectl (alias chctl) in ~/.local/bin, the official CLI for managing several local versions, starting servers in the background and connecting with ClickHouse Cloud.

If you only want the binary without the management CLI:

curl https://clickhouse.com/ | CLICKHOUSE_ONLY=1 sh

Note for macOS users: if Gatekeeper complains that it can't verify the binary's developer, go to System Settings → Privacy & Security and authorize the execution. It's the normal macOS behavior for unsigned binaries.

Start the server:

./clickhouse server

And in another terminal, the client:

./clickhouse client

You should see something like this:

ClickHouse client version 24.5.1.117 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.5.1.

local-host :)

Data is stored in the current directory and survives server restarts.

Option 2: Docker (recommended if you already use containers)

My default option when the project already has a docker-compose.yml, because it avoids dirtying the system and makes the environment reproducible.

docker run -d \
  --name clickhouse \
  -p 8123:8123 \
  -p 9000:9000 \
  -v clickhouse_data:/var/lib/clickhouse \
  --ulimit nofile=262144:262144 \
  clickhouse/clickhouse-server

The two ports matter and confuse everyone at first:

8123 is the HTTP interface. Most web clients, BI tools and curl use it.
9000 is the native TCP protocol. Faster and more compact; clickhouse-client and native drivers use it.

The --ulimit nofile flag isn't optional in practice: ClickHouse opens many file descriptors and without raising the limit you'll see weird errors under load.

As a docker-compose.yml:

services:
  clickhouse:
    image: clickhouse/clickhouse-server
    container_name: clickhouse
    ports:
      - "8123:8123"
      - "9000:9000"
    volumes:
      - clickhouse_data:/var/lib/clickhouse
      - ./config.d:/etc/clickhouse-server/config.d
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    environment:
      CLICKHOUSE_USER: creacosas
      CLICKHOUSE_PASSWORD: cambia_esto
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1

volumes:
  clickhouse_data:

Connect to the client inside the container:

docker exec -it clickhouse clickhouse-client --user creacosas --password cambia_esto

Option 3: without installing anything

If you just want to try the syntax and see the speed, the official playground has real datasets loaded and runs queries from the browser. Zero friction.

Your first queries: from zero to real data

Let's build something close to a real case: an events table for a website.

Create the database and the table

CREATE DATABASE creacosas;

USE creacosas;

CREATE TABLE eventos
(
    fecha       Date,
    timestamp   DateTime,
    usuario_id  UInt32,
    evento      LowCardinality(String),
    pais        LowCardinality(String),
    url         String,
    duracion_ms UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY (evento, fecha, usuario_id);

There are three decisions here worth explaining because they're the ClickHouse decisions:

ENGINE = MergeTree is the default table engine and the one you'll use 90% of the time. The whole MergeTree family (ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree) shares the same base with added behaviors in part merging.

ORDER BY is the most important decision in your whole schema. It defines the physical order of data on disk and, by default, also the primary key. The rule: put first the columns you filter by most and that have lower cardinality. Here we almost always filter by event type and by date, so they go first. Getting this wrong is the number one cause of "my ClickHouse is slow".

LowCardinality(String) is a type that applies dictionary encoding. If a column has fewer than ~10,000 distinct values (countries, event types, plan names), wrapping it in LowCardinality reduces storage and speeds up filters noticeably. It's a free win that almost nobody uses at first.

Insert test data

Let's generate 10 million synthetic rows to have something to play with:

INSERT INTO eventos
SELECT
    toDate('2026-01-01') + (number % 220)                       AS fecha,
    toDateTime(fecha) + (number % 86400)                        AS timestamp,
    (number % 50000) + 1                                        AS usuario_id,
    ['pageview','click','signup','purchase'][(intHash32(number) % 4) + 1]      AS evento,
    ['ES','MX','AR','CO','CL','US'][(intHash32(number + 7) % 6) + 1]           AS pais,
    concat('/pagina/', toString(number % 500))                  AS url,
    (number % 5000) + 100                                       AS duracion_ms
FROM numbers(10000000);

The numbers() function generates rows on the fly: it's the standard way to create test datasets in ClickHouse without downloading anything. I use intHash32() instead of number % N directly for categorical columns, because if two columns use modulos with common factors (4 and 6, for example) they end up correlated and the results come out artificially uniform.

Query

SELECT
    pais,
    evento,
    count()                         AS total,
    round(avg(duracion_ms))         AS duracion_media,
    uniqExact(usuario_id)           AS usuarios_unicos
FROM eventos
WHERE fecha >= '2026-03-01'
  AND evento = 'purchase'
GROUP BY pais, evento
ORDER BY total DESC;

Notice the last line of the client output. It tells you how many rows it processed, how many bytes it read and at what speed:

6 rows in set. Elapsed: 0.021 sec. Processed 1.14 million rows, 8.32 MB (54.28 million rows/s., 396.19 MB/s.)

That number —"processed 1.14 million rows" out of a total of 10 million— is what matters. ClickHouse only read 11% of the table because the ORDER BY (evento, fecha, ...) let it skip everything else. Optimizing ClickHouse is almost always about reducing that number.

A trick: see the real size on disk

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes))   AS comprimido,
    formatReadableSize(sum(data_uncompressed_bytes)) AS sin_comprimir,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE database = 'creacosas'
GROUP BY table;

System tables (system.columns, system.parts, system.query_log) are one of the best things about ClickHouse: all the engine's introspection is queryable SQL.

Mistakes you're going to make (I made them)

Inserting row by row. ClickHouse hates small inserts. Each INSERT creates a new "part" on disk that then has to be merged. A thousand one-row inserts generate a thousand parts and the server chokes with the Too many parts error. Insert in batches of at least 1,000 rows —ideally between 10,000 and 100,000— or turn on async inserts with async_insert=1.

Using Nullable by default. Each Nullable column adds an extra mask column and disables some optimizations. If you can use a sentinel value (0, empty string, 1970-01-01), do it.

Putting a high-cardinality column first in the ORDER BY. If you put usuario_id or a UUID first, you destroy compression and the ability to skip blocks. Low cardinality first, always.

Expecting UPDATE to work like in Postgres. It works, but it's a heavy operation. If your design depends on constantly updating rows, rethink the design (or rethink the database).

Conclusion

ClickHouse is not a general-purpose database and that's exactly its value: it does one thing —aggregate over large volumes— better than almost anyone, and it does it with a ridiculously small operational footprint for what it offers. One binary, no dependencies, running from a laptop to hundreds of nodes with the same engine.

For a maker working alone, that last point is what decides it. You don't need a data team to operate ClickHouse on a VPS. You need to understand your ORDER BY well and not insert row by row.

If you're coming from Postgres and your dashboards are dying, spend an afternoon on this. The initial learning curve is hours, not weeks, and the performance jump is the kind you notice without needing to measure it.

In the next posts of this series we go deeper into the two questions that come after installing it:

Alternatives to ClickHouse: a full comparison — DuckDB, StarRocks, Druid, Pinot, TimescaleDB and the cloud warehouses, with a comparison table and a verdict per use case.
Advanced ClickHouse guide — materialized views, projections, compression codecs, TTL, S3 tiering and slow-query diagnosis.

Frequently asked questions

Is ClickHouse free?

The engine is open source under the Apache 2.0 license and you can self-host it at no cost and with no feature limits. ClickHouse Cloud is the paid managed service, with a model based on separate compute and storage consumption and scale-to-zero when there's no activity.