Аналитические базы: колонки, партиции и почему SELECT * дорого

Фрея · риск-менеджер необанка «Монетка»

Банк переносит операции в ClickHouse. Инженеры запретили нам SELECT * и требуют фильтр по дате в каждом запросе. Это бюрократия или правда что-то меняет? Мне нужна цифра.

В уроке про индексы индекс помогал найти несколько строк в большой таблице. Но типичный аналитический запрос ищет не иголку, а считает сумму по всему стогу: траты по категориям за месяц, выручку по дням. Для таких запросов придуманы отдельные базы. Сегодня разбираемся, почему они быстрые и почему инженеры просят того, что просят. Подробно ClickHouse изнутри разобран в уроке для инженеров данных, здесь угол аналитика.

Картотека и ведомость

Представь отдел кадров с картотекой: на каждого сотрудника карточка, на ней всё сразу, имя, отдел, зарплата, телефон. Найти одного человека легко: вытащил карточку, прочитал. А чтобы посчитать среднюю зарплату, придётся перебрать все карточки и в каждой пробежать глазами мимо имени и телефона.

Теперь другая организация: на каждое поле своя ведомость. Ведомость имён, ведомость отделов, ведомость зарплат. Средняя зарплата это одна ведомость сверху вниз, остальные не нужны.

PostgreSQL хранит данные как картотека: строка целиком лежит рядом. Это строковое хранение. ClickHouse, BigQuery и другие аналитические базы хранят как ведомости: каждый столбец отдельно. Это колоночное хранение. Запрос читает с диска только упомянутые столбцы.

Сколько весит каждый столбец

Прикинем на операциях «Монетки». Функция pg_column_size показывает, сколько байт занимает значение, а pg_column_size(t.*) вся строка:

SELECT sum(pg_column_size(merchant)) AS merchant,
       sum(pg_column_size(mcc))      AS mcc,
       sum(pg_column_size(t.*))      AS whole_rows
FROM transactions t;

столбец	байт
merchant	2 065 154
city	1 317 788
status	641 493
created_at	570 216
amount_rub	358 679
id, card_id, mcc	по 285 108
строки целиком	7 954 909

Строки целиком весят больше суммы столбцов: у каждой строки PostgreSQL есть служебный заголовок. Самые тяжёлые столбцы текстовые, название магазина и город.