Data engineering
OLTP vs OLAP: чтение запросов и планов
Суть Читайте реальные планы запросов, DDL и конфиг пайплайна по юниту OLTP vs OLAP, прогнозируйте стоимость скана на row vs column store и выбирайте фикс с наибольшим рычагом.
Высота — путь к senior
НольJuniorMiddleSenior
Ты на senior-высоте — в орбитеПлан запроса и DDL — там, где разделение нагрузок проявляет себя. Прочитайте каждый сниппет, спрогнозируйте поведение на row vs column store и выберите фикс, который senior делает первым.
Цель
Отработайте цикл, который вы запускаете на каждом ревью архитектуры данных: прочитать запрос, спрогнозировать просканированные байты и потянуться к смене layout или пайплайна — а не к костыльному индексу — прежде чем аналитическая нагрузка отравит транзакционную.
Сниппет 1 — EXPLAIN на row store
EXPLAIN (ANALYZE, BUFFERS)
SELECT country, SUM(total)
FROM orders -- 40M строк, 50 колонок, row-store Postgres
WHERE created_at >= now() - interval '90 days'
GROUP BY country;
Seq Scan on orders (cost=0.00..2.1M rows=38_000_000)
Buffers: shared read=4_900_000 -- ~38 GB протянуто через кэш
Planning Time: 0.2 ms
Execution Time: 31_482 ms Викторина
Completed
Планировщик выбрал Seq Scan и прочитал ~38 GB, хотя запросу нужны только country и total. О чём это говорит и какой фикс правильный?
Heads-up 90-дневное окно по всем странам всё равно подходит почти всем строкам, поэтому планировщик остаётся на Seq Scan; покрывающий индекс к тому же сохранит почти всю таблицу и добавит стоимость записи. Индексы чинят selectivity, а не объём скана.
Heads-up Buffers: shared read считает страницы по 8KB, реально прочитанные из heap. ~4.9M страниц — это ~38 GB реального I/O, протянутого через shared_buffers — ровно тот вытесняющий кэш скан, которого надо избегать на прод-машине.
Heads-up work_mem помогает шагу агрегации, а не скану: движок всё равно читает каждый байт каждой подошедшей строки ради двух колонок. Объём скана задаёт layout строки, а не память под хеш.
Сниппет 2 — разделение схемы
-- OLTP: сильно нормализовано ради дешёвых консистентных одиночных записей
CREATE TABLE orders (
id bigint PRIMARY KEY,
user_id bigint REFERENCES users(id),
country_id smallint REFERENCES countries(id),
total_cents bigint,
created_at timestamptz
);
-- OLAP target: денормализованный широкий факт, country свёрнут в низкокардинальную колонку
CREATE TABLE orders_fact (
order_id UInt64,
user_id UInt64,
country LowCardinality(String), -- dictionary-encoded
total_cents Int64,
created_at DateTime
) ENGINE = MergeTree ORDER BY (created_at, country); Викторина
Completed
Почему OLTP-таблица нормализована с FK country_id, а OLAP-факт денормализует country в колонку LowCardinality(String)?
Heads-up На аналитической стороне join измерения на каждом скане в миллионы строк — та цена, которую денормализацией убирают; целостность обеспечивается выше, в OLTP. Повторяющаяся низкокардинальная строка dictionary-encoded почти бесплатна, так что FK тут ничего не даёт.
Heads-up Денормализация OLTP умножает стоимость записи и возвращает аномалии обновления — противоположное тому, что нужно запись-тяжёлой транзакционной таблице. Каждый layout соответствует своей нагрузке; OLTP не делают похожим на OLAP.
Heads-up LowCardinality dictionary-encoded кодирует колонку, так что повторяющиеся значения становятся маленькими int — это большой выигрыш в сжатии и скане на колонке, повторяющейся миллионы раз, а не косметика.
Сниппет 3 — конфиг пайплайна
# Коннектор Debezium: стримит изменения WAL Postgres в Kafka, приземляет в склад
connector.class: io.debezium.connector.postgresql.PostgresConnector
plugin.name: pgoutput
table.include.list: public.orders,public.users,public.countries
snapshot.mode: initial # один бэкфилл, затем стрим изменений
# downstream sink батчит в orders_fact каждые 30s Викторина
Completed
Этот CDC-пайплайн стримит WAL в склад с 30s батчем sink. Что он даёт и какой компромисс надо принять?
Heads-up Захват WAL, транспорт и 30s батч sink каждый добавляют задержку; склад отстаёт от primary на небольшое окно. Смысл — изоляция с ограниченным лагом, а не нулевой лаг.
Heads-up CDC только перемещает данные; на скорость скана он не влияет. Скорость — от колоночного target (orders_fact / MergeTree), делающего pruning и сжатие. CDC — это feed, layout — это выигрыш.
Heads-up Весь смысл в том, что аналитика идёт на отдельный column store, а не на какую-либо реплику Postgres. Гонять её на реплике — это режим отказа, ради ухода от которого и существует CDC.
Сниппет 4 — ловушка write-amplification
-- Инженер «чинит» медленные дашборды, записывая orders прямо в ClickHouse,
-- по одному INSERT на заказ, из горячего пути checkout:
INSERT INTO orders_fact (order_id, user_id, country, total_cents, created_at)
VALUES (918273, 4412, 'DE', 4999, now()); -- вызывается ~5000×/s, по строке за раз Викторина
Completed
Почему проталкивание одиночных INSERT из горячего пути checkout прямо в column store даёт обратный эффект?
Heads-up Column store медленный на одиночных записях: каждая трогает каждый файл колонки и порождает мелкие part-ы для мёрджа. Ставить это на горячий путь checkout — ровно неверный layout для записи.
Heads-up Дедуп — отдельная забота. Ядро проблемы — форма записи: одиночные вставки патологичны для column store независимо от ключей; фикс — батчинг.
Heads-up Индексы не делают одиночные записи в column store дешёвыми — запись всё равно трогает каждый файл колонки. Фикс — батчить записи (или держать их в OLTP и переливать через CDC), а не индексировать.
Итог
Читаешь артефакты — и layout говорит тебе стоимость: Seq Scan, читающий десятки GB на row store, означает агрегат по всей таблице, который не починит ни один индекс — фикс — column store, делающий pruning. Нормализованный OLTP и денормализованный низкокардинальный OLAP-факт противоположны по дизайну, а не баг. CDC покупает изоляцию ценой ограниченного лага, и сам по себе CDC не делает сканы быстрыми — это делает колоночный target. А одиночные записи в column store патологичны: держи записи в row store, батчи их в column store. Диагностируй по плану и layout, чини layout, затем проверь, что просканированные байты упали.