ClickHouse High Availability¶

ClickHouse is a columnar database for analytical workloads: high-volume ingestion and queries that scan billions of rows. Its availability model works differently from a transactional database — there is no primary and standby, but replicas that coordinate with each other through a consensus service, ClickHouse Keeper.

Two independent concepts define the topology:

• Replica — a full copy of the same data. It provides availability: if a node goes down, another answers.
• Shard — a slice of the data. It provides scale: it spreads volume and parallelizes the query.

A production cluster normally combines both: each shard has at least two replicas.

Reference topology¶

The distributed table is the query entry point; each shard holds a slice of the data in two replicas, and Keeper coordinates replication.

• ReplicatedMergeTree tables — replication happens per table, not per server, and is always multi-master: any replica accepts INSERT.
• ClickHouse Keeper quorum with an odd number of nodes (three in the standard topology) — it holds the replication log, coordinates merges and performs block deduplication.
• Replicas on distinct physical hosts, with anti-affinity when the cluster runs on Kubernetes.
• Distributed table over the shards, so the application has a single entry point.

The write path¶

Any replica accepts the write; the entry recorded in Keeper makes the other replica fetch exactly the same data block.

1. The INSERT arrives at any replica of the shard and is written as a local part.
2. The replica records the operation in the replication log, in Keeper.
3. The other replicas of the same shard read the log and fetch the part.
4. Identical blocks resent are deduplicated automatically by hash — which makes retrying an interrupted ingestion safe.

Ingestion recommendations that matter more than any hardware tuning:

• Large, infrequent batches — ClickHouse was built to receive blocks, not individual rows. Thousands of single-row INSERTs will bring a healthy cluster to its knees.
• A queue in front (Kafka, a buffer or a staging table) absorbs spikes and survives an unavailable replica.
• Idempotent INSERT — keeping the same batch on retry lets deduplication prevent duplicate data.

The read path¶

The query is sent to one healthy replica of each shard; the unavailable replica is skipped and the partial results are merged on the initiator node.

• Each shard processes its slice and returns a partial aggregation; the initiator node merges.
• One healthy replica per shard is enough to answer the query.
• If an entire shard is down, the result is incomplete — which is why every shard has at least two replicas.
• Replica selection accounts for replication lag and load, avoiding sending queries to a node that is behind.

Kubernetes or virtual machines?¶

Analytical workloads are heavy on disk and memory. In practice, we decide by data volume and growth pattern — and, in both cases, with storage dedicated to the database.

Backup and retention¶

Replication does not protect against human error, and in ClickHouse that is especially true: a DROP TABLE propagates to every replica.

• Backup of the parts to object storage, with a defined retention policy and tested restores.
• Encrypted volume snapshots on the Kubernetes cluster, kept in Brazil or replicated to another country — see Data sovereignty.
• Data TTL — old analytical data can be moved to cheaper storage or discarded automatically; less volume means lower cost and faster queries.
• Restricted SYSTEM grants — destructive and replica-manipulation commands do not belong to the application user.

Monitoring that comes with the cluster¶

• replication lag and queue size per replica;
• Keeper quorum availability and latency;
• parts per partition and merge rate — an excess of parts is the classic symptom of INSERTs that are too small;
• disk usage per shard, with growth projection;
• slowest queries and memory usage per query;
• backup and test-restore success.

Data sovereignty¶

Every byte in this cluster stays in Brazil. Both InteSys regions are Brazilian — br-sp-1 (Cirion SAO1), in Cotia/SP, and br-sp-2 (Equinix SP3), in the São Paulo metro area — and data only leaves the country if the customer asks for it.

• Data residency in Brazil — primary nodes, replicas, transaction log archives and backups all live in Brazilian datacenters, on infrastructure operated by InteSys.
• LGPD with no international transfer — in the default configuration there is no cross-border transfer of personal data to document, and no additional legal basis to build.
• Single jurisdiction — Brazilian infrastructure, Brazilian contract and a Brazilian operations team, without the exposure to foreign data-access legislation that reaches providers headquartered abroad.
• Latency as a side effect — keeping data close to Brazilian users is a compliance requirement and a performance gain at the same time.
• Sending data abroad is an explicit customer decision — any copy outside the country exists only when requested, and only on an encrypted volume.

Encrypted volume snapshots¶

On top of the parts backup to object storage, a cluster on Kubernetes can use volume snapshots (CSI VolumeSnapshot): a copy of the entire volume, taken in seconds — which matters when each replica holds terabytes.

• Consistent snapshots — the operator coordinates the flush and freezes writes before triggering the snapshot, so a restore does not depend on crash recovery.
• Always on an encrypted volume — data is encrypted at rest and the snapshot inherits that encryption; the key is managed by InteSys or supplied by the customer.
• Encrypted in transit — replicating the snapshot to another destination travels encrypted and is stored encrypted at the destination.
• Retention and tested restores — a defined retention policy and restores exercised periodically; a snapshot that has never been restored is a hypothesis, not a backup.
• Fast recovery — a snapshot brings the whole volume back in minutes; the parts backup is still what protects against the DROP TABLE that propagates to every replica.

How InteSys deploys it¶

1. Workload modelling — ingestion volume, retention, sorting key and query pattern.
2. Topology — number of shards and replicas, sizing of the Keeper quorum.
3. Provisioning — replicas on distinct physical hosts, on Kubernetes or VMs, with dedicated storage.
4. Ingestion layer — batching, queueing and idempotency defined together with the application team.
5. Distributed tables and permissions — a single entry point and separation between the read and ingestion users.
6. Backup, TTL and retention in object storage.
7. Failure testing — we take down a replica and a Keeper node in a controlled environment, measuring the real impact.
8. Observability and alerting — metrics, dashboards and alerts wired to the operations team.

Next Steps¶

• PostgreSQL High Availability — HA for the transactional workload that feeds analytics
• MySQL High Availability — The same architecture applied to MySQL
• Kubernetes — Managed clusters where ClickHouse can be operated
• InteSys Datacenters — Details of the br-sp-1 and br-sp-2 regions