ClickHouse is an open-source column-oriented database built for analytics: aggregating billions of rows in seconds, on modest hardware. It powers web analytics (Plausible uses it), observability platforms, ad tech and product analytics. If you need fast GROUP BY over large event tables, ClickHouse is hard to beat.

Row vs column store

A row store (MySQL, PostgreSQL) reads whole rows — efficient for fetching one record. A column store reads only the columns a query needs, compresses them very well, and processes them in vectorised batches — ideal for “sum revenue by country per day over 500 million events”.

ClickHouse isn’t a replacement for your transactional database: frequent single-row updates and deletes are expensive, and it has no full multi-statement transactions in the OLTP sense.

1. Install

sudo apt install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt update
sudo apt install -y clickhouse-server clickhouse-client

(Check clickhouse.com for the current repository instructions.) The installer asks for a password for the default user — set a strong one.

sudo systemctl enable --now clickhouse-server
clickhouse-client --password
SELECT version();

2. Keep it private

ClickHouse listens on localhost by default:

  • 8123 — HTTP interface
  • 9000 — native protocol (clickhouse-client, drivers)

If apps on other servers need access, set listen_host in /etc/clickhouse-server/config.d/listen.xml to a private IP and firewall the ports to those servers only. Never expose them publicly with the default user.

Create users with limited rights instead of using default:

CREATE USER app IDENTIFIED WITH sha256_password BY 'a-long-random-password';
CREATE DATABASE analytics;
GRANT SELECT, INSERT ON analytics.* TO app;

(SQL-driven user management requires access_management enabled for the admin user, which recent packages configure.)

3. Create a table

The MergeTree family is ClickHouse’s main table engine:

CREATE TABLE analytics.events
(
    ts        DateTime,
    user_id   UInt64,
    event     LowCardinality(String),
    country   LowCardinality(String),
    url       String,
    revenue   Decimal(10, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (event, ts);
  • ORDER BY — the sorting key, which also acts as the primary index; choose columns you filter by most.
  • PARTITION BY — usually by month; makes dropping old data instant (ALTER TABLE … DROP PARTITION).
  • LowCardinality — dictionary-encodes repetitive strings for speed and compression.

4. Load data

Insert in batches (thousands of rows per insert), not row by row:

clickhouse-client --password --query "INSERT INTO analytics.events FORMAT CSVWithNames" < events.csv

ClickHouse reads CSV, TSV, JSONEachRow, Parquet and many more formats, and can query files and S3 directly (SELECT … FROM s3(…)). For many small inserts from apps, enable async_insert or buffer in your application.

5. Query

SELECT country, count() AS events, sum(revenue) AS revenue
FROM analytics.events
WHERE ts >= now() - INTERVAL 30 DAY AND event = 'purchase'
GROUP BY country
ORDER BY revenue DESC
LIMIT 10;

Check compression:

SELECT table, formatReadableSize(sum(data_compressed_bytes)) AS compressed,
       formatReadableSize(sum(data_uncompressed_bytes)) AS raw
FROM system.parts WHERE active GROUP BY table;

Ratios of 5–20× are common.

6. Memory on a small VPS

ClickHouse is efficient but can use a lot of memory for large aggregations. On a 2–4 GB VPS, set limits in a user profile or config.d:

<clickhouse>
  <profiles><default>
    <max_memory_usage>1500000000</max_memory_usage>
    <max_bytes_before_external_group_by>1000000000</max_bytes_before_external_group_by>
  </default></profiles>
</clickhouse>

External GROUP BY spills to disk instead of failing. ClickHouse’s own guidance suggests more RAM for production; it runs on less for small datasets.

7. Backups and retention

  • BACKUP DATABASE analytics TO Disk('backups', 'analytics-2026-11-04.zip') (configure a backup disk), or clickhouse-backup tool.
  • TTLs delete old data automatically: ALTER TABLE analytics.events MODIFY TTL ts + INTERVAL 12 MONTH;

Related: Plausible Analytics (built on ClickHouse), time-series databases and DuckDB for local analytics.

  • ClickHouse
  • Analytics
  • Databases