1. Sizing #
Starting from real volume.
Estimating #
Available in: ✓ Open source ✓ Managed ClickStack managed for you in ClickHouse Cloud
| Data | Example |
|---|---|
| Raw volume per day | 500 GB |
| Observed compression ratio | × 12 |
| Stored volume per day | ≈ 42 GB |
| Retention | 30 days |
| Usable disk (× 2 replicas, + 30% headroom) | ≈ 42 × 30 × 2 × 1.3 ≈ 3.3 TB |
- Measure the compression ratio on your own data (
system.partsquery): it varies a lot with log verbosity. - Memory: plan generously for aggregation queries and merges; ClickHouse likes fast disks (NVMe) for recent data.
- Object storage (S3) for data older than a few days greatly reduces cost, at the price of slower queries on history.
2. Replicating and sharding #
High availability and capacity.
The concepts #
- Replicas:
ReplicatedMergeTreetables copied across several servers; a failed replica interrupts neither writes nor reads. - ClickHouse Keeper (3 nodes) coordinates replication; it replaces ZooKeeper.
- Shards: data is spread across several server groups when one is no longer enough; a
Distributedtable presents them as one. - To start: one shard, two replicas, three Keepers. Only add shards when volume requires it.
3. Monitoring ClickHouse #
The system tables.
Monitoring queries #
-- Active parts per table (too many small parts = inserts too frequent)
SELECT table, count() AS parts, max(modification_time) AS latest
FROM system.parts WHERE active GROUP BY table ORDER BY parts DESC LIMIT 10;
-- Running merges
SELECT table, round(progress * 100) AS pct, formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges;
-- Slowest queries in the last hour (often searches without a time filter)
SELECT query_duration_ms, read_rows, formatReadableSize(memory_usage) AS memory, left(query, 120) AS query
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC LIMIT 10;
-- Disk space
SELECT name, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total FROM system.disks;
-- Insert errors (server side)
SELECT name, value FROM system.errors ORDER BY value DESC LIMIT 10;| Symptom | Common cause | Fix |
|---|---|---|
| “Too many parts” | inserts too small and too frequent | increase collector batch size, or async inserts |
| Slow searches | no time or service filter, unindexed attributes | filters on the sort key, skip indexes, materialized columns |
| Disk filling up | missing or too long TTL, merges lagging | TTL, cold volume, more disk |
| Memory saturated | aggregations over long periods | limit profiles, materialized views |
Best practice : ClickHouse also exposes its metrics in Prometheus format (prometheus configuration section): send them into ClickStack itself or into a separate Prometheus, to be warned even if ClickStack is struggling.4. Securing, backing up, upgrading #
Operations tasks.
Accounts and backup #
-- Separate accounts: write for the collector, read for the UI and analysts
CREATE USER otel_writer IDENTIFIED WITH sha256_password BY '…';
GRANT INSERT, SELECT, CREATE TABLE ON otel.* TO otel_writer;
CREATE USER hyperdx_reader IDENTIFIED WITH sha256_password BY '…';
GRANT SELECT ON otel.* TO hyperdx_reader;
-- Limit readers' heaviest queries
CREATE SETTINGS PROFILE readers SETTINGS max_execution_time = 60, max_memory_usage = 10000000000 TO hyperdx_reader;
-- Backup to object storage
BACKUP DATABASE otel TO S3('https://s3.example.com/backups/otel/2026-11-17', '<clé>', '<secret>');- Also back up MongoDB (dashboards, alerts, accounts) and the UI and collector configuration.
- Test a restore (
RESTORE DATABASE … FROM S3(…)) on a staging server at least once a quarter. - Upgrade one replica at a time, after reading release notes; keep the same version on every replica.
Self-hosted or ClickHouse Cloud? #
If the team has no ClickHouse experience, Managed ClickStack (or ClickStack on ClickHouse Cloud) avoids sizing, replication, backups and upgrades. Self-hosting is justified by data control, very high volumes at controlled cost, or an already expert team.
Commands to know #
Commands to know
SELECT … FROM system.parts: parts, size, compressionSELECT … FROM system.merges: running mergesSELECT … FROM system.query_log: slow or failing queriesBACKUP DATABASE otel TO S3(…): backupCREATE SETTINGS PROFILE … max_execution_time: limit queriesclickhouse-keeper: replication coordination
Sources #
Checked in November 2026: ClickStack evolves fast (Managed ClickStack is in beta), check the ClickHouse documentation before relying on a detail.
My progress
Sign in to track your progress and take notes. Sign in
💬 Questions about this lesson
No questions yet: be the first to ask for help or share your configuration.
Recommended Articles
Operating an observability platform on ClickHouse and choosing a tool
Operate ClickHouse for telemetry (replication, disks, merges, object storage, monitoring) and com...
SigNoz, ClickStack and ClickHouse best practices for observability
Best practices for observability on ClickHouse with SigNoz or ClickStack: OpenTelemetry instrumen...
ClickStack: architecture, components and offerings (ClickHouse, HyperDX, OpenTelemetry)
ClickStack, ClickHouse's official observability stack: role of ClickHouse, the UI (HyperDX), the ...
Observability on ClickHouse: why a columnar database changes the game
Why more and more observability platforms store logs, traces and metrics in ClickHouse: columnar ...
Telemetry in ClickHouse: schema, SQL, materialized views and retention
Telemetry in ClickHouse: otel_logs, otel_traces, otel_metrics_* and hyperdx_sessions tables, sort...
Alerts and dashboards in ClickStack
Building charts and dashboards in ClickStack, creating alerts on a search or a chart, sending the...
Installing and deploying ClickStack: all-in-one, Docker Compose, Helm and Managed ClickStack
Deploying ClickStack to fit the need: all-in-one container with persistence, Docker Compose and i...
Sending telemetry to ClickStack: OpenTelemetry SDKs, collectors, logs, Kubernetes and browser sessions
Feeding ClickStack: ingestion key, OpenTelemetry SDK variables, forwarding through an existing co...
The observability trilemma: cost, completeness and performance, and how solutions resolve it
Understanding the observability trilemma (cost, data completeness, query performance), its three ...