ClickHouse for Observability: Logs, Metrics, and Traces at Scale

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

# docker-compose.yml
version: '3.8'

services:
  clickhouse:
    image: clickhouse/clickhouse-server:24.3-alpine
    container_name: clickhouse
    ports:
      - "8123:8123"   # HTTP interface
      - "9000:9000"   # Native protocol (for clients and replication)
    volumes:
      - clickhouse-data:/var/lib/clickhouse
      - clickhouse-logs:/var/log/clickhouse-server
      - ./config/clickhouse/users.xml:/etc/clickhouse-server/users.xml
      - ./config/clickhouse/config.xml:/etc/clickhouse-server/config.d/custom.xml
    environment:
      CLICKHOUSE_DB: observability
      CLICKHOUSE_USER: default
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    healthcheck:
      test: ["CMD", "wget", "--spider", "-q", "http://localhost:8123/ping"]
      interval: 10s
      timeout: 5s
      retries: 5

  # ClickHouse Keeper (replaces ZooKeeper for coordination)
  # Only needed for replicated setups — skip for single node

  grafana:
    image: grafana/grafana:10.4.0
    container_name: grafana
    ports:
      - "3000:3000"
    environment:
      GF_SECURITY_ADMIN_PASSWORD: changeme
      GF_INSTALL_PLUGINS: grafana-clickhouse-datasource
    volumes:
      - grafana-data:/var/lib/grafana
      - ./config/grafana/provisioning:/etc/grafana/provisioning
    depends_on:
      clickhouse:
        condition: service_healthy

  vector:
    image: timberio/vector:0.39.0-alpine
    container_name: vector
    volumes:
      - ./config/vector/vector.toml:/etc/vector/vector.toml:ro
      - /var/log:/var/log:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
    depends_on:
      clickhouse:
        condition: service_healthy

volumes:
  clickhouse-data:
  clickhouse-logs:
  grafana-data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

<!-- config/clickhouse/config.xml -->
<clickhouse>
  <!-- Allow external connections -->
  <listen_host>0.0.0.0</listen_host>

  <!-- Memory settings -->
  <max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>

  <!-- MergeTree defaults optimized for observability workloads -->
  <merge_tree>
    <!-- Write parts are smaller — better for high-ingest workloads -->
    <min_rows_for_wide_part>0</min_rows_for_wide_part>
    <min_bytes_for_wide_part>0</min_bytes_for_wide_part>
  </merge_tree>

  <!-- Disable query complexity limits for analytics -->
  <max_query_size>1073741824</max_query_size>
</clickhouse>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

<!-- config/clickhouse/users.xml -->
<clickhouse>
  <users>
    <default>
      <password>your_password_here</password>
      <networks>
        <ip>::/0</ip>
      </networks>
      <profile>default</profile>
      <quota>default</quota>
    </default>

    <readonly>
      <password>readonly_password</password>
      <networks>
        <ip>::/0</ip>
      </networks>
      <profile>readonly</profile>
      <quota>default</quota>
    </readonly>
  </users>

  <profiles>
    <default>
      <max_memory_usage>10000000000</max_memory_usage>
      <use_uncompressed_cache>0</use_uncompressed_cache>
      <load_balancing>random</load_balancing>
    </default>
    <readonly>
      <readonly>1</readonly>
    </readonly>
  </profiles>
</clickhouse>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

CREATE DATABASE IF NOT EXISTS observability;

CREATE TABLE observability.logs
(
    -- Temporal fields first — always filter by time
    timestamp       DateTime64(9, 'UTC'),
    timestamp_date  Date DEFAULT toDate(timestamp),

    -- Identity
    service         LowCardinality(String),
    environment     LowCardinality(String),
    host            LowCardinality(String),
    container_id    String,
    container_name  LowCardinality(String),

    -- Log body
    level           LowCardinality(String),
    message         String,

    -- Structured fields stored as JSON map for flexibility
    attributes      Map(String, String),

    -- Trace correlation
    trace_id        FixedString(32),
    span_id         FixedString(16),

    -- Indexing hints
    severity_number UInt8
)
ENGINE = MergeTree()
PARTITION BY (toYYYYMM(timestamp), service)
ORDER BY (service, level, timestamp)
TTL timestamp_date + INTERVAL 30 DAY
    DELETE,
    timestamp_date + INTERVAL 7 DAY
    TO VOLUME 'cold'
SETTINGS
    index_granularity = 8192,
    ttl_only_drop_parts = 1;

-- Bloom filter index for fast full-text search on message
ALTER TABLE observability.logs
ADD INDEX idx_message message TYPE tokenbf_v1(32768, 3, 0)
GRANULARITY 4;

-- Bloom filter for trace correlation
ALTER TABLE observability.logs
ADD INDEX idx_trace_id trace_id TYPE bloom_filter(0.001)
GRANULARITY 4;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

CREATE TABLE observability.metrics
(
    timestamp       DateTime64(3, 'UTC'),
    timestamp_date  Date DEFAULT toDate(timestamp),

    metric_name     LowCardinality(String),
    service         LowCardinality(String),
    environment     LowCardinality(String),
    host            LowCardinality(String),

    -- Metric dimensions as a sorted map for consistent ordering
    labels          Map(String, String),

    value           Float64,
    metric_type     LowCardinality(String)  -- gauge, counter, histogram, summary
)
ENGINE = MergeTree()
PARTITION BY (toYYYYMM(timestamp), metric_name)
ORDER BY (metric_name, service, labels, timestamp)
TTL timestamp_date + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192;

-- For Prometheus remote_write compatibility, create a materialized view
-- that pre-aggregates to 1-minute resolution after 7 days
CREATE MATERIALIZED VIEW observability.metrics_1m_mv
TO observability.metrics_1m
AS SELECT
    toStartOfMinute(timestamp) AS timestamp,
    timestamp_date,
    metric_name,
    service,
    environment,
    host,
    labels,
    avg(value) AS value,
    metric_type
FROM observability.metrics
GROUP BY
    toStartOfMinute(timestamp),
    timestamp_date,
    metric_name, service, environment, host, labels, metric_type;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

CREATE TABLE observability.traces
(
    timestamp           DateTime64(9, 'UTC'),
    timestamp_date      Date DEFAULT toDate(timestamp),

    -- OpenTelemetry span fields
    trace_id            FixedString(32),
    span_id             FixedString(16),
    parent_span_id      FixedString(16),
    operation_name      LowCardinality(String),
    service_name        LowCardinality(String),
    service_version     LowCardinality(String),

    -- Timing
    start_time_unix_nano UInt64,
    end_time_unix_nano   UInt64,
    duration_ns          UInt64 MATERIALIZED (end_time_unix_nano - start_time_unix_nano),

    -- Status
    status_code         LowCardinality(String),  -- OK, ERROR, UNSET
    status_message      String,

    -- Span kind: SERVER, CLIENT, PRODUCER, CONSUMER, INTERNAL
    span_kind           LowCardinality(String),

    -- Attributes
    attributes          Map(String, String),
    resource_attributes Map(String, String),

    -- HTTP-specific (common enough to be top-level for query performance)
    http_method         LowCardinality(String),
    http_url            String,
    http_status_code    UInt16,

    -- Events (errors, exceptions) stored as JSON array
    events              String
)
ENGINE = MergeTree()
PARTITION BY (toYYYYMMDD(timestamp), service_name)
ORDER BY (service_name, timestamp, trace_id)
TTL timestamp_date + INTERVAL 14 DAY DELETE
SETTINGS index_granularity = 8192;

-- Index for trace ID lookups (finding all spans in a trace)
ALTER TABLE observability.traces
ADD INDEX idx_trace_id trace_id TYPE bloom_filter(0.001)
GRANULARITY 4;

-- Index for slow query analysis
ALTER TABLE observability.traces
ADD INDEX idx_duration duration_ns TYPE minmax()
GRANULARITY 4;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

# config/vector/vector.toml

# ── Sources ────────────────────────────────────────────────────────────────

[sources.docker_logs]
type = "docker_logs"
docker_host = "unix:///var/run/docker.sock"
# Collect from all containers except vector itself
exclude_containers = ["vector"]

[sources.journald]
type = "journald"
current_boot_only = true

[sources.syslog]
type = "syslog"
mode = "tcp"
address = "0.0.0.0:5140"

# ── Transforms ─────────────────────────────────────────────────────────────

[transforms.parse_docker]
type = "remap"
inputs = ["docker_logs"]
source = '''
# Parse JSON log messages if possible
parsed, err = parse_json(.message)
if err == null {
  .attributes = {}
  for_each(object!(parsed)) -> |k, v| {
    .attributes[k] = to_string(v) ?? ""
  }
  # Extract common fields
  if exists(parsed.level) { .level = to_string!(parsed.level) }
  if exists(parsed.msg)   { .message = to_string!(parsed.msg) }
  if exists(parsed.trace_id) { .trace_id = to_string!(parsed.trace_id) }
  if exists(parsed.span_id)  { .span_id = to_string!(parsed.span_id) }
}

# Normalize log level
.level = upcase(string!(.level ?? "INFO"))
.level = if !includes(["DEBUG","INFO","WARN","WARNING","ERROR","FATAL"], .level) { "INFO" } else { .level }
.level = if .level == "WARNING" { "WARN" } else { .level }

# Extract service name from Docker label or container name
.service = string!(.label."com.docker.compose.service" ?? .container_name ?? "unknown")
.environment = string!(.label."environment" ?? "production")
.host = get_hostname!()
.container_id = string!(.container_id ?? "")
'''

[transforms.add_timestamps]
type = "remap"
inputs = ["parse_docker", "journald"]
source = '''
# Ensure timestamp is set
if !exists(.timestamp) {
  .timestamp = now()
}
'''

# ── Sinks ──────────────────────────────────────────────────────────────────

[sinks.clickhouse_logs]
type = "clickhouse"
inputs = ["add_timestamps"]
endpoint = "http://clickhouse:8123"
database = "observability"
table = "logs"
auth.strategy = "basic"
auth.user = "default"
auth.password = "${CLICKHOUSE_PASSWORD}"

# Batch for efficiency — ClickHouse loves large inserts
batch.max_bytes = 10485760   # 10MB
batch.timeout_secs = 5

# Retry on transient failures
request.retry_attempts = 5
request.retry_initial_backoff_secs = 1
request.retry_max_duration_secs = 30

encoding.timestamp_format = "unix"

[sinks.clickhouse_logs.buffer]
type = "disk"
max_size = 268435456  # 256MB on-disk buffer for durability
when_full = "block"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

# config/otel-collector/config.yaml
receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

  prometheus:
    config:
      scrape_configs:
        - job_name: otel-collector
          scrape_interval: 15s
          static_configs:
            - targets: [localhost:8888]

processors:
  batch:
    timeout: 5s
    send_batch_size: 10000
    send_batch_max_size: 50000

  memory_limiter:
    check_interval: 1s
    limit_mib: 512
    spike_limit_mib: 128

  # Add resource attributes as labels
  resourcedetection:
    detectors: [env, docker, system]
    timeout: 5s
    override: false

  # Tail-based sampling: keep 100% of error traces, 1% of healthy traces
  tail_sampling:
    decision_wait: 10s
    num_traces: 100000
    expected_new_traces_per_sec: 1000
    policies:
      - name: errors-policy
        type: status_code
        status_code: {status_codes: [ERROR]}
      - name: slow-traces-policy
        type: latency
        latency: {threshold_ms: 1000}
      - name: probabilistic-policy
        type: probabilistic
        probabilistic: {sampling_percentage: 1}

exporters:
  clickhouse:
    endpoint: tcp://clickhouse:9000
    database: observability
    logs_table_name: logs
    traces_table_name: traces
    metrics_table_name: metrics
    ttl: 720h      # 30 days
    compress: lz4
    dial_timeout: 5s
    connection_params: {}
    username: default
    password: ${CLICKHOUSE_PASSWORD}
    async_insert: true

  # Also export metrics to Prometheus for Grafana
  prometheus:
    endpoint: 0.0.0.0:8889

service:
  pipelines:
    traces:
      receivers: [otlp]
      processors: [memory_limiter, resourcedetection, tail_sampling, batch]
      exporters: [clickhouse]

    metrics:
      receivers: [otlp, prometheus]
      processors: [memory_limiter, resourcedetection, batch]
      exporters: [clickhouse, prometheus]

    logs:
      receivers: [otlp]
      processors: [memory_limiter, resourcedetection, batch]
      exporters: [clickhouse]

1
2
3
4
5
6
7
8

# prometheus.yml
remote_write:
  - url: http://clickhouse-adapter:9201/write
    queue_config:
      capacity: 100000
      max_shards: 10
      max_samples_per_send: 10000
      batch_send_deadline: 5s

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- Recent errors from the API service
SELECT
    timestamp,
    level,
    message,
    attributes['error'] AS error_detail,
    trace_id
FROM observability.logs
WHERE
    service = 'api'
    AND level = 'ERROR'
    AND timestamp >= now() - INTERVAL 1 HOUR
ORDER BY timestamp DESC
LIMIT 100;

-- Error rate by service over the last hour (1-minute buckets)
SELECT
    toStartOfMinute(timestamp) AS minute,
    service,
    countIf(level = 'ERROR') AS errors,
    count() AS total,
    round(countIf(level = 'ERROR') / count() * 100, 2) AS error_pct
FROM observability.logs
WHERE timestamp >= now() - INTERVAL 1 HOUR
GROUP BY minute, service
ORDER BY minute DESC, errors DESC;

-- Full-text search using the tokenbf index
SELECT timestamp, service, message
FROM observability.logs
WHERE
    timestamp >= now() - INTERVAL 24 HOUR
    AND hasToken(message, 'connection')
    AND hasToken(message, 'refused')
ORDER BY timestamp DESC
LIMIT 50;

-- Top log patterns by frequency (poor man's log clustering)
SELECT
    service,
    level,
    -- Strip numbers and UUIDs to normalize messages
    replaceRegexpAll(
        replaceRegexpAll(message, '[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}', '<uuid>'),
        '[0-9]+', '<num>'
    ) AS pattern,
    count() AS occurrences
FROM observability.logs
WHERE timestamp >= now() - INTERVAL 1 HOUR
GROUP BY service, level, pattern
ORDER BY occurrences DESC
LIMIT 20;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

-- Find all spans for a specific trace ID
SELECT
    span_id,
    parent_span_id,
    operation_name,
    service_name,
    round(duration_ns / 1e6, 2) AS duration_ms,
    status_code,
    http_method,
    http_url,
    http_status_code
FROM observability.traces
WHERE trace_id = '4bf92f3577b34da6a3ce929d0e0e4736'
ORDER BY start_time_unix_nano;

-- P50/P95/P99 latency per operation over the last hour
SELECT
    service_name,
    operation_name,
    count() AS requests,
    round(quantile(0.50)(duration_ns) / 1e6, 1) AS p50_ms,
    round(quantile(0.95)(duration_ns) / 1e6, 1) AS p95_ms,
    round(quantile(0.99)(duration_ns) / 1e6, 1) AS p99_ms,
    countIf(status_code = 'STATUS_CODE_ERROR') AS errors
FROM observability.traces
WHERE
    timestamp >= now() - INTERVAL 1 HOUR
    AND span_kind = 'SPAN_KIND_SERVER'
GROUP BY service_name, operation_name
HAVING count() > 10
ORDER BY p95_ms DESC
LIMIT 25;

-- Trace ID to log correlation: find logs for all spans in a slow trace
SELECT
    l.timestamp,
    l.service,
    l.level,
    l.message,
    l.trace_id
FROM observability.logs l
WHERE l.trace_id IN (
    SELECT DISTINCT trace_id
    FROM observability.traces
    WHERE
        service_name = 'checkout'
        AND duration_ns > 5000000000  -- > 5 seconds
        AND timestamp >= now() - INTERVAL 1 HOUR
)
ORDER BY l.timestamp;

-- Service dependency map: who calls whom
SELECT
    client.service_name AS caller,
    server.service_name AS callee,
    count() AS calls,
    round(avg(server.duration_ns) / 1e6, 1) AS avg_ms
FROM observability.traces server
INNER JOIN observability.traces client
    ON server.span_id = client.parent_span_id
    AND server.trace_id = client.trace_id
WHERE
    server.timestamp >= now() - INTERVAL 1 HOUR
    AND server.span_kind = 'SPAN_KIND_SERVER'
    AND client.span_kind = 'SPAN_KIND_CLIENT'
GROUP BY caller, callee
ORDER BY calls DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- CPU usage across all hosts (Prometheus-style metric)
SELECT
    toStartOfMinute(timestamp) AS minute,
    host,
    avg(value) AS cpu_percent
FROM observability.metrics
WHERE
    metric_name = 'node_cpu_usage_percent'
    AND timestamp >= now() - INTERVAL 3 HOUR
GROUP BY minute, host
ORDER BY minute DESC;

-- Memory usage trend
SELECT
    toStartOfFiveMinutes(timestamp) AS five_min,
    service,
    max(value) AS max_memory_bytes
FROM observability.metrics
WHERE
    metric_name = 'process_resident_memory_bytes'
    AND timestamp >= now() - INTERVAL 24 HOUR
GROUP BY five_min, service
ORDER BY five_min DESC;

-- Request rate using counter delta
SELECT
    toStartOfMinute(timestamp) AS minute,
    service,
    sum(value) AS requests
FROM observability.metrics
WHERE
    metric_name = 'http_requests_total'
    AND timestamp >= now() - INTERVAL 1 HOUR
GROUP BY minute, service
ORDER BY minute DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- Pre-aggregate logs to 1-minute error rate summary
CREATE TABLE observability.log_error_rate_1m
(
    minute        DateTime,
    service       LowCardinality(String),
    environment   LowCardinality(String),
    total         UInt64,
    errors        UInt64,
    warns         UInt64
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(minute)
ORDER BY (minute, service, environment)
TTL minute + INTERVAL 90 DAY DELETE;

CREATE MATERIALIZED VIEW observability.log_error_rate_1m_mv
TO observability.log_error_rate_1m
AS SELECT
    toStartOfMinute(timestamp) AS minute,
    service,
    environment,
    count()                        AS total,
    countIf(level = 'ERROR')       AS errors,
    countIf(level = 'WARN')        AS warns
FROM observability.logs
GROUP BY minute, service, environment;

-- Query the materialized view instead of the raw logs table
-- (returns in milliseconds instead of seconds for large datasets)
SELECT
    minute,
    service,
    errors,
    round(errors / total * 100, 2) AS error_pct
FROM observability.log_error_rate_1m
WHERE minute >= now() - INTERVAL 6 HOUR
ORDER BY minute DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- Pre-aggregate trace latency percentiles
CREATE TABLE observability.trace_latency_1m
(
    minute          DateTime,
    service_name    LowCardinality(String),
    operation_name  LowCardinality(String),
    total_count     UInt64,
    error_count     UInt64,
    duration_sum    UInt64,
    -- Quantile state stored for accurate merging
    p95_state       AggregateFunction(quantile(0.95), UInt64),
    p99_state       AggregateFunction(quantile(0.99), UInt64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(minute)
ORDER BY (minute, service_name, operation_name)
TTL minute + INTERVAL 90 DAY DELETE;

CREATE MATERIALIZED VIEW observability.trace_latency_1m_mv
TO observability.trace_latency_1m
AS SELECT
    toStartOfMinute(timestamp) AS minute,
    service_name,
    operation_name,
    count()                                         AS total_count,
    countIf(status_code = 'STATUS_CODE_ERROR')      AS error_count,
    sum(duration_ns)                                AS duration_sum,
    quantileState(0.95)(duration_ns)                AS p95_state,
    quantileState(0.99)(duration_ns)                AS p99_state
FROM observability.traces
WHERE span_kind = 'SPAN_KIND_SERVER'
GROUP BY minute, service_name, operation_name;

-- Query with accurate quantile merging
SELECT
    minute,
    service_name,
    sum(total_count) AS requests,
    round(quantileMerge(0.95)(p95_state) / 1e6, 1) AS p95_ms,
    round(quantileMerge(0.99)(p99_state) / 1e6, 1) AS p99_ms
FROM observability.trace_latency_1m
WHERE minute >= now() - INTERVAL 24 HOUR
GROUP BY minute, service_name
ORDER BY minute DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# config/grafana/provisioning/datasources/clickhouse.yml
apiVersion: 1

datasources:
  - name: ClickHouse
    type: grafana-clickhouse-datasource
    isDefault: true
    jsonData:
      host: clickhouse
      port: 9000
      username: readonly
      defaultDatabase: observability
      protocol: native
      # Enable query logging for debugging
      enableLogging: true
    secureJsonData:
      password: readonly_password

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    toStartOfMinute(timestamp) AS time,
    service,
    round(countIf(level = 'ERROR') / count() * 100, 2) AS error_pct
FROM observability.logs
WHERE
    $__timeFilter(timestamp)
    AND service IN ($service)
GROUP BY time, service
ORDER BY time

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    toStartOfMinute(timestamp) AS time,
    round(quantile(0.99)(duration_ns) / 1e6) AS p99_ms
FROM observability.traces
WHERE
    $__timeFilter(timestamp)
    AND service_name = '$service'
    AND span_kind = 'SPAN_KIND_SERVER'
GROUP BY time
ORDER BY time

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

<!-- config/clickhouse/storage.xml -->
<clickhouse>
  <storage_configuration>
    <disks>
      <hot_disk>
        <type>local</type>
        <path>/var/lib/clickhouse/hot/</path>
      </hot_disk>
      <cold_disk>
        <type>local</type>
        <path>/var/lib/clickhouse/cold/</path>
        <!-- Or use S3-compatible object storage: -->
        <!-- <type>s3</type> -->
        <!-- <endpoint>https://s3.amazonaws.com/my-bucket/clickhouse/</endpoint> -->
      </cold_disk>
    </disks>
    <policies>
      <tiered>
        <volumes>
          <hot>
            <disk>hot_disk</disk>
            <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
          </hot>
          <cold>
            <disk>cold_disk</disk>
          </cold>
        </volumes>
        <move_factor>0.1</move_factor>
      </tiered>
    </policies>
  </storage_configuration>
</clickhouse>

1
2
3
4

CREATE TABLE observability.logs
( ... )
ENGINE = MergeTree()
SETTINGS storage_policy = 'tiered';

1
2
3
4
5

-- Per-session setting
SET async_insert = 1;
SET wait_for_async_insert = 0;
SET async_insert_max_data_size = 10485760;  -- 10MB
SET async_insert_busy_timeout_ms = 1000;    -- Flush every second

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- See what queries are running
SELECT
    query_id,
    user,
    elapsed,
    read_rows,
    formatReadableSize(memory_usage) AS memory,
    substring(query, 1, 100) AS query_preview
FROM system.processes
ORDER BY elapsed DESC;

-- Find slow queries in history
SELECT
    normalized_query_hash,
    count() AS cnt,
    round(avg(query_duration_ms)) AS avg_ms,
    round(max(query_duration_ms)) AS max_ms,
    formatReadableSize(avg(memory_usage)) AS avg_memory,
    any(query) AS sample_query
FROM system.query_log
WHERE
    type = 'QueryFinish'
    AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY normalized_query_hash
ORDER BY avg_ms DESC
LIMIT 20;

-- Check part counts (too many small parts = slow queries)
SELECT
    table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active AND database = 'observability'
GROUP BY table
ORDER BY parts DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Delta codec works great for monotonically increasing timestamps
timestamp DateTime64(9) CODEC(Delta, ZSTD(3)),

-- DoubleDelta for metrics that change slowly
value Float64 CODEC(Gorilla, ZSTD),

-- T64 for integer columns with small deltas
duration_ns UInt64 CODEC(T64, ZSTD(3)),

-- High compression for low-entropy strings
service LowCardinality(String) CODEC(ZSTD(9))