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.
