PostgreSQL проти ClickHouse: де зберігати потік логів без болю

Рамка рішення: що ви насправді обираєте

Коли говорять «зберігати логи», зазвичай мають на увазі три різні речі:

• Буфер інжесту: приймати записи на високій швидкості, навіть під час сплесків. Дуробустійність важлива, латентність — здебільшого ні.
• Операційний пошук: знайти «той запит з id = X» або конкретне повідомлення про помилку за останню годину. Низька латентність має значення. Індексація важлива.
• Аналітика: group-by по мільярдам рядків, дашборди, виявлення аномалій, довге зберігання і важкі агрегати.

PostgreSQL — це загального призначення СУБД з відмінною коректністю, потужною індексацією та транзакційною семантикою. Він підходить, коли потрібні суворі обмеження та точні пошуки за ключами. Він менш комфортний для зберігання петабайтів майже-тільки-додаткових подій, одночасно обслуговуючи OLTP-запити.

ClickHouse — колонкова аналітична СУБД, оптимізована для швидких сканів і агрегатів. Він «жере» логи на сніданок — поки ви не спроектуєте партиції як для Postgres, не ігноруєте злиття або не ставитеся до нього як до суворої транзакційної системи. Це не гірший Postgres; це інший звір.

Отже, практичне правило:

• Якщо логи є частиною вашого транзакційного продукту (аудит, журнали відповідності з суворими гарантіями, або ви постійно джойните їх з OLTP-таблицями), Postgres може бути розумним вибором — з партиціюванням за часом і дисциплінованою ретенцією.
• Якщо логи призначені переважно для пошуку + дашбордів + агрегатів, і обсяг — «firehose», ClickHouse — стандартний вибір — з правильним дизайном MergeTree і операційними запобіжниками.
• Якщо ви намагаєтесь використовувати Postgres як склад для логів тому, що «вже є», ви не економите: ви позичаєте біль під змінний відсоток.

Одна перефразована ідея від Werner Vogels (CTO Amazon): надійність будують, проектуючи під відмови, а не сподіваючись, що їх не буде. Логи — це місце, де ця філософія проходить тест — бо ваша система ламається частіше, ніж ваш бюджет це визнає.

Цікаві факти та історичний контекст

1. Родовід PostgreSQL простежується до дослідницького проекту POSTGRES в UC Berkeley у 1980-х, з проєктним ухилом у бік коректності та розширюваності.
2. MVCC у Postgres означає, що оновлення/видалення створюють нові версії рядків; для таблиць логів з чурном (ретеншн deletes) це стає питанням простору і роботи vacuum, за які треба платити.
3. JSONB з’явився в Postgres 9.4 і зробив «напівструктуровані логи в реляційній БД» простими — іноді надто простими, бо це спокушає команди пропускати дисципліну схеми.
4. ClickHouse створювали в Yandex для реального часу аналітики на великому масштабі, що якраз підходить до сценарію «логи як аналітика».
5. Колонкове зберігання — не нове; колонкові сховища вивчали десятиліттями. Сучасна перевага — поєднання компресії, векторного виконання і дешевих дисків.
6. MergeTree движки в ClickHouse спираються на фоні злиття; ігнорування здоров’я злиттів — як ігнорування autovacuum у Postgres — рано чи пізно накопичує борг.
7. TTL у ClickHouse може примусово реалізувати ретенцію на шарі зберігання. У Postgres ретенція можлива, але зазвичай це практики на рівні застосунку або джобів (drop partitions, delete, vacuum).
8. TimescaleDB (розширення для Postgres) існує головно через те, що патерни часових рядів створюють навантаження на ванільний Postgres; це сигнал, що «логи як часові ряди» — це особливий випадок.

Шаблони навантажень, що роблять систему або ламають її

1) Сплески записів з прогнозованими ключами

Інжест не про середню пропускну здатність. Це про виживання 99-го перцентилю: шторми деплоїв, ретраї, каскадні відмови і той клієнт, який увімкнув детальне логування в продакшені «лише на годину». Якщо у вас є сплески, вам потрібні:

• Шлях запису, що не розвалюється під конкуренцією.
• Стратегія зворотного тиску (черга, буферизація, пакетні вставки).
• Макет збереження, що тримає записи послідовними або хоча б амортизованими.

ClickHouse любить пакетні вставки і може поглинати величезні обсяги ефективно. Postgres теж може швидко інжестити, але ви раніше вдарите в обсяг WAL, підтримку індексів і витрати на vacuum.

2) Пошук голки серед сіна

Оператори просять: «Покажи логи для request_id = X» і «Дай останні 200 рядків навколо помилки». Postgres з B-tree індексами по request_id і timestamp може бути надзвичайно швидким для цього, особливо якщо робочий набір в пам’яті.

ClickHouse також може це робити, але віддає перевагу сканам колонок і фільтрам. Його можна налаштувати відповідно з порядком primary key і skip-індексами, але це потрібно робити свідомо.

3) Агрегації та дашборди

Якщо ваша реальність — це GROUP BY service, status, endpoint, 5-minute bucket через тижні даних, Postgres протистоїть своїй природі. Ви можете додати materialized views, rollups і партиції. Це працює — поки ви не пропустите одне вікно обслуговування і не отримаєте беклог vacuum/analyze і диск, що виглядає так, ніби з’їв сам себе.

ClickHouse створений для цього. Він добре стискає, швидко сканує і паралелізує. Питання операційне: чи зможете ви утримувати merges у здоровому стані і контроль над частинами?

4) Ретеншн: delete vs drop vs TTL

Політика ретенції — це не «ми зберігаємо 30 днів». Це: «Як ми видаляємо дані, яким 29 днів, без руйнування системи?»

• У Postgres drop partition — чисто і швидко. Видалення рядків по одному дороге і створює борг vacuum.
• У ClickHouse TTL може автоматично видаляти старі дані, але це теж фізична робота фонового процесу. Зазвичай це дешевше, ніж deletes у Postgres, але якщо неправильно налаштовано, можна наситити диски.

PostgreSQL для логів: де він сильний, за що карає

Коли Postgres — правильний вибір

• Потрібні суворі транзакції: логи — частина бізнес-процесу (аудитні події, журнали-меми) з жорсткими гарантіями.
• Потрібні реляційні join’и між логами і OLTP-сутностями з консистентною семантикою.
• Обсяг помірний: «багато», але не «firehose», або вік зберігання короткий і drop-партиції — рутинна дія.
• Потрібна гнучка індексація: B-tree, GIN на JSONB, триграм-пошук, часткові індекси.

За що Postgres карає

Postgres зберігає рядки, а не колонки. Для аналітики логів ви часто читаєте кілька полів по великому часовому інтервалу. Це стає інтенсивним щодо I/O. І з MVCC «видалення старих логів» означає мертві кортежі і роботу vacuum. Партиціювання допомагає, але воно не опціональне.

Є ще WAL. WAL — ваш друг — поки таблиця логів не стає головним джерелом обсягу WAL, реплікаційного лага і churn’у бекапів.

Як зробити Postgres менш болючим для логів

• Партиціюйте за часом (щоденно або погодинно залежно від обсягу) і видаляйте партиції для ретенції.
• Мінімізуйте індекси у «гарячих» таблицях інжесту. Кожний індекс — податок на запис.
• Обережно використовуйте JSONB: зберігайте часто запитувані ключі як типізовані колонки; решту — у JSONB.
• Розділяйте відповідальності: не розміщуйте інтенсивний інжест логів поруч з критичним OLTP на тому ж primary, якщо можна уникнути цього.

Короткий жарт #1: Використовувати Postgres як склад логів — це як везти гравій на спортивному автомобілі: можливо, але підвіска подасть протест.

ClickHouse для логів: де він сильний, за що карає

Коли ClickHouse — правильний вибір

• Firehose інжест з пакетними вставками (або брокером спереду) і великою кількістю append-only даних.
• Аналітично-орієнтовані запити: дашборди, агрегати, перцентильні латентності, top-N, оцінка кардинальності.
• Довга ретенція з компресією і tiering зберігання.
• Конкурентність багатьох читачів, що роблять скани й group-by.

За що ClickHouse карає

ClickHouse карає за думку, що «ключ партиціювання» означає «те, по чому я найчастіше фільтрую». У ClickHouse партиціювання — про керування частинами і життєвим циклом даних; ordering (primary key) — про pruning запитів і локальність. Помилитися — і ви створите апокаліпсис дрібних частин, де злиття ніколи не встигають.

Він також карає за те, що ви уявляєте його суворою транзакційною системою. Вставки атомарні в межах batch, але ви не отримуєте тієї ж семантики, що в Postgres. Якщо потрібне «exactly once», ви будуєте це над БД за допомогою ідемпотентності й стратегій дедуплікації.

Як зробити ClickHouse менш болючим для логів

• Пакетні вставки (більші блоки, менше частин). Якщо пайплайн вставляє одиночні рядки, ви платите податок у вигляді частин і злиттів.
• Вибирайте ORDER BY для ваших найпоширеніших фільтрів (зазвичай timestamp + service + щось на кшталт host або trace id).
• Тримайте партиції грубими (часто за день), якщо обсяг не змушує робити менші. Занадто багато партицій збільшує метадані й overhead злиттів.
• Слідкуйте за merges і parts, як за CPU. Merge debt — це операційний борг.

Короткий жарт #2: Кластер ClickHouse з неконтрольованими частинами — як гараж, повний «організованих куп»: технічно відсортовано, емоційно руйнівно.

Схема та дизайн таблиць, що переживуть реальність

Дані логів не повністю безсхемні

Чисто безсхемні логи — фантазія. Завжди є кілька полів, які ви запитуєте постійно: timestamp, service, environment, severity, host, request_id/trace_id, status і, можливо, user_id. Ці поля мають бути типізованими колонками і в Postgres, і в ClickHouse. Усе інше можна покласти в JSON payload або структуру Map-подібну.

Postgres: стратегія партиціювання

Використовуйте декларативне партиціювання за часом. Щоденні партиції — хороший старт. Годинні партиції можуть підійти для дуже великого обсягу, але ви платите кількістю таблиць і операційним overhead. Переможним ходом є зробити ретенцію операцією DROP TABLE, а не DELETE.

ClickHouse: стратегія MergeTree

Для логів типовий патерн такий:

• PARTITION BY toDate(ts) (або toYYYYMM(ts) для дуже довгої ретенції)
• ORDER BY (service, environment, toDateTime(ts), trace_id) або подібне
• TTL ts + INTERVAL 30 DAY

Правильний ORDER BY залежить від ваших фільтрів. Якщо ви завжди фільтруєте за service і часовим діапазоном, ставте service раніше. Якщо у вас багато пошуків по request_id, включіть його — але не уявляйте, що це поводитиметься як унікальний індекс.

Компресія та типи даних

У ClickHouse вибір відповідних типів (LowCardinality для імен сервісів, Enum для рівня логів, IPv4/IPv6 типи, DateTime64 для timestamps) дає реальну економію і швидкість. У Postgres обережна нормалізація допомагає, але надмірна нормалізація може зашкодити інжесту. Логи — це місце, де «легка денормалізація, але типізована» часто перемагає.

Практичні завдання: команди, виходи, рішення

Це реальні перевірки, які ви можете виконати в продакшені. Кожна включає: команду, що означає вивід, і яке рішення з цього випливає.

Завдання 1: Підтвердити роздування таблиць та індексів у Postgres

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT relname, n_live_tup, n_dead_tup, ROUND(100.0*n_dead_tup/GREATEST(n_live_tup,1),2) AS dead_pct FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 5;"
  relname       | n_live_tup | n_dead_tup | dead_pct
----------------+------------+------------+----------
logs_2025_12_28 |   98000000 |   34000000 |   34.69
logs_2025_12_27 |   99000000 |   21000000 |   21.21
...

Значення: Мертві кортежі — це простір і робота vacuum. 35% мертвих у партиції логів зазвичай означає, що виконуються deletes/updates або autovacuum не встигає.

Рішення: Перестаньте видаляти рядки для ретенції; перейдіть на drop партицій. Якщо ви вже партиціонуєте, розберіться, чому кортежі гинуть (оновлення чи затриманий vacuum) і налаштуйте autovacuum для партицій.

Завдання 2: Перевірити, чи autovacuum справді працює на гарячих партиціях

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE relname LIKE 'logs_%' ORDER BY last_autovacuum NULLS FIRST LIMIT 10;"
   relname      |     last_autovacuum     |     last_autoanalyze
---------------+--------------------------+--------------------------
logs_2025_12_28 |                          |
logs_2025_12_27 | 2025-12-30 08:14:02+00   | 2025-12-30 08:15:31+00
...

Значення: NULL у last_autovacuum на великій активній партиції — червоний прапорець: vacuum може бути заблокований, вимкнений або голодний.

Рішення: Для таблиці з інтенсивним інжестом знизьте пороги autovacuum для цієї таблиці або перегляньте ретенцію та індексацію. Якщо vacuum блокується — знайдіть блокувальників.

Завдання 3: Знайти блокуючі сесії в Postgres

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT a.pid, a.state, a.query, pg_blocking_pids(a.pid) AS blockers FROM pg_stat_activity a WHERE a.datname='app' AND a.wait_event_type IS NOT NULL;"
 pid  | state  |                 query                  | blockers
------+--------+----------------------------------------+----------
18422 | active | VACUUM (ANALYZE) logs_2025_12_28;      | {17201}
...

Значення: Vacuum чекає на блок, який тримає інший PID. Якщо цей PID — довга транзакція, вона може перешкоджати очистці.

Рішення: Виправте поведінку застосунку (не тримати довгі idle-in-transaction), або вбийте порушників. Потім перевірте прогрес vacuum знову.

Завдання 4: Виміряти тиск WAL від інжесту логів

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),'0/0')) AS wal_bytes_since_boot;"
 wal_bytes_since_boot
----------------------
 812 GB

Значення: Грубий сигнал: якщо WAL зростає вибухоподібно під час сплесків логів, це вплине на реплікацію й бекапи.

Рішення: Розгляньте переміщення логів з primary OLTP, мінімізацію індексів, пакетні вставки або перехід на ClickHouse для аналітичних логів.

Завдання 5: Перевірити реплікаційний лаг (Postgres)

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT application_name, state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag FROM pg_stat_replication;"
 application_name |   state   |  lag
------------------+-----------+--------
 standby-a        | streaming | 14 GB

Значення: 14 GB лаґ під час сплеску логів зазвичай означає, що репліка не встигає за генерацією WAL або за дисковою/мережевою пропускною здатністю.

Рішення: Якщо логи — головний вкладник, розділіть їх. Якщо ні — дослідіть I/O на репліці та налаштування приймача WAL.

Завдання 6: Підтвердити кількість партицій і різницю розмірів (Postgres)

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT inhparent::regclass AS parent, count(*) AS partitions FROM pg_inherits WHERE inhparent::regclass::text='logs';"
 parent | partitions
--------+------------
 logs   | 62

Значення: Надто багато партицій шкодить плануванню й обслуговуванню; замало — робить ретенцію дорогою.

Рішення: Якщо партицій більше, ніж ваша операційна каденція може обслуговувати, перейдіть з погодинних на щоденні. Якщо партиції дуже великі і виконуються deletes — зробіть їх меншими і drop.

Завдання 7: Перевірити стан інжесту ClickHouse через system.parts

cr0x@server:~$ clickhouse-client --query "SELECT table, count() AS parts, sum(rows) AS rows FROM system.parts WHERE database='logs' AND active GROUP BY table ORDER BY parts DESC LIMIT 5;"
log_events	12480	9812234451
log_errors	2210	88233412

Значення: 12k активних частин для однієї таблиці часто забагато. Дрібні частини означають надто малі батчі вставок або надто дрібне партиціювання.

Рішення: Збільшіть розмір батчів, відкоригуйте партиціювання і розгляньте налаштування, що зменшують churn частин. Також перевірте беклог злиттів.

Завдання 8: Перевірити беклог злиттів у ClickHouse

cr0x@server:~$ clickhouse-client --query "SELECT database, table, sum(rows) AS rows_in_merges, sum(bytes_on_disk) AS bytes_in_merges FROM system.parts WHERE database='logs' AND active=0 GROUP BY database, table ORDER BY bytes_in_merges DESC LIMIT 5;"
logs	log_events	1823311220	412339922944

Значення: Неактивні частини — це частини, що зливаються або старі. Величезні байти «in merges» говорять про тиск злиттів і потенційне уповільнення запитів.

Рішення: Якщо злиття не встигають, зменшіть фрагментацію вставок, додайте ресурси для фонового злиття або перебудуйте таблицю (order key, partition key).

Завдання 9: Перевірити гарячі запити ClickHouse

cr0x@server:~$ clickhouse-client --query "SELECT query_duration_ms, read_rows, read_bytes, result_rows, substring(query,1,120) AS q FROM system.query_log WHERE type='QueryFinish' ORDER BY read_bytes DESC LIMIT 5;"
8421	9122333441	188233992110	120	SELECT service, count() FROM log_events WHERE ts >= now()-INTERVAL 7 DAY GROUP BY service

Значення: Один дашборд-запит, що читає 188 GB — це не «трохи повільно», це привід для рев’ю дизайну. Або фільтр занадто широкий, або ORDER BY не підходить для шаблону доступу.

Рішення: Додайте попередню агрегацію, звузьте фільтри, перегляньте ORDER BY або створіть materialized views для дашбордів.

Завдання 10: Перевірити розподіл партицій у ClickHouse

cr0x@server:~$ clickhouse-client --query "SELECT partition, count() AS parts, sum(rows) AS rows FROM system.parts WHERE database='logs' AND table='log_events' AND active GROUP BY partition ORDER BY parts DESC LIMIT 5;"
20251230	980	812233441
20251229	910	799334221

Значення: Сотні частин на денну партицію — запах проблеми. Зазвичай це означає, що батчі вставок занадто малі або у вас надто багато паралельних записувачів.

Рішення: Пакетні вставки і/або фільтрація записів через менше паралельних потоків вставки на шард.

Завдання 11: Перевірити насичення дискового I/O на Linux-хості

cr0x@server:~$ iostat -xm 1 3
Linux 6.5.0 (server) 	12/30/2025 	_x86_64_	(32 CPU)

Device            r/s     w/s   rMB/s   wMB/s  await  %util
nvme0n1         320.0   900.0   180.0   420.0   28.5  99.2

Значення: 99% util і високий await кажуть, що ви обмежені I/O. І Postgres, і ClickHouse «будуть виглядати повільними» з різних причин, але корінь — диск.

Рішення: Зменшіть підсилення записів (індекси/частини), додайте диски, відокремте WAL, перейдіть на швидше зберігання або налаштуйте паралелізм merge/vacuum, щоб уникнути самонакладених I/O-штормів.

Завдання 12: Перевірити тиск пам’яті та поведінку кешу

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:           256Gi       210Gi       3.1Gi       2.0Gi        43Gi        39Gi
Swap:           16Gi        12Gi       4.0Gi

Значення: Інтенсивне використання swap на хості БД — це обрив продуктивності. Для Postgres це може знищити ефективність shared buffers; для ClickHouse це може загальмувати merges і виконання запитів.

Рішення: Зменшіть пам’ятковий слід (знизьте concurency, налаштуйте кеші), додайте RAM або ізолюйте навантаження. Якщо ви свопите під час merges/vacuum — обмежте фонову роботу.

Завдання 13: Інспектувати повільні запити Postgres і чи допомагають індекси

cr0x@server:~$ sudo -u postgres psql -d app -c "EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM logs_2025_12_28 WHERE ts >= now()-interval '1 hour' AND service='api';"
Aggregate  (cost=...)
  Buffers: shared hit=120 read=54000
  ->  Seq Scan on logs_2025_12_28 ...

Значення: Послідовне сканування, що читає 54k буферів для годинного вікна, підказує: таблиця надто велика, фільтр неселективний або індекси відсутні/не використовуються через погану статистику.

Рішення: Розгляньте BRIN-індекс по timestamp для великих append-only партицій або перевірте partition pruning і статистику. Якщо аналітика домінує, подумайте про перенесення цього навантаження в ClickHouse.

Завдання 14: Перевірити визначення таблиці ClickHouse на предмет ORDER BY/partition/TTL

cr0x@server:~$ clickhouse-client --query "SHOW CREATE TABLE logs.log_events"
CREATE TABLE logs.log_events
(
    `ts` DateTime64(3),
    `service` LowCardinality(String),
    `env` LowCardinality(String),
    `level` LowCardinality(String),
    `trace_id` String,
    `message` String
)
ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (service, env, ts)
TTL ts + INTERVAL 30 DAY
SETTINGS index_granularity = 8192

Значення: Це загалом розумно для фільтрів «service/env/час». Якщо ваші топ-запити фільтрують тільки по trace_id, такий ORDER BY не допоможе.

Рішення: Узгодьте ORDER BY з реальними предикатами запитів. Якщо потрібні lookups по trace-id, розгляньте вторинні data skipping індекси або окреме сховище для пошуку.

Плейбук швидкої діагностики

Коли сховище логів «повільне», часу на ідеологію немає. Потрібен трипрохідний метод, щоб знайти вузьке місце без гадань.

Перший: чи проблема в хості (CPU, пам’ять, диск, мережа)?

• Насичення диска: запустіть iostat -xm 1. Якщо %util близький до 100% і await росте — ви I/O bound.
• Пам’ять: запустіть free -h і перевірте swap. Використання swap на хості БД — визнання проблеми продуктивності.
• CPU: запустіть mpstat -P ALL 1 (не показано вище). Якщо CPU завантажений і iowait низький — ви compute bound (часто компресія, парсинг, regex або group-by).
• Мережа: в кластерах перевірте, чи реплікація або distributed-запити не насичують NIC.

Рішення: Якщо хост — межа, налаштуйте БД пізніше; спочатку припиніть самонашкодження (зменшіть merge/vacuum concurency, знизьте concurency запитів, пакетуйте вставки), потім додайте потужність або розділіть ролі.

Другий: чи це шлях запису чи читання?

• Симптоми шляху запису: відставання інжесту, зростання черги, збільшення кількості частин (ClickHouse), зростання WAL і реплікаційного лага (Postgres).
• Симптоми шляху читання: тайм-аути дашбордів, високі read_bytes у логах запитів (ClickHouse), послідовні скани і читання буферів (Postgres).

Рішення: Якщо записи падають — пріоритезуйте батчування, санітарність партицій і здоров’я фонового обслуговування. Якщо читаються повільно — пріоритет: схема/order keys, попередня агрегація і обмеження запитів.

Третій: визначте борг обслуговування (vacuum vs merges)

• Postgres: мертві кортежі, довгі транзакції, що блокують vacuum, autovacuum не працює або замало агресивний для партицій логів.
• ClickHouse: забагато частин, злиття не встигають, диск зайнятий merge I/O, TTL-очищення накопичується.

Рішення: Борг обслуговування не зникне сам по собі. Або ви змінюєте шаблони інжесту (батчування), або змінюєте життєвий цикл даних (drop партицій / TTL), або ви будете платити вічно.

Три корпоративні міні-історії (досить правдиві)

Інцидент: хибне припущення («логи — append-only, тож Postgres не роздується»)

Середня SaaS-компанія вирішила зберігати application logs у Postgres «тимчасово». Припущення було просте: логи — append-only, тож MVCC не заважатиме. Вони використали одну велику таблицю з індексом за timestamp і JSONB payload. Інжест був нормальний тижнями. Дашборди теж — поки не настала ретенція.

Ретенція була реалізована як нічний DELETE FROM logs WHERE ts < now() - interval '30 days'. Це виконувалось години. Потім дні. Autovacuum почав ганятися за рухомою ціллю: мертві кортежі скупчувалися швидше, ніж його встигали чистити. Використання диску росло. Реплікаційний лаг ріс. Primary став більше часу витрачати на запис WAL і менше на обслуговування продуктових запитів.

У понеділок вранці деплой підвищив рівень логування для галасливого компонента. Обсяг WAL різко виріс. Репліка відстала настільки, що безпека failover зникла. Потім диск досяг порогу і інцидент-реакція стала полюванням: видалити старі дані, vacuum, сподіватися, що завершиться, повторювати.

Виправлення не було героїчним. Вони запартиціювали таблицю логів за днем і змінили ретенцію на drop партицій. Налаштування autovacuum були тонко відрегульовані для партицій. «Тимчасова» система логів перестала загрожувати OLTP. Великий урок: «append-only» — це брехня з моменту, коли ви реалізуєте ретенцію як delete.

Оптимізація, що відкотилася: «Розпартіціюємо ClickHouse по годинах для швидших видалень»

Інша організація перемістила логи в ClickHouse для аналітики. На початку продуктивність була відмінна. Хтось помітив, що TTL-очищення іноді буває стрибкоподібним, і вирішив «допомогти», розбивши партиції по годинах замість дня, припускаючи, що дрібніші партиції видаляти швидше.

Інжест ішов від багатьох сервісів, кожен вставляв невеликі батчі. Годинні партиції помножили кількість активних партицій. Кожна партиція накопичувала частини. Частини помножилися до десятків тисяч на таблицю. Злиття почали відставати. Латентність запитів виросла, бо системі доводилося відкривати й розглядати величезну кількість частин, а фонові злиття насичували диск.

Команда відповіла збільшенням кількості потоків background merge. Це зробило диски зайнятими більше, що погіршило запити, дашборди почали тайм-аутитися, що викликало масові ретраї у клієнтів. Тепер інжест став спіківим, і вся система набула ритму: merge storm, query storm, retry storm.

Вони повернулись до щоденних партицій, збільшили розміри вставок і зменшили кількість паралельних потоків вставки на шард. TTL знову став банальним. Мораль: менші партиції не автоматично дешевші — частини й злиття — це реальна одиниця болю.

Сумна, але правильна практика, що врятувала ситуацію: «розділити зберігання логів, встановити бюджети і практикувати ретенцію»

Фінансова платформа мала суворі вимоги аудиту, але також великі операційні логи. Вони відмовились змішувати два типи. Аудитні події жили в Postgres з жорсткими обмеженнями і ретельною схемою. Операційні логи пішли в ClickHouse, через чергу і батчер.

Нудна практика — квартальний «ретеншн-дрил». Не столовий тренінг. Реальний: вони перевіряли, що drop партицій в Postgres працює, що TTL у ClickHouse видаляє правильні діапазони, і що бекапи/відновлення не відновлюють випадково прострочені дані. Також вони ввели бюджет запитів: дашборди мали ліміти по часовому діапазону, а все ширше — вимагало попередньо агрегованої таблиці.

Одного дня галасливий деплой підняв обсяг логів. Черга поглинула сплеск. Інжест у ClickHouse відстав, але залишався стабільним. Оператори мали чітке SLO: «логи доступні для пошуку в межах X хвилин». Вони досягли цього, тимчасово масштабувавши інжест-воркерів — без втручання в OLTP-базу.

Результат не був драматичним — і в цьому суть. Правильна практика — розділення відповідальностей плюс репетиції операцій життєвого циклу. Коли firehose став голоснішим, архітектура не панікувала.

Типові помилки: симптом → корінь → виправлення

1) Диск Postgres продовжує рости навіть після видалення старих логів

Симптоми: Використання диску зростає; deletes виконуються вночі; vacuum працює постійно; запити з часом уповільнюються.

Корінь: MVCC мертві кортежі + deletes для ретенції. Простір не звільняється швидко; vacuum не встигає; роздування накопичується.

Виправлення: Партиціювання за часом і drop партицій. Якщо треба видаляти, робіть це невеликими батчами і агресивно vacuum, але розглядайте це як тимчасовий захід.

2) Латентність запитів ClickHouse пікірує під час сплесків інжесту

Симптоми: Дашборди тайм-аутяться; диски показують високу завантаженість; кількість частин швидко зростає; беклог злиттів росте.

Корінь: Забагато дрібних вставок, що створюють багато частин; злиття споживають I/O і CPU; читачі конкурують з фоновими злиттями.

Виправлення: Пакетні вставки (більші блоки), зменшити паралельних писачів на таблицю/шард, тримати партиції грубими (щодня) і моніторити parts/merges як первинні метрики.

3) Реплікаційний лаг Postgres зростає при сплесках логів

Симптоми: Streaming replica відстає; failover небезпечний; WAL диск росте; бекапи повільні.

Корінь: Обсяг WAL домінують логи; підтримка індексів підсилює записи; I/O репліки не встигає.

Виправлення: Перемістіть логи з OLTP-кластера, мінімізуйте індекси, пакетні вставки або використайте ClickHouse для масових логів і лишіть критичні аудити в Postgres.

4) ClickHouse попереджає про «занадто багато частин» і не встигає зливати

Симптоми: Кількість активних частин росте; metadata overload; повільні запити; фонові злиття постійно зайняті.

Корінь: Надто дрібне партиціювання (годинне/хвилинне), крихітні вставки або забагато шард з нерівномірним навантаженням.

Виправлення: Грубіші партиції (день/місяць), менші/більші вставки, і переоцініть ключ шардінгу, щоб уникнути перекосу.

5) «Не можемо швидко знайти конкретний request в ClickHouse»

Симптоми: Пошуки по trace_id/request_id сканують великі діапазони; латентність непередбачувана.

Корінь: ORDER BY оптимізований під service/time аналітику, а не під id-л lookup; немає підходящого skipping-індексу.

Виправлення: Додайте окрему lookup-структуру: менша таблиця, індексована за trace_id з вказівками (ts/service), або data skipping індекс; або тримайте останні N годин у Postgres/Redis для needle-пошуків.

6) Планувальник Postgres перестає правильно відсікати партиції

Симптоми: Запити торкаються багатьох партицій навіть з часовими фільтрами; час планування зростає; CPU на координаторі підіймається.

Корінь: Нездатні предикати (функції над ts), невідповідні типи/зони часу або надто багато партицій.

Виправлення: Тримайте предикати простими (ts >= константа), стандартизувати тип timestamp, зменшити кількість партицій і тримати статистику актуальною.

Чеклісти / покроковий план

Покроково: як обрати Postgres чи ClickHouse для логів

1. Класифікуйте випадки використання логів: операційний пошук vs аналітика vs аудиторний слід. Якщо це аудиторний слід — Postgres залишається в грі.
2. Запишіть ваші топ-10 запитів з реальними фільтрами і часовими діапазонами. Якщо більшість — group-by за днями, ClickHouse більш підходить.
3. Квантифікуйте інжест: середня швидкість, пікова швидкість, середній розмір повідомлення, пік паралельних записувачів.
4. Встановіть ретеншн та SLO пошуку: «пошук в межах 5 хв», «зберігати 30 днів гарячо, 180 днів холодно» і т.д.
5. Вирішіть механізм життєвого циклу: drop партицій у Postgres або TTL у ClickHouse. Якщо ви не операціоналізуєте ретенцію, зрештою випадково збережете все назавжди.
6. Обирайте мінімальну схему: типізовані колонки для частих предикатів; payload у JSON або стрічці.
7. Побудуйте тест навантаження, що враховує ретенцію і запити дашбордів. Інжест — це просто; інжест + ретеншн + запити — тут системи помирають.

Операційний чекліст: зберігання логів у Postgres (якщо наполягаєте)

• Партиціюйте за день; автоматизуйте створення і видалення партицій.
• Використовуйте BRIN для timestamp у великих append-only партиціях; мінімізуйте B-tree індекси.
• Слідкуйте за мертвими кортежами і відставанням autovacuum по партиціях.
• Тримайте логи окремо від критичного OLTP на primary, якщо можливо.
• Встановіть жорстке обмеження на часові рамки ad-hoc-аналітики; важку аналітику перемістіть кудись інде.

Операційний чекліст: зберігання логів у ClickHouse (рекомендований дефолт для firehose)

• Пакетні вставки; вимагайте мінімальний розмір батчу і максимальну частоту вставок.
• Використовуйте щоденні партиції, якщо немає сильної причини інакше.
• Встановіть ORDER BY, відповідний до ваших найпоширеніших фільтрів; не сприймайте його як унікальний індекс.
• Моніторьте: active parts, bytes in merges, read_bytes запитів, disk util і помилки вставки.
• Визначте TTL і перевіряйте, що він видаляє саме те, що ви очікуєте.
• Визначте ліміти: максимальний часовий діапазон на дашбордах; використовуйте rollups/materialized views для довгих вікон.

План міграції: Postgres logs → ClickHouse без драм

1. Dual-write або replay: почніть надсилати логи в ClickHouse, зберігаючи Postgres як було.
2. Backfill недавні партиції першими (останні 7–30 днів). Не заповнюйте всю історію, поки не доведете працездатність нової системи.
3. Перевірте запити: порівняйте підрахунки і ключові агрегати. Чекайте невеликих відмінностей, якщо були дублі або запізнілі події; вирішіть, як їх обробляти.
4. Перенесіть дашборди у ClickHouse, зберігайте Postgres для point-lookups, якщо потрібно.
5. Обріжте ретеншн у Postgres до коротшого вікна; видаліть старі партиції.
6. Майте план відкату: якщо інжест у ClickHouse впаде, логи досі потрапляють у чергу і їх можна перемотати.

Питання й відповіді

1) Чи може Postgres обробляти «багато» логів?

Так, якщо «багато» вписується в операційну модель: партиції за часом, ретеншн через drop, мінімум індексів, і ви не використовуєте його як аналітичний склад. Якщо потрібні багатонедельні group-by по мільярдах рядків — ви просите ClickHouse функціональність від системи з іншими гарантіями.

2) Чи може ClickHouse замінити Elasticsearch для логів?

Часто так, для аналітики і структурованих фільтрів. Для повнотекстового пошуку по довільним рядкам з ранжуванням релевантності і нечітким матчингом ClickHouse може робити частково, але це не його головна спеціалізація. Багато команд використовують ClickHouse для метрикоподібної аналітики логів і тримають окремий шар для «grep at scale».

3) Що з JSON-логами — Postgres JSONB чи ClickHouse Map/String?

Postgres JSONB відмінний для гнучкої індексації і containment-запитів, але дорогий у масштабі, якщо індексувати занадто багато. ClickHouse краще працює, коли часто запитувані поля — типізовані колонки, а решта — сирий JSON рядок або Map-подібна структура, залежно від інструментів інжесту. Перемагає шаблон: піднімайте «гарячі» ключі в колонки.

4) Як безпечно забезпечити ретеншн?

У Postgres: drop партицій, не робіть масових delete. У ClickHouse: TTL зазвичай правильна важіль, але перевіряйте її і слідкуйте за merge pressure. Також репетируйте ретеншн як продакшн-фічу — бо воно так і є.

5) А якщо потрібне «exactly once» інжестування?

Жодна система не дає скрізь end-to-end exactly once без допомоги. Postgres може забезпечити унікальність через constraints, але це дорого на високих обсягах логів. ClickHouse зазвичай «at least once»-дружній; ідемпотентність робіть на вході або застосовуйте дедуплікацію (зберігаючи event id і дедуплюючи у запитах або через спеціальні механізми).

6) Чи треба шардувати ClickHouse з самого початку?

Шардуйте, коли один вузол не може впоратися з інжестом або запитами з запасом. Шардінг додає операційної складності: ребаланс, distributed-запити, режими відмов. Почніть з одного вузла або невеликого реплікованого сетапу, якщо можете, але не чекайте, поки вже скінчиться диск.

7) Чи TimescaleDB — проміжна опція?

Може бути, особливо якщо ви хочете зберегти Postgres-інструментарій і потрібні оптимізації для часових рядів. Але під капотом це все ще Postgres: ретеншн і важка аналітика залишаються витратними. Якщо основна мета — масштабна аналітика логів, ClickHouse буде більш прямим вибором.

8) Який найбільший «підводний камінь» з ClickHouse?

Частини і злиття. Якщо вставляти крихітні батчі, ви створюєте крихітні частини. Крихітні частини дають борг злиттів. Борг злиттів перетворюється на насичення диска і латентність запитів. Проєктуйте і оперуйте з цим з першого дня.

9) Який найбільший «підводний камінь» з Postgres?

Видалення не безкоштовні. MVCC означає, що churn створює мертві кортежі, а vacuum — реальна робота, що конкурує з вашим навантаженням. Якщо ретеншн — це рядкові deletes для масивних таблиць, ви вже вибрали інцидент у майбутньому.

Наступні кроки, які можна зробити цього тижня

• Запишіть ваші топ-10 запитів по логах і класифікуйте їх: point lookup, short-window search або long-window analytics. Цей список вирішить вибір БД більше, ніж думки.
• Якщо ви на Postgres: впровадьте партиціювання за часом і перейдіть ретеншн на drop партицій. Потім знову виміряйте відсоток мертвих кортежів.
• Якщо ви на ClickHouse: перевірте активні parts і беклог злиттів сьогодні. Якщо parts вибухають — виправте батчування перед додаванням заліза.
• Встановіть операційні ліміти: дашборди не повинні за замовчуванням показувати 30 днів, якщо кластер розрахований на 24 години. Зробіть «дорогі» запити явними.
• Розділіть аудиторні логи від операційних: зберігайте перші точними і реляційними; робіть другі швидкими й дешевими для агрегацій.

Правильна відповідь рідко «Postgres або ClickHouse». Зазвичай це «Postgres для того, що має бути коректним і реляційним, ClickHouse — для firehose». Якщо ви намагаєтесь зробити одну систему одночасно для обох, в підсумку отримаєте ні те, ні інше — просто багато графіків і календар, заповнений інцидент-рев’ю.