ClickHouse для веб-аналитики: materialized views, MergeTree, интеграция с PostgreSQL

Зачем ClickHouse, если есть PostgreSQL

PostgreSQL — отличная база для OLTP: транзакции, ACID, foreign keys, JSONB. Но когда таблица событий перевалила за 50 миллионов строк, а дашборд аналитики стал отвечать за 12 секунд — стало ясно, что нужно что-то другое.

ClickHouse — колоночная СУБД, заточенная под аналитические запросы. Там, где PostgreSQL сканирует все столбцы строки, ClickHouse читает только нужные колонки. Разница — порядки магнитуды на агрегациях.

Я подключил ClickHouse к production-проекту как вторую базу: PostgreSQL остался для основных данных, ClickHouse — для аналитики, логов и метрик. Вот как это выглядит на практике.

Архитектура: PostgreSQL + ClickHouse

Почему не «или-или», а «и-и»

ClickHouse не заменяет PostgreSQL. У него нет транзакций, UPDATE работает через мутации (асинхронно), DELETE — тоже. Для CRUD-операций он не подходит. Зато для аналитики — идеален.

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

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

Приложение пишет основные данные в PostgreSQL. Фоновая задача (Sidekiq/SolidQueue) переливает события и метрики в ClickHouse. Дашборды читают из ClickHouse.

Что хранить в ClickHouse

Правило простое: если данные пишутся часто, читаются агрегированно и не требуют UPDATE — это для ClickHouse.

Установка и настройка

Установка на Ubuntu

# Добавить репозиторий ClickHouse
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

# Установить сервер и клиент
sudo apt-get install -y clickhouse-server clickhouse-client

# Запустить
sudo systemctl start clickhouse-server
sudo systemctl enable clickhouse-server

# Проверить
clickhouse-client --query "SELECT version()"
# 24.8.x

Docker (для разработки)

# docker-compose.yml
services:
  clickhouse:
    image: clickhouse/clickhouse-server:24.8-alpine
    ports:
      - "8123:8123"   # HTTP-интерфейс
      - "9000:9000"   # Native-протокол
    volumes:
      - clickhouse_data:/var/lib/clickhouse
      - ./clickhouse/config.xml:/etc/clickhouse-server/config.d/custom.xml
    environment:
      CLICKHOUSE_DB: analytics
      CLICKHOUSE_USER: app
      CLICKHOUSE_PASSWORD: secure_password
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  clickhouse_data:

Базовая конфигурация

<!-- /etc/clickhouse-server/config.d/custom.xml -->
<clickhouse>
    <max_concurrent_queries>100</max_concurrent_queries>
    <max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>

    <!-- Сжатие данных -->
    <compression>
        <case>
            <min_part_size>10000000000</min_part_size>
            <min_part_size_ratio>0.01</min_part_size_ratio>
            <method>zstd</method>
            <level>3</level>
        </case>
    </compression>

    <!-- Логирование запросов -->
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>
</clickhouse>

Движки таблиц: MergeTree и его семейство

MergeTree — основной движок

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;

Разберём по частям:

• PARTITION BY toYYYYMM(event_date) — данные разбиваются по месяцам. Запрос за конкретный месяц читает только одну партицию, а не всю таблицу
• ORDER BY (eventdate, userid, session_id) — физическая сортировка данных на диске. Запросы по этим колонкам используют sparse index и работают мгновенно
• TTL event_date + INTERVAL 12 MONTH — автоматическое удаление данных старше года. Не нужно руками чистить
• index_granularity = 8192 — каждые 8192 строк создаётся запись в primary index. Баланс между размером индекса и точностью поиска

ReplacingMergeTree — для дедупликации

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 при слиянии частей оставляет только строку с максимальным version для каждого уникального ключа (ORDER BY). Это решает проблему дублирования при повторной вставке.

Важный нюанс: дедупликация происходит при фоновом merge, а не при INSERT. Пока merge не произошёл, дубликаты видны. Для гарантированной дедупликации при чтении — используйте FINAL:

-- Гарантированная дедупликация
SELECT * FROM user_sessions FINAL WHERE user_id = 12345;

-- Или через subquery (быстрее на больших объёмах)
SELECT * FROM user_sessions
WHERE (session_id, version) IN (
    SELECT session_id, max(version)
    FROM user_sessions
    GROUP BY session_id
);

AggregatingMergeTree — предагрегация

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

AggregatingMergeTree хранит промежуточные состояния агрегатных функций. При merge — автоматически объединяет их. Результат: запросы к агрегированным данным работают мгновенно, даже на миллиардах исходных событий.

SummingMergeTree — для счётчиков

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);

При merge строки с одинаковым ключом суммируются. Идеально для счётчиков: просто вставляйте (now(), '/api/users', 1, 0, 45) при каждом запросе — ClickHouse сам сагрегирует.

Типы данных: правильный выбор экономит в 10 раз

Числовые типы

-- Плохо: всё через UInt64
user_id UInt64,        -- 8 байт
age UInt64,            -- 8 байт (max 255 — зачем 8 байт?)
is_active UInt64,      -- 8 байт (0 или 1 — зачем 8 байт?)

-- Хорошо: подходящие типы
user_id UInt64,        -- 8 байт — ID могут быть большими
age UInt8,             -- 1 байт — хватает до 255
is_active UInt8,       -- 1 байт — boolean через UInt8
http_status UInt16,    -- 2 байта — коды 100-599
price Decimal64(2),    -- 8 байт — точная арифметика для денег

В колоночной базе каждый сэкономленный байт на значении умножается на количество строк. 100M строк × 7 сэкономленных байт = 700MB меньше на диске и в памяти.

Строковые типы

-- FixedString для данных фиксированной длины
country_code FixedString(2),     -- 'US', 'RU', 'DE' — всегда 2 байта
currency FixedString(3),          -- 'USD', 'EUR', 'RUB'
ip_v4 IPv4,                       -- 4 байта вместо ~15 для строки

-- LowCardinality для повторяющихся значений
browser LowCardinality(String),   -- 'Chrome', 'Firefox', 'Safari' — словарное сжатие
os LowCardinality(String),        -- 'Windows', 'macOS', 'Linux'
device_type LowCardinality(String),

-- Enum для известного набора значений
status Enum8('active' = 1, 'inactive' = 2, 'banned' = 3),

LowCardinality — один из самых мощных приёмов. Для колонки с browser ClickHouse создаёт словарь: {1: 'Chrome', 2: 'Firefox', 3: 'Safari'} — и хранит только числовые ID. На 100M строк это экономия в 5-10 раз по сравнению с обычным String.

Даты и время

event_date Date,                -- 2 байта — только дата
event_time DateTime,            -- 4 байта — секундная точность
event_time_ms DateTime64(3),    -- 8 байт — миллисекундная точность
event_time_us DateTime64(6),    -- 8 байт — микросекундная точность

Используйте Date для партиционирования и фильтрации по дням, DateTime для обычных временных меток, DateTime64(3) только когда нужны миллисекунды (performance monitoring).

Materialized Views — автоматическая агрегация

Концепция

Materialized View в ClickHouse — это триггер на INSERT. Когда данные поступают в исходную таблицу, materialized view автоматически трансформирует и вставляет их в целевую таблицу.

-- Исходная таблица: сырые события
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);

-- Целевая таблица: агрегированные метрики по часам
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: автоматическая агрегация
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;

Теперь при INSERT INTO raw_events данные автоматически агрегируются в hourly_page_stats. Запрос к агрегированной таблице — миллисекунды вместо секунд.

Каскадные Materialized Views

-- Уровень 1: почасовая агрегация (из raw_events)
-- (уже создана выше)

-- Уровень 2: дневная агрегация (из 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;

-- Уровень 3: месячная агрегация (из daily_page_stats)
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;

Три уровня агрегации: час → день → месяц. Каждый следующий уровень читает из предыдущего. Запрос «сколько просмотров за последний год» обращается к monthly_page_stats — 12 строк вместо 500 миллионов.

Воронка конверсий через Materialized View

CREATE TABLE conversion_funnel
(
    date Date,
    funnel_step LowCardinality(String),
    user_count UInt64,
    step_order UInt8
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, step_order);

CREATE MATERIALIZED VIEW mv_conversion_funnel
TO conversion_funnel
AS
SELECT
    toDate(event_time) AS date,
    event_type AS funnel_step,
    uniqExact(user_id) AS user_count,
    CASE event_type
        WHEN 'page_view' THEN 1
        WHEN 'add_to_cart' THEN 2
        WHEN 'checkout_start' THEN 3
        WHEN 'payment_complete' THEN 4
    END AS step_order
FROM raw_events
WHERE event_type IN ('page_view', 'add_to_cart', 'checkout_start', 'payment_complete')
GROUP BY date, funnel_step, step_order;

Интеграция с Rails

Гем clickhouse-activerecord

# 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
    debug: false

# 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

# app/models/analytics/daily_metric.rb
module Analytics
  class DailyMetric < ClickhouseRecord
    self.table_name = 'daily_page_stats'
  end
end

Прямой клиент (для сложных запросов)

# 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

  def execute(sql)
    response = @connection.post('/', sql, {
      '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?
    true
  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: перенос данных из PostgreSQL в ClickHouse

# 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,
          session_id: event.session_id || '',
          page: event.page || '',
          referrer: event.referrer || '',
          device_type: event.device_type || 'desktop',
          country_code: event.country_code || 'XX',
          browser: event.browser || 'Unknown',
          duration_ms: event.duration_ms || 0,
        }
      end

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

      Rails.logger.info("[ClickHouse Sync] Synced #{batch.size} events, last_id=#{batch.last.id}")
    end
  end
end

# config/recurring.yml (SolidQueue)
sync_events_to_clickhouse:
  class: SyncEventsToClickhouseJob
  schedule: every 5 minutes

Запросы: от простых к сложным

Базовые аналитические запросы

-- Топ-10 страниц за последние 7 дней
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;

-- Трафик по часам за сегодня
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;

-- Распределение по устройствам
SELECT
    device_type,
    count() AS count,
    round(count() * 100.0 / sum(count()) OVER (), 2) AS percentage
FROM raw_events
WHERE event_date >= today() - 30
GROUP BY device_type
ORDER BY count DESC;

Когортный анализ

-- Когорты по неделе регистрации: retention по неделям
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,
    count(DISTINCT IF(
        toMonday(e.event_time) = c.cohort_week + INTERVAL 4 WEEK,
        e.user_id, NULL
    )) AS week_4
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;

Воронка конверсий с windowFunnel

-- Встроенная функция воронки — уникальная фича 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 — одна из killer-фич ClickHouse. Она считает, до какого шага воронки дошёл каждый пользователь, с учётом временного окна (86400 секунд = 24 часа). Один запрос вместо серии JOIN-ов.

Скользящее среднее и тренды

-- 7-дневное скользящее среднее просмотров
SELECT
    date,
    views,
    round(avg(views) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )) AS moving_avg_7d,
    round(views * 100.0 / lagInFrame(views, 7) OVER (ORDER BY date) - 100, 1) AS wow_change_pct
FROM daily_page_stats
WHERE page = '/'
  AND date >= today() - 60
ORDER BY date;

Анализ путей пользователей (sessionization)

-- Самые частые последовательности из 3 страниц
SELECT
    path,
    count() AS frequency
FROM (
    SELECT
        user_id,
        session_id,
        arrayStringConcat(
            groupArray(page) OVER (
                PARTITION BY user_id, session_id
                ORDER BY event_time
                ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
            ),
            ' → '
        ) AS path
    FROM raw_events
    WHERE event_type = 'page_view'
      AND event_date >= today() - 7
)
WHERE length(splitByString(' → ', path)) = 3
GROUP BY path
ORDER BY frequency DESC
LIMIT 20;

Оптимизация производительности

Правильный ORDER BY

ORDER BY в ClickHouse — не про сортировку результатов, а про физическое расположение данных на диске. Это primary key.

-- Плохо: ORDER BY по высококардинальному полю первым
ORDER BY (user_id, event_date)
-- Запрос WHERE event_date = '2026-05-01' сканирует ВСЕ данные

-- Хорошо: низкокардинальные поля первыми
ORDER BY (event_date, user_id)
-- Запрос WHERE event_date = '2026-05-01' читает только один гранул

Правило: в ORDER BY первыми идут поля, по которым чаще всего фильтруете. Обычно это дата.

Skip-индексы

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 — для строк с высокой кардинальностью (URL, email). False positive ~1%
• set — для колонок с ограниченным набором значений (страна, браузер). Хранит множество уникальных значений в грануле
• minmax — для числовых полей. Хранит min/max по грануле — если искомое значение вне диапазона, гранула пропускается

Проекции

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;

Проекция — это альтернативный физический порядок данных. Основная таблица отсортирована по (event_date, user_id), проекция — по (user_id, event_time). Запросы WHERE user_id = X автоматически используют проекцию.

Мониторинг запросов

-- Самые медленные запросы за последний час
SELECT
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Использование дискового пространства по таблицам
SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS rows,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

Сравнение: ClickHouse vs PostgreSQL на реальных запросах

Все тесты на таблице с 100M строк, одна машина (4 ядра, 16GB RAM).

Разница не в процентах — в порядках магнитуды. На агрегационных запросах по большим объёмам ClickHouse быстрее в 100-200 раз.

Причины:

• Колоночное хранение: запрос SELECT count(*) WHERE event_date = X читает только колонку event_date (~2 байта на строку), а не все 20 колонок
• Векторное выполнение: обработка данных SIMD-инструкциями, пачками по 8192 строк
• Сжатие: колонки с повторяющимися данными сжимаются в 10-20 раз (LZ4/ZSTD)
• Sparse index: вместо B-tree индекса — sparse index с шагом 8192. На 100M строк это ~12K записей в индексе

Потоковая вставка и буферизация

Buffer-таблица для высокой частоты записи

-- Основная таблица
CREATE TABLE events_main (...)
ENGINE = MergeTree() ...;

-- Буферная таблица
CREATE TABLE events_buffer AS events_main
ENGINE = Buffer(
    analytics,          -- database
    events_main,        -- destination table
    16,                 -- num_layers
    10, 100,            -- min/max time (seconds)
    10000, 1000000,     -- min/max rows
    10000000, 100000000 -- min/max bytes
);

Приложение пишет в events_buffer. ClickHouse накапливает данные в памяти и периодически сбрасывает в events_main. Это снимает нагрузку от высокочастотных мелких INSERT-ов.

Batch INSERT через асинхронные вставки

-- Настройка на уровне сервера
SET async_insert = 1;
SET wait_for_async_insert = 0;  -- не ждать подтверждения
SET async_insert_max_data_size = 10000000;  -- 10MB буфер
SET async_insert_busy_timeout_ms = 5000;     -- сброс каждые 5 секунд

С async_insert ClickHouse сам буферизирует мелкие вставки и объединяет их в батчи. Клиент отправляет по одной строке — ClickHouse вставляет пачками. Идеально для трекинга событий.

Репликация и отказоустойчивость

ReplicatedMergeTree + ZooKeeper

CREATE TABLE page_views_replicated ON CLUSTER '{cluster}'
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    page String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/page_views', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

Для production с высокой доступностью — минимум 3 ноды ClickHouse + 3 ноды ZooKeeper (или ClickHouse Keeper — встроенная замена). Данные автоматически реплицируются, при падении ноды — read/write продолжают работать.

Для небольших проектов (до 1TB данных, до 10K запросов/сек) — одна нода ClickHouse достаточна. Бэкапы через clickhouse-backup.

Бэкап и восстановление

# Установить clickhouse-backup
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/

# Создать бэкап
clickhouse-backup create daily_backup_$(date +%Y%m%d)

# Список бэкапов
clickhouse-backup list

# Восстановить
clickhouse-backup restore daily_backup_20260527

# Загрузить в S3
clickhouse-backup upload daily_backup_20260527

# Автоматический ежедневный бэкап (cron)
# 0 3 * * * /usr/local/bin/clickhouse-backup create auto_$(date +\%Y\%m\%d) && /usr/local/bin/clickhouse-backup upload auto_$(date +\%Y\%m\%d)

Мониторинг ClickHouse

Prometheus + Grafana

# prometheus.yml
scrape_configs:
  - job_name: 'clickhouse'
    static_configs:
      - targets: ['localhost:9363']
    metrics_path: '/metrics'

ClickHouse из коробки отдаёт метрики в формате Prometheus на порту 9363. Ключевые метрики:

# Запросы
ClickHouseProfileEvents_Query                    # всего запросов
ClickHouseProfileEvents_SelectQuery              # SELECT-запросы
ClickHouseProfileEvents_InsertQuery              # INSERT-запросы
ClickHouseProfileEvents_FailedQuery              # ошибки

# Производительность
ClickHouseProfileEvents_RealTimeMicroseconds     # время выполнения
ClickHouseProfileEvents_ReadRows                 # прочитано строк
ClickHouseProfileEvents_ReadBytes                # прочитано байт

# Ресурсы
ClickHouseMetrics_MemoryTracking                 # использование памяти
ClickHouseMetrics_BackgroundMergesAndMutationsPoolTask  # фоновые merge
ClickHouseAsyncMetrics_MaxPartCountForPartition  # здоровье партиций

Системные таблицы для диагностики

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

-- Здоровье merge
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  -- слишком много частей — merge не успевает
ORDER BY parts DESC;

-- Ошибки за последний час
SELECT
    type,
    event_time,
    exception_code,
    exception,
    query
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC;

Итоговый чеклист внедрения ClickHouse

Перед стартом

• [ ] Определить, какие данные переносить: логи, события, метрики — а не транзакционные данные
• [ ] Оценить объём: < 10M строк/мес — PostgreSQL справится, > 100M — ClickHouse оправдан
• [ ] Выбрать способ интеграции: гем clickhouse-activerecord или прямой HTTP-клиент
• [ ] Спроектировать схему с правильными типами: LowCardinality, Enum, FixedString

Проектирование таблиц

• [ ] ORDER BY: низкокардинальные поля первыми (дата, тип события)
• [ ] PARTITION BY: toYYYYMM(date) для большинства случаев
• [ ] TTL: автоудаление старых данных (12 месяцев для событий, 36 для агрегатов)
• [ ] Движок: MergeTree для append-only, ReplacingMergeTree для дедупликации, SummingMergeTree для счётчиков

Materialized Views

• [ ] Создать MV для каждого уровня агрегации: час → день → месяц
• [ ] Проверить что MV работает: вставить тестовые данные и запросить целевую таблицу
• [ ] Каскадные MV: проверить что данные проходят через все уровни

ETL и синхронизация

• [ ] Фоновая задача для переноса данных из PostgreSQL
• [ ] Идемпотентность: при повторном запуске не создаются дубликаты
• [ ] Мониторинг лага: разница между последним событием в PostgreSQL и ClickHouse

Production

• [ ] Бэкапы: ежедневный clickhouse-backup + upload в S3
• [ ] Мониторинг: Prometheus + Grafana dashboard для ClickHouse
• [ ] Алерты: too many parts, high memory usage, failed queries
• [ ] Тестирование: нагрузочное тестирование с реальным объёмом данных
• [ ] Документация: схема данных, ETL pipeline, ключевые запросы