ClickHouse is a column-oriented database built for analytics — it scans billions of rows per second for aggregation queries that would crush a row store like MySQL. The Altinity Kubernetes Operator makes running it on Kubernetes declarative and repeatable. This guide stands up a sharded, replicated cluster and covers day-2 operations.

Prerequisites

  • A Kubernetes cluster with kubectl, a fast-SSD StorageClass, and 3+ nodes for replication.
  • Object storage (S3/GCS) if you want backups (recommended).

Why ClickHouse (and when not to)

  • Great for: dashboards, event/log analytics, time-series, "count/sum/group-by over huge tables."
  • Not for: high-rate single-row updates/deletes or transactions — that's Postgres/MySQL.

Its speed comes from columnar storage, aggressive compression, and vectorized execution.

Shards vs replicas — the core idea

  • Shard = a slice of the data (horizontal scale; more shards = more throughput and capacity).
  • Replica = a copy of a shard (high availability; more replicas = survive node loss).

A 2 shards × 2 replicas layout spreads data across two shards and keeps each shard on two nodes.

Install the operator

kubectl apply -f https://raw.githubusercontent.com/Altinity/clickhouse-operator/master/deploy/operator/clickhouse-operator-install-bundle.yaml

Deploy Keeper (replication needs a coordinator)

Replicated tables use ClickHouse Keeper (or ZooKeeper) to coordinate. Run 3 nodes for quorum — the operator's companion chart or a ClickHouseKeeperInstallation handles it. Point the cluster at it.

Declare the cluster

apiVersion: clickhouse.altinity.com/v1
kind: ClickHouseInstallation
metadata: {name: analytics}
spec:
  configuration:
    clusters:
      - name: main
        layout:
          shardsCount: 2
          replicasCount: 2
    zookeeper:
      nodes: [{host: keeper.db.svc, port: 2181}]
  defaults:
    templates: {dataVolumeClaimTemplate: data}
  templates:
    volumeClaimTemplates:
      - name: data
        spec:
          accessModes: [ReadWriteOnce]
          resources: {requests: {storage: 100Gi}}
          storageClassName: fast-ssd
    podTemplates:
      - name: default
        spec:
          containers:
            - name: clickhouse
              image: clickhouse/clickhouse-server:24.8

The operator creates StatefulSets, services, and replication config for you.

Create replicated, distributed tables

-- one replicated table per shard (data + replicas coordinate via Keeper)
CREATE TABLE events_local ON CLUSTER main (
  ts DateTime, user_id UInt64, event String
) ENGINE = ReplicatedMergeTree
ORDER BY (ts, user_id);

-- a distributed table that fans queries across shards
CREATE TABLE events ON CLUSTER main AS events_local
ENGINE = Distributed(main, default, events_local, rand());

Insert into events; query events and ClickHouse scatters/gathers across shards.

Verify it worked

SELECT * FROM system.clusters WHERE cluster = 'main';   -- see all shard/replica hosts
SELECT count() FROM events;                              -- fans out across shards
SELECT database, table, is_readonly FROM system.replicas; -- is_readonly must be 0

is_readonly = 1 means a replica lost its Keeper connection — investigate before trusting HA.

MergeTree, partitioning & TTL — the performance levers

ClickHouse's speed isn't magic; it comes from the MergeTree engine family, and using it well is the difference between millisecond and minute queries.

The ORDER BY (sorting key) is the most important decision. Data is stored sorted by it, and a sparse primary index is built on it. Queries that filter or group on the leading columns of the sort key skip almost all the data; queries that don't will scan everything. So order by the columns you filter on most — often (tenant_id, ts) or (ts, user_id). There are no per-row indexes like OLTP databases; the sort order is the index.

Partitioning (PARTITION BY toYYYYMM(ts)) splits data into parts you can drop or move as a unit. It's for data management, not query speed — its superpower is that dropping an old month is an instant metadata operation, and it pairs perfectly with TTL. Don't over-partition (thousands of tiny partitions hurt); monthly or daily is typical.

TTL ages data out automatically — the cleanest cost control:

CREATE TABLE events_local (...)
ENGINE = ReplicatedMergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, ts)
TTL ts + INTERVAL 90 DAY DELETE;          -- or TO VOLUME 'cold' to tier to cheap storage

Parts and merges. Each insert creates a part; a background process continuously merges parts into bigger ones. Many small inserts create many small parts, and "too many parts" errors are ClickHouse telling you to batch your inserts (thousands of rows at a time, or use async inserts / a buffer). This is the single most common newcomer mistake — ClickHouse loves big batches, hates row-at-a-time.

Materialized views pre-aggregate on insert (e.g. per-minute rollups), so dashboard queries read a tiny summary table instead of scanning raw events — the standard pattern for fast analytics at scale.

Ingestion patterns: how to get data in fast

How you insert into ClickHouse matters as much as how you query it. The golden rule from the parts/ merges discussion: big batches, not row-at-a-time. Three common patterns:

  • Batched application inserts. Buffer thousands of rows in your app (or a queue) and insert them in one statement. This is the simplest high-throughput approach.
  • Async inserts. Set async_insert=1 and ClickHouse buffers small inserts server-side and flushes them in batches — great when many clients each send a few rows and you can't easily batch in the app.
  • The Kafka table engine. ClickHouse can consume directly from Kafka: a Kafka engine table reads the topic, and a materialized view moves rows into a MergeTree table as they arrive. This turns ClickHouse into a streaming sink with no external ingestion service — a very common analytics pattern.
CREATE TABLE events_queue (raw String) ENGINE = Kafka
  SETTINGS kafka_broker_list='kafka:9092', kafka_topic_list='events', kafka_group_name='ch';
CREATE MATERIALIZED VIEW events_mv TO events_local AS
  SELECT JSONExtractString(raw,'event') AS event, now() AS ts FROM events_queue;

Query optimization: reading EXPLAIN

When a query is slow, the cause is almost always it's reading data it shouldn't. Check with:

EXPLAIN indexes = 1 SELECT count() FROM events WHERE tenant_id = 7 AND ts > now() - INTERVAL 1 DAY;

The plan shows how many granules (blocks) are scanned. If your WHERE filters on the leading columns of the ORDER BY, ClickHouse skips almost everything; if it doesn't, it scans the table. Fixes: order the table by your common filter columns; add a data-skipping index (minmax, set, bloom_filter) for a secondary filter column; use PREWHERE (ClickHouse often does this automatically) to filter before reading other columns; and select only the columns you need — it's a column store, so SELECT * reads every column from disk. SELECT * ... LIMIT 1 on a billion-row table is a classic accidental full scan.

Replication & sharding topologies

Recapping the levers with production shapes in mind:

  • Replication (ReplicatedMergeTree + Keeper) is for HA and read scaling — each shard's replicas hold the same data, coordinated by a 3-node Keeper quorum. Lose a node, keep serving.
  • Sharding (Distributed table) is for capacity and write/scan throughput — data is split across shards and queries fan out. A Distributed table routes inserts by a sharding key (rand() for even spread, or a key to co-locate related rows).

A typical production layout is N shards × 2 replicas: shards give you scale, the replica gives each shard HA. Add shards to grow capacity (with data redistribution), add replicas to grow read throughput and resilience. Keep replicas of the same shard on different nodes/zones via anti-affinity.

Monitoring & common production issues

ClickHouse exposes rich internals through system.* tables and Prometheus metrics. Watch:

  • system.parts — a climbing part count per table means inserts are too small; batch them.
  • system.replicas — is_readonly=1 or growing absolute_delay means a replica lost Keeper or is lagging.
  • system.merges / system.mutations — long-running merges or stuck mutations (mutations are heavy — ALTER ... UPDATE/DELETE rewrites parts; avoid frequent ones).
  • Memory — big GROUP BY/JOIN can hit max_memory_usage; use aggregation-friendly schemas and max_bytes_before_external_group_by to spill to disk instead of failing.

The two recurring newcomer incidents are "too many parts" (fix: batch inserts) and read-only replicas (fix: healthy Keeper). Alert on both.

Data modeling: think in columns, not rows

Modeling for ClickHouse is the opposite of modeling for Postgres, and getting it right is where most of the performance comes from.

  • Denormalize. In OLTP you normalize to avoid duplication; in ClickHouse you flatten — wide tables with the columns you query, so a read doesn't need joins. Storage is cheap (great compression); scans of many small joined tables are not. Write the data pre-joined.
  • Pick data types tightly. UInt32 vs UInt64, LowCardinality(String) for columns with few distinct values (status, country) — this dramatically improves compression and speed. LowCardinality is a ClickHouse superpower for repetitive string columns.
  • Use dictionaries for lookups. When you do need to enrich (map a country_code to a name), load a dictionary — an in-memory key-value table ClickHouse joins against extremely fast — instead of a runtime join to another table.
  • Order for your queries. As covered, the ORDER BY key is your index; lead with the columns you filter on most.

The instinct from relational databases ("normalize everything, join at query time") is exactly wrong here. Wide, denormalized, tightly-typed tables are the ClickHouse way.

Aggregating engines & materialized views

For dashboards over huge tables, don't scan raw rows every time — pre-aggregate on insert. A materialized view feeding an AggregatingMergeTree or SummingMergeTree table maintains running aggregates as data lands:

CREATE MATERIALIZED VIEW events_per_min
ENGINE = SummingMergeTree ORDER BY (service, minute) AS
SELECT service, toStartOfMinute(ts) AS minute, count() AS hits
FROM events_local GROUP BY service, minute;

Now a "hits per minute" dashboard reads a tiny summary table instead of scanning billions of raw events — milliseconds instead of seconds. This is the standard pattern for fast analytics at scale: keep raw data for flexibility (with a TTL), and maintain rollups for the queries you run constantly. Combined with denormalization and LowCardinality, it's how ClickHouse serves interactive dashboards over enormous datasets.

Projections & data-skipping indexes

When one table serves queries with different filter/sort needs, you don't have to choose a single ORDER BY. Two features add secondary acceleration:

  • Data-skipping indexes let ClickHouse skip granules based on a secondary column. A minmax index on a column lets range queries skip blocks; a bloom_filter index speeds up equality checks on a high-cardinality column that isn't in the sort key. They don't point to rows (this isn't a B-tree) — they let ClickHouse avoid reading blocks that can't match.
ALTER TABLE events_local ADD INDEX idx_user user_id TYPE bloom_filter GRANULARITY 4;
  • Projections store an alternate physical ordering/aggregation of the same table, and ClickHouse automatically picks the best one per query. A projection sorted by user_id makes user-centric queries fast even though the base table is sorted by ts — without you maintaining a second table.

Reach for these when a table is queried several different ways; they trade a little extra storage and insert cost for large query speedups, and they're maintained automatically as data lands.

When ClickHouse is the wrong choice

Being honest about fit saves you a painful migration later. ClickHouse is superb for analytical reads over huge, mostly-immutable datasets — and a poor fit when you need frequent single-row updates/deletes (mutations rewrite whole parts and are heavy), transactions across rows, primary-key point lookups at OLTP rates, or strong uniqueness constraints. Those are jobs for Postgres or MySQL. The common, correct architecture is to use both: your transactional database of record handles writes and operational queries, and data is streamed into ClickHouse (via CDC or the Kafka engine) for analytics and dashboards. Reach for ClickHouse when the question is "aggregate billions of rows fast," not "update this one order" — matching the workload to the engine is the whole game.

Day-2 operations

  • Storage: always fast SSD PVs; ClickHouse is I/O hungry, and merges need headroom. Size generously.
  • Backups: use clickhouse-backup to snapshot to S3/GCS; schedule it and test restores.
  • Scaling out: add replicas by bumping replicasCount. Adding shards means data must be redistributed — plan it, don't do it casually.
  • Monitoring: watch system.parts (too many parts = slow inserts/merges), replication lag, and merge activity. ClickHouse exposes Prometheus metrics.
  • Upgrades: the operator does rolling updates; keep a PodDisruptionBudget so a shard never loses all replicas at once.

Troubleshooting

  • Replica stuck read-only → Keeper is down or unreachable; a 3-node Keeper quorum must be healthy.
  • "Too many parts" errors → insert batches are too small/frequent. Batch inserts (thousands of rows) and let background merges catch up.
  • Pods Pending → no matching PVs / wrong StorageClass, or anti-affinity can't be satisfied.
  • Query only hits one shard → you queried the local table, not the Distributed table.
  • Disk fills fast → no TTL on the data. Add TTL ts + INTERVAL 90 DAY to age out old data.

Where to go next

Our free course installs the Altinity Operator, stands up a sharded + replicated cluster with Keeper, loads a sample dataset, and runs backup/restore and a scale-out — everything to operate ClickHouse in production, guided in a lab.