# ITMO DB lesson 4 Занятие 4. Колоночные СУБД: ClickHouse — колоночное хранение, MergeTree, предагрегация. Приложение П1–П8 запускается кнопкой Run. 34 slides, by Olga1507Rub. https://easeweb.host/movie/itmo-db-lesson-4-2/1/ ## Slide 1. Колоночные СУБД: ClickHouse (id 258, https://easeweb.host/movie/itmo-db-lesson-4-2/1/) - Университет ИТМО · Школа разработки видеоигр · 11.03.04 ТРКИ - Колоночные СУБД: ClickHouse - Занятие 4. Аналитика игровых событий: колоночное хранение, MergeTree и предагрегация - Блок 1. Хранилища данных · Санеев Святослав Олегович · 2026 ## Slide 2. План занятия (id 259, https://easeweb.host/movie/itmo-db-lesson-4-2/2/) - ЗАНЯТИЕ 4 - План занятия - 1 - Как устроен ClickHouse - Глоссарий, OLTP и OLAP, столбцы на диске, движки, части, партиции, слияния, индекс · слайды 3–11 - 2 - MergeTree на стенде - Таблица событий и ORDER BY, индекс в EXPLAIN, вставка пачками, дубли, изменения и TTL, materialized view, повтор вставки · 12–18 - 3 - ClickHouse в проекте - Доставка данных, метрики, граница с PostgreSQL, таблица решений, кейсы, частые ошибки · слайды 19–24; итоги и вопросы · 25–26 ## Slide 3. Глоссарий: хранение (id 260, https://easeweb.host/movie/itmo-db-lesson-4-2/3/) - 1 · УСТРОЙСТВО · ГЛОССАРИЙ 1/2 - Глоссарий: хранение - ТЕРМИН - ЧТО ЭТО И ЧЕМ ОТЛИЧАЕТСЯ ОТ ОБЫЧНЫХ СУБД - ПРИМЕР НА СТЕНДЕ - Движок таблицы - определяет, как данные пишутся, хранятся и читаются Обычно: один механизм хранения на всю СУБД - events: MergeTree - Часть (part) - неизменяемый набор строк на диске; создаётся вставкой - 202609_1_9_1 - Партиция - группа частей по PARTITION BY: удаление, TTL, перенос - toYYYYMM(ts) → 202609 - Слияние (merge) - фоновое объединение частей одной партиции в крупную - 5 частей → 1 - Ключ сортировки - ORDER BY: физический порядок строк в каждой части - (event_type, день, игрок) - Первичный ключ - разреженный индекс по гранулам; по умолчанию = ORDER BY - 301 запись на часть - Гранула - наименьшая группа строк, которую читает запрос: по умолчанию 8192 - 1 гранула из 612 - Файл засечек - где в сжатом файле столбца начинается каждая гранула Обычно: индекс указывает прямо на строку - event_type.cmrk2 - Термины — по глоссарию документации ClickHouse. Части, партиции, слияния и индекс подробно — на слайдах 8–11. ## Slide 4. Глоссарий: изменения и запросы (id 287, https://easeweb.host/movie/itmo-db-lesson-4-2/4/) - 1 · УСТРОЙСТВО · ГЛОССАРИЙ 2/2 - Глоссарий: изменения и запросы - ТЕРМИН - ЧТО ЭТО И ЧЕМ ОТЛИЧАЕТСЯ ОТ ОБЫЧНЫХ СУБД - ПРИМЕР НА СТЕНДЕ - Атомарность - INSERT виден целиком или никак в пределах блока - один INSERT = блок - Мутация - ALTER … UPDATE / DELETE: асинхронно переписывает части - ALTER TABLE … UPDATE - Удаление - DELETE FROM помечает строки; физически их убирает слияние - _row_exists = 0 - Upsert - нет ON CONFLICT: ReplacingMergeTree и FINAL при чтении - match_results - Дедупликация - убирает версии строк при слиянии и повторные блоки вставки - окно = 100 блоков - FINAL - применяет логику слияния при чтении; дороже по CPU и памяти - SELECT … FINAL - Materialized view - инкрементальное: запрос к каждому новому блоку → таблица - daily_stats_mv - TTL - удалить, перенести или свернуть данные по сроку — при слияниях - ts + INTERVAL 90 DAY - ClickHouse — колоночная СУБД для аналитики (OLAP). Читает много строк, но мало столбцов; пишет пачками; почти не меняет записанное. ## Slide 5. Две нагрузки: OLTP и OLAP (id 261, https://easeweb.host/movie/itmo-db-lesson-4-2/5/) - 1 · УСТРОЙСТВО · НАГРУЗКА - OLTP vs OLAP - OLTP — игровой сервис - OLAP — аналитика ```sql -- покупка: две строки, одна транзакция BEGIN; UPDATE wallets SET gold = gold - 100 WHERE player_id = 1042; INSERT INTO purchases (player_id, item_id, price_paid) VALUES (1042, 'sword_dawn', 100); COMMIT; ``` ```clickhouse -- DAU по режимам за сентябрь SELECT toDate(ts) AS day, mode, uniq(player_id) AS dau FROM events WHERE ts >= '2026-09-01' AND ts < '2026-10-01' GROUP BY day, mode ORDER BY day; ``` - Много коротких запросов, чтение и изменение строк по ключу, FK, CHECK, транзакции. - Мало запросов, каждый читает миллионы строк, но 3 столбца из 30. Данные дописываются. - Кто спрашивает - Сколько данных - Вопрос к слайду - Гейм-дизайнер, аналитик, LiveOps: сколько игроков дошли до 5 уровня, где бросают туториал. - 10 тыс. игроков в день × 2 тыс. событий за сессию = 20 млн строк в сутки. - Почему не посчитать то же в PostgreSQL? ## Slide 6. Строки и столбцы на диске (id 262, https://easeweb.host/movie/itmo-db-lesson-4-2/6/) - 1 · УСТРОЙСТВО · ДИСК - [button "RUN"] -> slide 27 "П1. Сколько весит каждый столбец" - Строки и столбцы на диске - Строковое хранение (PostgreSQL) - Колоночное (ClickHouse, стенд) ```plain страница: [ts|event|player|match|mode|...] [ts|event|player|match|mode|...] [ts|event|player|match|mode|...] SELECT count() FROM events WHERE event_type = 'purchase'; -- читает строки целиком: все столбцы ``` ```plain файл столбца на диске сжатие ts 25.46 MiB ×1.5 player_id 16.44 MiB ×1.2 event_type 23.13 KiB ×211.5 тот же запрос читает один файл: event_type — 23 КиБ из 70 МиБ ``` - Читается нужное - Сжатие - Цена - Запрос по 3 столбцам из 30 читает порядка десятой части таблицы, а не всю. - Рядом лежат значения одного типа. Серии одинаковых значений сжимаются в сотни раз. - Одна строка - дописать во все файлы столбцов. Поэтому пишут пачками, а UPDATE дорог. - Колоночная это о физическом хранении, а не о модели. ## Slide 7. Движки таблиц: какой когда (id 286, https://easeweb.host/movie/itmo-db-lesson-4-2/7/) - 1 · УСТРОЙСТВО · ДВИЖКИ - Движки таблиц: какой когда - ДВИЖОК - ЧТО ДЕЛАЕТ - КОГДА БРАТЬ - MergeTree - части по ORDER BY, индекс, партиции, TTL - события, логи, факты - ReplacingMergeTree - при слиянии оставляет последнюю версию по ключу - версии строк (CDC) - SummingMergeTree - при слиянии складывает числовые столбцы по ключу - счётчики - AggregatingMergeTree - хранит состояния агрегатов (uniqState и др.) - приёмник MV: DAU, квантили по дням - CollapsingMergeTree - строки со знаком +1 и −1 гасят друг друга - состояние: сессия, онлайн - Replicated…MergeTree - те же движки + копии на нескольких серверах - продакшен: отказоустойчивость - Distributed - данных не хранит, рассылает запрос по шардам - кластер из нескольких серверов - Kafka, PostgreSQL, S3 - линки во внешние систему - чтение потоков и прочее - Memory, Log, Null - в памяти · мелкие таблицы · ничего не хранит - тесты, справочники, конвейер MV - MergeTree. Это основа семейства: Replacing, Summing, Aggregating и CollapsingMergeTree тот же MergeTree с особым правилом слияния. ## Slide 8. Часть: что делает INSERT (id 288, https://easeweb.host/movie/itmo-db-lesson-4-2/8/) - 1 · УСТРОЙСТВО · ЧАСТИ - Часть: что делает INSERT - INSERT → часть - Внутри части 202609_1_9_1 ```clickhouse INSERT INTO events SELECT … ; -- 1. отсортировать строки по ORDER BY -- 2. разложить по столбцам -- 3. сжать каждый столбец -- 4. записать новую часть -- одна вставка 5 млн строк на стенде: -- блоки по ~1 млн × 2 месяца = 10 частей ``` ```plain ts.bin 13,1 МБ столбец player_id.bin 8,5 МБ столбец event_type.bin 11,6 КБ столбец event_type.cmrk2 засечки primary.cidx 1,2 КБ индекс minmax_ts.idx min/max ts count.txt checksums.txt columns.txt ``` - ИМЯ - НЕИЗМЕНЯЕМА - САМОДОСТАТОЧНА - 202609_1_9_1 - Не правится - Всё своё - Партиция 202609 · блоки вставок 1–9 · уровень слияния 1. - Новые данные — новая часть. Изменение — новая версия части. - Свой индекс, засечки, min/max и контрольные суммы: читается без общего каталога. - Часть это единица хранения MergeTree. Каждый INSERT создаёт новые части и не трогает старые. Порядок наводят слияния (слайд 10). Стенд, раздел 1; П3. ## Slide 9. Партиции: группы частей (id 289, https://easeweb.host/movie/itmo-db-lesson-4-2/9/) - 1 · УСТРОЙСТВО · ПАРТИЦИИ - Партиции: группы частей - Одна вставка — части по месяцам - Отсечение партиций ```plain PARTITION BY toYYYYMM(ts) -- 5 млн строк за август–сентябрь: 202608_2_2_0 565 159 строк 202608_4_4_0 565 374 … 202609_1_1_0 546 794 202609_3_3_0 546 579 … -- 10 частей, 2 партиции ``` ```clickhouse EXPLAIN indexes = 1 SELECT count() FROM events WHERE ts >= '2026-09-10' AND ts < '2026-09-11'; -- Min-Max Parts 1/2 Granules 301/612 -- PrimaryKey Granules 27/301 ``` - ЗАЧЕМ - ЗАПРОСЫ - СКОЛЬКО - Управление данными - Помогают не всегда - Немного - DROP PARTITION, TTL и перенос на дешёвый диск целыми частями, без переписывания. - Только при фильтре по ключу партиции. Запрос по всем партициям — медленнее. - Слияния только внутри партиции. Документация: до ~1000–10 000 значений. - Партиция используется для управления данными, а не для скорости. Скорость даёт ORDER BY. ## Slide 10. Слияния: из мелких частей в крупные (id 290, https://easeweb.host/movie/itmo-db-lesson-4-2/10/) - 1 · УСТРОЙСТВО · СЛИЯНИЯ - Слияния: из мелких частей в крупные ```plain -- партиция 202609, уровень 0 202609_1_1_0 546 794 строки 202609_3_3_0 546 579 202609_6_6_0 547 282 202609_7_7_0 547 425 202609_9_9_0 271 393 │ ▼ слияние: фоном или OPTIMIZE 202609_1_9_1 2 459 473 строки, уровень 1 -- старые: active = 0, удалятся через 8 мин ``` - Слияние склеивает части одной партиции. 1. Распаковать столбцы частей. 2. Слить уже отсортированные потоки. 3. Построить индекс, сжать, записать новую часть. 4. Подменить атомарно: запрос видит либо старые части, либо новую. - Только внутри партиции - Когда — решает сервер - Здесь работают движки - Части растут примерно до 150 ГБ; каждое слияние — уровень +1. - Фоновые потоки (background_pool_size). Гарантии «одна часть» нет никогда. - Replacing, Summing, Aggregating и TTL срабатывают при слиянии. До него — FINAL или GROUP BY. - Отсюда имя MergeTree: части сливаются в дерево уровней 0 → 1 → 2 … Пишем дёшево, порядок наводим потом. Стенд, раздел 4: 10 частей → 1. ## Slide 11. Разреженный индекс: запись на гранулу (id 291, https://easeweb.host/movie/itmo-db-lesson-4-2/11/) - 1 · УСТРОЙСТВО · ИНДЕКС - Разреженный индекс: запись на гранулу ```clickhouse -- индекс части 202609_1_9_1 SELECT mark_number, event_type, `toDate(ts)`, player_id FROM mergeTreeIndex('lesson04', 'events'); -- 0 damage 2026-09-01 1 -- 1 damage 2026-09-01 16580 -- 180 damage 2026-09-30 98874 -- 181 kill 2026-09-01 37335 -- … 301 гранула, ≈2 КиБ в памяти ``` - Одна запись на гранулу, а не на строку. Гранула — до 8192 строк подряд. Запись индекса — ключ первой строки гранулы. Запрос: индекс → нужные гранулы → засечки → чтение только их. - ГРАНУЛА - ЗАСЕЧКИ - ПОИСК - Единица чтения - Файл засечек - Порядок ключа важен - Ради одной строки читается вся гранула — до 8192 строк нужных столбцов. - event_type.cmrk2: для каждой гранулы — смещение в сжатом event_type.bin. - По первому столбцу — двоичный поиск. По третьему — перебор с исключением, слабее. - B-tree хранил бы 2,46 млн записей — по одной на строку. Разреженный индекс находит диапазон гранул, а не строку: он маленький и не проверяет уникальность. ## Slide 12. Таблица событий на MergeTree (id 265, https://easeweb.host/movie/itmo-db-lesson-4-2/12/) - 2 · MERGETREE · ТАБЛИЦА СОБЫТИЙ - Таблица событий на MergeTree ```clickhouse CREATE TABLE lesson04.events ( ts DateTime64(3, 'UTC'), event_type LowCardinality(String), player_id UInt32, match_id UInt64, mode LowCardinality(String), region LowCardinality(String), damage UInt16 ) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (event_type, toDate(ts), player_id); ``` - ORDER BY главное решение схемы. Он задаёт порядок строк на диске и разреженный индекс. PRIMARY KEY по умолчанию равен ORDER BY и не уникален. - КЛЮЧ - ПАРТИЦИЯ - ТИПЫ - ORDER BY - PARTITION BY месяц - Узкие типы - (тип события, день, игрок): «покупки за день», «урон игрока за день» читают мало гранул. - Для управления: удалить или перенести месяц целиком. Не для скорости и не по player_id. - UInt32 вместо Int64, LowCardinality для строк с сотнями значений, время в UTC. Nullable — по смыслу. ## Slide 13. Индекс на стенде: 1 гранула или 218 (id 266, https://easeweb.host/movie/itmo-db-lesson-4-2/13/) - 2 · MERGETREE · ИНДЕКС - [button "run"] -> slide 28 "П2. Какие гранулы отбросил индекс" - Индекс на стенде: 1 гранула или 218 ```clickhouse EXPLAIN ESTIMATE SELECT count() FROM events WHERE event_type = 'purchase' AND toDate(ts) = '2026-09-15'; -- parts 1 rows 8192 marks 1 EXPLAIN ESTIMATE SELECT count() FROM events WHERE player_id = 1042; -- parts 2 rows 1785856 marks 218 -- в веб-базе + октябрь: parts 3, marks 220 (П2) ``` - EXPLAIN ESTIMATE — сколько прочитает запрос. Без выполнения: части, строки, засечки. Главный способ проверить ORDER BY. Фильтр по префиксу ключа — двоичный поиск; без префикса читается почти всё. - РАБОТАЕТ - ХУЖЕ - ЕСЛИ НУЖНО - Префикс ключа - Третий столбец - Другой путь - Тип события и день: 1 гранула из 612, ответ — 4195 покупок 15 сентября. - Только player_id: 218 гранул, 1,8 млн строк ради 56 событий игрока. - Projection или таблица с ORDER BY (player_id, ts); skip-индекс. Каждый — цена при вставке. - Нет B-tree на строку и нет UNIQUE. Строки с одинаковым ключом норма. Дубли убирает движок при слиянии ## Slide 14. Вставка пачками: сколько частей (id 267, https://easeweb.host/movie/itmo-db-lesson-4-2/14/) - 2 · MERGETREE · ВСТАВКА - [button "run"] -> slide 29 "П3. Части и партиции таблицы" - Вставка пачками: сколько частей - Что видно на стенде - Как вставлять ```clickhouse -- 10 INSERT по строке без слияний -> 10 частей -- + один INSERT на 10 000 строк -> 11 -- в базе уже выполнен OPTIMIZE ... FINAL: SELECT count() FROM system.parts WHERE database = currentDatabase() AND table = 'small_inserts' AND active; -- 1 ``` ```clickhouse -- пачка 10 000–100 000 строк, -- порядка 1 INSERT в секунду INSERT INTO events VALUES (...), (...), ...; -- много мелких клиентов: буфер сервера SET async_insert = 1; -- по умолчанию с 26.3 SET wait_for_async_insert = 1; ``` - ПАЧКА - БУФЕР - ПРЕДЕЛ - Копить в сервисе - async_insert - Too many parts - По размеру или по времени: 10 000–100 000 строк, порядка 1 INSERT в секунду. - Сервер копит мелкие вставки многих клиентов и пишет одной частью. - С 1000 активных частей в партиции INSERT замедляется, с 3000 — отклоняется. - INSERT по одной строке из цикла - ошибка. Копите пачку в сервисе (по размеру или по времени) или включайте async_insert с ожиданием подтверждения. ## Slide 15. ReplacingMergeTree: дубли и исправления (id 268, https://easeweb.host/movie/itmo-db-lesson-4-2/15/) - 2 · MERGETREE · ДУБЛИ - [button "run"] -> slide 30 "П4. Ещё один повтор: FINAL против сырых строк" - ReplacingMergeTree: дубли и исправления - Повтор доставки и исправление - Как читать ```clickhouse CREATE TABLE match_results ( match_id UInt64, player_id UInt32, kills UInt16, version UInt32 ) ENGINE = ReplacingMergeTree(version) ORDER BY (match_id, player_id); -- доставка, её повтор, -- исправление: 2001 -> 5 убийств, version 2 ``` ```clickhouse -- в базе после OPTIMIZE FINAL дубли уже схлопнуты; -- до слияния (П4): без FINAL 49, с FINAL 20 SELECT sum(kills) FROM match_results FINAL; -- 20 SELECT sum(k) FROM ( SELECT argMax(kills, version) AS k FROM match_results GROUP BY match_id, player_id); -- 20 ``` - КЛЮЧ - КОГДА - ЧТЕНИЕ - ORDER BY = дубль - Только при слиянии - FINAL или argMax - Строки с одинаковым (match_id, player_id) схлопнутся, останется наибольшая version. - До слияния дубли видны. Слияния может не быть; между партициями дубли остаются всегда. - FINAL схлопывает при чтении, argMax — явно. Без них — 49 вместо 20 убийств. - ReplacingMergeTree . Подходит для повторной загрузки и исправлений; запрос обязан учитывать дубли. ## Slide 16. Изменение и удаление: дорого и не сразу (id 269, https://easeweb.host/movie/itmo-db-lesson-4-2/16/) - 2 · MERGETREE · ИЗМЕНЕНИЯ - [button "run"] -> slide 33 "П7. Удалённые строки ещё на диске" - Изменение и удаление: дорого и не сразу - Мутация и lightweight DELETE - TTL — удаление по сроку ```clickhouse ALTER TABLE events UPDATE damage = 0 WHERE player_id = 777 SETTINGS mutations_sync = 2; -- ждать DELETE FROM events WHERE player_id = 1042; -- system.mutations: -- UPDATE _row_exists = 0 ... is_done 1 ``` ```clickhouse CREATE TABLE events_ttl ( ts DateTime, player_id UInt32 ) ENGINE = MergeTree ORDER BY (player_id, ts) TTL ts + INTERVAL 90 DAY; -- 2 строки -> OPTIMIZE FINAL -> 1 -- срок проверяется при слиянии ``` - ALTER UPDATE / DELETE - Lightweight DELETE - Lightweight UPDATE - Мутация переписывает части с данными, по умолчанию асинхронно. Для редких массовых правок. - Маска _row_exists: запросы строку не видят, физически её удалит слияние. - UPDATE … SET через патч-части. - Частые UPDATE отдельных строк — признак, что данные не для ClickHouse. Изменяемое состояние живёт в PostgreSQL. ## Slide 17. Materialized view: DAU без сырых событий (id 270, https://easeweb.host/movie/itmo-db-lesson-4-2/17/) - 2 · MERGETREE · ПРЕДАГРЕГАЦИЯ - [button "run"] -> slide 31 "П5. Читаем агрегат MV" - Materialized view: DAU без сырых событий ```clickhouse CREATE TABLE daily_stats ( day Date, mode LowCardinality(String), players AggregateFunction(uniq, UInt32), damage SimpleAggregateFunction(sum, UInt64) ) ENGINE = AggregatingMergeTree ORDER BY (day, mode); CREATE MATERIALIZED VIEW daily_stats_mv TO daily_stats AS SELECT toDate(ts) AS day, mode, uniqState(player_id) AS players, sum(toUInt64(damage)) AS damage FROM events GROUP BY day, mode; ``` - MV — триггер на INSERT. Каждый вставленный в events блок проходит через SELECT; результат дописывается в daily_stats. Старые данные MV не видит - uniqState / uniqMerge - GROUP BY при чтении - Выигрыш - Хранится промежуточное состояние, собирается при чтении: 63 126 игроков 1 октября. - Строки одного дня лежат в разных частях, пока не слились. - 186 строк агрегата вместо 5,1 млн событий: дашборд читает готовые дни (П5). - MV считает только вставленное после создания — и столько раз, сколько вставили. Повтор той же пачки удвоит суммы (следующий слайд). Стенд, раздел 6. ## Slide 18. Повтор вставки и двойной счёт (id 271, https://easeweb.host/movie/itmo-db-lesson-4-2/18/) - 2 · MERGETREE · ПОВТОР ВСТАВКИ - [button "run"] -> slide 32 "П6. Дедупликация сравнивает блоки" - Повтор вставки ```clickhouse -- consumer не получил ответ и повторил INSERT; -- в базе стенда оба INSERT уже выполнены: -- INSERT INTO kills_raw VALUES (888, 1042, 9), (888, 2001, 4); ×2 SELECT sum(kills) FROM kills_total WHERE player_id = 1042; -- 18, а не 9 -- *_dedup: SETTINGS non_replicated_deduplication_window = 100 -- на сырой таблице и на целевой таблице MV, те же два INSERT SELECT sum(kills) FROM kills_total_dedup WHERE player_id = 1042; -- 9 ``` - Доставка «хотя бы раз» означает повторы. Очередь, ретрай HTTP, перезапуск ETL — та же пачка придёт ещё раз. Replicated-таблицы помнят хеши блоков по умолчанию. Обычный MergeTree нет - ЛОВУШКА - ОКНО - НАДЁЖНЕЕ - Хеш блока, не ключ - На обеих таблицах - ID пачки или события - Та же строка в пачке другого состава — новый блок: 9 засчитаются ещё раз (П6). - Сырой таблице и целевой таблице MV — обеим при CREATE. - insert_deduplication_token = ID пачки; или ReplacingMergeTree по ID события. - Суммы в MV не прощают повторов. Проектируйте загрузку идемпотентной: стабильный ID пачки или события, как идемпотентность покупки на занятии 3. Стенд, раздел 7. ## Slide 19. Как данные попадают в ClickHouse (id 272, https://easeweb.host/movie/itmo-db-lesson-4-2/19/) - 3 · В ПРОЕКТЕ · ДОСТАВКА ДАННЫХ - Как данные попадают в ClickHouse - ПАКЕТАМИ - СОБЫТИЯМИ - ИЗМЕНЕНИЯМИ - Batch ETL по расписанию - Сервис -> очередь -> ClickHouse - CDC из PostgreSQL - Раз в N минут: новые строки PostgreSQL (id больше последнего) — INSERT пачкой. Функция postgresql() читает PG прямо из ClickHouse. - Сервис пишет событие в outbox (занятие 3), оттуда в Kafka; движок Kafka + MV или коннектор вставляют пачками. Секунды; нужна идемпотентность. - Логическая репликация -> Debezium или ClickPipes -> ReplacingMergeTree(version) Сложнее всего в эксплуатации. - Источник истины — PostgreSQL и сервисы, ClickHouse — производные данные. Храните сырые события со сроком (TTL), чтобы пересчитать агрегаты после ошибки в MV. ## Slide 20. Метрики игры: функции ClickHouse (id 273, https://easeweb.host/movie/itmo-db-lesson-4-2/20/) - 3 · В ПРОЕКТЕ · МЕТРИКИ - [button "run"] -> slide 34 "П8. Удержание: retention()" - Метрики игры: функции ClickHouse - DAU / MAU - УДЕРЖАНИЕ - РАСПРЕДЕЛЕНИЯ - ВОРОНКА - uniq(player_id) - retention() - quantile(0.95) - windowFunnel() - GROUP BY toDate(ts) Приближённо: ошибка 0,3 % на стенде. uniqExact — точно и дороже по памяти. - retention(день 0, день 1, день 7) по игроку, затем sum: сколько вернулись на D1, D7 (П8). - p95 урона, длительности матча, пинга. Среднее прячет хвост; quantileExact . - Шаги туториал -> матч -> покупка в окне времени: сколько игроков дошли до каждого шага. - Приближённые функции отвечают за долю памяти и времени, для дашбордов этого достаточно. Деньги, награды и всё, что игрок видит как факт, считают точно и в источнике истины. ## Slide 21. PostgreSQL или ClickHouse (id 274, https://easeweb.host/movie/itmo-db-lesson-4-2/21/) - 3 · В ПРОЕКТЕ · ГРАНИЦА - PostgreSQL или ClickHouse - ДАННЫЕ - ГДЕ - ПОЧЕМУ - players, wallets, purchases - PostgreSQL - Деньги и состояние: транзакции, FK, UNIQUE, частые UPDATE - matches, match_participants - PG + копия - сорс: PostgreSQL; в ClickHouse для отчётов - события матча, телеметрия - ClickHouse - Дописываются, миллионы строк, агрегаты по периодам - DAU, удержание, воронки - ClickHouse (MV) - Предагрегация, приближённые функции, дашборды - Довод, который не засчитывается - «ClickHouse, потому что быстрее». При 50 матчах в день PostgreSQL с индексом посчитает DAU за миллисекунды. Нужны объём событий в день, частота и вид запросов, допустимое отставание и измерение: EXPLAIN ANALYZE в PG против EXPLAIN ESTIMATE и времени запроса в ClickHouse. - «Аналитика остаётся в PostgreSQL до N событий в день» . Как на занятии 3: решение плюс измеримое условие пересмотра. ## Slide 22. ClickHouse или нет: таблица решений (id 264, https://easeweb.host/movie/itmo-db-lesson-4-2/22/) - 3 · В ПРОЕКТЕ · РЕШЕНИЕ - ClickHouse или нет: таблица решений - ВОПРОС О ДАННЫХ И ЗАПРОСАХ - ОТВЕТ - КУДА - Данные дописываются и почти не меняются? - да - ClickHouse - Запросы агрегируют миллионы строк по нескольким столбцам? - да - ClickHouse - Отставание данных на секунды или минуты допустимо? - да - ClickHouse - да - PostgreSQL - Нужна транзакция на несколько строк? - Нужны FK, UNIQUE, CHECK как гарантия? - да - PostgreSQL - Частые UPDATE отдельных строк: статус, баланс? - да - PostgreSQL - Отчёт должен видеть изменение сразу после COMMIT? - да - PostgreSQL - Чтение одной строки по ключу за миллисекунды под нагрузкой? - да - PostgreSQL / Redis - Объём и запросы укладываются в PostgreSQL по измерениям? - да - остаться в PostgreSQL - ClickHouse не заменяет PostgreSQL. Это второе хранилище: события и агрегаты. Деньги, инвентарь и состояние игрока остаются в источнике истины. ## Slide 23. ClickHouse в играх и рядом (id 263, https://easeweb.host/movie/itmo-db-lesson-4-2/23/) - 3 · В ПРОЕКТЕ · КЕЙСЫ - ClickHouse в индустрии - [card] ЯНДЕКС · 2009–2016 · Происхождение · Экспериментальный проект Яндекса для отчётов Метрики по неагрегированным данным; с 2016 — открытый код под Apache 2.0. С 2021 развивает ClickHouse Inc. · wikipedia.org → -> https://en.wikipedia.org/wiki/ClickHouse - [card] AZUR GAMES · 2024 · Аналитика мобильных игр · 120 ТБ телеметрии перенесли с 20+ своих серверов ClickHouse в ClickHouse Cloud за три месяца без простоя. Администратор освободил 60 % времени. · aws.amazon.com · блог → -> https://aws.amazon.com/blogs/gametech/azur-games-migrates-all-game-analytics-data-to-clickhouse-cloud-on-aws/ - [card] EXITLAG · 2023 · Сервис для онлайн-игр · Около 6 млн событий в день о поведении игроков и сетевых маршрутах. Ушли с MySQL: аналитика тормозила. MV для предагрегации, дашборды Grafana. · clickhouse.com · блог → -> https://clickhouse.com/blog/boosting-game-performance-exitlag-quest-for-a-better-data-management-system - [card] ROBLOX · Безопасность платформы · «Основной real-time OLAP»: около 100 млн событий безопасности в день. Переход с собственных серверов в ClickHouse Cloud; схему поддерживают MV. · clickhouse.com · доклад → -> https://clickhouse.com/videos/clickhouse-at-roblox-safety - [card] АНАЛОГИ · Тот же класс задач · Apache Druid, Apache Pinot; облачные BigQuery, Snowflake, Redshift; встраиваемая DuckDB. Идеи общие: столбцы, сжатие, предагрегация. · clickhouse.com · docs → -> https://clickhouse.com/docs/concepts/why-clickhouse-is-so-fast - МЕСТО В АРХИТЕКТУРЕ - Рядом, а не вместо - Сервисы пишут в PostgreSQL и отправляют события; ClickHouse отвечает аналитикам и дашбордам с отставанием в секунды или минуты. - → слайды 19, 21 - Общее: поток событий, вопросы «сколько и как распределено» за большие периоды, дашборды. Кейсы — из материалов ClickHouse, AWS и самих компаний, цифры заявлены ими. ## Slide 24. Частые ошибки в проектах (id 275, https://easeweb.host/movie/itmo-db-lesson-4-2/24/) - 3 · В ПРОЕКТЕ · ОШИБКИ - Частые ошибки в проектах - ОШИБКА 1 - ОШИБКА 2 - ОШИБКА 3 - ClickHouse как OLTP - Ждём FK и UNIQUE - INSERT по строке - Статус заказа, баланс, инвентарь с UPDATE на каждое действие. Мутации тяжёлые, транзакций нет. - PRIMARY KEY не уникален, FK нет. «Игрок существует», «одна строка на матч» проверяют загрузка или запрос. - Цикл в сервисе: событие — INSERT. Тысячи частей и too many parts. Пачки или async_insert. - → слайды 16, 22 - → слайды 11, 15 - → слайды 8, 14 - ОШИБКА 4 - ОШИБКА 5 - ОШИБКА 6 - Двойной счёт в MV - PARTITION BY player_id - Nullable и String везде - Повтор пачки после ретрая удваивает sum. Окно дедупликации на обеих таблицах, ID пачки. - Сотни тысяч партиций и частей. Партиция — по времени для управления данными; игрок — в ORDER BY. - Лишняя маска NULL, строки вместо LowCardinality, Int64 «на всякий случай» — лишние байты и время. - → слайд 18 - → слайды 9, 12 - → слайд 12 - Все шесть ошибок видны в DDL и коде загрузки ещё до первого запроса. Проверяйте их на ревью схемы — так же, как FK и CHECK на занятии 2. ## Slide 25. Что забрать с занятия (id 276, https://easeweb.host/movie/itmo-db-lesson-4-2/25/) - ИТОГИ - Что забрать с занятия - Колонки, а не строки - Индекс разреженный, ключ не уникален - Запрос читает только нужные столбцы; сжатие зависит от типа и порядка строк. ORDER BY — главное решение схемы. - Метка на 8192 строки. Дубли норма; Уникальность дают ReplacingMergeTree и FINAL. - Пишем пачками, почти не меняем - Второе хранилище, а не замена - INSERT это часть, слияние фоном. Мутации и DELETE редкие и тяжёлые. - PostgreSQL источник истины. ClickHouse события и агрегаты. - Практика 4. Аналитическое хранилище для проекта - Обязательно на стенде: таблица событий своего проекта с обоснованием ORDER BY и PARTITION BY, загрузка пачкой, EXPLAIN ESTIMATE двух запросов, MV с агрегатом, три метрики и проверка повтора вставки. Решение «аналитика пока в PostgreSQL» допустимо с условием пересмотра. ## Slide 26. Контрольные вопросы (id 277, https://easeweb.host/movie/itmo-db-lesson-4-2/26/) - ПРОВЕРКА СЕБЯ - Контрольные вопросы - 1 - 2 - Почему запрос по 3 столбцам из 30 читает примерно десятую часть данных? Отчего event_type сжался в 211 раз? - Как выбрать ORDER BY для таблицы событий? Почему фильтр только по player_id прочитал 218 гранул, а не одну? - 3 - Чем PARTITION BY отличается от ORDER BY? Почему PARTITION BY player_id — ошибка? - 4 - Что происходит при каждом INSERT? Откуда «too many parts» и как этого избежать? - 5 - Когда ReplacingMergeTree удаляет дубли? Как получить правильную сумму до слияния? - 6 - Почему MV, созданный после загрузки, пуст? Что станет с суммой в MV при повторе вставки? - 7 - 8 - Чем мутация отличается от lightweight DELETE и TTL? Почему частые UPDATE — плохой знак? - Какие данные вашего проекта останутся в PostgreSQL, а какие пойдут в ClickHouse? При каком измеримом условии? - Вопрос 8 — начало практики. Опорные ответы — в конспекте, раздел 16. ## Slide 27. П1. Сколько весит каждый столбец (id 278, https://easeweb.host/movie/itmo-db-lesson-4-2/27/) - ПРИЛОЖЕНИЕ · П1 · СЖАТИЕ · К СЛАЙДУ 6 - [button "← к слайду 6"] -> slide 6 "Строки и столбцы на диске" - П1. Сколько весит каждый столбец ```clickhouse SELECT name, formatReadableSize(data_compressed_bytes) AS disk, round(data_uncompressed_bytes / data_compressed_bytes, 1) AS ratio FROM system.columns WHERE database = currentDatabase() AND table = 'events' ORDER BY data_compressed_bytes DESC; ``` - Что должно получиться ```plain ts 25.46 MiB 1.5 match_id 18.08 MiB 2.1 player_id 16.44 MiB 1.2 damage 4.32 MiB 2.2 mode 2.62 MiB 1.8 region 2.61 MiB 1.8 event_type 23.13 KiB 211.5 ``` - event_type ×211 - player_id ×1,2 - ts — самый тяжёлый - 4 значения, LowCardinality и первое место в ORDER BY: длинные серии одинаковых значений. - Случайные номера внутри дня почти не сжимаются: порядок строк важен не меньше типа. - Миллисекунды DateTime64(3). Хватает секунд — DateTime; для времени есть кодеки Delta, DoubleDelta. - Сжатие зависит от порядка строк. Поменяйте ORDER BY — изменятся размеры столбцов. Байты могут немного отличаться между версиями ClickHouse. ## Slide 28. П2. Какие гранулы отбросил индекс (id 279, https://easeweb.host/movie/itmo-db-lesson-4-2/28/) - ПРИЛОЖЕНИЕ · П2 · ИНДЕКС · К СЛАЙДУ 13 - [button "← к слайду 13"] -> slide 13 "Индекс на стенде: 1 гранула или 218" - П2. Какие гранулы отбросил индекс ```clickhouse EXPLAIN indexes = 1 SELECT count() FROM events WHERE event_type = 'purchase' AND toDate(ts) = '2026-09-15'; EXPLAIN indexes = 1 SELECT count() FROM events WHERE player_id = 1042; ``` - Что должно получиться ```plain -- первый запрос, фрагмент MinMax Parts: 1/3 Granules: 301/624 PrimaryKey Keys: event_type, toDate(ts) Granules: 1/301 -- второй запрос, фрагмент PrimaryKey Keys: player_id Granules: 220/624 ``` - MinMax по ts - Префикс ключа - Третий столбец - По min/max даты в каждой части отброшены август и октябрь — ещё до первичного индекса. - Условие на префикс ключа: поиск по меткам оставляет одну гранулу из 301. - Проверка диапазонов меток: читаются 220 гранул из 624. - В базе три партиции: август, сентябрь и 1 октября. Поэтому гранул 624, а не 612, как на слайде 13 до вставки октября. ## Slide 29. П3. Части и партиции таблицы (id 280, https://easeweb.host/movie/itmo-db-lesson-4-2/29/) - ПРИЛОЖЕНИЕ · П3 · ЧАСТИ · К СЛАЙДУ 14 - [button "← к слайду 14"] -> slide 14 "Вставка пачками: сколько частей" - П3. Части и партиции таблицы ```clickhouse SELECT partition, count() AS parts, sum(rows) AS rows, sum(marks) AS marks FROM system.parts WHERE database = currentDatabase() AND table = 'events' AND active GROUP BY partition ORDER BY partition; ``` - Что должно получиться ```plain partition parts rows marks 202608 1 2540527 312 202609 1 2459473 302 202610 1 100000 13 ``` - Партиция — месяц - Метка ≈ 8192 строки - Октябрь — один INSERT - toYYYYMM(ts): части разных месяцев никогда не сливаются между собой. - 312 меток на 2,54 млн строк. Индекс отбрасывает гранулы между метками, а не строки. - 100 000 строк одной пачкой — одна часть. 100 000 одиночных INSERT дали бы «too many parts». - В рабочей таблице частей больше: слияния идут фоном. Одна часть на партицию здесь — следствие OPTIMIZE FINAL в учебном сценарии. ## Slide 30. П4. Ещё один повтор: FINAL против сырых строк (id 281, https://easeweb.host/movie/itmo-db-lesson-4-2/30/) - ПРИЛОЖЕНИЕ · П4 · ДУБЛИ · К СЛАЙДУ 15 - [button "← к слайду 15"] -> slide 15 "ReplacingMergeTree: дубли и исправления" - П4. Ещё один повтор: FINAL против сырых строк ```clickhouse SYSTEM STOP MERGES match_results; INSERT INTO match_results VALUES (777, 1042, 12, 1); SELECT count() FROM match_results; SELECT count(), sum(kills) FROM match_results FINAL; SYSTEM START MERGES match_results; ``` - Что должно получиться ```plain 4 // без FINAL: дубль виден 3 20 // FINAL: как до повтора ``` - Без FINAL - FINAL - Та же version - 4 строки: повтор лежит в отдельной части, пока она не слилась с остальными. - 3 строки и 20 убийств: дубли по ключу ORDER BY схлопнуты при чтении. - Из строк с равной версией остаётся последняя вставленная; здесь они одинаковы. - STOP MERGES — только для демонстрации. Запрос не должен зависеть от того, было ли слияние: правильный ответ дают FINAL или argMax. ## Slide 31. П5. Читаем агрегат MV (id 282, https://easeweb.host/movie/itmo-db-lesson-4-2/31/) - ПРИЛОЖЕНИЕ · П5 · MATERIALIZED VIEW · К СЛАЙДУ 17 - [button "← к слайду 17"] -> slide 17 "Materialized view: DAU без сырых событий" - П5. Читаем агрегат MV ```clickhouse SELECT day, uniqMerge(players) AS dau, sum(damage) AS damage FROM daily_stats WHERE day >= '2026-09-28' GROUP BY day ORDER BY day; SELECT count() FROM daily_stats; ``` - Что должно получиться ```plain day dau damage 2026-09-28 55983 2691724 2026-09-29 56055 2684592 2026-09-30 55953 2665714 2026-10-01 63126 3264955 186 // строк в агрегате ``` - uniqMerge - GROUP BY обязателен - 186 строк - Состояния uniqState трёх режимов объединяются: игрок двух режимов считается один раз. - Строки одного дня могут лежать в разных частях: без GROUP BY — частичные агрегаты. - 62 дня × 3 режима вместо 5,1 млн событий: дашборд читает в тысячи раз меньше строк. - Сумма DAU по дням ≠ уникальные за неделю. WAU считают uniqMerge по всем дням недели, а не сложением дневных чисел. ## Slide 32. П6. Дедупликация сравнивает блоки (id 283, https://easeweb.host/movie/itmo-db-lesson-4-2/32/) - ПРИЛОЖЕНИЕ · П6 · ПОВТОР ВСТАВКИ · К СЛАЙДУ 18 - [button "← к слайду 18"] -> slide 18 "Повтор вставки и двойной счёт" - П6. Дедупликация сравнивает блоки ```clickhouse INSERT INTO kills_raw_dedup VALUES (888, 1042, 9); -- та же строка, другая пачка SELECT sum(kills) FROM kills_total_dedup WHERE player_id = 1042; INSERT INTO kills_raw_dedup SETTINGS insert_deduplication_token = 'match-889' VALUES (889, 1042, 2); INSERT INTO kills_raw_dedup SETTINGS insert_deduplication_token = 'match-889' VALUES (889, 1042, 2); SELECT sum(kills) FROM kills_total_dedup WHERE player_id = 1042; ``` - Что должно получиться ```plain 18 // другая пачка — новый блок: // 9 + 9 20 // 18 + 2: второй INSERT // с тем же токеном отброшен ``` - Хеш блока - Токен пачки - Откуда ID - Та же строка в пачке другого состава — другой хеш: убийства засчитаны второй раз. - insert_deduplication_token заменяет хеш: повтор с тем же ID отброшен, итог 20, а не 22. - Стабильный при повторе: ID outbox-сообщения, offset в очереди, номер матча и пачки. - Повторный Run П6 ничего не добавит: оба блока уже в окне. Чтобы начать заново, пересоздайте свою базу (сброс в окне результата). ## Slide 33. П7. Удалённые строки ещё на диске (id 284, https://easeweb.host/movie/itmo-db-lesson-4-2/33/) - ПРИЛОЖЕНИЕ · П7 · УДАЛЕНИЕ · К СЛАЙДУ 16 - [button "← к слайду 16"] -> slide 16 "Изменение и удаление: дорого и не сразу" - П7. Удалённые строки ещё на диске ```clickhouse SELECT count() FROM events WHERE player_id = 1042; SELECT count() FROM events WHERE player_id = 1042 SETTINGS apply_deleted_mask = 0; SELECT command, is_done FROM system.mutations WHERE database = currentDatabase() AND table = 'events'; ``` - Что должно получиться ```plain 0 // запросы не видят 56 // байты ещё в частях UPDATE damage = 0 WHERE player_id = 777 1 UPDATE _row_exists = 0 WHERE player_id = 1042 1 ``` - _row_exists - Когда исчезнет - Мутация - Lightweight DELETE записывает маску: строка скрыта от запросов, данные остаются в части. - При слиянии части или после ALTER TABLE … APPLY DELETED MASK. Для удаления по требованию нужен срок. - ALTER … UPDATE переписала damage во всех частях: дорого, асинхронно, чтение не блокирует. - apply_deleted_mask = 0 — отладочная настройка. Она показывает: удаление в ClickHouse — пометка и отложенная перезапись, а не мгновенное стирание. ## Slide 34. П8. Удержание: retention() (id 285, https://easeweb.host/movie/itmo-db-lesson-4-2/34/) - ПРИЛОЖЕНИЕ · П8 · МЕТРИКИ · К СЛАЙДУ 20 - [button "← к слайду 20"] -> slide 20 "Метрики игры: функции ClickHouse" - П8. Удержание: retention() ```clickhouse SELECT sum(r[1]) AS d0, sum(r[2]) AS d1, sum(r[3]) AS d7 FROM ( SELECT player_id, retention( toDate(ts) = '2026-09-01', toDate(ts) = '2026-09-02', toDate(ts) = '2026-09-08') AS r FROM events WHERE toDate(ts) IN ('2026-09-01', '2026-09-02', '2026-09-08') GROUP BY player_id); ``` - Что должно получиться ```plain d0 d1 d7 55860 31192 31366 D1 = 31192 / 55860 ≈ 56 % D7 = 31366 / 55860 ≈ 56 % ``` - retention() - Данные синтетические - Когорта - r[1] — выполнено первое условие; r[i] — первое и i-е вместе. Игрок без дня 0 не считается. - Игроки выбраны случайно, поэтому D1 ≈ D7. В настоящей игре D7 заметно ниже D1. - Здесь d0 — активные 1 сентября. Когорта регистрации требует дату из таблицы игроков в PostgreSQL. - Удержание определяют словами до запроса. Какой день — нулевой, что считается возвратом, по какому часовому поясу: запишите в документации, как схему.