ClickHouse: моделирование схемы, запросы, materialized views

← назад к разделу

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

Порядок сортировки — главное решение

Когда ClickHouse записывает данные, он сортирует строки внутри каждой части по колонкам из ORDER BY и строит разреженный индекс. При запросе он читает не строки по одной, а блоки по 8 192 строки (гранулы). Если первые колонки ORDER BY совпадают с условием WHERE, движок пропускает ненужные гранулы целиком.

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

CREATE TABLE order_events (
    event_time   DateTime,
    event_type   LowCardinality(String),
    region       LowCardinality(String),
    customer_id  UInt64,
    order_id     UUID,
    amount       Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, event_type, event_time);

Запрос «по региону за период» использует префикс ключа и читает минимум гранул. Если поставить ORDER BY (event_time, region, event_type), фильтр по типу события перестаёт отсекать гранулы: внутри каждого временного диапазона все типы вперемешку.

У правила есть обратная сторона, и её стоит держать в голове. Чем меньше уникальных значений у первой колонки, тем шире полоса гранул, которую занимает каждое из них. Пока по этой колонке фильтруют — всё хорошо, отсекается почти всё лишнее. А вот запрос, в котором фильтра по ней нет («сколько всего событий за вчера»), отсечь по ней ничего не сможет и прочитает всю партицию: диапазон-то у каждого значения широкий. Поэтому «малая кардинальность в начале» работает только вместе со второй половиной правила — «по которым фильтруют всегда». Колонка, по которой фильтруют редко, в начале ключа только мешает.

Дополнительный бонус: одинаковые значения, стоящие рядом после сортировки, жмутся кодеками лучше: удачный ключ ускоряет чтение и уменьшает объём на диске.

Если порядок сортировки выбран неверно

Менять его на живой таблице нельзя — разрешено только дописать колонки в хвост. Рабочий путь один, и он планируется как отдельная операция.

-- 1. новая таблица с правильным ключом
CREATE TABLE orders_v2 AS orders ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at) ORDER BY (customer_id, created_at);

-- 2. перелив по партициям, а не всё разом
INSERT INTO orders_v2 SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
-- ... по месяцу за раз

-- 3. сверка
SELECT count(), sum(total_amount) FROM orders  WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
SELECT count(), sum(total_amount) FROM orders_v2 WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- 4. переименование местами
RENAME TABLE orders TO orders_old, orders_v2 TO orders;

Три обязательных условия. Место: во время перелива данные лежат в двух копиях. Порции: перелив одной командой на большой таблице упрётся в память и время выполнения, поэтому идут по партициям. И запись: пока идёт перелив, новые данные продолжают приходить в старую таблицу — поэтому либо переливают до «вчера», а последний кусок догоняют после переименования, либо на время переключения пишут в обе таблицы.

Пределы памяти на агрегации

Совет «берите uniq вместо uniqExact» имеет числовую причину: группировка держит состояние в памяти, и у него есть предел. По умолчанию запрос, превысивший max_memory_usage (порядка нескольких гигабайт на запрос), отменяется с ошибкой о нехватке памяти.

Два способа не упираться. Первый — дешёвые по памяти функции: uniq (приблизительный подсчёт уникальных, память почти не растёт) вместо uniqExact (хранит все значения), quantile вместо quantileExact. Второй — разрешить выгрузку на диск: max_bytes_before_external_group_by задаёт порог, после которого группировка частями уходит на диск, и запрос выполняется медленнее, но выполняется. Обычно его ставят примерно в половину лимита памяти запроса, чтобы у сервера остался запас на слияние.

Отдельно стоит помнить про кардинальность: GROUP BY по колонке с миллионами разных значений — это миллионы состояний в памяти; такие разрезы либо не делают в интерактивных запросах, либо считают заранее материализованным представлением.

Типы данных: где экономят место и время

Каждая колонка в ClickHouse хранится отдельным файлом, и её размер напрямую влияет на скорость сканирования. Поэтому тип — не формальность.

• LowCardinality(String) — словарное кодирование для колонок с сотнями или тысячами уникальных значений: статусы, типы событий, регионы, валюты. Меньше места, быстрее GROUP BY. При миллионах уникальных значений словарь раздувается и становится вредным.
• Decimal(18, 2) — для денег. Float64 даёт погрешности при арифметике, как и в любой другой базе.
• DateTime vs DateTime64(3) — секундная точность против миллисекундной. DateTime64 занимает вдвое больше места; его берут только когда миллисекунды действительно важны.
• UInt8/16/32/64 — беззнаковые целые вместо bigint на всё. Чем меньше тип — тем быстрее сканирование.
• Enum8('created' = 1, 'paid' = 2, 'shipped' = 3) — компактнее и жёстче LowCardinality, но добавить новое значение — это ALTER. Подходит для закрытых наборов, которые редко меняются.
• Nullable(T) — создаёт отдельный файл-маску для каждой колонки, и по умолчанию такую колонку не пускают в ключ сортировки. Формально это не запрет, а настройка allow_nullable_key, но включать её не стоит: пропуски в ключе портят и отсечение гранул, и предсказуемость запросов. По умолчанию лучше обходиться дефолтами (0, ''); Nullable — только когда «нет значения» семантически отличается от нуля или пустой строки.
• Array(T), Map(K, V) — легальная денормализация: теги, произвольные атрибуты события. Вместе с функциями arrayJoin, has, mapKeys закрывают большинство случаев «гибкой схемы» без отдельных таблиц.

Агрегатные запросы: идиомы, которых нет в PostgreSQL

ClickHouse предназначен для агрегирования. Несколько встроенных функций делают запросы короче и быстрее по сравнению с обычным SQL:

SELECT
    toStartOfMonth(event_time)            AS month,
    count()                               AS events,
    countIf(event_type = 'order_paid')    AS paid_orders,
    sumIf(amount, event_type = 'order_paid') AS revenue,
    uniq(customer_id)                     AS customers,
    quantile(0.95)(amount)                AS p95_check
FROM order_events
WHERE event_time >= '2026-01-01'
GROUP BY month
ORDER BY month;

• countIf / sumIf / avgIf — условные агрегаты вместо CASE WHEN внутри агрегата. Читаются лучше и работают быстрее.
• uniq vs uniqExact — uniq считает приближённо и на порядки дешевле по памяти и процессору. Точного числа погрешности документация для него не называет: внутри собственный алгоритм с выборкой хешей, и ошибка зависит от того, сколько уникальных значений в данных. Порядок величины даёт родственная uniqHLL12 на чистом HyperLogLog — у неё на наборах от десяти тысяч до сотни миллионов уникальных погрешность не выше примерно 1,6 %, а вот на маленьких наборах доходит до 10 %: приближённые счётчики тем хуже, чем меньше данных. Если нужен именно классический HyperLogLog, его дают uniqCombined и uniqHLL12. uniqExact отвечает точно, но дорого. Для дашбордов почти всегда достаточно uniq.
• quantile / quantileExact — та же пара для перцентилей.
• argMax(col, ts) — возвращает значение col из строки с максимальным ts. Удобно для «последнего статуса заказа» без оконных функций и self-join.

Materialized views: предагрегация на лету

В PostgreSQL materialized view — это снимок результата запроса, который обновляется по команде. В ClickHouse всё иначе: materialized view работает как триггер на вставку. Каждый INSERT в исходную таблицу прогоняется через запрос view и дописывается в целевую таблицу прямо во время записи.

Стандартная связка — с AggregatingMergeTree:

CREATE TABLE revenue_by_region_daily (
    day      Date,
    region   LowCardinality(String),
    revenue  AggregateFunction(sum, Decimal(18, 2)),
    orders   AggregateFunction(uniq, UUID)
)
ENGINE = AggregatingMergeTree
ORDER BY (region, day);

CREATE MATERIALIZED VIEW revenue_by_region_daily_mv
TO revenue_by_region_daily AS
SELECT
    toDate(event_time)        AS day,
    region,
    sumState(amount)          AS revenue,
    uniqState(order_id)       AS orders
FROM order_events
WHERE event_type = 'order_paid'
GROUP BY day, region;

Чтение — через -Merge-функции:

SELECT day, region, sumMerge(revenue) AS revenue, uniqMerge(orders) AS orders
FROM revenue_by_region_daily
GROUP BY day, region;

Почему sumState/uniqState, а не просто sum/uniq? Они хранят промежуточное состояние агрегата, а не готовое число. Это значит, что предагрегат по дням корректно доагрегируется в месяцы и годы. Для uniq это принципиально: сложить два готовых числа нельзя — нужны исходные структуры. Дашборд читает таблицу в тысячи строк вместо сырых миллиардов.

Разницу видно на средней сумме заказа. Сложить дневные средние и поделить нельзя: день с двумя заказами весит столько же, сколько день с тысячей. Состояние хранит пару «сумма и количество» — из неё ответ верный всегда:

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class StateMergeDemo {
    record Event(String day, String type, long amount) {}

    record State(long sum, long count) {
        State plus(State other) {
            return new State(sum + other.sum(), count + other.count());
        }
    }

    public static void main(String[] args) {
        List<Event> events = List.of(
                new Event("2026-01-05", "order_paid", 120),
                new Event("2026-01-05", "order_paid", 50),
                new Event("2026-01-05", "order_refund", 40),
                new Event("2026-01-06", "order_paid", 80));

        Map<String, State> daily = new LinkedHashMap<>();
        for (Event e : events) {
            if (!e.type().equals("order_paid")) {
                continue;
            }
            daily.merge(e.day(), new State(e.amount(), 1), State::plus);
        }
        daily.forEach((day, s) ->
                System.out.println("состояние " + day + ": sum=" + s.sum() + " count=" + s.count()));

        double avgOfAvg = daily.values().stream()
                .mapToDouble(s -> (double) s.sum() / s.count())
                .average().orElse(0);
        State merged = daily.values().stream().reduce(new State(0, 0), State::plus);

        System.out.println("среднее по средним: " + round2(avgOfAvg));
        System.out.println("слияние состояний:  " + round2((double) merged.sum() / merged.count()));
    }

    static double round2(double value) {
        return Math.round(value * 100) / 100.0;
    }
}

package main

import (
	"fmt"
	"math"
)

type event struct {
	day, kind string
	amount    int64
}

type state struct{ sum, count int64 }

func (s state) plus(other state) state { return state{s.sum + other.sum, s.count + other.count} }

func round2(value float64) float64 { return math.Round(value*100) / 100 }

func main() {
	events := []event{
		{"2026-01-05", "order_paid", 120},
		{"2026-01-05", "order_paid", 50},
		{"2026-01-05", "order_refund", 40},
		{"2026-01-06", "order_paid", 80},
	}

	var days []string
	daily := map[string]state{}
	for _, e := range events {
		if e.kind != "order_paid" {
			continue
		}
		if _, ok := daily[e.day]; !ok {
			days = append(days, e.day)
		}
		daily[e.day] = daily[e.day].plus(state{e.amount, 1})
	}
	for _, day := range days {
		fmt.Printf("состояние %s: sum=%d count=%d\n", day, daily[day].sum, daily[day].count)
	}

	avgOfAvg, merged := 0.0, state{}
	for _, day := range days {
		s := daily[day]
		avgOfAvg += float64(s.sum) / float64(s.count)
		merged = merged.plus(s)
	}
	avgOfAvg /= float64(len(days))

	fmt.Println("среднее по средним:", round2(avgOfAvg))
	fmt.Println("слияние состояний: ", round2(float64(merged.sum)/float64(merged.count)))
}

const events = [
  { day: '2026-01-05', type: 'order_paid', amount: 120 },
  { day: '2026-01-05', type: 'order_paid', amount: 50 },
  { day: '2026-01-05', type: 'order_refund', amount: 40 },
  { day: '2026-01-06', type: 'order_paid', amount: 80 },
];
const plus = (a, b) => ({ sum: a.sum + b.sum, count: a.count + b.count });
const round2 = (value) => Math.round(value * 100) / 100;

const daily = new Map();
for (const e of events) {
  if (e.type !== 'order_paid') continue;
  daily.set(e.day, plus(daily.get(e.day) ?? { sum: 0, count: 0 }, { sum: e.amount, count: 1 }));
}
for (const [day, s] of daily) console.log(`состояние ${day}: sum=${s.sum} count=${s.count}`);

const states = [...daily.values()];
const avgOfAvg = states.reduce((acc, s) => acc + s.sum / s.count, 0) / states.length;
const merged = states.reduce(plus, { sum: 0, count: 0 });

console.log('среднее по средним: ' + round2(avgOfAvg));
console.log('слияние состояний:  ' + round2(merged.sum / merged.count));

from dataclasses import dataclass


@dataclass(frozen=True)
class Event:
    day: str
    type: str
    amount: int


@dataclass(frozen=True)
class State:
    sum: int
    count: int

    def plus(self, other: "State") -> "State":
        return State(self.sum + other.sum, self.count + other.count)


events = [Event("2026-01-05", "order_paid", 120), Event("2026-01-05", "order_paid", 50),
          Event("2026-01-05", "order_refund", 40), Event("2026-01-06", "order_paid", 80)]

daily: dict[str, State] = {}
for e in events:
    if e.type != "order_paid":
        continue
    daily[e.day] = daily.get(e.day, State(0, 0)).plus(State(e.amount, 1))
for day, s in daily.items():
    print(f"состояние {day}: sum={s.sum} count={s.count}")

avg_of_avg = sum(s.sum / s.count for s in daily.values()) / len(daily)
merged = State(0, 0)
for s in daily.values():
    merged = merged.plus(s)

print(f"среднее по средним: {round(avg_of_avg, 2)}")
print(f"слияние состояний:  {round(merged.sum / merged.count, 2)}")

82.5 против 83.33 — это и есть цена готового числа вместо состояния.

Два важных ограничения. Во-первых, MV срабатывает только на новые вставки — данные, вставленные до создания view, в агрегат не попадут. Засыпку делают отдельным INSERT ... SELECT. Во-вторых, ошибка в каскаде view может завалить INSERT в исходную таблицу. Каскады глубже одного-двух уровней быстро становятся неотлаживаемыми.

И важная оговорка про надёжность, из-за которой агрегаты иногда расходятся с фактами. Вставка в исходную таблицу и запись в целевую таблицу представления происходят не атомарно. Если представление упало на своём запросе (ошибка приведения типа, переполнение), поведение зависит от настройки: по умолчанию ошибка представления заваливает всю вставку (данные не попадут никуда), а при включённой настройке игнорирования ошибок представлений блок уйдёт в исходную таблицу, но не попадёт в агрегат — и расхождение придётся чинить пересчётом.

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

ReplacingMergeTree: как хранить «текущее состояние»

ClickHouse — append-only система. Чтобы обновить строку, нужно вставить новую версию. ReplacingMergeTree при слиянии частей оставляет только строку с максимальным значением указанного поля:

CREATE TABLE orders_latest (
    order_id    UUID,
    status      LowCardinality(String),
    amount      Decimal(18, 2),
    updated_at  DateTime64(3)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY order_id;

Каждое изменение заказа — это новая вставка с той же order_id. При слиянии останется строка с максимальным updated_at. Проблема в том, что слияние случается «когда-нибудь», и до его завершения дубликаты видны в запросах. Честное чтение — одно из двух:

-- Короткий вариант: сливает версии прямо во время чтения
SELECT * FROM orders_latest FINAL WHERE status = 'paid';

-- Альтернатива: предсказуемая стоимость на больших таблицах
SELECT order_id, argMax(status, updated_at) AS status, argMax(amount, updated_at) AS amount
FROM orders_latest
GROUP BY order_id;

FINAL удобен, но на больших таблицах дорого обходится. Вариант с argMax многословнее, но предсказуемее по нагрузке. В обоих случаях это сознательная плата за «обновляемость» в append-only мире.

Отдельно про удаление. Само по себе ReplacingMergeTree умеет только «новая версия вытесняет старую», а «строки больше нет» так не выразить — поэтому у движка есть второй необязательный параметр, колонка-признак удаления:

CREATE TABLE customers (
    id         UInt64,
    name       String,
    updated_at DateTime,
    is_deleted UInt8 DEFAULT 0
) ENGINE = ReplacingMergeTree(updated_at, is_deleted)
ORDER BY id;

Удаление превращается во вставку строки с тем же ключом, свежей меткой времени и единицей в признаке. При слиянии остаётся последняя версия, а строки с признаком удаления убираются совсем — при условии, что включена настройка очистки удалённых строк (clean_deleted_rows) либо используется FINAL при чтении, который такие строки не показывает.

Без этой колонки схема «текущее состояние» удалять не умеет вовсе, и это первое, обо что спотыкаются при переносе данных из основной базы: обновления приезжают, а удаления теряются.

JOIN и словари

JOIN в ClickHouse есть, но с важной особенностью: по умолчанию правая таблица целиком загружается в память. Другие способы соединения ClickHouse умеет (их выбирают настройкой join_algorithm, и часть из них умеет сбрасывать данные на диск), но по умолчанию действует именно этот. Соединить таблицу фактов со справочником регионов — нормально. Соединить две таблицы событий по миллиарду строк — нехватка памяти.

Как обходятся:

• Денормализация при записи — главный приём. Название категории, регион, тариф кладут прямо в строку события на этапе загрузки. Дополнительное место на диске дешевле, чем дорогие JOIN-ы при чтении.
• Словари (dictionary) — справочники, которые ClickHouse сам подгружает из PostgreSQL, файла или HTTP-источника и обновляет по расписанию. Обращение — через функцию dictGet('regions_dict', 'name', region_id) без JOIN вовсе.
• Если JOIN неизбежен — маленькая таблица справа, фильтры применяются до JOIN, а не после.

Частые ошибки

Глубже: фильтр мимо ORDER BY: пропускающие индексы и проекциирасширенное

Дважды в разделе ставится проблема: ORDER BY (site_id, event_time), а запрос фильтрует по user_id, и ClickHouse читает всё. Первичный ключ один, второй не сделать. Штатных ответа два, и оба не индексы в смысле PostgreSQL.

Пропускающий индекс (data skipping index) не ищет строки, а разрешает не читать целые блоки гранул. Для каждого блока из GRANULARITY гранул он хранит сводку по колонке, и по ней видно, что нужного значения в блоке точно нет. Виды сводок: minmax хранит минимум и максимум и работает для колонок, коррелирующих с порядком сортировки (время создания при сортировке по времени события); set(N) хранит до N различных значений и подходит колонкам с малым числом значений в блоке; bloom_filter отвечает «точно нет» или «может быть» для точечного поиска по колонке с высокой кардинальностью, как раз user_id; tokenbf_v1 и ngrambf_v1 то же для слов и подстрок в тексте.

ALTER TABLE events ADD INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 4;
ALTER TABLE events MATERIALIZE INDEX idx_user;

MATERIALIZE строит индекс по уже лежащим данным, новые куски получают его при вставке. Сработал ли он, показывает EXPLAIN indexes = 1: там видно, сколько гранул отобрал первичный ключ и сколько отбросил пропускающий. Он помогает, когда значение редкое и сосредоточено в немногих блоках; если user_id встречается в каждом блоке (крупный клиент), фильтр Блума ничего не отбросит, и чтение останется полным.

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

ALTER TABLE events ADD PROJECTION by_user (SELECT * ORDER BY user_id, event_time);
ALTER TABLE events MATERIALIZE PROJECTION by_user;

После этого WHERE user_id = ? читает по первичному ключу проекции, как будто таблица отсортирована по нему. Цена: данные хранятся дважды, вставка и слияние делают двойную работу, а мутации и DELETE на таблицах с проекциями ограничены. Проекция с агрегацией (SELECT site_id, count() GROUP BY site_id) заменяет материализованное представление для одного запроса, и в отличие от него не требует менять запросы: планировщик подставит её сам.

Что выбрать. Редкий точечный поиск по колонке с высокой кардинальностью: пропускающий индекс, он почти бесплатен. Частый запрос с другим порядком, ради которого читают много: проекция, если запись выдержит удвоение, иначе отдельная таблица через материализованное представление, как в разделе выше. И всегда сначала EXPLAIN: половина «медленных фильтров» лечится добавлением условия по первой колонке ключа, которое в запросе просто забыли.

Коротко

• Схема проектируется от запросов: сначала список аналитических вопросов, потом ORDER BY — в начало ключа колонки с малой кардинальностью (регион, тип), в конец время.
• Типы решают: LowCardinality(String) экономит место и ускоряет GROUP BY на сотнях уникальных значений, а Nullable добавляет файл на каждую колонку — по умолчанию дефолты.
• countIf/sumIf/argMax — основные идиомы агрегатных запросов.
• Materialized view — триггер на вставку, а не снимок: срабатывает только на новые данные, старые засыпают отдельным INSERT ... SELECT.
• sumState/uniqState хранят промежуточное состояние, поэтому предагрегат по дням корректно доагрегируется в месяцы; готовое число считает sumMerge при чтении.
• ReplacingMergeTree откладывает дедупликацию до слияния (честное чтение через FINAL или argMax) и умеет удалять только с колонкой-признаком вторым параметром — без неё удаления из источника теряются; JOIN грузит правую таблицу в память, вместо него денормализация при записи и словари.
• Фильтр мимо ORDER BY: пропускающий индекс (bloom_filter для точечного поиска, minmax для коррелирующих колонок) отбрасывает блоки, проекция хранит копию с другим порядком; проверять EXPLAIN indexes = 1.
• Неверный ключ сортировки чинится только новой таблицей: перелив по партициям, сверка, RENAME местами — с запасом места и планом на запись во время переезда.
• Агрегации ограничены памятью запроса: приблизительные функции (uniq, quantile) или выгрузка группировки на диск через max_bytes_before_external_group_by.
• Вставка в таблицу и запись в материализованное представление не атомарны: ошибка представления либо заваливает вставку, либо оставляет агрегат неполным — поэтому логику держат простой и сверяют по закрытым периодам.

Что почитать дальше

• Fundamentals — как работают части и разреженный индекс под капотом.
• Интеграция с сервисом — как наполнять эти таблицы из приложения на примере Java; в Go официальный clickhouse-go, в Node @clickhouse/client, в Python clickhouse-connect, правило одно: вставлять пачками, а не по строке.
• Эксплуатация — TTL, репликация, мониторинг слияний и мутаций.
• PostgreSQL или ClickHouse — когда вторая база нужна.