Mukesh Barapatre | Personal Portfolio

Why ClickHouse?

ClickHouse is an open-source, columnar database management system tailored specifically for online analytical processing (OLAP). Unlike traditional row-based databases (PostgreSQL, MySQL), ClickHouse physically stores data by columns.

This architecture enables blazing-fast query execution, as it only reads the columns required for the query and leverages vectorized query execution to process data in blocks. It is engineered to handle billions of rows per second on standard hardware.

When to use it

Analyzing petabytes of log or event data.
Real-time analytics dashboards.
Time-series data and observability pipelines.
Not for: OLTP workloads, heavy updates/deletes, or distributed transactions.

Implementation Deep Dive

Step-by-step guide to installing, scaling, and optimizing a ClickHouse cluster on Amazon Linux (ARM64).

Phase 01

Environment Setup (Amazon Linux ARM64)

We'll deploy ClickHouse on an AWS Graviton (ARM64) instance to leverage massive price-to-performance benefits. The installation is streamlined using the official Yandex/ClickHouse RPM repositories.

Graviton instances provide ~40% better price performance

install_clickhouse.sh

# Install yum-utils to manage repositories
sudo yum install -y yum-utils

# Add the official ClickHouse repository
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo

# Install the server and client packages
sudo yum install -y clickhouse-server clickhouse-client

# Enable the service to start on boot and start it now
sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server

# Verify the service is running
sudo systemctl status clickhouse-server

Phase 02

Single Node Setup & Validation

Before building a cluster, let's validate our single node setup. We will use the clickhouse-client CLI to create a database, a MergeTree table (the core engine of ClickHouse), insert sample data, and query it.

MergeTree engine optimizes for fast inserts and reads

validation_queries.sql

-- Connect via CLI: clickhouse-client

CREATE DATABASE IF NOT EXISTS analytics;

-- Create a table using the standard MergeTree engine
CREATE TABLE analytics.events (
    event_id UUID,
    event_type String,
    user_id UInt32,
    event_time DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, event_type);

-- Insert a sample row utilizing built-in functions
INSERT INTO analytics.events 
VALUES (generateUUIDv4(), 'login', 1042, now());

-- Run a basic aggregation
SELECT event_type, count() as total 
FROM analytics.events 
GROUP BY event_type;

High Availability & Sharding Architecture

Scaling ClickHouse horizontally requires two concepts: Replication (for fault tolerance and read scaling) and Sharding (for write scaling and distributed computing).

BI Dashboards / Applications

Distributed Table (Query Router)

ENGINE = Distributed(...)

Shard 1

Replica 1A

ReplicatedMergeTree

Replica 1B

ReplicatedMergeTree

Shard 2

Replica 2A

ReplicatedMergeTree

Replica 2B

ReplicatedMergeTree

ClickHouse Keeper Quorum

Acts as the coordination layer, managing distributed DDL, replica consistency, and quorum without the JVM overhead of ZooKeeper.

Phase 03

Configuring ClickHouse Keeper & Replicas

To enable replication, ClickHouse requires a consensus system. ClickHouse Keeper is a lightweight C++ replacement for ZooKeeper. Once Keeper is configured, we can replace MergeTree with ReplicatedMergeTree to ensure data written to one node is replicated automatically.

Automatic failover and strict consistency

keeper_config.xml

<!-- /etc/clickhouse-server/config.d/keeper.xml -->
<yandex>
    <zookeeper>
        <node>
            <host>keeper-node-1</host>
            <port>9181</port>
        </node>
        <node>
            <host>keeper-node-2</host>
            <port>9181</port>
        </node>
        <node>
            <host>keeper-node-3</host>
            <port>9181</port>
        </node>
    </zookeeper>
</yandex>

-- SQL to create a replicated table:
CREATE TABLE analytics.events_replica (
    event_id UUID,
    event_type String,
    user_id UInt32,
    event_time DateTime
) ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events', -- ZooKeeper Path
    '{replica}'                          -- Replica Name Macro
)
ORDER BY (event_time, event_type);

Phase 04

Sharding via Distributed Tables

Sharding splits your data horizontally. In ClickHouse, you don't query shards directly; you create a 'Distributed' table that acts as a view/router over the underlying ReplicatedMergeTree tables.

Massively parallelizes read and write workloads

remote_servers.xml

<!-- /etc/clickhouse-server/config.d/remote_servers.xml -->
<yandex>
    <remote_servers>
        <analytics_cluster>
            <!-- Shard 1 -->
            <shard>
                <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica><host>replica-1a</host><port>9000</port></replica>
                <replica><host>replica-1b</host><port>9000</port></replica>
            </shard>
            <!-- Shard 2 -->
            <shard>
                <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica><host>replica-2a</host><port>9000</port></replica>
                <replica><host>replica-2b</host><port>9000</port></replica>
            </shard>
        </analytics_cluster>
    </remote_servers>
</yandex>

-- SQL to create the Distributed Router Table:
CREATE TABLE analytics.events_distributed AS analytics.events_replica
ENGINE = Distributed(
    'analytics_cluster',  -- Cluster name from config
    'analytics',          -- Database
    'events_replica',     -- Underlying table
    cityHash64(user_id)   -- Sharding key (ensures users stay on same shard)
);

Phase 05

Monitoring and Observability

ClickHouse provides incredible visibility through system tables (like system.query_log). Furthermore, enabling the native Prometheus endpoint allows you to effortlessly stream metrics into Grafana.

Deep insights into query latency and merges

prometheus.xml

<!-- /etc/clickhouse-server/config.d/prometheus.xml -->
<yandex>
    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
    </prometheus>
</yandex>

-- Query to check currently running queries:
SELECT query_id, user, query, elapsed 
FROM system.processes 
ORDER BY elapsed DESC LIMIT 5;

-- Check background merge performance:
SELECT database, table, elapsed, progress 
FROM system.merges;

Performance Optimization Strategies

The Primary Key (ORDER BY)

ClickHouse stores data sorted on disk by the ORDER BY tuple. It utilizes a sparse index. Always place columns used in WHERE clauses at the beginning of the tuple, from lowest cardinality to highest cardinality.

Smart Partitioning

PARTITION BY toYYYYMM(date) is standard. Avoid partitioning by high cardinality columns (like user_id) as it creates too many parts on disk, leading to massive overhead during background merges (the "Too many parts" error).

Batch Ingestion is Mandatory

Never execute single-row INSERT statements. Buffer your data at the application layer or via Kafka, and insert in batches of 10,000 to 1,000,000 rows. ClickHouse creates a new physical disk part per insert.

LowCardinality Data Types

If a String column has fewer than 10,000 unique values (e.g., status, country_code), wrap it in LowCardinality(String). This employs dictionary encoding, drastically reducing RAM usage and accelerating string filters.

Best Practices & Common Pitfalls

Ignoring Data TypesUsing standard 64-bit integers when an 8-bit or 16-bit integer would suffice wastes disk space and slows down memory bandwidth during large vector scans.
Too Many Concurrent QueriesClickHouse utilizes all available CPU cores for a single query. It is not designed to handle thousands of concurrent queries per second (QPS) like a traditional key-value store. Cache aggregate results in Redis if you need extreme concurrency.
Kafka Engine IntegrationFor real-world pipelines, use the native Kafka Table Engine. It allows ClickHouse to consume directly from Kafka topics without needing external middleware or connector services.