Where ClickHouse fits
ClickHouse is a column-oriented database for analytical queries, licensed under Apache-2.0. It reads only the columns a query needs and compresses them well, which suits append-heavy data such as events, logs, traces and product analytics. It is less suited to transactional workloads with frequent single-row updates, and joins across large tables need deliberate schema design.
ClickHouse also runs underneath other platforms. Langfuse and SigNoz, for example, store trace and telemetry data in it. When you self-host one of those tools, most of the operating effort sits in the ClickHouse layer: disk growth, retention, merges, replication and backups. Give it an owner, even when it arrived as a dependency.
Cluster building blocks
Knowing which pieces you use shapes failure handling and upgrades.
ClickHouse server
Stores data and runs queries. Clients connect over the HTTP interface or the native TCP protocol, each of which can use TLS.
MergeTree tables
The main table engine family. Inserts write immutable parts sorted by the sorting key, and background merges combine them.
Replicated tables
ReplicatedMergeTree keeps copies of a table on several servers, which coordinate inserts and merges through ClickHouse Keeper.
Distributed tables
Route inserts and queries across shards. They hold no data themselves and depend on a sharding key chosen up front.
ClickHouse Keeper
A Raft-based coordination service that replaces ZooKeeper for replication and distributed DDL. A three-node quorum survives one node failure.
Schema and storage choices drive cost
Performance and disk use depend heavily on table design. Test each choice with representative data and queries.
| Choice | What it controls | Guidance |
|---|---|---|
| Sorting key | The sparse primary index and how much data a filter can skip. | Lead with columns most queries filter on. Changing it substantially later usually means rewriting the table. |
| Partition key | The unit for dropping and moving data. | Partition coarsely, often by month or day. Too many partitions create many small parts and slow merges. |
| TTL | Deleting rows or moving them to another volume after an age. | Match the agreed retention policy, and use storage policies to move older data to cheaper disks. |
| Insert pattern | How many parts are created, since each insert writes a new part. | Batch inserts or enable asynchronous inserts to avoid merge backlogs. |
| Memory | Large GROUP BY and JOIN operations hold intermediate state in memory. | Set per-user memory limits and quotas so one query cannot exhaust a server. |
| Disks and Keeper | Merges use CPU and disk throughput; Keeper needs low-latency storage for its logs. | Leave headroom for merges and keep Keeper on hosts free of heavy query work. |
Pick a topology
One server for development, small datasets or a tool with modest retention. Recovery depends entirely on backups, so plan the move to replication before the data becomes important.
One shard with two or more replicas and a three-node Keeper quorum. Queries continue if a replica is lost, and a replacement replica fetches data from its peers.
Several replicated shards behind Distributed tables. Adding shards does not move existing data automatically, so choose the sharding key carefully and treat rebalancing as a planned operation.
The Altinity Kubernetes Operator for ClickHouse (Apache-2.0) manages clusters from custom resources, including configuration, users, scaling and upgrades. Run Keeper as its own pods on storage with predictable latency.
Harden access to data and nodes
Analytical stores collect sensitive data from many sources and need the same care as any production database.
| Area | Approach | Check |
|---|---|---|
| Network | Expose only the interfaces clients need, with TLS on client ports and between replicas. | Keeper and inter-server ports are unreachable from user networks. |
| Default user | Set a password on the default user or disable it, and create named administrator accounts. | No account can log in without credentials. |
| Users and roles | SQL-driven access control with roles, row policies, settings profiles and quotas. | A read-only application user cannot alter tables or exceed its quota. |
| Directory and SSO | Authenticate people through LDAP, Kerberos or X.509 certificates. Browser SSO usually happens in the BI or observability tool in front of ClickHouse. | Disabling a directory account removes access. |
| Configuration | Keep users and secrets in configuration files under change control. | Access defined in users.xml has its own restore procedure, since SQL backups leave it out. |
Back up and restore
Replication protects against losing a server, and it also copies an accidental DROP to every replica, so separate backups are still required. ClickHouse BACKUP and RESTORE statements write to a local disk, S3-compatible object storage or Azure Blob Storage. Incremental backups need every backup in their chain to restore, so retention must keep chains intact. The open-source clickhouse-backup tool is another common option.
Decide what a restore brings back: table data, schemas, SQL-defined users and roles, and the configuration files SQL backups exclude. Restoring replicated tables involves Keeper metadata, so rehearse on a cluster with the same layout and record how long your largest tables take.
Version policy and upgrades
ClickHouse publishes stable releases roughly monthly and long-term support releases twice a year. Stable is the default recommendation, with the three latest supported for bug-fix backports, and each LTS release is supported for a year. A version left in place much longer falls out of security support, so plan a regular upgrade rhythm.
Read the changelog for backward-incompatible changes, test on a pre-production cluster with representative data and queries, then upgrade replicas one at a time while watching replication and merges. Upgrade Keeper separately. Moving from ZooKeeper to Keeper uses clickhouse-keeper-converter and needs a maintenance window with ingestion paused, because mixed ensembles are not supported.
ClickHouse operating ownership
The platform team keeps the cluster healthy; data owners decide what is stored, how it is shaped and how long it stays.
Platform
- Monitor disk, part counts, merge backlog, replication queue and Keeper health.
- Upgrade on a planned rhythm with a tested rollback path.
- Add replicas and shards with a written procedure.
Data owners
- Approve schemas, sorting keys and TTL rules.
- Review materialized views when source tables change.
- Confirm retention meets legal and business requirements.
Security
- Review users, roles, row policies and quotas.
- Rotate service credentials and certificates.
- Protect backup storage and its keys.
Consuming applications
- Keep dashboard and API queries within agreed limits.
- Report slow queries with the query ID and time range.
- Coordinate Langfuse or SigNoz upgrades with their schema migrations.
ClickHouse deployment questions
Can ClickHouse replace our transactional database?
It is designed for analytics. Keep transactional records in a database such as PostgreSQL and copy them into ClickHouse for reporting. ClickHouse can read PostgreSQL tables directly, which helps with initial loads.
Should Langfuse, SigNoz and our own analytics share one cluster?
They can share hardware through separate databases, users and quotas, but their upgrades and load then affect each other. Separate clusters are simpler when tools have different owners or recovery needs.
How do we move log data from another store?
Define the target schema and retention, backfill history in batches, and run both systems in parallel while dashboards and alerts are checked. Compare results for known time windows before retiring the old store.
How does self-hosted ClickHouse relate to ClickHouse Cloud?
ClickHouse Cloud is a separate managed service from ClickHouse, Inc. with some features of its own. A self-hosted deployment runs the open-source server, and you operate Keeper, storage and backups.
Scope and handover
DeployOpen plans the topology and storage with you, deploys ClickHouse and Keeper on virtual machines or Kubernetes, configures TLS, users, roles and directory authentication, sets up backups and runs a restore test, carries out upgrades and Keeper migrations, documents runbooks and provides ongoing support if you want it. Scope depends on data volume and retention, shards and replicas, consuming applications, environments and network restrictions.
Handover includes the cluster layout, a table register with sorting keys and TTL rules, the user, role and quota model, backup schedule and latest restore result, alert routes, and the upgrade plan with supported versions.
