PostgreSQL vs ClickHouse: ETL-патерни, що не породжують хаос даних

November 8, 2025 • February 3, 2026 • Читання: 6 хв • Views: 10

Було корисно?

Хаос у даних рідко входить із феєрверками. Він приходить тихо: дашборди перестають збігатися з рахунками, «прості» бекфіли перетворюються на вихідні, а аналітичний кластер починає спалювати CPU так, ніби гріє офіс.

PostgreSQL і ClickHouse обидва можуть підтримувати відмінні конвеєри. Вони також обидва можуть породити вишуканий безлад, якщо поводитися з ними як із взаємозамінними відрами. Порада — обирати ETL-патерни, що відповідають тому, як кожна система поводиться під навантаженням, під помилками та в умовах пізніх і дублікованих даних.

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

PostgreSQL починався як POSTGRES в UC Berkeley у 1980-х, цілеспрямовано вивчаючи розширюваність (типи, індекси, правила) задовго до того, як «плагінована» стала маркетинговим лозунгом.
MVCC у PostgreSQL (многоверсійний контроль конкурентності) — це причина, чому читання не блокують записів, але також причина того, що vacuum не є необов’язковим, якщо ви багато оновлюєте.
ClickHouse вийшов з Yandex у 2010-х для веб-аналітики на високій шкалі; його орієнти однозначно «скануй швидко, агрегіруй швидше».
ClickHouse — колонковий: він зберігає кожну колонку окремо, тому читає лише потрібні колонки для запиту, але це також означає, що багато дрібних колонок збільшують накладні витрати.
MergeTree-движки не «лише додавання» в наївному сенсі; вони «додають, потім зливають у фоні», і це формує поведінку щодо оновлень і дедуплікації.
Реплікація PostgreSQL еволюціонувала поетапно: фізична стрімінг-реплікація, потім логічна реплікація для потоків змін рядків, що дозволило ETL-патерни без постійних повних екстрактів.
Матеріалізовані подання ClickHouse тригеряться при вставці: вони перетворюють дані під час вставки, чудово для пайплайнів інжесту, але не перераховують ретроспективно, якщо ви не реінсертнете/не бекфілите.
У обох систем є «залежить від випадку» історії стиснення: Postgres стискає TOAST-значення; ClickHouse дуже добре стискає колонкові дані, особливо низькокардинальні колонки.
JSON став повноцінним по-різному: Postgres реалізував потужну індексацію JSONB і оператори; ClickHouse підтримує JSON, але зазвичай очікує нормалізації гарячих шляхів запитів у типізовані колонки.

Дві ментальні моделі: OLTP-істина vs аналітична істина

PostgreSQL — це місце, де зберігають істину. Не «істиноподібну». Не «досить близько». Істину, з обмеженнями, транзакціями й незручним завданням казати «ні» поганим даним. ClickHouse — це місце для аналітичної істини: високо запитувана, зазвичай денормалізована, оптимізована для групувань по мільярдах рядків.

Якщо ви будете трактувати PostgreSQL як сховище даних, ви напрацюєте bloat, конфлікти блокувань і схему, що намагається бути й нормалізованою, й денормалізованою одночасно (в результаті — ні тією, ні тією). Якщо ви будете трактувати ClickHouse як транзакційну базу, то зрештою виявите, що «оновлення» — це філософське питання, і відповідь — «не таким чином».

Операційний висновок

ETL-патерни, що не створюють хаосу, — це ті, що поважають обмеження кожного рушія:

У PostgreSQL: застосовуйте обмеження рано, тримайте записи ефективними, використовуйте інкрементальний екстракт і уникайте «аналітики, замаскованої під звіти» на продуктивних примаріях.
У ClickHouse: прийміть семантику append-and-merge, проектуйте партиціонування для pruning, і явно моделюйте дедуплікацію та пізно прибуваючі дані.

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

Що означає «без хаосу даних»

«Без хаосу даних» — це не атмосфера; це набір інваріантів, які можна протестувати. Ось які важливі в продакшені:

Ідемпотентність: повторний запуск роботи не створює дублікатів і не викликає дрейфу. Можна перезапускати без молитов.
Детермінізм: однакове вхідне вікно дає однакові вихідні рядки (або той самий кінцевий стан), навіть при повторних спробах.
Спостережувана свіжість: ви можете відповісти «наскільки пізні дані?» через запит, а не здогадки.
Реконсиляція: ви можете порівняти підрахунки/контрольні суми між джерелом і приймачем по значимих межах і виявити тихі втрати.
Контрольовані бекфіли: повторна обробка історичних вікон не повинна плавити кластер або змінювати метрики без сліду.
Договір щодо схеми: коли upstream додає колонку чи змінює семантику, пайплайн або голосно падає, або безпечно адаптується — ніяких тихих сюрпризів.

Жарт №1: ETL означає «Eventually Things Lag». Якщо ваший не лагає, ймовірно, він щось пропускає.

Прихований лиходій: час

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

ETL-патерни для PostgreSQL, що залишаються адекватними

Патерн 1: розглядайте Postgres як межу контракту (обмеження, а не надія)

Якщо ви можете валідувати щось один раз — валідуйте там, де воно входить у систему. Використовуйте NOT NULL, foreign keys там, де це операційно розумно, check-обмеження для доменних правил і унікальні індекси для ідемпотентних ключів. Це найдешевше місце для відлову сміття.

Для ETL-екстракції вам потрібні стабільні ідентифікатори й монотонний курсор. Це може бути:

послідовнісний первинний ключ (добре для вставок, не дуже для оновлень),
timestamp last_updated (добре, якщо його коректно підтримують),
або логічний реплікаційний слот / CDC-потік (найкраще, коли потрібні всі зміни).

Патерн 2: інкрементальні завантаження з водяними мітками (і вікном lookback)

«Завантажувати все щоразу» — валідний план, коли все маленьке. Він припиняє бути валідним, як тільки хтось каже «треба зберігати більше історії». Інкрементальні завантаження — дефолт. Але інкрементальні завантаження на основі timestamp потребують вікна lookback для обробки пізніх комітів і дрейфу годинників.

Що робити:

Підтримуйте watermark для таблиці/пайплайну (наприклад, останній успішний updated_at).
При кожному запуску екстрагуйте updated_at > watermark - lookback.
Upsert-те в staging/warehouse з детермінованим ключем.

Чого уникати: використання now() як межі без її збереження або припущення, що timestamp-и строго зростають. Вони — ні, особливо при ретраях і мультиписачах.

Патерн 3: використовуйте CDC, коли «оновлення важливі»

Якщо рядки джерела змінюються й ви хочете, щоб ці зміни відобразилися далі — CDC це чистий підхід. Логічна реплікація (або плагін декодування) дає впорядковані події змін з позиціями LSN. Операційна вигода — ви припиняєте вгадувати, що змінилося.

Операційна вартість: тепер ви запускаєте станфул-споживачів і маєте моніторити lag реплікаційних слотів. Але ви вже запускаєте станфул-системи; ви просто називаєте їх «пайплайнами» і вдаєте, що вони безстанні.

Патерн 4: відокремте OLTP від навантаження екстракції

Проводьте важкі екстракти проти репліки, а не проти первинного вузла. Якщо треба екстрагувати з примарі, використовуйте ретельно індексовані предикати, невеликі батчі й явні statement timeouts. Аналітичні запити на примарі — це шлях, яким «data engineering» стає категорією інцидентів.

Патерн 5: staging у Postgres — якщо ви вмієте підтримувати порядок

Staging-таблиці в Postgres підходять, коли вони короткоживучі й вакуумляться, і коли ви не робите з бази місце для необмежених сирих подій. Використовуйте UNLOGGED таблиці для тимчасового staging, якщо ви можете терпіти втрату даних при краші і хочете швидкості. Для стійкого сирого інжесту в масштабі Postgres зазвичай не найкращий майданчик.

Патерн 6: Upsert з наміром (не з надією)

INSERT ... ON CONFLICT — це подарунок. Також це заряджена зброя. Якщо ви робите upsert великими батчами в гарячі таблиці без думки про індекси й bloat, ви дізнаєтеся, що означає «autovacuum scale factor» о 2:00 ранку.

Для ETL-staging використовуйте детерміновані ключі й додавайте колонку source-version (наприклад, source LSN, або updated_at, або хеш), щоб виявляти no-op оновлення й уникати непотрібної перезапису рядків.

ETL-патерни для ClickHouse, що залишаються адекватними

Патерн 1: вставляйте блоками, а не рядок за рядком

ClickHouse любить великі вставки. Ваша мета — менше, але більших блоків, що добре стискаються і створюють менше частин. Багато маленьких вставок створюють надто багато частин, і фонова злиття буде жити, щоб прибирати за вами.

Патерн 2: проєктуйте MergeTree-таблиці під запити, а не під «схему джерела»

Проєктування таблиці в ClickHouse починається з:

partition key (що ви відсікаєте),
order by (як ви кластеризуєте для range scan-ів і дедуплікації),
primary key (рідкісний індекс над відсортованими даними).

Якщо ви оберете partition key, що дає дрібні партиції (наприклад, по user_id), ви створите зоопарк партицій і злиттів. Якщо партиціонувати занадто грубо — втрачається pruning і бекфіли стають дорогими. Часові партиції (день/тиждень/місяць) поширені, бо вони відображають обробку пізніх даних і TTL.

Патерн 3: явна робота з дублікатами — ReplacingMergeTree (і розуміння компромісу)

ReplacingMergeTree — це движок «я отримую дублі і хочу останній запис виграти». Він стискає дублі під час злиттів на основі сортування (і опційного колонки версії). Небанальна річ: дедуплікація — це остаточна операція. Запити можуть бачити дублікати, поки злиття не пройдуть, якщо ви не виконуєте FINAL-запит (дорого) або не проєктуєте систему з урахуванням цього.

Використовуйте, коли:

ви можете терпіти тимчасові дублікати,
у вас є стабільний dedup-ключ,
і ви можете надати колонку версії (наприклад, source updated_at або послідовність при інжесті).

Уникайте, коли вам потрібна сувора унікальність на момент читання. Це не робота ClickHouse.

Патерн 4: для «append-only» фактів тримайтеся простоти: MergeTree + незмінні рядки

Якщо ваші дані природньо незмінні (події, логи, перегляди сторінок), не вигадуйте оновлень. Використовуйте plain MergeTree або ReplicatedMergeTree, партиціонуйте за часом, order by — за ключами запитів, і дайте цьому працювати.

Патерн 5: матеріалізовані подання для трансформів під час інжесту, не як магічна паличка

Матеріалізовані подання хороші для:

попередніх агрегацій (наприклад, погодинні зведення),
денормалізації атрибутів довідників під час інжесту,
розподілу сирого потоку на кілька табличок з різними формами.

Але вони обробляють лише те, що вставлено після їх створення. Якщо ви створите view і чекатимете, що історичні дані з’являться — отримаєте урок про причинність. Тригери вставки не подорожують у часі.

Патерн 6: пізні дані: стратегія партиціювання + «grace window» для злиттів

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

Тримайте денні партиції для останніх N днів.
Агресивно зливайте ці партиції.
Після N днів — компактнізуйте або переносьте старі партиції на дешевше сховище через TTL.

Патерн 7: агрегації: AggregatingMergeTree потужний, але не безкоштовний

Якщо ви використовуєте AggregatingMergeTree, ви зберігаєте стани агрегатів і зливаєте їх. Це може бути дуже ефективно для роллапів, але ускладнює ad-hoc запити й відлагодження, бо збережені дані — не сирі факти. Використовуйте для стабільних, чітко визначених метрик. Тримайте сирі факти для реконсиляції та переобробки.

Жарт №2: злиття в ClickHouse як прання — ігноруй їх достатньо довго, і все накопичиться, забридить і забере твій уїк-енд.

Передача: staging, контракти й реконсиляція

Припиніть думати «ETL», почніть думати «контракти»

Найбільший стрибок у надійності конвеєрів — визначити контракт на межі: що є валідним рядком, які ключі роблять його унікальним, що означає «оновлення» і який timestamp авторитетний.

Корисний контракт для подібних подіям даних звичайно містить:

event_id або детермінований ключ ідемпотентності,
event_time (коли сталося),
ingest_time (коли ви побачили),
source_version (LSN, updated_at або подібне),
schema_version (щоб еволюціонувати безпечно).

Стратегія staging: посадити сире, потім форму

Існує два загалом адекватні підходи до staging:

Сире в ClickHouse, формування в ClickHouse: посадіть сирий стіл (типізовані колонки для гарячих полів плюс blob/JSON для решти), потім матеріалізовані подання будують форми. Це поширено, коли ClickHouse — головне аналітичне сховище.
Сире поза обома, завантаження в обидва: спочатку в об’єктне сховище або чергу, потім завантажуйте в Postgres (для операційних потреб) і ClickHouse (для аналітики). Це зменшує зв’язність і робить перезапуски безпечнішими.

Коли Postgres — джерело істини, поширений патерн — CDC з Postgres у ClickHouse. Це добре працює, якщо ви трактуєте CDC-потік як сирий контракт і захоплюєте видалення/оновлення явно.

Реконсиляція: підрахунки — це початок, але не все

Підрахунки рядків — пожежна сигналізація. Вони не скажуть, яка саме кімната горить, але підкажуть, що будівля не в порядку. Краще: реконсиляція по партиціях/вікнах з використанням:

підрахунків за день/годину,
distinct-підрахунків ключів,
контрольних сум/хешів по стабільних проекціях,
шкали пізніх даних (події, що прибувають > X годин пізно).

Якщо ви не вмієте реконсиляцію — у вас не конвеєр, а чутки.

12+ практичних завдань з командами, виводами та рішеннями

Ось типи команд, які ви запускаєте, коли ви на чергуванні за даними. Кожне містить, що означає вивід і яке рішення потрібно ухвалити далі.

Завдання 1: перевірити симптоми bloat у Postgres через dead tuples

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),2) AS dead_pct FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 10;"
      relname       | n_live_tup | n_dead_tup | dead_pct
------------------+------------+------------+----------
events            |   92034112 |   18010233 |    16.37
orders            |    1203321 |     220331 |    15.48
...

Значення: Велике n_dead_tup свідчить про часті оновлення/видалення без достатнього прогресу vacuum.

Рішення: Якщо dead_pct високе в таблицях, що апсертяться ETL-ом — зменшіть churn оновлень (уникайте no-op оновлень), відрегулюйте налаштування autovacuum або перейдіть на pattern append + періодичний ребілд.

Завдання 2: перевірити, чи автовакамп робить своє (по таблиці)

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT relname, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY last_autovacuum NULLS FIRST LIMIT 10;"
   relname   |     last_autovacuum     |    last_autoanalyze     | autovacuum_count | autoanalyze_count
------------+--------------------------+--------------------------+------------------+-------------------
events      | 2025-12-30 02:11:41+00   | 2025-12-30 02:15:03+00   |             182  |               210
...

Значення: Застаріле last_autovacuum на гарячих таблицях означає, що vacuum не запускається достатньо часто або блокується.

Рішення: Якщо ETL-таблиці голодують autovacuum — знизьте scale factors для цих таблиць або змініть ETL на insert-new + swap.

Завдання 3: перевірити використання індексу для предикату інкрементального екстракту

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE updated_at > now() - interval '2 hours';"
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using orders_updated_at_idx on orders  (cost=0.43..12842.11 rows=24000 width=312)
  Index Cond: (updated_at > (now() - '02:00:00'::interval))
  Buffers: shared hit=802 read=19
  Execution Time: 48.221 ms

Значення: Ви отримуєте index scan з невеликим числом read buffers. Добре.

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

Завдання 4: перевірити lag replication slot (здоров’я CDC)

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS restart_lag, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS flush_lag FROM pg_replication_slots;"
   slot_name    | active | restart_lag | flush_lag
----------------+--------+-------------+-----------
ch_cdc_slot     | t      | 12 GB       |  420 MB

Значення: WAL утримується, бо консьюмер не просунувся. Restart lag впливає на використання диска; flush lag — це «наскільки консьюмер відстає».

Рішення: Якщо lag росте — обмежте продуцентів, виправте консьюмера або збільшіть диск для WAL. Не видаляйте слот легковажно; так втрачається історія змін.

Завдання 5: інспектувати блокування Postgres, що можуть блокувати ETL

cr0x@server:~$ psql -h pg-prod-primary -U app -d appdb -c "SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle' AND wait_event IS NOT NULL ORDER BY pid LIMIT 5;"
 pid  | wait_event_type |  wait_event   |                       query
------+-----------------+---------------+--------------------------------------------------
48211 | Lock            | relation      | ALTER TABLE orders ADD COLUMN promo_code text;
...

Значення: Ваш ETL може стояти в черзі за DDL-локом або навпаки.

Рішення: Переносьте DDL у вікна технічного обслуговування, використовуйте concurrent index builds і встановлюйте statement timeouts для ETL-сесій.

Завдання 6: перевірити вибух частин у ClickHouse (занадто багато дрібних вставок)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT table, sum(parts) AS parts, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts WHERE active AND database='analytics' GROUP BY table ORDER BY parts DESC LIMIT 5;"
events_raw	18234	1.21 TiB
sessions	 3921	211.33 GiB

Значення: Велика кількість активних частин вказує на багато дрібних частин; злиття будуть боротися, і запити повільніють.

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

Завдання 7: перевірити, чи злиття в ClickHouse відстають

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT database, table, sum(rows) AS rows_merging, count() AS merge_jobs FROM system.merges GROUP BY database, table ORDER BY rows_merging DESC LIMIT 5;"
analytics	events_raw	1289031123	7
analytics	sessions	 210331119	3

Значення: Багато рядків у злитті означає інтенсивну фонову роботу; відбувається write amplification.

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

Завдання 8: перевірити hot-запити ClickHouse та обсяг читань

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS read, query FROM system.query_log WHERE type='QueryFinish' AND event_time > now() - INTERVAL 10 MINUTE ORDER BY read_bytes DESC LIMIT 3;"
8421	1290341122	78.12 GiB	SELECT * FROM events_raw WHERE ...
2103	230113112	12.03 GiB	SELECT user_id, count() FROM events_raw ...

Значення: Хтось робить широкий SELECT * скан або відсутнє partition pruning.

Рішення: Виправте запити, додайте проєкції/матеріалізовані роллапи або введіть обмеження на запити. І ще: перестаньте вибирати * з сирих факт-таблиць, якщо не любите перегортання сторінок.

Завдання 9: перевірити, чи працює partition pruning у ClickHouse

cr0x@server:~$ clickhouse-client -h ch01 -q "EXPLAIN indexes=1 SELECT count() FROM events_raw WHERE event_date = toDate('2025-12-29') AND customer_id = 42;"
Expression (Projection)
  Aggregating
    ReadFromMergeTree (analytics.events_raw)
    Indexes:
      MinMax
        Keys: event_date
        Condition: (event_date in [2025-12-29, 2025-12-29])
        Parts: 3/120
        Granules: 512/20480

Значення: Читаються лише 3 з 120 частин. Це pruning. Ваш partition key реально працює.

Рішення: Якщо читаються більшість частин — перегляньте partition key, предикати запитів або розгляньте додавання вторинного data-skipping індексу для поширених фільтрів.

Завдання 10: виявити дублікати ключів у сирому інжесті ClickHouse (перед тим як злиття їх сховають)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT event_id, count() AS c FROM events_raw WHERE event_date >= today()-1 GROUP BY event_id HAVING c > 1 ORDER BY c DESC LIMIT 5;"
e_9f3b2	4
e_1a77c	3

Значення: Дублікати приходять. Це може бути очікувано (ретраї) або баг (неідемпотентний продуцент).

Рішення: Якщо дублікати очікувані — переконайтеся, що стратегія дедуплікації (ReplacingMergeTree/version) відповідає семантиці. Якщо не очікувані — виправте ідемпотентність продуцента і додайте дедуп на рівні інжесту.

Завдання 11: перевірити чергу реплікації ClickHouse (якщо використовується ReplicatedMergeTree)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT database, table, count() AS queue, sum(num_tries) AS tries FROM system.replication_queue GROUP BY database, table ORDER BY queue DESC LIMIT 5;"
analytics	events_raw	23	41

Значення: Завдання реплікації очікують; ретраї свідчать про проблеми мережі/диска або перевантаження.

Рішення: Якщо черга росте — перевірте здоров’я ZooKeeper/ClickHouse Keeper, мережу, латентність диска і чи не насичують merges I/O.

Завдання 12: перевірити «свіжість» в Postgres і ClickHouse за явними водяними мітками

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT max(updated_at) AS pg_max_updated_at FROM orders;"
     pg_max_updated_at
-----------------------------
2025-12-30 08:41:12.912+00

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT max(source_updated_at) AS ch_max_updated_at FROM orders_dim;"
2025-12-30 08:39:58

Значення: ClickHouse відстає приблизно на 74 секунди для цього набору даних.

Рішення: Якщо лаг перевищує SLA — інспектуйте CDC lag, черги інжесту, злиття й кількість частин. Якщо в межах SLA — не чіпайте.

Завдання 13: відслідкувати тиск на диск ClickHouse і байти, записані злиттями

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT name, value FROM system.asynchronous_metrics WHERE name IN ('DiskUsed_default','DiskAvailable_default','MergesBytesWritten') ORDER BY name;"
DiskAvailable_default	2.31e+12
DiskUsed_default	5.84e+12
MergesBytesWritten	1.19e+12

Значення: Використання диска й обсяг записів злиттів високі; можливо, ви в зоні write amplification.

Рішення: Призупиніть великі бекфіли, зменшіть кількість частин і переконайтеся, що TTL-операції не викликають постійного шуму.

Завдання 14: перевірити позицію консьюмера CDC щодо LSN Postgres

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT pg_current_wal_lsn() AS current_lsn;"
 current_lsn
-------------
 5A3/1F2B9C0

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name='ch_cdc_slot';"
 confirmed_flush_lsn
---------------------
 5A3/1A90D10

Значення: Консьюмер відстає на вимірювану відстань WAL.

Рішення: Якщо вона росте — масштабируйте консьюмера, виправте зворотний тиск вниз по потоку (часто злиття/disk у ClickHouse), або тимчасово зменшіть обсяг змін upstream для неключових таблиць.

План швидкої діагностики

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

Перше: це проблема свіжості, коректності чи витрат?

Проблема свіжості: дані пізні; max timestamps відстають.
Проблема коректності: дублікати, відсутні рядки, неправильні join-и або «числа змінилися після бекфілу».
Проблема витрат: спайки CPU/I/O, неконтрольовані merges, таймаути запитів.

Друге: знайдіть межу вузького місця

Джерело (Postgres) екстракція: повільні запити, конфлікти блокувань, lag replication slot.
Транспорт: черга backlog, помилки консьюмера, питання розміру батчів.
Приймач (ClickHouse) інжест: вибух частин, відставання merge-ів, черга реплікації.
Серверні запити: погані шаблони запитів, відсутнє pruning, поганий дизайн таблиць.

Третє: мінімальні перевірки, що зазвичай знаходять проблему

Відставання водяної мітки: порівняйте max updated/event times джерело vs приймач (Завдання 12).
Lag слота CDC: якщо використовуєте CDC — перевірте replication slot lag (Завдання 4/14).
Частини і злиття ClickHouse: кількість частин і backlog merges (Завдання 6/7).
Запит-лог топ по read bytes: знайдіть запит, що навантажує кластер (Завдання 8).
Перевірка partition pruning: підтвердьте, що гарячі запити відсікають частини (Завдання 9).

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

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

1) Метрики стрибали після повторних прогонів

Симптоми: повторний запуск роботи змінює денні підсумки; дублікати сесій; «те саме вікно — різний результат».

Корінь: неідемпотентні завантаження (append без дедупа) або upsert-ключі не відображають реальної унікальності.

Виправлення: визначіть детермінований ключ ідемпотентності, завантажуйте в staging, а потім мерджте в фінальні таблиці з явною дедуплікацією (Postgres: ON CONFLICT; ClickHouse: ReplacingMergeTree з версією або явна дедуплікація на рівні запитів для сирих таблиць).

2) Інжест в ClickHouse починається швидко, потім повільнішає до повного стопу

Симптоми: вставки таймаутяться; CPU і диск зайняті; активні частини роздуваються.

Корінь: занадто багато дрібних вставок, що викликають part explosion; злиття не встигають.

Виправлення: збирайте вставки в більші блоки, налаштуйте буферизацію вставок, уникайте посторінкових вставок. Розгляньте зменшення гранулярності партиціонування і перегляд ORDER BY для кращої локальності злиттів.

3) Бекфіл «працював», але запити стали повільнішими

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

Корінь: бекфіл вставив дані не в порядку відносно ORDER BY, що створило багато частин і погану локальність; або він зачепив старі партиції з TTL/merges і спричинив шум.

Виправлення: бекфіл по порядку партицій, контролюйте розмір батчів і уникайте перемішування старих і нових партицій. Перевіряйте pruning через EXPLAIN до та після.

4) Primary Postgres повільніє під час екстрактів

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

Корінь: ETL-зчитування конкурують з OLTP-записами; sequential scans через відсутність індексів; тривалі транзакції стримують vacuum.

Виправлення: проводьте екстракти на репліках, додайте індекси для інкрементальних предикатів, обмежте розмір батчів і введіть statement timeouts. Тримайте ETL-транзакції короткими.

5) «Ми використовуємо updated_at», але рядки все одно зникають

Симптоми: у джерелі є рядки, яких немає в приймачі; зазвичай невеликі відсотки; складно відтворити.

Корінь: updated_at ненадійно оновлюється при усіх змінах; плутанина з часовими зонами; межа вікна екстракту занадто вузька; зсув годинників від серверів додатку.

Виправлення: додайте тригер у БД, щоб гарантувати оновлення updated_at, включіть lookback-вікно, зберігайте межу екстракту явно або переходьте на CDC.

6) ClickHouse іноді показує дублікати

Симптоми: дублікати з’являються в сирих або навіть у таблицях з дедупом; згодом «зникають».

Корінь: покладання на злиття ReplacingMergeTree для дедупа; дедуп — остаточний.

Виправлення: прийміть остаточну дедуплікацію і пишіть запити обережно (уникайте FINAL, якщо не потрібно), або дедупуйте при інжесті. Для суворих вимог тримайте таблицю «поточний стан», побудовану періодичними компактаційними задачами.

7) Коректність пайплайну ламається після зміни схеми

Симптоми: стовпці змістилися, null-и там, де не повинні бути, дашборди тихо невірні.

Корінь: еволюція схеми без контрактів; використання SELECT *; залежність від позиційного CSV-імпорту; слабка типізація при інжесті.

Виправлення: версіюйте схеми, фіксуйте списки колонок, валідовуйте null-rate і кардинальність нових колонок, і падайте швидко при несумісностях.

Три корпоративні міні-історії (болісно знайомі)

Міні-історія 1: інцидент через неправильне припущення

Компанія A вела продукт підписки. Postgres був білінговою системою; ClickHouse — аналітикою. Данна команда зробила інкрементальний екстракт на основі updated_at з таблиці subscriptions. Виглядало ідеально в деві, нормально в стейджингу і «переважно нормально» в проді.

Потім фінанси помітили, що показники відтоку постійно трохи не сходяться. Не сильно. Достатньо для щотижневих нарад, де всі приносили власні таблиці як ковдру безпеки.

Неправильне припущення було простим: «updated_at змінюється щоразу, коли змінюється бізнес-сенс рядка». Насправді фоновий процес перемикав булевий прапор через stored procedure, яка не торкалася updated_at. Такі рядки ніколи не екстрагувалися. Вони не були пізні — вони були невидимі.

Вони спробували розширити вікно екстракту. Це трохи допомогло, потім вийшло на плато. Вони спробували повний перезавантаження щотижня. Це «виправляло» числа і перетворило суботу на неприємний ритуал.

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

Міні-історія 2: оптимізація, що відкотилася назад

Компанія B мала високошвидкісний подієвий пайплайн в ClickHouse. Вставки йшли від кількох мікросервісів, кожен відправляв невеликі батчі кожні кілька секунд. Хтось помітив латентність інжесту і «оптимізував» шляхом збільшення конкурентності: більше потоків, більше паралельних вставок, коротші інтервали ретраїв. Графіки виглядали вражаюче. Наче запуск ракети.

Два дні потому латентність запитів різко зросла. CPU не був проблемою. Диск — так. Активні частини на таблицю виросли від «здорово» до «це баг?». Фонові злиття почали займати життя кластера, а черги реплікації зросли, бо репліки не встигали підібрати частини й зливатися.

Вони оптимізували не ті речі. Скоротили латентність на стороні продуцентів ціною стабільності кластера. ClickHouse може інжестити дуже швидко, але хоче великі блоки. Багато крихітних блоків — як годувати дроворуб по одному сірнику: технічно можливо, емоційно виснажливо.

Відкат не був гламурним: вони примусили upstream батчити (більші блоки, менше вставок), ввели буфер/чергу для згладжування сплесків і обмежили бекфіли на часи низької активності. Латентність інжесту все одно покращилася, бо злиття стали керованими. «Оптимізація» була податком, а не фічею.

Міні-історія 3: нудна, але правильна практика, що врятувала день

Компанія C управляла маркетплейсом. Їхня аналітика впливала на ціноутворення, тому коректність була важлива. У них було правило: кожен пайплайн публікує щоденний звіт реконсиляції в невеликій Postgres-таблиці — підрахунки, distinct-ключі й контрольну суму по партиції. Це не було модним. Це була арифметика.

Одного вівторка розгортання змінило спосіб генерації event ID сервісом. Це не було зловмисно. Навіть не явно помилково. Але змінило семантику ідемпотентності: ретраї тепер генерували нові ID замість повторного використання старих.

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

Реконсиляційна робота помітила це за години: distinct event_id за день впав відносно total rows, і контрольна сума розійшлася. Аларм пішов у правильний канал, і он-колл мав достатньо доказів, щоб прямо вказати на «зміни у генерації ID», замість біганини за привидами злиттів або звинуваченням ClickHouse.

Вони виправили продуцента, програли невелике вікно повторно і повернулися до справ. Нічого героїчного не сталося. Ось у чому суть: нудні перевірки запобігають гучним інцидентам.

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

Покроково: вибір правильного патерну для кожного набору даних

Класифікуйте набір даних: незмінні події, повільно змінні виміри, змінні сутності або агреговані метрики.
Визначте унікальний ключ: event_id, natural key або synthetic key + version.
Обирайте авторитетний час: event_time vs updated_at vs CDC LSN; задокументуйте це.
Розв’яжіть, як обробляти оновлення/видалення: ігнорувати, додавати нову версію або застосовувати зміни стану через дедуп/мердж.
Обирайте механізм екстракції: інкрементальний timestamp з lookback, числовий курсор або CDC.
Обирайте двигун ClickHouse: MergeTree для незмінних, ReplacingMergeTree для «остання версія», AggregatingMergeTree для роллапів.
Виберіть партиціювання: зазвичай на основі часу; підтвердіть pruning через EXPLAIN.
Виберіть ORDER BY: співпадайте з найпоширенішими фільтрами й групуваннями; уникайте висококардинальних полів, якщо вони руйнують локальність злиттів.
Побудуйте реконсиляцію: по партиції/вікну; підрахунки + distinct-ключі + контрольна сума.
Визначте процедуру бекфілу: упорядковано по партиціях, лімітована за швидкістю і прозора.

Операційний чекліст: перед релізом нового ETL-пайплайну

Чи можу я перезапустити роботу без дублікатів або дрейфу?
Чи зберігаються явні водяні мітки десь надійно?
Чи знаю я максимально допустиму затримку, і чи дизайн її враховує?
Чи важкі екстракти не працюють проти Postgres primary?
Чи інжест у ClickHouse батчиться у великі блоки?
Чи можу підтвердити, що partition pruning працює для топ-3 запитів?
Чи є алерт на lag CDC slot / ingestion lag / parts explosion?
Чи можу бекфілити один день без впливу на свіжість поточного дня?

План бекфілу, що не спалить продакшн

Зафіксуйте визначення: версіюйте код трансформацій, що використовуються для бекфілу.
Виберіть вікна: не робіть «всю історію» однією задачею. Використовуйте дні/тижні.
Тротлінг: обмежте швидкість вставок в ClickHouse і кількість паралельних merges; уникайте пікових годин.
Пишіть у shadow-таблицю: перевіряйте підрахунки/контрольні суми перед свопом views або маршрутизацією запитів.
Реконсиляція: порівнюйте джерело/приймач по партиції, а не глобально.
Плавний cutover: почніть з внутрішніх дашбордів перед виконавчими. Ваше майбутнє «я» полюбить менше несподіваних нарад.

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

1) Чи використовувати PostgreSQL або ClickHouse як staging?

Якщо staging тимчасовий і малий — Postgres підходить. Якщо staging великий, сирий і append-heavy — зазвичай краще ClickHouse або зовнішня зона зберігання. Staging не повинен стати необмеженою кошиком сміття.

2) Чи може ClickHouse замінити Postgres для аналітичних запитів прямо?

Для read-heavy аналітики часто — так. Але ClickHouse не замінить транзакційну семантику, суворі обмеження і патерни оновлення рядків без переосмислення моделі даних.

3) Який найпростіший безпечний інкрементальний завантажувач з Postgres?

Інкрементальний екстракт з надійним updated_at плюс lookback-вікно, завантаження в staging і потім upsert у фінальну модель з детермінованими ключами.

4) Коли переходити від timestamp-інкременту до CDC?

Коли оновлення/видалення важливі, коли updated_at не можна довіряти або коли потрібен точний capture змін без сканування великих вікон. CDC додає операційної складності, але прибирає двозначність.

5) Чи достатньо ReplacingMergeTree, щоб «робити upsert» у ClickHouse?

Цього достатньо для «остаточного last-write-wins», якщо ви даєте стабільний dedup-ключ і (бажано) колонку версії. Якщо потрібна сувора унікальність під час читання — потрібна додаткова логіка або інший підхід.

6) Чому FINAL-запити не радять використовувати?

FINAL змушує дедуп/злиття під час запиту і може перетворити швидке сканування на дорогоцінну операцію. Це інструмент для відладки або крайній засіб, а не дефолт.

7) Як уникнути поломки дашбордів під час еволюції схеми?

Ведіть версії схем, уникайте SELECT *, валідовуйте null-rate і кардинальність нових колонок, використовуйте явні списки колонок при інжесті. Падіння швидко краще за тиху помилку.

8) Як краще обробляти видалення з Postgres у ClickHouse?

Або моделюйте «тумбстони» (подію delete) і фільтруйте під час запиту, або підтримуйте таблицю поточного стану, де видалення застосовуються через дедуп/версію. ClickHouse може робити delete-и, але часті видалення рядків — дорогі.

9) Як зрозуміти, що ClickHouse повільний через інжест або через запити?

Перевірте кількість активних частин і backlog merges спочатку (натиск інжесту), потім лог запитів по read bytes і durations (натиск запитів). Якщо обидва високі — ваші записи створили проблему читання — зазвичай занадто багато частин або погане партиціювання.

10) Який розумний базовий реконсиляційний набір?

По партиції/вікну: підрахунок рядків, distinct-підрахунок ключів і контрольна сума/хеш по стабільній проекції ключових полів. Додавайте коефіцієнт пізніх прибуттів, якщо час має значення (а він має).

Висновок: наступні кроки, що знижують ризик цього тижня

Якщо ви хочете ETL, що не породжує хаос даних — не починайте з крутилки налаштувань. Почніть з визначення, що означає «коректно», а потім зробіть це спостережуваним.

Визначте ключі ідемпотентності для ваших топ-3 наборів даних і задокументуйте їх там, де інженери натраплятимуть на них.
Додайте явні водяні мітки (max time/version у джерелі і приймачі) і налаштуйте алерти на лаг.
Запустіть одну щоденну реконсиляцію для одного критичного пайплайну: підрахунки, distinct-ключі, контрольна сума по дню. Впровадьте і ітераціонуйте.
У ClickHouse виміряйте частини й злиття і виправте дрібно-вставочні патерни до того, як вони стануть стилем життя.
Зніміть важкі екстракти з Postgres primary або перейдіть на CDC, якщо оновлення важливі й таблиця велика.

Продакшн-системи не винагороджують оптимізм. Вони винагороджують явні контракти, контрольовані режими відмов і пайплайни, які можна перезапустити у вівторок без переговорів з всесвітом.