ClickHouse vs PostgreSQL: когда нужна колоночная СУБД

PostgreSQL — отличная OLTP-СУБД, оптимизированная для транзакций: вставка, обновление, удаление отдельных строк. Но когда аналитический запрос сканирует миллионы строк по нескольким колонкам, строковое хранилище PostgreSQL читает все колонки каждой строки — даже если запросу нужны только 3 из 50.

ClickHouse — колоночная СУБД от Яндекса, спроектированная для OLAP-нагрузок. Данные хранятся по колонкам: при запросе SELECT avg(amount) FROM orders WHERE date > '2024-01-01' считываются только колонки amount и date, а не вся строка. Это даёт прирост скорости в 10-100 раз на аналитических запросах по таблицам с миллиардами строк.

Выбор между PostgreSQL и ClickHouse — не вопрос «лучше/хуже», а вопрос рабочей нагрузки. В этой статье разберём архитектурные различия, покажем бенчмарки на реальных запросах и определим критерии выбора.

В строковом хранилище (PostgreSQL) данные записываются по строкам:

-- Физическое хранение PostgreSQL (упрощённо)
[id=1, name="Иванов", amount=5000, date="2024-01-15"]
[id=2, name="Петров", amount=3200, date="2024-01-16"]
[id=3, name="Сидоров", amount=7800, date="2024-01-16"]

В колоночном хранилище (ClickHouse) данные группируются по колонкам:

-- Физическое хранение ClickHouse (упрощённо)
id:     [1, 2, 3, ...]
name:   ["Иванов", "Петров", "Сидоров", ...]
amount: [5000, 3200, 7800, ...]
date:   ["2024-01-15", "2024-01-16", "2024-01-16", ...]

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

• Сжатие: однотипные данные в одной колонке сжимаются в 5-20 раз эффективнее (LZ4, ZSTD)
• Vectorized execution: операции применяются к целым блокам значений, используя SIMD-инструкции CPU
• Меньше I/O: читаются только нужные колонки, остальные пропускаются

Устанавливаем ClickHouse на Ubuntu:

sudo apt install -y apt-transport-https ca-certificates curl
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 update
sudo apt install -y clickhouse-server clickhouse-client

sudo systemctl enable --now clickhouse-server

Создаём таблицу для аналитики (движок MergeTree):

CREATE TABLE events (
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    page_url String,
    duration_ms UInt32,
    country LowCardinality(String),
    device LowCardinality(String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_time)
TTL event_date + INTERVAL 365 DAY
SETTINGS index_granularity = 8192;

Ключевые элементы: PARTITION BY для управления хранением (можно удалять старые партиции), ORDER BY определяет физическую сортировку и первичный индекс, TTL — автоматическое удаление устаревших данных.

Тестовый набор: таблица событий с 500 млн строк, 8 колонок. Сервер: 16 ядер, 64 ГБ RAM, NVMe SSD.

*ClickHouse оптимизирован для пакетной вставки (тысячи строк за раз), а не для одиночных INSERT. Рекомендуется вставлять данные батчами по 1000-100000 строк.

Для гибридной архитектуры (OLTP в PostgreSQL, аналитика в ClickHouse) настройте репликацию данных. Самый простой способ — встроенный движок PostgreSQL:

-- Внешняя таблица, ссылающаяся на PostgreSQL
CREATE TABLE pg_orders
    ENGINE = PostgreSQL('pg-server:5432', 'production', 'orders', 'readonly', 'password');

-- Копирование данных в ClickHouse-таблицу
INSERT INTO ch_orders
    SELECT * FROM pg_orders WHERE created_at >= '2024-01-01';

Для потоковой репликации используйте MaterializedPostgreSQL (экспериментальный движок):

CREATE DATABASE pg_replica
ENGINE = MaterializedPostgreSQL('pg-server:5432', 'production', 'replicator', 'password')
SETTINGS materialized_postgresql_tables_list = 'orders,users,events';

Альтернативные инструменты для ETL: Airbyte, dbt (для трансформаций), Debezium + Kafka (CDC). Для однократного импорта больших объёмов эффективен CSV-формат:

# Экспорт из PostgreSQL
psql -h pg-server -U readonly -d production -c \
    "COPY (SELECT * FROM events WHERE date > '2024-01-01') TO STDOUT WITH CSV" \
    | clickhouse-client --query="INSERT INTO events FORMAT CSV"

ClickHouse — не универсальная СУБД. Он не подходит для:

• OLTP-нагрузок: частые UPDATE и DELETE отдельных строк. ClickHouse поддерживает мутации (ALTER TABLE UPDATE/DELETE), но они выполняются асинхронно и перезаписывают целые партиции
• Транзакций: нет поддержки ACID-транзакций в классическом смысле. Нет ROLLBACK
• Ключ-значение: запросы по единственной строке (SELECT * WHERE id = 123) работают медленнее, чем в PostgreSQL
• Малых данных: если в таблице менее 1 млн строк, PostgreSQL с правильными индексами будет не медленнее

Оптимальная архитектура: PostgreSQL для транзакционной работы (заказы, пользователи, состояние), ClickHouse для аналитики (отчёты, дашборды, агрегации). Данные реплицируются из PostgreSQL в ClickHouse с задержкой от секунд до минут.

ClickHouse предоставляет системные таблицы для мониторинга:

-- Текущие запросы
SELECT query, elapsed, read_rows, memory_usage
FROM system.processes;

-- Медленные запросы за последний час
SELECT query, query_duration_ms, read_rows, result_rows
FROM system.query_log
WHERE event_date = today()
    AND query_duration_ms > 5000
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Размер таблиц
SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size,
    sum(rows) AS total_rows,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

Для продакшена настройте max_memory_usage (лимит памяти на запрос), max_execution_time (таймаут) и профили пользователей с ограничениями ресурсов.