RousselTMLEARNING

Observability

Operating ClickHouse for ClickStack: sizing, replication, monitoring, security and backup

Running ClickHouse in production for observability: estimating volume and disk, replicating with ClickHouse Keeper, monitoring parts, merges and slow queries, securing accounts, backing up, upgrading, and when to prefer ClickHouse Cloud.

1. Sizing #

Starting from real volume.

Estimating #

Available in: ✓ Open source ✓ Managed ClickStack managed for you in ClickHouse Cloud

DataExample
Raw volume per day500 GB
Observed compression ratio× 12
Stored volume per day≈ 42 GB
Retention30 days
Usable disk (× 2 replicas, + 30% headroom)≈ 42 × 30 × 2 × 1.3 ≈ 3.3 TB
  • Measure the compression ratio on your own data (system.parts query): 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: ReplicatedMergeTree tables 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 Distributed table 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;
SymptomCommon causeFix
“Too many parts”inserts too small and too frequentincrease collector batch size, or async inserts
Slow searchesno time or service filter, unindexed attributesfilters on the sort key, skip indexes, materialized columns
Disk filling upmissing or too long TTL, merges laggingTTL, cold volume, more disk
Memory saturatedaggregations over long periodslimit 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, compression
  • SELECT … FROM system.merges : running merges
  • SELECT … FROM system.query_log : slow or failing queries
  • BACKUP DATABASE otel TO S3(…) : backup
  • CREATE SETTINGS PROFILE … max_execution_time : limit queries
  • clickhouse-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.

Related articles

The observability trilemma: cost, completeness and performance, and how solutions resolve it

Understanding the observability trilemma (cost, data completeness, query performance), its three trade-offs, …

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 its variables…

Sending telemetry to ClickStack: OpenTelemetry SDKs, collectors, logs, Kubernetes and browser sessions

Feeding ClickStack: ingestion key, OpenTelemetry SDK variables, forwarding through an existing collector, fil…

Keep learning

Support us

♥ SUPPORT US

Support RousselTM Learning

Courses, articles, labs and certificates stay free with no intrusive ads. A voluntary contribution, of any amount you choose, pays for hosting, lab environments and new content.

Contribute with PayPal Any amount · card or PayPal accountLearn more
QR code PayPal
Or scan this QR code with your phone

Thank you, every contribution counts!