Your ClickHouse Server

-- Is data still arriving? The single most useful alert you can build:
-- it catches a broken integration, an expired credential and a firewall
-- change all at once.
SELECT
    max(event_time)                          AS newest_event,
    dateDiff('minute', max(event_time), now()) AS minutes_stale,
    count()                                  AS events_last_hour
FROM website_events
WHERE event_date >= today() - 1
  AND event_time >= now() - INTERVAL 1 HOUR;

-- Are merges keeping up? A part count climbing into the thousands for one
-- partition means inserts are too small and too frequent.
SELECT
    partition,
    count()          AS parts,
    sum(rows)        AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE table = 'website_events' AND active
GROUP BY partition
ORDER BY partition DESC
LIMIT 12;

-- Anything stuck?
SELECT database, table, elapsed, progress, num_parts, result_part_name
FROM system.merges;

-- For replicated setups: is any replica falling behind?
SELECT
    table, replica_name, is_readonly, is_session_expired,
    absolute_delay, queue_size, inserts_in_queue, merges_in_queue
FROM system.replicas
WHERE table = 'website_events';

-- What is running right now, and what is it costing?
SELECT
    query_id, elapsed, formatReadableSize(memory_usage) AS memory,
    read_rows, substring(query, 1, 120) AS query
FROM system.processes
WHERE query NOT LIKE '%system.processes%'
ORDER BY elapsed DESC;

# ClickHouse has native BACKUP/RESTORE. A container volume is not a backup.

# One-off backup to a local disk configured in storage.xml:
clickhouse-client --query "
  BACKUP TABLE analytics.website_events
  TO Disk('backups', 'website_events_$(date +%Y%m%d).zip')
"

# Incremental, based on the previous backup — far cheaper day to day:
clickhouse-client --query "
  BACKUP TABLE analytics.website_events
  TO Disk('backups', 'website_events_$(date +%Y%m%d).zip')
  SETTINGS base_backup = Disk('backups', 'website_events_full.zip')
"

# Straight to S3, which is usually what you want:
clickhouse-client --query "
  BACKUP TABLE analytics.website_events
  TO S3('https://acme-backups.s3.eu-west-1.amazonaws.com/clickhouse/website_events',
        '\${AWS_ACCESS_KEY_ID}', '\${AWS_SECRET_ACCESS_KEY}')
"

# Restore into a different table name first. Verify, then swap — never
# restore over live data on the basis that the backup is probably fine.
clickhouse-client --query "
  RESTORE TABLE analytics.website_events AS analytics.website_events_restored
  FROM Disk('backups', 'website_events_20260701.zip')
"

# Confirm the restore before trusting it:
#   SELECT count(), min(event_time), max(event_time)
#   FROM analytics.website_events_restored;

# Cheaper alternative for point-in-time copies: FREEZE creates hardlinks
# in the shadow directory, so it is near-instant and costs no extra space
# until parts change.
clickhouse-client --query "
  ALTER TABLE analytics.website_events FREEZE WITH NAME 'nightly'
"
# Then rsync /var/lib/clickhouse/shadow/nightly/ off the host.

-- The sort key is (user_id, event_time, event_type, event_id), which makes
-- per-user timelines very fast. Reports that filter on event_type or
-- page_path without a user_id have to scan more, so help them along.

-- 1. A skip index on event_type. Cheap to maintain, and lets ClickHouse
--    skip whole granules for a filtered report.
ALTER TABLE website_events
    ADD INDEX idx_event_type event_type TYPE set(100) GRANULARITY 4;

ALTER TABLE website_events MATERIALIZE INDEX idx_event_type;

-- 2. A bloom filter for page_path lookups.
ALTER TABLE website_events
    ADD INDEX idx_page_path page_path TYPE bloom_filter(0.01) GRANULARITY 4;

ALTER TABLE website_events MATERIALIZE INDEX idx_page_path;

-- 3. For a dashboard panel that is opened constantly, pre-aggregate it.
--    This is the single biggest win at high volume.
CREATE TABLE website_events_daily
(
    event_date   Date,
    site_id      String,
    event_type   LowCardinality(String),
    country_code LowCardinality(String),
    device_type  LowCardinality(String),
    events       AggregateFunction(count),
    visitors     AggregateFunction(uniq, String),
    sessions     AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, site_id, event_type, country_code, device_type);

CREATE MATERIALIZED VIEW website_events_daily_mv TO website_events_daily AS
SELECT
    event_date,
    site_id,
    event_type,
    country_code,
    device_type,
    countState()                AS events,
    uniqState(anonymous_id)     AS visitors,
    uniqState(session_id)       AS sessions
FROM website_events
GROUP BY event_date, site_id, event_type, country_code, device_type;

-- Read it with the -Merge combinators:
SELECT
    event_date,
    countMerge(events)   AS events,
    uniqMerge(visitors)  AS visitors
FROM website_events_daily
WHERE event_date >= today() - 90
GROUP BY event_date
ORDER BY event_date;

-- Note the materialised view only sees rows inserted after you create it.
-- Backfill it once from the raw table if you need the history.