«Сколько выручки принёс каждый магазин за январь?» — казалось бы, безобидный вопрос от бизнеса. А он способен положить рабочую базу приложения. Причём дело не в размере данных, а в том, что это запрос из другого мира. Базы данных и запросы к ним делятся на два больших класса, и устроены эти классы противоположно: что хорошо для одного, мучительно для другого. Разберём оба мира и мост между ними.
Два способа обращаться к данным
OLTP (online transaction processing) — это мир приложений, обычная работа с пользователями. Кто-то оформил заказ, открыл профиль, обновил корзину. Каждый такой запрос трогает несколько строк, найденных по ключу: нашли по индексу, прочитали или изменили, ответили за миллисекунды. Данные тут — актуальное состояние мира прямо сейчас. Именно под это заточены B-деревья и вся дисциплина транзакций.
OLAP (online analytical processing) — это мир аналитики. Вопросы задаёт уже не пользователь, а аналитик: «на сколько больше бананов продали во время акции, чем обычно?». Такой запрос пробегает по миллионам строк, но из каждой берёт всего два-три столбца и сворачивает их в один итог — сумму, количество, среднее. Данные тут — история всех событий за годы. И узкое место совсем другое: не «как быстро найти нужную строку», а «сколько байт в секунду мы успеваем прокачать через сканирование».
Один и тот же язык SQL умеет и то и другое. Но хранить данные под эти два вида запросов выгодно по-разному — и отсюда всё дальнейшее.
Склад данных и ETL
Пускать аналитиков напрямую в рабочую базу приложения — плохо с обеих сторон. Их тяжёлые запросы-сканы отъедают ресурсы у пользовательских транзакций (и в час пик пользователи это почувствуют). А ещё в большой компании OLTP-систем много — сайт, склад, доставка, CRM, — и одним запросом их всё равно не опросишь.
Поэтому аналитику выносят в отдельную базу — склад данных (data warehouse). Это read-only копия данных из всех OLTP-систем, собранная в одном месте. Собирает её процесс ETL — три шага: extract (извлечь из источников), transform (привести к удобной для аналитики форме), load (загрузить в склад). Работает он либо периодическими выгрузками (например, ночью), либо непрерывным потоком событий.
Маленькой компании склад не нужен — её данные помещаются в обычный PostgreSQL, а то и в электронную таблицу. Склад появляется, когда OLTP-систем становится много, а истории накопились терабайты.
Схема «звезда»: факты и измерения
В мире приложений схемы баз разные под каждую задачу. А вот аналитические склады почти все устроены одинаково — по схеме «звезда».
В центре звезды — таблица фактов: одна строка на каждое событие («покупатель купил такой-то товар в такой-то момент»). Столбцов у неё сотни, строк — миллиарды. Вокруг центра — таблицы измерений: товар, магазин, покупатель, дата, акция. Факт ссылается на измерения по внешним ключам, а сами измерения отвечают на вопросы «кто, что, где, когда и почему» про каждое событие.
Даже дата выносится в отдельное измерение — таблицу со строкой на каждый календарный день и признаками вроде «праздник/будни». Без этого не спросишь «как продажи в выходные против будней». (Есть вариант, где измерения ещё сильнее разбиты на подтаблицы, — его называют «снежинкой», — но на практике чаще выигрывает более «плоская» звезда: с ней аналитику проще работать.)
Столбцовое хранение: почему аналитика летает
Обычные OLTP-базы хранят данные построчно — вся строка лежит на диске одним куском. Это идеально для «прочитай заказ целиком». Но аналитический запрос из таблицы в сотню столбцов трогает всего три — а построчная база всё равно вынуждена поднять с диска строки целиком, со всеми ненужными столбцами.
Столбцовое хранилище переворачивает раскладку: теперь каждый столбец лежит отдельным файлом, а строка восстанавливается по позиции (пятое значение в каждом файле — это одна и та же пятая строка). Запрос читает только те столбцы, что ему нужны, — и это уже выигрыш в разы. Но дальше включается второй, ещё более сильный эффект. Значения внутри одного столбца похожи друг на друга (в столбце «товар» миллиарды строк, но уникальных значений — тысяч десять), поэтому столбцы прекрасно сжимаются. Столбец на миллиарды строк ужимается до мегабайт, фильтры превращаются в быстрые побитовые операции, и сканирование упирается уже не в диск, а в кеш процессора.
Ровно так устроены ClickHouse, Vertica, Redshift и формат файлов Parquet. И именно поэтому ClickHouse — не замена PostgreSQL, а инструмент другого мира. Плата за скорость чтения — дорогая запись: вставить одну строку «в середину» сжатых отсортированных столбцов нельзя, поэтому столбцовые базы принимают записи по LSM-подходу — копят в памяти и сбрасывают пачками.
Материализованные сводки
Если тысяча запросов в день считает SUM(net_price) по одним и тем же осям, честно сканировать всё каждый раз — расточительство. Ответ можно посчитать заранее и сохранить в материализованном представлении — таблице с уже готовым результатом запроса (в PostgreSQL это materialized view). Крайняя форма этой идеи — OLAP-куб: заранее посчитанная сетка итогов по комбинациям измерений (дата × товар × магазин), где «выручка за вчера» — это одно чтение ячейки вместо перебора миллионов строк.
Плата — гибкость. В кубе нельзя спросить то, чего нет среди его измерений (например, «долю продаж товаров дешевле 100 ₽», если цена не заведена как измерение). Поэтому склады всё равно хранят сырые события, а сводки держат сверху — как ускоритель для самых частых запросов.
Где это применяется
Развилка «OLTP или OLAP» всплывает раньше, чем кажется. Первый же дашборд «продажи по дням» — это уже аналитический запрос, и вопрос «гонять его по рабочей базе или завести отдельное хранилище» — ровно об этой статье. Ответ по шагам: пока данных немного — хватит читающей реплики PostgreSQL и материализованных представлений; когда история разрослась и тяжёлые сканы стали обычным делом — заводят отдельное столбцовое хранилище и поток событий в него.
Где спотыкаются начинающие:
- Гоняют аналитику по рабочей базе. Один тяжёлый скан в час пик — и время ответа обычных пользовательских запросов уезжает в хвост.
- Строят одну «универсальную» схему на всё. Нормализованная OLTP-схема неудобна аналитику, а звезда неудобна приложению. Это два разных представления одних и тех же данных — и это нормально.
- Берут ClickHouse под точечные чтения («найди заказ по id») — на таком паттерне столбцовое хранилище вчистую проигрывает PostgreSQL. И наоборот.
- Прячут все ответы в кубы. Сводка без сырых событий — тупик: первый же новый вопрос бизнеса потребует измерения, которого в кубе нет.
Что почитать дальше: B-деревья и LSM — движки, которые стоят под обоими мирами; PostgreSQL или ClickHouse — та же развилка на практике; моделирование в ClickHouse и материализованные представления PostgreSQL — оба мира в деле.