ClickHouse for delivery analytics

ClickHouse turns billions of log rows into sub-second analytics. KumoMTA documents the schema upstream — most ESPs run with it from day one.

Sample table:

CREATE TABLE kumomta_events (
  ts DateTime,
  type LowCardinality(String),
  tenant LowCardinality(String),
  campaign LowCardinality(String),
  domain LowCardinality(String),
  egress_source LowCardinality(String),
  response_code UInt16,
  enhanced_code LowCardinality(String),
  bounce_category LowCardinality(String),
  recipient String,
  message_id String
) ENGINE = MergeTree
ORDER BY (ts, tenant, domain);

Bread-and-butter queries:

-- Delivery rate per destination, last hour
SELECT domain,
       countIf(type='Delivered') / countIf(type IN ('Delivered','Bounced')) AS delivery_rate
FROM kumomta_events
WHERE ts > now() - INTERVAL 1 HOUR
GROUP BY domain ORDER BY delivery_rate DESC;

-- Top bounce categories per tenant
SELECT tenant, bounce_category, count() AS n
FROM kumomta_events WHERE type='Bounced' AND ts > today()
GROUP BY tenant, bounce_category ORDER BY n DESC LIMIT 50;

This is where deliverability stops being guesswork. You can answer "is our Yahoo delivery degrading?" in seconds.