ClickHouse vs PostgreSQL: когда что выбрать для аналитики

PostgreSQL — отличная база для OLTP (транзакции, оперативные данные). Но для аналитики на сотнях миллионов строк появляется ClickHouse. Разберём разницу.

Строчное vs Колоночное хранение

PostgreSQL (строчное): каждая строка хранится вместе:

[id=1, name="Alice", city="Moscow", age=28, revenue=5000]
[id=2, name="Bob",   city="SPb",   age=35, revenue=8000]

ClickHouse (колоночное): каждый столбец отдельно:

id:      [1, 2, 3, ...]
name:    ["Alice", "Bob", ...]
city:    ["Moscow", "SPb", ...]
revenue: [5000, 8000, ...]

Преимущество колоночного для аналитики:

-- Запрос использует только revenue и city
SELECT city, AVG(revenue) FROM events GROUP BY city;

PostgreSQL читает всю строку (все поля), ClickHouse читает только нужные два столбца. На миллиарде строк → экономия в 10-100x.

Скорость: реальные цифры

ClickHouse в 100-1000 раз быстрее PostgreSQL на аналитических запросах по большим объёмам.

SQL-совместимость

ClickHouse использует диалект SQL, похожий на стандартный, но с отличиями:

-- ClickHouse: ORDER BY обязателен для оконных функций LIMIT
SELECT * FROM events ORDER BY created_at DESC LIMIT 100

-- Функции отличаются
-- PostgreSQL:
NOW(), CURRENT_TIMESTAMP

-- ClickHouse:
now(), toStartOfDay(created_at), toYYYYMM(created_at)

-- Агрегатные функции ClickHouse
uniq(user_id)           -- приближённый COUNT DISTINCT (быстро)
uniqExact(user_id)      -- точный COUNT DISTINCT (медленнее)
groupArray(value)       -- массив значений (как ARRAY_AGG)
quantile(0.95)(value)   -- процентиль

Типы таблиц ClickHouse

-- MergeTree — основной движок (для большинства случаев)
CREATE TABLE events (
    event_date  Date,
    user_id     UInt64,
    event_type  String,
    amount      Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)  -- партиции по месяцам
ORDER BY (user_id, event_date);    -- сортировка (первичный ключ)

-- ReplacingMergeTree — дедупликация строк
ENGINE = ReplacingMergeTree(updated_at)

-- AggregatingMergeTree — хранит агрегаты
ENGINE = AggregatingMergeTree()

Вставка данных: ТОЛЬКО батчами

-- Плохо: много мелких INSERT
INSERT INTO events VALUES (1, ...);  -- не делайте так
INSERT INTO events VALUES (2, ...);

-- Хорошо: батч минимум 10k строк
INSERT INTO events SELECT * FROM staging_events;

ClickHouse оптимизирован для батчевой записи. Мелкие INSERT создают много мелких файлов → деградация производительности.

Materialized Views в ClickHouse

Ключевая фича — материализованные представления строятся инкрементально при INSERT:

-- Исходная таблица
CREATE TABLE raw_events (...) ENGINE = MergeTree() ORDER BY (user_id, created_at);

-- Агрегация автоматически обновляется при вставке
CREATE MATERIALIZED VIEW daily_stats
ENGINE = SummingMergeTree()
ORDER BY (day, event_type) AS
SELECT
    toDate(created_at) AS day,
    event_type,
    count() AS events,
    uniq(user_id) AS unique_users
FROM raw_events
GROUP BY day, event_type;

-- Запрос по агрегату — мгновенный даже на миллиардах
SELECT day, event_type, sum(events), uniq(unique_users)
FROM daily_stats GROUP BY day, event_type;

Когда PostgreSQL лучше

✅ OLTP: транзакции, INSERT/UPDATE/DELETE построчно ✅ JOIN сложных запросов: PostgreSQL оптимизирует лучше ✅ Мало данных: до ~100M строк — PostgreSQL вполне справится ✅ ACID-транзакции: ClickHouse не поддерживает классические транзакции ✅ Гибкая схема: PostgreSQL + JSONB ✅ Единый стек: не хочется поддерживать две БД

Когда ClickHouse лучше

✅ Аналитика событий: логи, clickstream, IoT (миллиарды строк) ✅ Дашборды реального времени: запросы за секунды вместо минут ✅ Агрегации на больших данных: GROUP BY на 1B+ строк ✅ Данные только добавляются: INSERT-heavy, без UPDATE

Типичная архитектура

PostgreSQL                ClickHouse
(основная БД)             (аналитика)
users, orders ──────────→ events, logs, metrics
orders.completed ────────→ revenue_fact

Репликация:
- Airbyte / Debezium (CDC) → ClickHouse
- dbt → трансформации в обеих БД

Запросы: сравнение синтаксиса

-- PostgreSQL
SELECT
    DATE_TRUNC('day', created_at) AS day,
    COUNT(DISTINCT user_id) AS dau,
    ROUND(AVG(amount), 2) AS avg_amount
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 1;

-- ClickHouse (аналог)
SELECT
    toDate(created_at) AS day,
    uniq(user_id) AS dau,          -- приближённо (быстрее)
    round(avg(amount), 2) AS avg_amount
FROM orders
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY day ORDER BY day;

Итог

Начните с PostgreSQL. Когда аналитические запросы занимают > 10 секунд на ваших объёмах — рассматривайте ClickHouse для аналитического слоя.