How to Install ClickHouse on Ubuntu 24.04 — Production Analytics Database on Your VPS

ClickHouse is the columnar analytics database behind some of the most demanding real-time workloads on the internet — Cloudflare's HTTP request logs, Uber's trip analytics, eBay's observability stack, and countless billion-row dashboards. It ingests millions of rows per second, compresses data to a fraction of its raw size, and answers aggregation queries over terabytes in seconds on a single server. This guide walks you through a full production install on Ubuntu 24.04, from the official apt repository to replicated clusters backed by ClickHouse Keeper, with detailed coverage of users.xml, config.xml, MergeTree table design, partitioning, materialized views, and backup with clickhouse-backup.

Want to run ClickHouse alongside PostgreSQL, Grafana, and Kafka on the same infrastructure? Our Professional VPS gives you 16 GB of RAM and 200 GB of NVMe — enough to run a single-node ClickHouse deployment handling tens of thousands of inserts per second. Launch a Professional VPS now and follow along in under an hour.

Table of Contents

What is ClickHouse?

ClickHouse is an open-source columnar OLAP (online analytical processing) database management system developed originally at Yandex and now maintained by ClickHouse Inc. Unlike row-oriented databases such as PostgreSQL or MySQL that store each row contiguously on disk, ClickHouse stores each column as a separate file. When you run an aggregation like SELECT sum(revenue) FROM events WHERE date > '2026-01-01', the engine only reads the date and revenue columns — the other fifty columns in your table are never touched. Combined with aggressive compression (LZ4 and ZSTD), vectorized query execution, and SIMD instructions, this produces analytical query performance that is typically 100 to 1000 times faster than a traditional row-store.

ClickHouse is designed for immutable, append-heavy workloads. It excels at ingesting high-velocity event streams — web analytics, application logs, IoT sensor readings, trade ticks, ad impressions, clickstreams, and observability telemetry — and then answering slicing-and-dicing queries over billions or trillions of rows. Single-node deployments routinely handle 500,000 to 2 million rows per second on commodity hardware. Sharded clusters at companies like Cloudflare and ByteDance process petabytes per day.

The core table engine is MergeTree. Data is written to small sorted parts in the background, those parts are merged into larger parts over time, and every query reads the union of all parts filtered by the primary key. This design is why ClickHouse feels slow to update or delete individual rows (it is not built for that) but blazing fast to aggregate and scan. Related engines — ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree — handle common patterns like deduplication, rollups, and mutable-state-as-events.

Typical use cases include real-time web analytics (a Google Analytics alternative with Plausible, Umami, or custom dashboards), product analytics (events tables with billions of user actions), observability (logs, metrics, and traces — OpenTelemetry has a native ClickHouse exporter), financial tick data, ad-tech attribution, and fraud detection. If your workload is "append a lot, aggregate a lot, rarely update," ClickHouse is almost certainly the right answer.

Why Self-Host ClickHouse on Your VPS?

Running ClickHouse on your own VPS instead of using a managed service offers concrete advantages:

• Flat-rate cost at any scale — A Professional VPS costs EUR 19.99/month regardless of how many rows you ingest or queries you run. Managed services bill per GB of storage, per compute hour, and per terabyte scanned.
• Full SQL and engine access — Every table engine, every function, every experimental feature is available. Managed services frequently lag the open-source release by months and disable admin-level settings.
• Data residency and GDPR — Your data stays on a server you control, in the region you chose. No cross-border replication, no vendor-side backups you cannot audit.
• No per-query metering — Run exploratory queries, rebuild materialized views, and backfill tables freely. You are paying for the server, not each scan.
• Integration with the rest of your stack — Run ClickHouse next to PostgreSQL, Grafana, and Kafka on the same private network — zero egress fees, single-digit-millisecond latency.
• Custom dictionaries and UDFs — Ship your own user-defined functions, external dictionaries, and storage policies. This is restricted or unavailable on most managed offerings.

Cost Comparison: Self-Hosted vs. Managed Analytics Databases

ClickHouse vs Snowflake, BigQuery, and TimescaleDB

ClickHouse vs Snowflake. Snowflake is a fully managed cloud data warehouse with separated storage and compute, virtual warehouses that auto-suspend, and first-class support for semi-structured data (VARIANT type). It excels at enterprise BI workloads where analysts run ad-hoc queries against a shared data lake. ClickHouse is faster per dollar for high-cardinality, time-series, and real-time workloads — the query patterns where you filter by timestamp and aggregate over a handful of dimensions. Snowflake wins on convenience, SSO, governance, and sharing. ClickHouse wins on cost, raw ingest throughput (500k–2M rows/sec single-node), and query latency (sub-second aggregations over billions of rows).

ClickHouse vs BigQuery. BigQuery is serverless — you pay per terabyte scanned with no infrastructure to manage. It shines for ad-hoc analytics over massive datasets that you touch infrequently. ClickHouse excels when you run the same queries repeatedly (dashboards, alerting, real-time APIs) because a well-designed MergeTree with a good primary key answers in milliseconds at essentially zero marginal cost. BigQuery's strengths — geospatial functions, ML integration, federated queries across Google Cloud — are unmatched, but the scan premium adds up fast for production dashboards. Rule of thumb: BigQuery for data science exploration, ClickHouse for operational analytics.

ClickHouse vs TimescaleDB. TimescaleDB is a PostgreSQL extension that turns Postgres into a time-series database with automatic partitioning (hypertables), continuous aggregates, and compression. It retains full PostgreSQL compatibility — foreign keys, transactions, joins, the entire extension ecosystem — which makes it ideal when time-series is one workload among many (OLTP + time-series in a single database). ClickHouse is purpose-built for analytics and drops OLTP features (no foreign keys, limited UPDATE/DELETE, no true transactions across tables) in exchange for 10–50x faster analytical throughput. Pick TimescaleDB when your queries join time-series to relational data and you value Postgres ergonomics. Pick ClickHouse when analytics is the product and you will design your schema around columnar access patterns.

Prerequisites

Before you begin, make sure you have:

• A VPS running Ubuntu 24.04 LTS with root or sudo access
• SSH access to your server
• At least 8 GB of RAM for development, 16 GB+ for production workloads
• At least 50 GB of free disk space — NVMe SSD strongly recommended, ClickHouse is I/O-bound
• An x86_64 CPU with SSE 4.2 (all modern VPS CPUs support this)

Recommended Plan: Professional

For running ClickHouse against real workloads (tens of millions of rows, dashboards, a materialized view or two), we recommend the Professional VPS:

- 8 vCPU cores

- 16 GB RAM

- 200 GB NVMe SSD

- Unmetered bandwidth

- EUR 19.99/month

For smaller dev or staging setups, the Starter plan at EUR 7.99/month (4 vCPU, 8 GB RAM, 100 GB NVMe) is sufficient. For replicated production clusters, dedicated Keeper nodes, or workloads ingesting 500k+ rows/sec sustained, step up to Business at EUR 29.99/month (16 vCPU, 32 GB RAM, 400 GB NVMe) or higher.

Connect to your server via SSH to get started:

ssh root@your-server-ip

Step 1: Update System Packages

Start by updating your package index and upgrading installed packages:

sudo apt update && sudo apt upgrade -y

Install the prerequisites needed to add the ClickHouse repository:

sudo apt install -y apt-transport-https ca-certificates curl gnupg

If your kernel was updated during the upgrade, reboot before continuing:

sudo reboot

Step 2: Add the ClickHouse APT Repository

ClickHouse publishes official deb packages in its own repository. Using the apt repo (rather than a manual .deb download) gives you automatic security updates and simple version upgrades.

Import the signing key:

curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' \
  | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

Add the repository to your apt sources:

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

Refresh the package index:

sudo apt update

Expected output (abbreviated):

Get:1 https://packages.clickhouse.com/deb stable InRelease [6,831 B]
Get:2 https://packages.clickhouse.com/deb stable/main amd64 Packages [43.5 kB]
Reading package lists... Done

Step 3: Install clickhouse-server and clickhouse-client

Install both the server and the command-line client:

sudo apt install -y clickhouse-server clickhouse-client

During installation, the package prompts you to set a password for the default user. You can leave it blank for now (local development) and set a proper password in Step 5, or enter a secure password at the prompt — ClickHouse will hash it with SHA-256 and store the hash in users.d/default-password.xml.

The package performs these actions:

Step 4: Start ClickHouse and Verify the Installation

Start the service and enable it on boot:

sudo systemctl enable --now clickhouse-server

Check the service status:

sudo systemctl status clickhouse-server

Expected output:

● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
     Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled)
     Active: active (running) since Wed 2026-04-16 10:00:00 UTC; 15s ago
   Main PID: 2345 (clckhouse-watch)
      Tasks: 182 (limit: 18902)
     Memory: 520.3M

Connect with the client:

clickhouse-client --password

You should see the interactive prompt:

ClickHouse client version 24.10.1.xxxx
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.10.1.
host :)

Run a sanity query:

SELECT version(), now(), hostName();

Exit with exit or Ctrl+D. ClickHouse is running.

Step 5: Configure users.xml (Authentication and Quotas)

The users.xml file at /etc/clickhouse-server/users.xml defines user accounts, profiles (per-query resource limits), and quotas (per-user-per-time-window limits). The ClickHouse convention is to never edit users.xml directly — instead, drop override files into /etc/clickhouse-server/users.d/. This keeps upgrades safe and changes auditable.

Set a SHA-256 password for the default user

Generate a password hash:

PASSWORD=$(openssl rand -base64 24)
HASH=$(echo -n "$PASSWORD" | sha256sum | awk '{print $1}')
echo "Password: $PASSWORD"
echo "SHA-256:  $HASH"

Write an override file:

sudo tee /etc/clickhouse-server/users.d/default-password.xml > /dev/null <<EOF
<clickhouse>
    <users>
        <default>
            <password_sha256_hex>$HASH</password_sha256_hex>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <access_management>1</access_management>
        </default>
    </users>
</clickhouse>
EOF

Setting <access_management>1</access_management> promotes default to a full admin user that can manage other users via SQL (CREATE USER, GRANT, etc.) — this is the modern, recommended approach over managing everything in XML.

Create a read-only analytics user via SQL

Restart ClickHouse to load the new password:

sudo systemctl restart clickhouse-server

Then connect and create scoped users:

clickhouse-client --password "$PASSWORD"

CREATE USER analyst IDENTIFIED WITH sha256_password BY 'choose-a-strong-password';
GRANT SELECT ON analytics.* TO analyst;
CREATE USER ingest IDENTIFIED WITH sha256_password BY 'another-strong-password';
GRANT INSERT, SELECT ON analytics.* TO ingest;

Tune query-level limits in the default profile

Edit the built-in default profile to cap runaway queries. Create /etc/clickhouse-server/users.d/limits.xml:

<clickhouse>
    <profiles>
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
            <max_memory_usage_for_user>12000000000</max_memory_usage_for_user>
            <max_execution_time>300</max_execution_time>
            <max_rows_to_read>10000000000</max_rows_to_read>
            <max_bytes_to_read>500000000000</max_bytes_to_read>
            <readonly>0</readonly>
        </default>
        <readonly>
            <max_memory_usage>4000000000</max_memory_usage>
            <max_execution_time>60</max_execution_time>
            <readonly>1</readonly>
        </readonly>
    </profiles>
</clickhouse>

• max_memory_usage — bytes a single query may allocate. 10 GB is a reasonable ceiling on a 16 GB server.
• max_memory_usage_for_user — ceiling across all concurrent queries from a user.
• max_execution_time — seconds before a query is cancelled.
• readonly=1 — disallows writes and most settings changes for the readonly profile.

Step 6: Configure config.xml (listen_host, Memory Limits)

config.xml at /etc/clickhouse-server/config.xml controls the server itself — networking, ports, storage paths, logging, and server-wide memory limits. Just like users, prefer drop-in overrides in /etc/clickhouse-server/config.d/.

Expose the server on your private network

By default ClickHouse binds to 127.0.0.1 only. To accept connections from another server on your private network, create /etc/clickhouse-server/config.d/listen.xml:

<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <listen_host>::</listen_host>
    <tcp_port>9000</tcp_port>
    <http_port>8123</http_port>
    <interserver_http_port>9009</interserver_http_port>
</clickhouse>

Security warning. Binding to 0.0.0.0 exposes ClickHouse on every network interface including the public one. Either restrict listen_host to the private interface IP (for example 10.0.0.5), or firewall ports 8123, 9000, and 9009:

bash

> sudo ufw allow from 10.0.0.0/24 to any port 8123

Cap server-wide memory and cache sizes

Create /etc/clickhouse-server/config.d/memory.xml:

<clickhouse>
    <max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
    <max_concurrent_queries>100</max_concurrent_queries>
    <mark_cache_size>2147483648</mark_cache_size>
    <uncompressed_cache_size>4294967296</uncompressed_cache_size>
    <background_pool_size>16</background_pool_size>
    <background_merges_mutations_concurrency_ratio>2</background_merges_mutations_concurrency_ratio>
</clickhouse>

• max_server_memory_usage_to_ram_ratio=0.8 — the server will refuse to allocate more than 80% of system RAM, leaving room for the kernel page cache. On a 16 GB VPS this is roughly 12.8 GB.
• max_concurrent_queries=100 — queue beyond this number of in-flight queries. Lower it (e.g. 20) on smaller servers.
• mark_cache_size=2 GiB — caches primary-key "marks" (index offsets). A hot mark cache is critical for low-latency point-lookups.
• uncompressed_cache_size=4 GiB — caches decompressed blocks. Useful for repeated scans over the same ranges.

Storage path on a separate volume

If you mounted an NVMe volume at /mnt/data, move the data directory:

sudo systemctl stop clickhouse-server
sudo rsync -a /var/lib/clickhouse/ /mnt/data/clickhouse/
sudo chown -R clickhouse:clickhouse /mnt/data/clickhouse

Then create /etc/clickhouse-server/config.d/path.xml:

<clickhouse>
    <path>/mnt/data/clickhouse/</path>
    <tmp_path>/mnt/data/clickhouse/tmp/</tmp_path>
    <user_files_path>/mnt/data/clickhouse/user_files/</user_files_path>
    <format_schema_path>/mnt/data/clickhouse/format_schemas/</format_schema_path>
</clickhouse>

Restart:

sudo systemctl restart clickhouse-server

Step 7: Design Your First MergeTree Table

The MergeTree engine is the core of ClickHouse. Let's create a realistic analytics schema — web events — and walk through the design choices.

clickhouse-client --password

CREATE DATABASE analytics;
CREATE TABLE analytics.events
(
    event_time      DateTime CODEC(DoubleDelta, ZSTD(3)),
    event_date      Date DEFAULT toDate(event_time),
    user_id         UInt64,
    session_id      UUID,
    event_name      LowCardinality(String),
    page_url        String CODEC(ZSTD(3)),
    referrer        String CODEC(ZSTD(3)),
    country         LowCardinality(FixedString(2)),
    device_type     LowCardinality(String),
    browser         LowCardinality(String),
    os              LowCardinality(String),
    revenue_cents   UInt32 DEFAULT 0,
    properties      Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_name, event_date, user_id)
TTL event_date + INTERVAL 2 YEAR
SETTINGS index_granularity = 8192;

Several design choices deserve explanation:

• LowCardinality(String) wraps columns that have a small number of distinct values (event names, countries, browsers). It stores a dictionary of values plus a column of small integers — typically 10–100x smaller and faster than raw strings.
• CODEC(DoubleDelta, ZSTD(3)) on event_time stores timestamps as the delta-of-delta between consecutive rows. On sorted time-series data this compresses to a handful of bits per row.
• CODEC(ZSTD(3)) overrides the default LZ4 with Zstandard at level 3 for large text columns — trades a few percent CPU for 30–50% smaller storage.
• ORDER BY (event_name, event_date, user_id) is the primary (sparse) index. ClickHouse stores a mark every index_granularity rows (8192 by default). Queries that filter on a leading prefix of the ORDER BY key skip huge ranges of data.
• PARTITION BY toYYYYMM(event_date) creates one partition per month. Partitions are the unit for drop/detach/backup — not for query pruning (that is the primary index's job).
• TTL event_date + INTERVAL 2 YEAR automatically deletes rows older than two years during background merges.

INSERT INTO analytics.events
SELECT
    now() - INTERVAL number SECOND AS event_time,
    toDate(event_time) AS event_date,
    rand64() AS user_id,
    generateUUIDv4() AS session_id,
    ['page_view', 'click', 'purchase', 'signup'][rand() % 4 + 1] AS event_name,
    concat('/page/', toString(rand() % 1000)) AS page_url,
    '' AS referrer,
    ['US', 'GB', 'DE', 'FR', 'JP'][rand() % 5 + 1] AS country,
    ['mobile', 'desktop', 'tablet'][rand() % 3 + 1] AS device_type,
    ['Chrome', 'Safari', 'Firefox', 'Edge'][rand() % 4 + 1] AS browser,
    ['Windows', 'macOS', 'Linux', 'iOS', 'Android'][rand() % 5 + 1] AS os,
    rand() % 10000 AS revenue_cents,
    map() AS properties
FROM numbers(10000000);

Ten million rows in a handful of seconds. Now try the kind of query ClickHouse was built for:

SELECT
    country,
    event_name,
    count() AS events,
    sum(revenue_cents) / 100.0 AS revenue
FROM analytics.events
WHERE event_date >= today() - 30
GROUP BY country, event_name
ORDER BY revenue DESC
LIMIT 20;

On a Professional VPS, this aggregation returns in tens of milliseconds.

Step 8: Partitioning and Ordering Strategy

Partitioning and ORDER BY are the two knobs that determine whether your cluster is fast or catastrophically slow. A few guiding principles:

Partition coarsely. Aim for 1–12 months per partition for time-series data. A partition should contain millions of rows minimum. Too many small partitions slow down every query and merge cycle. PARTITION BY toYYYYMM(event_date) is the default recommendation; toYYYYMMDD is only appropriate for truly massive daily volumes.

ORDER BY from lowest to highest cardinality filter. The primary key is a left-to-right sorted prefix. Queries that filter on the leading columns skip data efficiently. For web analytics, (event_name, event_date, user_id) works when many queries filter by event type; for log storage, (service, severity, timestamp) fits WHERE service = 'api' AND severity = 'ERROR'.

Avoid high-cardinality leading columns. Do not lead with user_id unless almost every query filters by a specific user — it scatters the index and defeats compression.

Use skipping indexes for secondary filters. When you need to filter on a column that is not in the ORDER BY, add a data-skipping index:

ALTER TABLE analytics.events
    ADD INDEX idx_url page_url TYPE bloom_filter(0.01) GRANULARITY 4;

Queries with WHERE page_url = '/checkout' will skip granules where the bloom filter proves the value cannot be present.

Projections for alternative orderings. If one dashboard filters by user_id and another by event_name, add a projection that physically stores a second copy sorted differently:

ALTER TABLE analytics.events
    ADD PROJECTION proj_by_user (
        SELECT * ORDER BY user_id
    );
ALTER TABLE analytics.events MATERIALIZE PROJECTION proj_by_user;

Step 9: Materialized Views for Real-Time Aggregation

Materialized views in ClickHouse are insert triggers that write aggregated data to a target table as new rows arrive. This is the standard pattern for real-time dashboards.

Create a target table using AggregatingMergeTree:

CREATE TABLE analytics.events_hourly
(
    event_hour      DateTime,
    event_name      LowCardinality(String),
    country         LowCardinality(FixedString(2)),
    events          AggregateFunction(count),
    users           AggregateFunction(uniq, UInt64),
    revenue_cents   AggregateFunction(sum, UInt32)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(event_hour)
ORDER BY (event_name, country, event_hour);

Create the materialized view that feeds it:

CREATE MATERIALIZED VIEW analytics.events_hourly_mv
TO analytics.events_hourly
AS SELECT
    toStartOfHour(event_time) AS event_hour,
    event_name,
    country,
    countState() AS events,
    uniqState(user_id) AS users,
    sumState(revenue_cents) AS revenue_cents
FROM analytics.events
GROUP BY event_hour, event_name, country;

Query the pre-aggregated table via the -Merge combinator:

SELECT
    event_hour,
    event_name,
    countMerge(events) AS events,
    uniqMerge(users) AS uniq_users,
    sumMerge(revenue_cents) / 100.0 AS revenue
FROM analytics.events_hourly
WHERE event_hour >= now() - INTERVAL 24 HOUR
GROUP BY event_hour, event_name
ORDER BY event_hour;

Dashboards that previously scanned a billion-row raw table now read a few thousand pre-aggregated rows — microsecond latency, trivially cacheable.

Step 10: Backup with clickhouse-backup

ClickHouse's native BACKUP and RESTORE SQL commands are fine for basic use, but the community tool clickhouse-backup adds incremental backups, compression, and direct upload to S3, Google Cloud Storage, or any S3-compatible object store.

Install:

curl -L https://github.com/Altinity/clickhouse-backup/releases/latest/download/clickhouse-backup-linux-amd64.tar.gz \
    | sudo tar -xz -C /usr/local/bin/
sudo chmod +x /usr/local/bin/clickhouse-backup

Create a configuration file at /etc/clickhouse-backup/config.yml:

general:
  remote_storage: s3
  backups_to_keep_local: 3
  backups_to_keep_remote: 30
clickhouse:
  username: default
  password: "your-clickhouse-password"
  host: localhost
  port: 9000
s3:
  access_key: "YOUR_S3_KEY"
  secret_key: "YOUR_S3_SECRET"
  bucket: "clickhouse-backups"
  endpoint: "https://s3.eu-central-1.amazonaws.com"
  region: "eu-central-1"
  path: "{cluster}/{shard}"
  compression_level: 3
  compression_format: zstd

Take a full backup:

sudo clickhouse-backup create full_$(date +%Y%m%d)
sudo clickhouse-backup upload full_$(date +%Y%m%d)

Take an incremental backup (only changed parts since the last full):

sudo clickhouse-backup create_remote --diff-from-remote=full_20260416 incr_$(date +%Y%m%d_%H)

Schedule via cron:

0 2   0  root  clickhouse-backup create_remote full_$(date +\%Y\%m\%d)
0 2   1-6 root clickhouse-backup create_remote --diff-from-remote=$(clickhouse-backup list remote | tail -1 | awk '{print $1}') incr_$(date +\%Y\%m\%d)

Restore:

sudo clickhouse-backup download full_20260416
sudo clickhouse-backup restore full_20260416

Step 11: Replication with ClickHouse Keeper

Replication in ClickHouse requires a coordination service — historically Apache ZooKeeper, but the modern recommendation is ClickHouse Keeper, a drop-in replacement written in C++ that ships with the server and uses the Raft consensus protocol. A production cluster runs 3 (or 5) Keeper nodes and 2+ replica nodes per shard.

Configure ClickHouse Keeper (3-node quorum)

On each of your three coordinator servers (or co-located on the ClickHouse servers for small clusters), create /etc/clickhouse-server/config.d/keeper.xml:

<clickhouse>
    <keeper_server>
        <tcp_port>9181</tcp_port>
        <server_id>1</server_id>
        <log_storage_path>/var/lib/clickhouse/coordination/log</log_storage_path>
        <snapshot_storage_path>/var/lib/clickhouse/coordination/snapshots</snapshot_storage_path>
        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>
            <raft_logs_level>information</raft_logs_level>
        </coordination_settings>
        <raft_configuration>
            <server>
                <id>1</id>
                <hostname>ch-keeper-1.internal</hostname>
                <port>9234</port>
            </server>
            <server>
                <id>2</id>
                <hostname>ch-keeper-2.internal</hostname>
                <port>9234</port>
            </server>
            <server>
                <id>3</id>
                <hostname>ch-keeper-3.internal</hostname>
                <port>9234</port>
            </server>
        </raft_configuration>
    </keeper_server>
</clickhouse>

Change <server_id> to 2 and 3 on the respective nodes. Restart all three. Verify the quorum is healthy:

echo stat | nc localhost 9181

You should see one leader and two follower nodes.

Point ClickHouse at Keeper

On every ClickHouse node that will hold replicated tables, create /etc/clickhouse-server/config.d/zookeeper.xml:

<clickhouse>
    <zookeeper>
        <node><host>ch-keeper-1.internal</host><port>9181</port></node>
        <node><host>ch-keeper-2.internal</host><port>9181</port></node>
        <node><host>ch-keeper-3.internal</host><port>9181</port></node>
    </zookeeper>
    <macros>
        <cluster>analytics_cluster</cluster>
        <shard>01</shard>
        <replica>replica_1</replica>
    </macros>
</clickhouse>

The <macros> differ per replica — change <replica> to replica_2 on the second replica, and <shard> for each shard.

Create a replicated table

CREATE TABLE analytics.events_replicated ON CLUSTER analytics_cluster
(
    event_time  DateTime,
    event_date  Date DEFAULT toDate(event_time),
    user_id     UInt64,
    event_name  LowCardinality(String),
    revenue_cents UInt32 DEFAULT 0
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_name, event_date, user_id);

Writes to any replica propagate to the others through the Keeper quorum. Reads can hit any replica. For client-side load balancing across replicas, put HAProxy or the built-in Distributed engine in front of the cluster.

Troubleshooting

Viewing Logs

Tail the server log:

sudo tail -f /var/log/clickhouse-server/clickhouse-server.log

Or via journald:

sudo journalctl -u clickhouse-server -f

Query the built-in system.query_log for slow or failed queries:

SELECT
    event_time,
    query_duration_ms,
    memory_usage,
    read_rows,
    exception
FROM system.query_log
WHERE event_time >= now() - INTERVAL 1 HOUR
  AND type = 'ExceptionWhileProcessing'
ORDER BY event_time DESC
LIMIT 20;

FAQ

What are the minimum hardware requirements for ClickHouse?

ClickHouse runs on modest hardware — the binary itself needs only ~300 MB of RAM at idle. For meaningful workloads, plan 8 GB RAM for development and 16 GB+ for production single-node deployments. Storage is the dominant concern: ClickHouse is I/O-bound on cold queries, so NVMe SSD is strongly preferred over spinning disks. Allocate 3–10x your compressed data size to accommodate merges, temporary sort files, and growth. CPU-wise, more cores directly improve scan throughput because queries parallelize across column chunks; 8 cores handle typical dashboards, 16+ cores push into serious analytics territory.

Does ClickHouse replace PostgreSQL?

No — they solve different problems. PostgreSQL is a transactional row-store: fast single-row reads, updates, deletes, and foreign keys. ClickHouse is an analytical column-store: fast aggregations over billions of rows, slow single-row operations, no real transactions. Most production stacks run both — PostgreSQL for application state (users, orders, configuration) and ClickHouse for analytical events derived from that state. ClickHouse can even connect to PostgreSQL directly via the postgresql table function or the materialized PostgreSQL engine for streaming CDC replication.

How does ClickHouse compare to TimescaleDB?

TimescaleDB extends PostgreSQL with time-series superpowers (hypertables, continuous aggregates, compression) while keeping full Postgres compatibility — joins, foreign keys, extensions, OLTP. Choose TimescaleDB when your time-series needs to coexist with relational data in one database and you value ecosystem compatibility. Choose ClickHouse when analytical performance is the product — typically 10–50x faster scans and aggregations at scale, and natively built for clustering.

Can ClickHouse handle updates and deletes?

Yes, but not cheaply. Traditional ALTER TABLE ... UPDATE/DELETE commands are called "mutations" and rewrite entire data parts in the background — fine for occasional GDPR deletes, terrible for frequent per-row updates. For mutable workloads, use ReplacingMergeTree (keeps latest version per sort key on merge), CollapsingMergeTree (cancels rows via sign flag), or lightweight DELETE with DELETE FROM table WHERE ... which uses a lightweight mask. If your workload is fundamentally update-heavy, reconsider whether ClickHouse is the right engine.

How do I stream data from Kafka into ClickHouse?

ClickHouse has a built-in Kafka table engine that consumes a topic directly. Pair it with a materialized view that writes to a MergeTree target. See the Kafka install guide for setting up the broker, then:

CREATE TABLE events_queue (raw String) ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka1:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse_consumer',
         kafka_format = 'JSONAsString';
CREATE MATERIALIZED VIEW events_mv TO analytics.events AS
SELECT JSONExtract(raw, 'event_time', 'DateTime') AS event_time,
       JSONExtract(raw, 'user_id', 'UInt64') AS user_id
FROM events_queue;

The materialized view runs on every Kafka batch, parses the payload, and writes structured rows to your MergeTree — reliable exactly-once-ish ingestion with no separate consumer service.

How do I visualize ClickHouse data?

Grafana has a first-class ClickHouse data source plugin maintained by ClickHouse Inc. — point it at http://your-server:8123, provide credentials, and query directly in Grafana's panel editor. Metabase, Apache Superset, and Redash all support ClickHouse. For OLAP-style pivot tables, ClickHouse's own clickhouse-ui (Tabix, ClickHouse Play) works in the browser against the HTTP interface.

Next Steps

Now that ClickHouse is running on your VPS, here are recommended next steps:

• Add Grafana for dashboards — Install Grafana on the same server, add the ClickHouse data source, and build real-time dashboards over events_hourly. Five-minute setup for end-to-end analytics.

• Stream events from Kafka — Deploy Kafka alongside ClickHouse and wire up the built-in Kafka table engine for production-grade event ingestion with automatic offset management.

• Integrate with your PostgreSQL application database — Use the postgresql() table function for ad-hoc joins, or the MaterializedPostgreSQL engine for continuous CDC replication from your OLTP store into ClickHouse.

• Compare against TimescaleDB — If you are evaluating both, benchmark identical queries on matched hardware. Typical findings: TimescaleDB wins on joins and OLTP, ClickHouse wins on pure analytical scans by an order of magnitude.

• Explore the official docs — The ClickHouse documentation is exceptional. Start with the "MergeTree" and "Materialized Views" sections, then dive into the SQL reference for the hundreds of analytical functions (percentiles, funnels, retention, geospatial, ML).

• Tune for your workload — Monitor system.query_log, system.parts, and system.metrics. Adjust index_granularity, codecs, and projections based on real query patterns, not guesses.

Skip the Manual Install — Get a Professional VPS

Our Professional VPS plan gives you the hardware headroom to run ClickHouse against real analytical workloads — 8 vCPU, 16 GB RAM, 200 GB NVMe, all on a flat EUR 19.99/month bill. Need less? Starter at EUR 7.99/month is perfect for dev. Need more? Business starts at EUR 29.99/month for replicated production clusters.

Launch Your ClickHouse VPS Now