ClickHouse für Web-Analytics: Materialized Views, MergeTree, PostgreSQL-Integration

Warum ClickHouse, wenn es PostgreSQL gibt

PostgreSQL ist eine hervorragende OLTP-Datenbank: Transaktionen, ACID, Foreign Keys, JSONB. Aber als die Events-Tabelle 50 Millionen Zeilen überschritt und das Analytics-Dashboard 12 Sekunden zum Laden brauchte — wurde klar, dass etwas anderes her muss.

ClickHouse ist eine spaltenorientierte Datenbank, die für analytische Abfragen optimiert ist. Wo PostgreSQL alle Spalten einer Zeile scannt, liest ClickHouse nur die benötigten Spalten. Der Unterschied liegt bei Größenordnungen in Aggregationen.

Ich habe ClickHouse als zweite Datenbank in ein Production-Projekt integriert: PostgreSQL blieb für die Kerndaten, ClickHouse — für Analytics, Logs und Metriken. So sieht das in der Praxis aus.

Architektur: PostgreSQL + ClickHouse

Warum "Beides" statt "Entweder-Oder"

ClickHouse ersetzt PostgreSQL nicht. Es hat keine Transaktionen, UPDATE funktioniert über Mutationen (asynchron), DELETE ebenso. Für CRUD-Operationen ist es nicht geeignet. Aber für Analytics — ideal.

Typische Architektur:

┌─────────────┐     ┌──────────────┐     ┌──────────────┐
│   Rails App  │────▶│  PostgreSQL  │     │  ClickHouse  │
│              │     │  (OLTP)      │     │  (OLAP)      │
│              │────▶│              │     │              │
└─────────────┘     └──────────────┘     └──────────────┘
       │                                        ▲
       │          ┌──────────────┐              │
       └─────────▶│  Background  │──────────────┘
                  │  Job (ETL)   │
                  └──────────────┘

Die Anwendung schreibt Kerndaten in PostgreSQL. Ein Hintergrund-Job (Sidekiq/SolidQueue) überträgt Events und Metriken nach ClickHouse. Dashboards lesen aus ClickHouse.

Was in ClickHouse gespeichert werden sollte

Einfache Regel: Wenn Daten häufig geschrieben, aggregiert gelesen werden und kein UPDATE benötigen — gehören sie in ClickHouse.

Installation und Konfiguration

Installation auf Ubuntu

# ClickHouse-Repository hinzufügen
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

# Server und Client installieren
sudo apt-get install -y clickhouse-server clickhouse-client

# Starten
sudo systemctl start clickhouse-server
sudo systemctl enable clickhouse-server

# Überprüfen
clickhouse-client --query "SELECT version()"

Docker (für Entwicklung)

# docker-compose.yml
services:
  clickhouse:
    image: clickhouse/clickhouse-server:24.8-alpine
    ports:
      - "8123:8123"   # HTTP-Schnittstelle
      - "9000:9000"   # Natives Protokoll
    volumes:
      - clickhouse_data:/var/lib/clickhouse
    environment:
      CLICKHOUSE_DB: analytics
      CLICKHOUSE_USER: app
      CLICKHOUSE_PASSWORD: secure_password
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  clickhouse_data:

Tabellen-Engines: MergeTree und seine Familie

MergeTree — Die Kern-Engine

CREATE TABLE page_views
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    session_id String,
    url String,
    referrer String,
    device_type Enum8('desktop' = 1, 'mobile' = 2, 'tablet' = 3),
    country_code FixedString(2),
    browser String,
    os String,
    load_time_ms UInt16,
    is_bounce UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, session_id)
TTL event_date + INTERVAL 12 MONTH
SETTINGS index_granularity = 8192;

Im Detail:

• PARTITION BY toYYYYMM(event_date) — Daten werden nach Monaten aufgeteilt. Eine Abfrage für einen bestimmten Monat liest nur eine Partition
• ORDER BY (eventdate, userid, session_id) — physische Sortierreihenfolge auf der Festplatte. Dies ist der Primary Key. Abfragen auf diese Spalten nutzen den Sparse Index
• TTL event_date + INTERVAL 12 MONTH — automatisches Löschen von Daten älter als ein Jahr
• index_granularity = 8192 — alle 8192 Zeilen wird ein Eintrag im Primary Index erstellt

ReplacingMergeTree — Für Deduplizierung

CREATE TABLE user_sessions
(
    session_id String,
    user_id UInt64,
    started_at DateTime,
    ended_at DateTime,
    page_count UInt16,
    total_time_seconds UInt32,
    version UInt32
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(started_at)
ORDER BY (session_id);

ReplacingMergeTree behält beim Merge nur die Zeile mit der höchsten version für jeden eindeutigen Schlüssel. Wichtig: Die Deduplizierung passiert beim Hintergrund-Merge, nicht beim INSERT. Für garantierte Deduplizierung beim Lesen — FINAL verwenden:

SELECT * FROM user_sessions FINAL WHERE user_id = 12345;

AggregatingMergeTree — Voraggregierung

CREATE TABLE daily_metrics
(
    date Date,
    page String,
    views AggregateFunction(count, UInt64),
    unique_users AggregateFunction(uniq, UInt64),
    avg_load_time AggregateFunction(avg, Float32)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, page);

AggregatingMergeTree speichert Zwischenzustände von Aggregatfunktionen. Beim Merge werden sie automatisch kombiniert. Ergebnis: Abfragen gegen aggregierte Daten laufen in Millisekunden.

SummingMergeTree — Für Zähler

CREATE TABLE hourly_counters
(
    hour DateTime,
    endpoint String,
    requests UInt64,
    errors UInt64,
    total_duration_ms UInt64
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMMDD(hour)
ORDER BY (hour, endpoint);

Beim Merge werden Zeilen mit gleichem Schlüssel summiert. Perfekt für Zähler: Einfach (now(), '/api/users', 1, 0, 45) bei jeder Anfrage einfügen — ClickHouse aggregiert automatisch.

Datentypen: Die richtige Wahl spart das 10-fache

Numerische Typen

-- Schlecht: alles als UInt64
user_id UInt64,        -- 8 Bytes
age UInt64,            -- 8 Bytes (max 255 — warum 8 Bytes?)
is_active UInt64,      -- 8 Bytes (0 oder 1 — warum 8 Bytes?)

-- Gut: passende Typen
user_id UInt64,        -- 8 Bytes — IDs können groß sein
age UInt8,             -- 1 Byte — reicht bis 255
is_active UInt8,       -- 1 Byte — Boolean über UInt8
http_status UInt16,    -- 2 Bytes — Codes 100-599
price Decimal64(2),    -- 8 Bytes — exakte Arithmetik für Geld

In einer spaltenorientierten Datenbank wird jedes gesparte Byte pro Wert mit der Anzahl der Zeilen multipliziert. 100M Zeilen × 7 gesparte Bytes = 700MB weniger auf der Festplatte und im RAM.

String-Typen

-- FixedString für Daten fester Länge
country_code FixedString(2),     -- 'US', 'RU', 'DE' — immer 2 Bytes
currency FixedString(3),          -- 'USD', 'EUR', 'RUB'
ip_v4 IPv4,                       -- 4 Bytes statt ~15 für String

-- LowCardinality für sich wiederholende Werte
browser LowCardinality(String),   -- Dictionary-Kodierung
os LowCardinality(String),
device_type LowCardinality(String),

-- Enum für bekannte Wertesets
status Enum8('active' = 1, 'inactive' = 2, 'banned' = 3),

LowCardinality ist eine der mächtigsten Techniken. Für eine Browser-Spalte erstellt ClickHouse ein Dictionary: {1: 'Chrome', 2: 'Firefox', 3: 'Safari'} — und speichert nur numerische IDs. Bei 100M Zeilen spart das 5-10x gegenüber normalem String.

Materialized Views — Automatische Aggregation

Das Konzept

Ein Materialized View in ClickHouse ist ein Trigger auf INSERT. Wenn Daten in die Quelltabelle eingefügt werden, transformiert und fügt das Materialized View sie automatisch in die Zieltabelle ein.

-- Quelltabelle: Roh-Events
CREATE TABLE raw_events
(
    event_time DateTime,
    event_type LowCardinality(String),
    user_id UInt64,
    page String,
    duration_ms UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- Zieltabelle: stündlich aggregierte Metriken
CREATE TABLE hourly_page_stats
(
    hour DateTime,
    page String,
    views UInt64,
    unique_users UInt64,
    avg_duration Float32,
    max_duration UInt32
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, page);

-- Materialized View: automatische Aggregation
CREATE MATERIALIZED VIEW mv_hourly_page_stats
TO hourly_page_stats
AS
SELECT
    toStartOfHour(event_time) AS hour,
    page,
    count() AS views,
    uniqExact(user_id) AS unique_users,
    avg(duration_ms) AS avg_duration,
    max(duration_ms) AS max_duration
FROM raw_events
WHERE event_type = 'page_view'
GROUP BY hour, page;

Kaskadierende Materialized Views

-- Ebene 2: tägliche Aggregation (aus hourly_page_stats)
CREATE TABLE daily_page_stats
(
    date Date,
    page String,
    views UInt64,
    unique_users UInt64,
    avg_duration Float32
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, page);

CREATE MATERIALIZED VIEW mv_daily_page_stats
TO daily_page_stats
AS
SELECT
    toDate(hour) AS date,
    page,
    sum(views) AS views,
    sum(unique_users) AS unique_users,
    avg(avg_duration) AS avg_duration
FROM hourly_page_stats
GROUP BY date, page;

-- Ebene 3: monatliche Aggregation
CREATE TABLE monthly_page_stats
(
    month Date,
    page String,
    views UInt64,
    unique_users UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (month, page);

CREATE MATERIALIZED VIEW mv_monthly_page_stats
TO monthly_page_stats
AS
SELECT
    toStartOfMonth(date) AS month,
    page,
    sum(views) AS views,
    sum(unique_users) AS unique_users
FROM daily_page_stats
GROUP BY month, page;

Drei Aggregationsebenen: Stunde → Tag → Monat. Die Abfrage "wie viele Aufrufe im letzten Jahr" greift auf monthly_page_stats zu — 12 Zeilen statt 500 Millionen.

Integration mit Rails

Das clickhouse-activerecord Gem

# Gemfile
gem 'clickhouse-activerecord', '~> 1.0'

# config/database.yml
production:
  primary:
    adapter: postgresql
    database: myapp_production
  clickhouse:
    adapter: clickhouse
    host: localhost
    port: 8123
    database: analytics
    username: app
    password: secure_password

# app/models/clickhouse_record.rb
class ClickhouseRecord < ActiveRecord::Base
    self.abstract_class = true
    connects_to database: { writing: :clickhouse, reading: :clickhouse }
end

# app/models/analytics/page_view.rb
module Analytics
  class PageView < ClickhouseRecord
    self.table_name = 'page_views'
  end
end

Direkter Client (für komplexe Abfragen)

# app/services/clickhouse_client.rb
class ClickhouseClient
  def initialize
    @connection = Faraday.new(url: ENV['CLICKHOUSE_URL'] || 'http://localhost:8123') do |f|
      f.request :url_encoded
      f.adapter Faraday.default_adapter
    end
  end

  def query(sql, format: 'JSON')
    response = @connection.post('/', "#{sql} FORMAT #{format}", {
      'X-ClickHouse-User' => ENV.fetch('CLICKHOUSE_USER', 'default'),
      'X-ClickHouse-Key' => ENV.fetch('CLICKHOUSE_PASSWORD', ''),
      'X-ClickHouse-Database' => ENV.fetch('CLICKHOUSE_DB', 'analytics'),
    })

    raise "ClickHouse error: #{response.body}" unless response.success?
    JSON.parse(response.body)
  end

  def insert(table, rows)
    columns = rows.first.keys
    values = rows.map { |r| columns.map { |c| format_value(r[c]) }.join(',') }
    sql = "INSERT INTO #{table} (#{columns.join(',')}) VALUES #{values.map { |v| "(#{v})" }.join(',')}"
    execute(sql)
  end

  private

  def format_value(val)
    case val
    when String then "'#{val.gsub("'", "\\\\'")}'"
    when Time, DateTime then "'#{val.strftime('%Y-%m-%d %H:%M:%S')}'"
    when Date then "'#{val.strftime('%Y-%m-%d')}'"
    when nil then 'NULL'
    when true then '1'
    when false then '0'
    else val.to_s
    end
  end
end

ETL: Daten von PostgreSQL nach ClickHouse übertragen

# app/jobs/sync_events_to_clickhouse_job.rb
class SyncEventsToClickhouseJob < ApplicationJob
  queue_as :analytics

  BATCH_SIZE = 10_000

  def perform
    last_synced_id = Rails.cache.read('clickhouse:last_event_id') || 0

    Event.where('id > ?', last_synced_id)
         .find_in_batches(batch_size: BATCH_SIZE) do |batch|
      rows = batch.map do |event|
        {
          event_time: event.created_at,
          event_type: event.event_type,
          user_id: event.user_id || 0,
          page: event.page || '',
          duration_ms: event.duration_ms || 0,
        }
      end

      ClickhouseClient.new.insert('raw_events', rows)
      Rails.cache.write('clickhouse:last_event_id', batch.last.id)
    end
  end
end

Abfragen: Von einfach bis komplex

Grundlegende analytische Abfragen

-- Top 10 Seiten der letzten 7 Tage
SELECT
    page,
    count() AS views,
    uniqExact(user_id) AS unique_users,
    round(avg(duration_ms)) AS avg_duration_ms
FROM raw_events
WHERE event_type = 'page_view'
  AND event_time >= now() - INTERVAL 7 DAY
GROUP BY page
ORDER BY views DESC
LIMIT 10;

-- Stündlicher Traffic für heute
SELECT
    toStartOfHour(event_time) AS hour,
    count() AS views,
    uniqExact(user_id) AS users
FROM raw_events
WHERE event_type = 'page_view'
  AND event_date = today()
GROUP BY hour
ORDER BY hour;

Kohortenanalyse

WITH cohorts AS (
    SELECT
        user_id,
        toMonday(min(event_time)) AS cohort_week
    FROM raw_events
    WHERE event_type = 'page_view'
    GROUP BY user_id
)
SELECT
    c.cohort_week,
    count(DISTINCT c.user_id) AS cohort_size,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 1 WEEK,
        e.user_id, NULL
    )) AS week_1,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 2 WEEK,
        e.user_id, NULL
    )) AS week_2,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 3 WEEK,
        e.user_id, NULL
    )) AS week_3
FROM cohorts c
LEFT JOIN raw_events e ON c.user_id = e.user_id
WHERE c.cohort_week >= today() - INTERVAL 8 WEEK
GROUP BY c.cohort_week
ORDER BY c.cohort_week;

Konversionstrichter mit windowFunnel

-- Eingebaute Trichterfunktion — ein Killer-Feature von ClickHouse
SELECT
    level,
    count() AS users,
    round(count() * 100.0 / max(count()) OVER (), 2) AS conversion_rate
FROM (
    SELECT
        user_id,
        windowFunnel(86400)(
            event_time,
            event_type = 'page_view',
            event_type = 'add_to_cart',
            event_type = 'checkout_start',
            event_type = 'payment_complete'
        ) AS level
    FROM raw_events
    WHERE event_date >= today() - 30
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;

windowFunnel ist eines der Killer-Features von ClickHouse. Es berechnet, bis zu welchem Trichterschritt jeder Benutzer gekommen ist, unter Berücksichtigung eines Zeitfensters (86400 Sekunden = 24 Stunden). Eine Abfrage statt einer Serie von JOINs.

Performance-Optimierung

Korrektes ORDER BY

ORDER BY in ClickHouse bestimmt nicht die Ergebnissortierung — sondern die physische Datenanordnung auf der Festplatte.

-- Schlecht: hohes Kardinalitätsfeld zuerst
ORDER BY (user_id, event_date)
-- WHERE event_date = '2026-05-01' scannt ALLE Daten

-- Gut: niedrige Kardinalität zuerst
ORDER BY (event_date, user_id)
-- WHERE event_date = '2026-05-01' liest nur ein Granul

Skip-Indexe

ALTER TABLE raw_events
    ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 4;

ALTER TABLE raw_events
    ADD INDEX idx_country country_code TYPE set(100) GRANULARITY 4;

ALTER TABLE raw_events
    ADD INDEX idx_duration duration_ms TYPE minmax GRANULARITY 4;

• bloom_filter — für Strings mit hoher Kardinalität (URL, Email). False Positive ~1%
• set — für Spalten mit begrenztem Wertebereich (Land, Browser)
• minmax — für numerische Felder. Speichert Min/Max pro Granul

Projektionen

ALTER TABLE raw_events
    ADD PROJECTION prj_by_user (
        SELECT user_id, event_type, event_time, page, duration_ms
        ORDER BY (user_id, event_time)
    );

ALTER TABLE raw_events MATERIALIZE PROJECTION prj_by_user;

Eine Projektion ist eine alternative physische Datenreihenfolge. Abfragen WHERE user_id = X verwenden automatisch die Projektion.

Benchmarks: ClickHouse vs PostgreSQL

Alle Tests auf einer Tabelle mit 100M Zeilen, gleiche Maschine (4 Kerne, 16GB RAM).

Der Unterschied liegt nicht in Prozenten — sondern in Größenordnungen. Bei Aggregationsabfragen über große Volumen ist ClickHouse 100-200x schneller.

Gründe:
- Spaltenorientierte Speicherung: SELECT count(*) WHERE event_date = X liest nur die event_date-Spalte
- Vektorisierte Ausführung: Datenverarbeitung mit SIMD-Instruktionen
- Kompression: Spalten mit sich wiederholenden Daten komprimieren 10-20x (LZ4/ZSTD)
- Sparse Index: statt B-Tree — ein Sparse Index mit 8192-Zeilen-Granularität

Backup und Wiederherstellung

# clickhouse-backup installieren
wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.6.0/clickhouse-backup-linux-amd64.tar.gz
tar xzf clickhouse-backup-linux-amd64.tar.gz
sudo mv clickhouse-backup /usr/local/bin/

# Backup erstellen
clickhouse-backup create daily_backup_$(date +%Y%m%d)

# Backups auflisten
clickhouse-backup list

# Wiederherstellen
clickhouse-backup restore daily_backup_20260527

# In S3 hochladen
clickhouse-backup upload daily_backup_20260527

Monitoring

Prometheus + Grafana

ClickHouse liefert Prometheus-Metriken auf Port 9363 out of the box. Wichtige Metriken:

ClickHouseProfileEvents_Query                    # Gesamtabfragen
ClickHouseProfileEvents_FailedQuery              # Fehler
ClickHouseMetrics_MemoryTracking                 # Speicherverbrauch
ClickHouseAsyncMetrics_MaxPartCountForPartition  # Partitionsgesundheit

Systemtabellen für Diagnose

-- Aktuelle Abfragen
SELECT query_id, elapsed, query FROM system.processes;

-- Merge-Gesundheit
SELECT
    database, table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, table
HAVING parts > 100
ORDER BY parts DESC;

Abschließende Checkliste zur ClickHouse-Implementierung

Vor dem Start

• [ ] Bestimmen, welche Daten übertragen werden: Logs, Events, Metriken — keine Transaktionsdaten
• [ ] Volumen abschätzen: < 10M Zeilen/Monat — PostgreSQL reicht, > 100M — ClickHouse lohnt sich
• [ ] Integrationsmethode wählen: clickhouse-activerecord Gem oder direkter HTTP-Client
• [ ] Schema mit korrekten Typen entwerfen: LowCardinality, Enum, FixedString

Tabellendesign

• [ ] ORDER BY: Felder mit niedriger Kardinalität zuerst (Datum, Eventtyp)
• [ ] PARTITION BY: toYYYYMM(date) für die meisten Fälle
• [ ] TTL: automatisches Löschen alter Daten
• [ ] Engine: MergeTree für Append-Only, ReplacingMergeTree für Deduplizierung

Materialized Views

• [ ] MV für jede Aggregationsebene erstellen: Stunde → Tag → Monat
• [ ] MV verifizieren: Testdaten einfügen und Zieltabelle abfragen
• [ ] Kaskadierende MVs: Datenfluss durch alle Ebenen prüfen

ETL und Synchronisation

• [ ] Hintergrund-Job für Datenübertragung aus PostgreSQL
• [ ] Idempotenz: Wiederholte Ausführungen erzeugen keine Duplikate
• [ ] Lag-Monitoring: Differenz zwischen letztem Event in PostgreSQL und ClickHouse

Produktion

• [ ] Backups: tägliches clickhouse-backup + Upload in S3
• [ ] Monitoring: Prometheus + Grafana Dashboard für ClickHouse
• [ ] Alerts: zu viele Parts, hoher Speicherverbrauch, fehlgeschlagene Abfragen
• [ ] Lasttests: Testen mit realistischem Datenvolumen
• [ ] Dokumentation: Datenschema, ETL-Pipeline, wichtige Abfragen