Аналітичні БД
Навіщо це
Section titled “Навіщо це”Керівництво «Крамниці» просить два звіти: виручку за місяцями й категоріями та конверсію переглядів у додавання в кошик
за пристроями. На розмірі full (2 млн замовлень, 3.6 млн позицій, 8.1 млн подій; див. Датасет) PostgreSQL 18.6
виконує перший звіт за 3.2 с. Другий іде 9.3 с.
Індекс тут не допоможе: звітам потрібні майже всі рядки. ClickHouse дає ті самі результати за 0.32 с і 0.17 с, DuckDB за 0.24 с і 0.41 с.
Передумови. OLTP і OLAP та таблиця orders_big: модуль 1. Рядкове й колонкове зберігання:
модуль 7. BRIN: модуль 8. HashAggregate, паралельні плани, скидання на диск:
модуль 9. Матеріалізовані представлення: модуль 4. Хмарні сервіси для тих самих
задач (BigQuery, Athena): модуль 14 курсу «Хмарні технології».
Чому одна база не робить обидва добре
Section titled “Чому одна база не робить обидва добре”Транзакційні запити беруть кілька рядків за ключем. Аналітичні проходять мільйони рядків і читають кілька колонок.
У модулі 1 звіт за місяцями на orders_big читав 24 тисячі сторінок заради трьох колонок із дев’яти. Модуль 7 порахував
те саме для view_events на medium: колонка product_id займає 2.4 МБ із 55.6 МБ таблиці, тож рядкове зберігання
читає в 22.8 раза більше, ніж потрібно.
Звідси причини тримати аналітику окремо. Індекс прискорює вибірку кількох рядків і марний, коли потрібні 90 % таблиці,
а кожен індекс робить запис дорожчим (модуль 8). Довге сканування займає диск, якого чекають транзакції, і частково витісняє
з кешу гарячі сторінки (кільце буферів із модуля 7 цей ефект стримує). Довгий запит ще й тримає знімок, який не дає
VACUUM чистити версії рядків (модуль 10).
Колонкова система платить за це з іншого боку. Вставка рядка зачіпає файли кожної колонки, тому ClickHouse приймає дані великими пачками, а зміна окремого рядка переписує шматки цілком.
Колонкове зберігання
Section titled “Колонкове зберігання”У колонковому зберіганні (columnar storage) значення однієї колонки йдуть підряд у власному файлі або фрагменті
файлу. Запит читає лише потрібні колонки, і на це не треба індексів. На full таблиця view_events у PostgreSQL важить
736 МБ (з первинним ключем 919 МБ), а запит про пристрої читає одну колонку, device, із вісьмох.
Другий виграш у однорідності. Поруч лежать значення одного типу з малою кількістю різних, а таке стискається набагато краще, ніж змішаний рядок. Сучасного вигляду ідея набула в C-Store і MonetDB/X100 (обидві 2005 року); огляд дають Абаді зі співавторами.
Ціна: зібрати рядок цілком означає прочитати вісім місць, а вставка й зміна окремого рядка дорогі. Тож модель підходить аналітиці: дані дописують пачками й агрегують.
Стиснення: RLE, словник, дельта
Section titled “Стиснення: RLE, словник, дельта”Три прийоми, на яких тримається стиснення колонок.
- RLE (run-length encoding) замінює серію однакових значень парою «значення, скільки разів». Працює, коли колонка відсортована або має довгі серії.
- Словникове кодування зберігає короткий перелік різних значень і замість кожного значення пише його номер. Для
event_typeз трьома значеннями це один байт замість рядка. - Дельта-кодування зберігає різницю з попереднім значенням, а не саме значення. Для зростаючого
event_idрізниця завжди 1, і її можна упакувати в один біт.
У ClickHouse на full кожна колонка view_events (8.1 млн рядків, таблиця відсортована за
toDate(occurred_at), event_type, product_id) стискається так:
колонка тип нестиснений стиснений разів session_id UUID 130232096 100419162 1.3 customer_id Nullable(UInt64) 73255554 23133138 3.2 event_id UInt64 65116048 32687827 2.0 product_id UInt32 32558024 20880647 1.6 occurred_at DateTime('UTC') 32558024 32377986 1.0 referrer LowCardinality(Nullable(String)) 8166274 5352942 1.5 event_type LowCardinality(String) 8166115 67422 121.1 device LowCardinality(String) 8166103 4094494 2.0event_type стискається в 121 раз: три значення, а в межах дня й типу таблиця має суцільні серії. device має лише
три значення, але розкидані в довільному порядку, тож виграш удвічі менший. Випадковий session_id майже не
стискається, а occurred_at із типовим LZ4 не стискається зовсім: різниці між сусідніми значеннями не використано.
Вплив порядку й кодеків перевірено на medium: ті самі дані, різні ORDER BY і кодеки:
колонка ORDER BY occurred_at ORDER BY event_type, ORDER BY occurred_at, (стандартний кодек) occurred_at кодеки Delta і DoubleDelta + ZSTD event_type 211105 Б 2985 Б 211105 Б event_id 2443078 Б 2449126 Б 236364 Б occurred_at 2450312 Б 2450388 Б 803330 БСортування за event_type стискає цю колонку в 205 разів, а на інших колонках нічого не змінює. Кодеки Delta для
event_id і DoubleDelta для часу стискають їх у 10 і 3 рази. DuckDB обирає схему сам для кожного сегмента
(pragma_storage_info): у view_events на medium для event_type, device, referrer і session_id це словник,
для чисел і часу бітове пакування.
Векторизоване виконання
Section titled “Векторизоване виконання”Виконавець PostgreSQL бере з вузла плану один рядок, обробляє його і просить наступний (модуль 9). Кожен виклик має свою ціну, і на мільйонах рядків вона перевищує вартість самої арифметики.
Векторизоване виконання передає між вузлами пачку з тисяч значень однієї колонки: DuckDB обробляє вектори по 2048 значень, ClickHouse блоки за замовчуванням по 65 409 рядків. Цикл усередині пачки простий, дані лежать у кеші процесора, а команди SIMD обробляють кілька значень за раз.
Вплив розміру пачки можна виміряти в ClickHouse, де він налаштовується: сума quantity * unit_price по order_items
на full (3.6 млн рядків, один потік):
SELECT sum(quantity * unit_price) FROM shop_full.order_itemsSETTINGS max_block_size = 1, max_threads = 1; max_block_size час, с 1 17.358 16 1.111 128 0.170 1024 0.067 8192 0.027 65409 0.023Блок з одного рядка повторює виконання «по одному рядку», і запит іде в 750 разів довше. Вже на 1024 рядках накладні витрати майже зникають.
Мін-макс індекси
Section titled “Мін-макс індекси”Мін-макс індекс (zone map) зберігає для кожного шматка колонки найменше й найбільше значення. Запит з умовою на цю колонку не читає шматків, діапазон яких умову не перетинає. Модуль 8 описав те саме для BRIN (мінімум і максимум на 128 сторінок); у колонкових системах такі індекси вбудовано.
DuckDB веде їх сам для кожної колонки на кожну групу рядків (122 880 рядків). На full запит про тиждень по таблиці,
вставленій у часовому порядку, використав до 0.003 с процесорного часу. По копії з випадково перемішаними рядками
пішло 0.032–0.036 с: діапазони перемішаної копії перетинають будь-який тиждень, тож пропускати нічого. Така сама
статистика лежить у футері файлу Parquet (розділ нижче).
ClickHouse має інший пристрій: розріджений первинний індекс (sparse primary index). Таблиця MergeTree фізично
впорядкована за ключем ORDER BY, а для кожної гранули (8192 рядки) в пам’яті зберігається один запис: значення ключа
її першого рядка. Запит з умовою на початок ключа бінарним пошуком знаходить потрібні гранули. Індекс не унікальний:
рядки з однаковим ключем не конфліктують.
Схеми «зірка» і «сніжинка»
Section titled “Схеми «зірка» і «сніжинка»”Аналітичні дані розкладають на таблиці фактів і таблиці вимірів. Факт — подія, яку вимірюють: рядок замовлення з кількістю й ціною. Вимір — довідник, за яким дивляться на факти: товар, час, покупець.
У схемі «зірка» (star schema) факти стоять у центрі, а кожен вимір пов’язано з ними одним ключем. У «сніжинці» (snowflake schema) вимір нормалізовано далі: товар посилається на категорію, покупець на населений пункт, а той на область.
Спершу визначають гранулярність: що означає один рядок фактів. Тут це рядок замовлення (order_id, line_no), і будь-який
звіт стає сумою фактів, згрупованою за колонкою виміру. Ціна unit_price лежить у фактах, а не береться з
products.price, бо це ціна на момент покупки: довідник міняється, а факт лишається. Якщо звіт має показувати й адресу
покупця на день замовлення, довідник доводиться версіонувати.
Сніжинка економить місце, зірка економить JOIN. Звіти й так з’єднують факти з довідниками, тож виміри часто
денормалізують, а то й переносять у широку таблицю подій, як device і referrer у view_events. Як і в модулі 5,
це свідоме рішення: дані пишуться раз і не змінюються.
DuckDB і ClickHouse
Section titled “DuckDB і ClickHouse”DuckDB — вбудована аналітична база: бібліотека в процесі застосунку, як SQLite для аналітики, без сервера. Читає CSV і
Parquet прямо з файлів, без завантаження, а таблиці зберігає в одному файлі. Той самий звіт (листопад 2025, п’ять
найбільших категорій) у PostgreSQL (PGlite) і DuckDB (WASM) одним текстом: timezone(зона, значення) і множення на
0.01 працюють в обох.
category | orders | revenue-----------------------+--------+------------ (без категорії) | 75 | 2632499.66 Портативні колонки | 333 | 833201.35 Ноутбуки для роботи | 17 | 745558.85 Кухонна техніка | 225 | 439643.75 Ноутбуки для навчання | 17 | 338389.50Два зауваження до цього виводу (пісочниця працює на small). Товари без категорії дають найбільший рядок, 2.6 млн
проти 0.8 млн у наступного; INNER JOIN з categories їх мовчки втратив би.
Зовнішній round потрібен через типи: у пісочниці DuckDB гроші мають тип DOUBLE (типи він визначає за вмістом файлів),
і без нього сума виглядала б як 2632499.6599999997. Для DECIMAL DuckDB ще й ділить інакше: discount_pct / 100
дає DOUBLE, тому вище стоїть множення на 0.01. На medium виручка за 1 жовтня 2025, порахована через / 100,
дала 772624.16 проти 772624.17 у PostgreSQL.
ClickHouse — серверна колонкова база для аналітики подій. Таблиця MergeTree складається зі шматків (parts): кожна
вставка пише новий впорядкований шматок, а фонові злиття зливають їх у більші. Звідси вимога вставляти пачками. Для
view_events еталонна таблиця лабораторної така:
CREATE TABLE shop.view_events ( event_id UInt64, occurred_at DateTime('UTC'), session_id UUID, customer_id Nullable(UInt64), product_id UInt32, event_type LowCardinality(String), device LowCardinality(String), referrer LowCardinality(Nullable(String))) ENGINE = MergeTreeORDER BY (toDate(occurred_at), event_type, product_id);ORDER BY задає і порядок рядків на диску, і розріджений індекс. Перший у ключі день, бо запити за період найчастіші;
далі event_type, чиї серії добре стискаються. PARTITION BY toYYYYMM(occurred_at) додатково розкладає таблицю за
місяцями: запит за період пропускає цілі партиції, а стару партицію видаляють одним DROP PARTITION. Дані
завантажують зі stdin через клієнт:
docker compose exec -T clickhouse clickhouse-client --user shop --password shop \ --query "INSERT INTO shop.view_events SETTINGS date_time_input_format = 'best_effort' FORMAT CSVWithNames" \ < dataset/medium/view_events.csvbest_effort потрібне, бо в CSV час має суфікс +00. Дію ключа видно в EXPLAIN indexes = 1 для тижня
24–30 листопада 2025 на medium:
EXPLAIN indexes = 1SELECT count() FROM shop.view_eventsWHERE occurred_at >= '2025-11-24 00:00:00' AND occurred_at < '2025-12-01 00:00:00'; ReadFromMergeTree (shop.view_events) Indexes: PrimaryKey Keys: toDate(occurred_at) Condition: and((toDate(occurred_at) in (-Inf, 20423]), (toDate(occurred_at) in [20416, +Inf))) Parts: 1/1 Granules: 2/75 Search Algorithm: binary searchПрочитано 2 гранули з 75. З ORDER BY tuple() секції Indexes немає, і читаються всі 610 011 рядків. Те саме показує
rows_read у відповіді клієнта: 16 384 проти 610 011.
Ще дві можливості. Матеріалізоване представлення тут не знімок, який оновлюють командою (як у модулі 4), а тригер на вставку: кожна нова пачка агрегується й дописується в цільову таблицю.
CREATE TABLE shop.daily_events (day Date, event_type LowCardinality(String), events UInt64)ENGINE = SummingMergeTree ORDER BY (day, event_type);CREATE MATERIALIZED VIEW shop.mv_daily TO shop.daily_events ASSELECT toDate(occurred_at) AS day, event_type, count() AS events FROM shop.view_events GROUP BY day, event_type;Після вставки подій до 10 січня 2024 у цільовій таблиці з’явилося 29 рядків, і SummingMergeTree додає значення
рядків з однаковим ключем під час злиття. Пастка: день тут за UTC, бо toDate без зони.
Приблизні агрегати обмінюють точність на швидкість: uniq(session_id) на 8.1 млн подій (full) дав 2 901 199 проти
точних 2 882 664 від uniqExact (похибка 0.64 %) за 37 мс проти 159 мс. Для фінансового звіту беріть uniqExact,
для панелі uniq; count(DISTINCT …) у ClickHouse виконується як uniqExact.
Parquet, Iceberg і lakehouse
Section titled “Parquet, Iceberg і lakehouse”Parquet — колонковий файловий формат, яким обмінюються аналітичні системи. Файл ділиться на групи рядків (row groups),
у кожній колонка лежить окремим стисненим шматком, а футер містить схему й мінімум та максимум кожного шматка. Тому
запит читає з файла лише потрібні колонки й пропускає групи за статистикою, як мін-макс індекс. view_events з small
у пісочниці:
col | compression | encodings | compressed | uncompressed-------------+-------------+------------------+------------+-------------- session_id | ZSTD | PLAIN | 500597 | 2428752 event_id | ZSTD | PLAIN | 96337 | 485775 occurred_at | ZSTD | PLAIN | 254719 | 485775 product_id | ZSTD | PLAIN_DICTIONARY | 91694 | 117351 customer_id | ZSTD | PLAIN_DICTIONARY | 44447 | 69327 referrer | ZSTD | PLAIN_DICTIONARY | 17781 | 27887 event_type | ZSTD | PLAIN_DICTIONARY | 7028 | 15488 device | ZSTD | PLAIN_DICTIONARY | 8420 | 15438У файлі Parquet, який записав DuckDB для view_events на medium, п’ять груп рядків. В останній мінімум і максимум
occurred_at дорівнюють 2025-09-12 і 2025-12-31, тож запит за тиждень листопада читає лише її.
Розмір таблиці view_events на full (8.1 млн рядків) у різних форматах:
| Формат | Розмір |
|---|---|
| CSV | 789 МБ |
| PostgreSQL, таблиця (з первинним ключем) | 736 МБ (919 МБ) |
| Parquet, Snappy (типово для DuckDB) | 260 МБ |
| ClickHouse, MergeTree, LZ4 | 219 МБ |
| файл DuckDB | 216 МБ |
| Parquet, ZSTD | 147 МБ |
На medium CSV важить 57.9 МБ, Parquet із Snappy 18.7 МБ, із ZSTD 10.7 МБ.
Файли Parquet не є базою: немає транзакцій, не можна змінити один рядок, а набір із сотні файлів не міняється атомарно, тож читач посеред запису побачить половину. Apache Iceberg (так само Delta Lake) додає над такими файлами шар таблиці. Таблицю описують файли метаданих: знімки, для кожного список маніфестів, а маніфести перелічують файли даних зі статистикою.
Зміна створює новий знімок, і коміт зводиться до атомарної заміни вказівника на поточні метадані в каталозі. Звідси snapshot isolation, історія версій і спільний доступ різних рушіїв. Архітектуру «озеро даних плюс шар таблиць» називають lakehouse. Озеро, сховище й оплату за прочитане в BigQuery та Athena розбирає модуль 14 хмарного курсу: правило те саме, що виміряно вище, платять за байти, які запит прочитав.
Як дані потрапляють з OLTP в аналітику
Section titled “Як дані потрапляють з OLTP в аналітику”Дані треба доставити з транзакційної бази. ETL (extract, transform, load) перетворює їх до завантаження окремим сервісом, і в сховище потрапляє готова модель. ELT спершу завантажує сирі дані й перетворює їх у сховищі запитами SQL; так зручніше, коли обчислення дешеві, як у ClickHouse і DuckDB.
CDC (change data capture) читає з транзакційної бази потік змін. Для PostgreSQL це логічне декодування WAL, на якому стоїть логічна реплікація; як вона влаштована, розбирає модуль 12. Debezium згідно з документацією читає журнал бази, перетворює кожну зміну рядка на подію й публікує в Kafka, звідки її споживає завантажувач аналітичної системи. Основну базу навантажує лише читання журналу, а аналітика відстає менше, ніж за нічного знімка.
«Запустити звіти на репліці» (модуль 12) переносить проблему, а не знімає її. Фізична репліка зберігає ті самі рядкові
сторінки, тож звіт читає ті самі 66 тисяч буферів (full), а змінюється лише те, чиї транзакції він гальмує.
До того ж довгий запит конфліктує з відтворенням WAL. За документацією PostgreSQL, відтворення чекає на запит до
max_standby_streaming_delay (типово 30 с) і скасовує його, а hot_standby_feedback запит рятує ціною мертвих версій
рядків на мастері. Для невеликих звітів репліка годиться, для важких потрібне інше зберігання.
Як це насправді
Section titled “Як це насправді”Профілі postgres і clickhouse піднімають PostgreSQL та ClickHouse, DuckDB іде одноразовим контейнером
duckdb/duckdb:1.5.6 з каталогом dataset (див. setup/README.md і лабораторну). Два звіти з початку модуля
на medium і full: виручка за місяцями й категоріями (JOIN orders, order_items, products, categories)
і кількість різних сеансів з переглядом і з додаванням у кошик за місяцями й пристроями (count(DISTINCT session_id)).
Docker: PostgreSQL 18.6 з нашого docker-compose.yml без індексів (work_mem типовий, 4 МБ), DuckDB 1.5.6 і
ClickHouse 26.3.37. У таблиці медіана п’яти запусків (для DuckDB трьох), кеш гарячий. На машині працювали й інші
контейнери, тож шукайте співвідношення, а не мілісекунди.
| Звіт | Розмір | PostgreSQL | DuckDB (файл) | ClickHouse |
|---|---|---|---|---|
| виручка | medium |
514 мс | 89 мс | 62 мс |
| виручка | full |
3.2 с | 0.24 с | 0.32 с |
| сеанси | medium |
552 мс | 61 мс | 20 мс |
| сеанси | full |
9.3 с | 0.41 с | 0.17 с |
Що читали на full. PostgreSQL: 66.2 тисячі буферів (517 МіБ) і 76 тисяч сторінок на диск для першого звіту, 89.8
тисячі (702 МіБ) і 85.8 тисячі для другого. ClickHouse: 129 МБ і 179 МБ нестиснених байтів, лише згадані в запиті
колонки. DuckDB із CSV для другого звіту витрачає 2.7–3.7 с, бо щоразу розбирає 789 МБ тексту; із файлу Parquet
0.5–0.9 с; зі свого файлу 0.41 с.
План другого звіту в PostgreSQL на full пояснює його 9 секунд:
GroupAggregate -> Sort (Sort Method: external merge Disk: 343360kB) Sort Key: (місяць), device, session_id -> Seq Scan on view_events (rows=8139506)count(DISTINCT) у PostgreSQL сортує всі 8.1 млн рядків за session_id і скидає на диск 343 МБ (work_mem 4 МБ типово),
а потім агрегує впорядковане. ClickHouse і DuckDB будують хеш-таблицю значень і не сортують нічого.
Для першого звіту Parallel Hash Join і Gather Merge розподілили роботу між трьома процесами, але рядки читаються
цілком: із products потрібні лише product_id і category_id, а читається ще й довгий description. Індекс не
допоміг би жодному зі звітів: вони читають майже всі рядки.
Типові помилки розуміння
Section titled “Типові помилки розуміння”«Колонкова база просто швидша». Вона швидша там, де запит читає багато рядків і кілька колонок. Вибірка одного рядка читає гранулу з кожної колонки (8192 рядки), а одиничні вставки й оновлення в ClickHouse дорогі.
«ORDER BY у ClickHouse — те саме, що первинний ключ у PostgreSQL». Він не унікальний і не шукає один рядок так, як
B+-дерево: це порядок на диску й розріджений індекс на гранули. Допомагає запитам за початком ключа: якщо часу в ключі
немає чи він не перший, запит за тиждень читає всю таблицю.
«Індекс прискорить звіт». Індекс допомагає, коли запит бере малу частку рядків. Звіт за рік бере більшість, і планувальник вибере послідовне сканування. Виграє тут колонкове читання, а не пошук.
«Parquet — це база даних». Це файл: без транзакцій, без атомарної зміни набору файлів і без оновлення рядка. Усе це додає шар таблиці на кшталт Iceberg.
Перевір себе
Лабораторна
Section titled “Лабораторна”L11. Аналітичний запит у трьох системах: чотири питання бізнесу «Крамниці» (когорти, воронка за категоріями, виручка з ковзним середнім, події за тиждень) у PostgreSQL, DuckDB і ClickHouse, власна таблиця MergeTree з обґрунтованим ORDER BY і пояснення, скільки читає кожна система.
Джерела
Section titled “Джерела”- M. Stonebraker та ін., C-Store: A Column-oriented DBMS, VLDB 2005.
- P. Boncz, M. Zukowski, N. Nes, MonetDB/X100: Hyper-Pipelining Query Execution, CIDR 2005.
- D. Abadi, P. Boncz, S. Harizopoulos, S. Idreos, S. Madden, The Design and Implementation of Modern Column-Oriented Database Systems, Foundations and Trends in Databases, 2013.
- M. Raasveldt, H. Mühleisen, DuckDB: an Embeddable Analytical Database, SIGMOD 2019.
- ClickHouse, документація: MergeTree, розріджений первинний індекс,
uniq. - DuckDB, документація: CSV і Parquet, формат сховища.
- Apache Parquet, специфікація формату; Apache Iceberg, Table Spec.
- M. Armbrust та ін., Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics, CIDR 2021.
- R. Kimball, M. Ross, The Data Warehouse Toolkit: схеми «зірка» і «сніжинка».
- Debezium, документація: коннектор PostgreSQL.
- PostgreSQL 18, документація: Hot Standby (конфлікти із відтворенням), логічне декодування.
- Хмарний курс, модуль 14: озеро даних і сховище даних.