Как устроен ClickHouse: колоночное хранение, MergeTree и OLAP

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

ClickHouse — база данных, которую делали для одной задачи: считать аналитику по огромным объёмам данных очень быстро. Агрегатный запрос по миллиарду строк — секунды на одном сервере. Чтобы понять, откуда такая скорость и где у ClickHouse ограничения, нужно разобраться, как он устроен внутри.

Почему обычные базы медленно считают аналитику

Представьте таблицу заказов с тридцатью колонками: id, дата, покупатель, статус, сумма, адрес, промокод, и так далее. В PostgreSQL каждая строка хранится целиком — все тридцать колонок лежат рядом на диске.

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

На миллионе строк это терпимо. На миллиарде — катастрофа.

Колоночное хранение

ClickHouse хранит данные иначе: все значения одной колонки лежат вместе, в отдельном файле. Все amount — в одном месте, все status — в другом, все даты — в третьем.

Тот же запрос по двум годам читает ровно два файла из тридцати; остальные двадцать восемь даже не открываются.

Второй эффект — сжатие. Файл с колонкой status содержит миллион значений из пяти вариантов («новый», «оплачен», «доставлен», «отменён», «возврат») — такие однородные данные сжимаются куда лучше, чем пёстрые строки с разнородными полями. Типичный коэффициент — 5–20x против 2–3x в строчных базах, и это прямая экономия чтения с диска.

ClickHouse и PostgreSQL: разные задачи

Это не конкуренты: каждый закрывает свой класс запросов.

ClickHouse хорошо дополняет PostgreSQL: основные данные и все изменения живут в PG, а поток событий и историческая аналитика уходят в ClickHouse.

ClickHouse пора заводить, когда GROUP BY по большим таблицам тормозит рабочую базу, а отдельная реплика под отчёты не спасает. Рано — пока данных меньше десяти миллионов строк: PostgreSQL с нормальными индексами справится сам, а второе хранилище придётся кому-то обслуживать.

MergeTree: как ClickHouse хранит данные

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

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

Одно слово в этом DDL стоит расшифровать сразу: LowCardinality(String) — это строка, у которой различных значений мало (у нас это горстка названий событий). ClickHouse хранит такую колонку словарём: сами строки лежат отдельным списком, а в колонке — короткие номера. Экономия и по месту, и по скорости. Подробнее — в статье про моделирование.

Каждая вставка данных создаёт на диске part — неизменяемый отсортированный кусок. Точнее, по куску на каждую партицию, в которую попали строки: один INSERT, размазанный по двенадцати месяцам, породит двенадцать кусков, а не один. Изменить part нельзя. Вместо этого ClickHouse в фоне постепенно сливает мелкие parts в крупные — отсюда и название MergeTree («дерево слияний»).

Из этой механики следуют три важных правила.

Вставлять нужно большими порциями. Тысяча вставок по одной строке — тысяча parts. Фоновые слияния не успевают, число активных кусков в партиции упирается в предел parts_to_throw_insert (по умолчанию 3000), и таблица начинает отвечать ошибкой TOO_MANY_PARTS. Нормальный режим — порции в десятки или сотни тысяч строк раз в несколько секунд. И порция должна ложиться в одну-две партиции: если каждая вставка задевает все, то «большая порция» превращается в россыпь мелких кусков и упирается в тот же предел.

Данные по природе неизменяемы. ALTER TABLE ... UPDATE или DELETE — это мутация: фоновое переписывание целых parts. Для редких операций, например удаления данных пользователя по запросу, это допустимо. Как повседневная практика — разрушительно.

Свежие данные не сразу «причёсаны». Движки, которые дедуплицируют или агрегируют строки, делают это в момент слияния. До него в таблице спокойно сосуществуют несколько версий одной записи.

ORDER BY и как ClickHouse ищет данные

ORDER BY в MergeTree — это не про сортировку результата запроса. Это физический порядок данных внутри part-ов, и это главное архитектурное решение при создании таблицы.

По этому порядку ClickHouse строит разреженный первичный индекс: одна засечка не на каждую строку, а на блок из 8192 строк (это называется гранула). Число 8192 — не магия, а значение настройки index_granularity у таблицы; менять его почти никогда не нужно, но знать, откуда оно берётся, стоит. Когда приходит запрос с фильтром WHERE event_type = 'order_paid', ClickHouse смотрит в индекс и читает только те гранулы, где такие значения могут встречаться — остальное пропускается.

Запрос WHERE order_id = '...' по таблице выше прочитает всю таблицу: order_id не входит в ORDER BY, индекс не помогает, ClickHouse вынужден просмотреть всё.

Ту же механику видно на маленькой программе: колонка из сорока строк, засечка на каждые восемь (в ClickHouse — на каждые 8192) и поиск по засечкам вместо чтения всех строк:

import java.util.ArrayList;
import java.util.List;

public class SparseIndex {
    static final int GRANULE = 8;

    public static void main(String[] args) {
        List<String> column = new ArrayList<>();
        for (int row = 0; row < 40; row++) {
            column.add(row < 12 ? "order_created" : row < 28 ? "order_paid" : "order_shipped");
        }
        List<String> marks = new ArrayList<>();
        for (int row = 0; row < column.size(); row += GRANULE) {
            marks.add(column.get(row));
        }
        System.out.println("засечки: " + marks);

        String wanted = "order_paid";
        int scanned = 0;
        int found = 0;
        for (int g = 0; g < marks.size(); g++) {
            boolean last = g == marks.size() - 1;
            boolean mayContain = marks.get(g).compareTo(wanted) <= 0
                    && (last || marks.get(g + 1).compareTo(wanted) >= 0);
            if (!mayContain) {
                System.out.println("гранула " + g + ": пропускаем, не читая");
                continue;
            }
            System.out.println("гранула " + g + ": читаем " + GRANULE + " строк");
            for (int row = g * GRANULE; row < (g + 1) * GRANULE; row++) {
                scanned++;
                if (column.get(row).equals(wanted)) {
                    found++;
                }
            }
        }
        System.out.println("прочитано строк: " + scanned + " из " + column.size());
        System.out.println("найдено " + wanted + ": " + found);
    }
}

package main

import (
	"fmt"
	"strings"
)

const granule = 8

func main() {
	var column []string
	for row := 0; row < 40; row++ {
		switch {
		case row < 12:
			column = append(column, "order_created")
		case row < 28:
			column = append(column, "order_paid")
		default:
			column = append(column, "order_shipped")
		}
	}
	var marks []string
	for row := 0; row < len(column); row += granule {
		marks = append(marks, column[row])
	}
	fmt.Println("засечки: [" + strings.Join(marks, ", ") + "]")

	wanted := "order_paid"
	scanned, found := 0, 0
	for g := range marks {
		last := g == len(marks)-1
		mayContain := marks[g] <= wanted && (last || marks[g+1] >= wanted)
		if !mayContain {
			fmt.Printf("гранула %d: пропускаем, не читая\n", g)
			continue
		}
		fmt.Printf("гранула %d: читаем %d строк\n", g, granule)
		for row := g * granule; row < (g+1)*granule; row++ {
			scanned++
			if column[row] == wanted {
				found++
			}
		}
	}
	fmt.Printf("прочитано строк: %d из %d\n", scanned, len(column))
	fmt.Printf("найдено %s: %d\n", wanted, found)
}

const GRANULE = 8;
const column = [];
for (let row = 0; row < 40; row++) column.push(row < 12 ? 'order_created' : row < 28 ? 'order_paid' : 'order_shipped');
const marks = [];
for (let row = 0; row < column.length; row += GRANULE) marks.push(column[row]);
console.log('засечки: [' + marks.join(', ') + ']');

const wanted = 'order_paid';
let scanned = 0, found = 0;
for (let g = 0; g < marks.length; g++) {
  const last = g === marks.length - 1;
  const mayContain = marks[g] <= wanted && (last || marks[g + 1] >= wanted);
  if (!mayContain) { console.log(`гранула ${g}: пропускаем, не читая`); continue; }
  console.log(`гранула ${g}: читаем ${GRANULE} строк`);
  for (let row = g * GRANULE; row < (g + 1) * GRANULE; row++) {
    scanned++;
    if (column[row] === wanted) found++;
  }
}
console.log(`прочитано строк: ${scanned} из ${column.length}`);
console.log(`найдено ${wanted}: ${found}`);

GRANULE = 8
column = ["order_created" if row < 12 else "order_paid" if row < 28 else "order_shipped" for row in range(40)]
marks = [column[row] for row in range(0, len(column), GRANULE)]
print("засечки: [" + ", ".join(marks) + "]")

wanted = "order_paid"
scanned = found = 0
for g, mark in enumerate(marks):
    last = g == len(marks) - 1
    may_contain = mark <= wanted and (last or marks[g + 1] >= wanted)
    if not may_contain:
        print(f"гранула {g}: пропускаем, не читая")
        continue
    print(f"гранула {g}: читаем {GRANULE} строк")
    for row in range(g * GRANULE, (g + 1) * GRANULE):
        scanned += 1
        if column[row] == wanted:
            found += 1
print(f"прочитано строк: {scanned} из {len(column)}")
print(f"найдено {wanted}: {found}")

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

Два важных следствия:

Первичный ключ в ClickHouse — не про уникальность. Это навигатор по гранулам. Дубликаты по ключу совершенно законны, уникальность — забота приложения.

Точечный поиск — не сильная сторона ClickHouse. «Найти одну запись по id» минимум прочитает гранулу из 8192 строк, а без попадания в индекс — всю таблицу. За точечными чтениями — в PostgreSQL.

PARTITION BY — второй уровень отсечения. Партиции по месяцам позволяют запросу за май вообще не трогать данные других месяцев. Удалить старые данные тоже просто: DROP PARTITION. Частая ошибка — делать слишком мелкие партиции, например по дням за несколько лет: получается тысячи партиций, и производительность падает. Месяц — разумный стандарт.

Асинхронная вставка: когда пачку собрать негде

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

INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1 VALUES (...);

С этой настройкой сервер не создаёт кусок на каждый запрос: он складывает данные в буфер и записывает их одним куском, когда буфер наполнился или истёк таймаут. Клиент при этом может ждать фактической записи (wait_for_async_insert = 1, надёжнее) или получать подтверждение сразу после попадания в буфер (быстрее, но при падении сервера буфер теряется).

Это и есть штатный ответ на «нельзя писать по одной строке»: писать по одной можно, если сервер собирает их сам. Настройки буфера и оговорки про них — в статье про интеграцию с приложением.

Когда фильтр идёт мимо ключа сортировки

Проблема «WHERE order_id = … прочитает всю таблицу» имеет два штатных решения, и оба дешевле, чем заводить вторую таблицу.

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

ALTER TABLE events ADD INDEX idx_order order_id TYPE bloom_filter GRANULARITY 4;

Типы выбирают по данным: minmax для значений, растущих вместе с ключом сортировки (дата, идентификатор); set(N) для колонок с небольшим числом разных значений; bloom_filter для точечного поиска по колонке с высокой кардинальностью. Выигрыш зависит от того, насколько значения «сгруппированы» на диске: если нужные строки разбросаны по всем блокам, пропускать будет нечего.

Проекция. Это вторая копия тех же данных с другим порядком сортировки, которую сервер поддерживает сам и выбирает автоматически, если она подходит под запрос.

ALTER TABLE events ADD PROJECTION by_order (SELECT * ORDER BY order_id);
ALTER TABLE events MATERIALIZE PROJECTION by_order;

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

Чего в ClickHouse нет

Это полезно знать заранее, а не обнаружить на рабочей системе.

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

Дешёвых UPDATE и DELETE. DELETE FROM лишь помечает строки удалёнными — физически они уходят при очередном слиянии. Для изменения значений остаются мутации, то есть перезапись целых кусков, или специальные движки.

Уникальных ограничений и внешних ключей. Целостность данных — ответственность того, кто их поставляет.

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

Глубже: кодеки сжатиярасширенное

Сжатие в пять-двадцать раз получается само, но его можно улучшить, если знать форму данных. Кодек задают у колонки:

CREATE TABLE metrics (
    ts        DateTime CODEC(DoubleDelta, LZ4),
    device_id UInt32   CODEC(Delta, ZSTD(1)),
    value     Float64  CODEC(Gorilla, LZ4)
) ENGINE = MergeTree ORDER BY (device_id, ts);

LZ4 стоит по умолчанию: быстрый, сжимает умеренно. ZSTD сжимает заметно лучше и стоит дороже по процессору — его берут для холодных данных и для колонок, которые читают редко. Специализированные кодеки работают до основного: Delta хранит разницу с предыдущим значением (хорошо для растущих идентификаторов), DoubleDelta — разницу разниц (идеально для меток времени с равным шагом), Gorilla — для чисел с плавающей точкой, которые меняются мало (показания датчиков).

Правило простое: на временных рядах специализированный кодек плюс LZ4 часто даёт лучший результат, чем один ZSTD, и без потери скорости чтения.

Глубже: ORDER BY, PRIMARY KEY и цена ошибкирасширенное

Две настройки, которые обычно совпадают и потому кажутся одним. ORDER BY задаёт физический порядок строк на диске. PRIMARY KEY задаёт, какие колонки попадут в разреженный индекс, по которому сервер ищет нужные куски. По умолчанию первичный ключ равен ключу сортировки, но его можно задать отдельно — и он обязан быть префиксом ключа сортировки:

ENGINE = MergeTree
ORDER BY (customer_id, created_at, order_id)
PRIMARY KEY (customer_id, created_at)

Зачем так делают: индекс держится в памяти, и лишняя колонка в нём — это память на каждый кусок. Если order_id нужен только для однозначности порядка внутри строки, а для поиска не используется, из первичного ключа его убирают.

И главная цена, которую стоит знать до создания таблицы: изменить ключ сортировки потом нельзя. Разрешено только дописать колонки в хвост (ALTER TABLE … MODIFY ORDER BY, и то с ограничениями). Если порядок выбран неверно, единственный путь — создать новую таблицу с правильным ключом, перелить данные (INSERT INTO new SELECT * FROM old, порциями по партициям) и переименовать таблицы местами (RENAME TABLE old TO old_backup, new TO old). На больших объёмах это часы и требует места под обе копии — поэтому ключ сортировки и называют главным архитектурным решением.

Глубже: специализированные движки таблицрасширенное

Все «специальные» движки — это тот же MergeTree с дополнительной логикой, которая срабатывает в момент слияния parts.

ReplacingMergeTree — при слиянии оставляет только последнюю версию строки с одинаковым ORDER BY-ключом. Удобен, когда нужно хранить «текущее состояние» сущности: вставляется новая версия, старая исчезнет при очередном слиянии. До слияния обе версии существуют одновременно — читать «чисто» можно через модификатор FINAL или функцию argMax.

SummingMergeTree — при слиянии суммирует числовые колонки строк с одинаковым ключом. Подходит для счётчиков и предагрегированных метрик.

AggregatingMergeTree — то же, но для любых агрегатных состояний (uniqState, quantileState). Основа материализованных представлений.

CollapsingMergeTree — «отмена» строки парной записью со знаком −1. Используется в потоках изменений, где нужно вычитание.

Replicated*MergeTree — любой из перечисленных плюс репликация.

Выбор движка — часть схемы. События, которые только добавляются, — просто MergeTree; снимки сущностей с обновлением — ReplacingMergeTree; готовые агрегаты для отчётов — SummingMergeTree или AggregatingMergeTree под материализованным представлением.

Коротко

• ClickHouse хранит данные по колонкам: запрос читает файлы только названных колонок, а однородные значения внутри колонки сжимаются в 5–20 раз против 2–3 в строчных базах.
• Каждая вставка создаёт part; ClickHouse сливает parts в фоне. Вставлять нужно большими порциями — тысяча одиночных INSERT приводит к TOO_MANY_PARTS.
• ORDER BY — физический порядок данных и основа разреженного индекса: фильтр по его полям читает часть гранул, фильтр по остальным — всю таблицу. PARTITION BY отсекает данные вторым уровнем, месяц — разумный размер партиции.
• Первичный ключ в ClickHouse — навигатор по гранулам, не ограничение уникальности.
• ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree — тот же MergeTree с логикой, которая срабатывает в момент слияния, а не сразу после вставки.
• Нет транзакций, нет дешёвых UPDATE/DELETE, нет быстрого точечного поиска — это осознанные компромиссы ради аналитической скорости.
• Писать по одной строке можно, если включить асинхронную вставку: сервер копит буфер сам и создаёт один кусок.
• Фильтр мимо ключа сортировки лечат пропускающими индексами (minmax, set, bloom_filter) или проекцией — второй копией данных с другим порядком, которую сервер выбирает сам.
• Сжатие настраивают кодеками по форме данных: Delta и DoubleDelta для растущих значений и меток времени, Gorilla для показаний датчиков, ZSTD для холодных колонок.
• PRIMARY KEY — префикс ORDER BY и живёт в памяти, а сам ключ сортировки после создания таблицы не меняется: только новая таблица, перелив и переименование.

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

• Моделирование и запросы — ORDER BY на практике, типы, материализованные представления, антипаттерны.
• Интеграция из приложения — драйвер, порции вставки, поток событий из PostgreSQL и Kafka.
• Эксплуатация — репликация, шардинг, TTL, мониторинг.
• PostgreSQL или ClickHouse — критерии, по которым решают, пора ли заводить вторую базу.