Что такое первичный ключ (primary key) в ClickHouse и чем он отличается от primary key в реляционных базах данных?

Primary key в ClickHouse vs реляционные БД

Термин «primary key» в ClickHouse означает совершенно другое, чем в PostgreSQL или MySQL. Понимание этих различий критично для правильного проектирования схемы.

Primary key в реляционных БД (PostgreSQL)

• Уникальность: гарантируется на уровне БД, вставка дубликата — ошибка.
• B-tree индекс: точечный поиск за O(log N), каждая строка адресуема.
• Foreign key reference: основа для ссылочной целостности.
• Физическое хранение: в PostgreSQL heap-таблицы, clustered index только через CLUSTER команду.

Primary key в ClickHouse

• НЕ уникальный: дубликаты строк с одинаковым ключом допустимы и хранятся.
• Sparse index: одна запись в индексе на каждые 8192 строк (granule).
• Цель — range scan: не точечный поиск, а пропуск ненужных блоков данных.
• Физическая сортировка: данные на диске физически отсортированы по primary key.

-- В PostgreSQL: PRIMARY KEY гарантирует уникальность
CREATE TABLE users (
  id      SERIAL PRIMARY KEY,  -- уникально, B-tree, FK-referenceable
  email   TEXT
);
INSERT INTO users VALUES (1, 'a@example.com');
INSERT INTO users VALUES (1, 'b@example.com');  -- ERROR: duplicate key

-- В ClickHouse: PRIMARY KEY это sparse index, дубликаты разрешены
CREATE TABLE users
(
  id    UInt64,
  email String
)
ENGINE = MergeTree()
ORDER BY id;  -- PRIMARY KEY по умолчанию = ORDER BY

INSERT INTO users VALUES (1, 'a@example.com');
INSERT INTO users VALUES (1, 'b@example.com');  -- OK! Обе строки существуют

SELECT * FROM users WHERE id = 1;
-- Вернёт ДВЕ строки!

Явное задание PRIMARY KEY vs ORDER BY

В ClickHouse PRIMARY KEY и ORDER BY — разные понятия. ORDER BY определяет физическую сортировку данных. PRIMARY KEY определяет какие колонки попадают в primary.idx.

-- PRIMARY KEY может быть prefix от ORDER BY
CREATE TABLE events
(
  user_id    UInt64,
  event_date Date,
  event_time DateTime,
  payload    String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date, event_time)
PRIMARY KEY (user_id, event_date);  -- индексируем только первые два поля
-- event_time в сортировке, но не в primary.idx

Файл primary.idx

Индекс хранится в файле primary.idx внутри каждой part. Размер файла: количество гранул × размер одной записи ключа. Файл полностью читается в RAM при каждом запросе с фильтром по ключу.

-- Размер primary.idx по parts
SELECT
  name AS part_name,
  primary_key_bytes_in_memory,
  marks,
  rows
FROM system.parts
WHERE table = 'events' AND active = 1
ORDER BY rows DESC;

Сравнение возможностей

В PostgreSQL: SELECT * FROM users WHERE id = 42 — O(log N) через B-tree, точечный поиск.

В ClickHouse: SELECT * FROM events WHERE user_id = 42 — читает целую гранулу (8192 строк), фильтрует в памяти. Никакого point lookup за O(1) нет.

Подводные камни

• Нет ссылочной целостности: ClickHouse не поддерживает foreign key constraints. Консистентность данных — ответственность приложения.
• Дубликаты неожиданны: разработчики из реляционного мира не ожидают дубликатов по «primary key». Нужно явно использовать ReplacingMergeTree или GROUP BY для дедупликации.
• COUNT(DISTINCT id) != COUNT(*): может быть сюрпризом если в таблице дубликаты из-за ретраев вставки.
• Нельзя использовать ClickHouse как lookup-таблицу: запрос SELECT ... WHERE pk = ? читает целую гранулу — для частых точечных lookup использовать Redis или словари.
• Изменить ORDER BY нельзя без пересоздания: в отличие от DROP INDEX в PostgreSQL, изменение ключа сортировки требует полного пересоздания таблицы и перезаписи всех данных.
• Низкая кардинальность первого ключа: если первая колонка ключа имеет 3 значения, sparse index почти бесполезен — большинство гранул попадут в диапазон.

• Объяснять primary key в clickhouse как OLTP-механику row-store базы вместо аналитической колоночной модели ClickHouse.
• Путать primary key ClickHouse с уникальным constraint из PostgreSQL или MySQL.
• Игнорировать parts, merges, ORDER BY, sparse index и стоимость маленьких вставок.
• Предлагать синтаксис или транзакционное поведение, которого в ClickHouse нет.

• Кандидат объясняет primary key в clickhouse через реальный механизм ClickHouse, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.