Витрина данных: проектирование под конкретные запросы

Прямой ответ

Витрина данных строится под конкретный набор запросов, поэтому проектирование начинается с перечня запросов, а не с перечня полей. Форма таблицы определяется тем, как данные читаются.

CREATE MATERIALIZED VIEW mart.sales_daily
AS
SELECT
    date_trunc('day', o.created_at)  AS day,
    c.region,
    p.category,
    COUNT(*)                         AS orders_cnt,
    SUM(o.amount_minor)              AS revenue_minor
FROM dwh.orders o
JOIN dwh.customers c ON c.id = o.customer_id
JOIN dwh.products  p ON p.id = o.product_id
GROUP BY 1, 2, 3;

CREATE UNIQUE INDEX idx_sales_daily ON mart.sales_daily (day, region, category);

Материализованное представление хранит результат запроса, поэтому чтение не выполняет соединения и агрегацию.

Денормализация устраняет соединения за счёт дублирования значений. Агрегация уменьшает объём данных за счёт потери детализации: запрос по отдельному заказу к агрегированной витрине невозможен.

О выборе модели данных — в статье про звезду и снежинку.

Обновление витрины и согласованность с источником

Витрина — производная структура, поэтому два вопроса определяют эксплуатацию.

Способ обновления. Полное перестроение заменяет содержимое целиком и просто в реализации, но требует времени и ресурсов. Инкрементальное обновление добавляет изменившиеся строки и быстрее, но требует определения изменения; о выборе — в статье про полное и инкрементальное обновление.

Момент согласованности. Витрина обновляется по расписанию, поэтому между обновлением источника и обновлением витрины данные расходятся. Допустимая задержка определяется потребителем.

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

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

BEGIN;
ALTER TABLE mart.sales_daily RENAME TO sales_daily_old;
ALTER TABLE mart.sales_daily_new RENAME TO sales_daily;
COMMIT;
DROP TABLE mart.sales_daily_old;

Отдельный аспект — индексация витрины. Индексы строятся под условия запросов витрины, а не наследуются из источника; о типах индексов — в статье про оптимизацию SQL-запросов.

Размер витрины и стоимость сопровождения

Витрина занимает место и требует сопровождения, поэтому число витрин ограничивается фактической потребностью.

Объём хранения. Денормализация повторяет значения связанных таблиц, поэтому объём витрины превышает объём агрегированных данных. При большом числе витрин стоимость хранения становится заметной статьёй расходов.

Время обновления. Перестроение витрины требует чтения исходных данных и записи результата, поэтому время обновления растёт с объёмом. Витрина, которая не успевает обновиться между запусками, указывает на избыточный объём либо на неверный уровень агрегации.

Число потребителей. Витрина, которую читает один отчёт, оправдана только при высокой частоте запросов. Витрина, которую читают несколько потребителей, окупается быстрее, поскольку стоимость обновления распределяется.

Практическое следствие: перед созданием витрины оценивается число потребителей и частота запросов. Витрина под единственный редкий запрос не окупает стоимость обновления и хранения.

Отдельный аспект — пересмотр формы. Изменение запросов потребителя делает форму витрины несоответствующей, поэтому при изменении перечня запросов форма пересматривается, а не дополняется полями.

Практика: проектирование и сопровождение витрины

1. Начинайте с перечня запросов. Форма витрины определяется условиями фильтрации, группировки и составом полей в реальных запросах.

2. Денормализуйте только используемые соединения. Дублирование всех связанных полей увеличивает объём без выигрыша в скорости.

3. Агрегируйте до уровня запроса. Агрегация ниже уровня запроса теряет детализацию, выше — не даёт выигрыша.

4. Обновляйте витрину атомарно. Замена таблицы в транзакции исключает чтение частично обновлённых данных.

5. Индексируйте под условия запросов витрины. Индекс по полю, не используемому в фильтрации, занимает место без пользы.

6. Фиксируйте допустимую задержку обновления. Потребитель должен знать, на какой момент актуальны данные.

7. Проверяйте скорость на реальных запросах. Тестовый запрос не отражает распределение реальных условий фильтрации.

Рекомендации:

1. Различайте витрину и интегрированный слой. Витрина строится под конкретные запросы, интегрированный слой содержит согласованную модель; о слоях — в статье про слои хранилища.
2. Не создавайте универсальную витрину. Таблица, соответствующая всем запросам, не даёт выигрыша ни в одном.
3. Учитывайте стоимость хранения. Денормализация увеличивает объём, поэтому число витрин ограничивается фактической потребностью.
4. Проверяйте согласованность с источником. Периодическая сверка агрегатов выявляет расхождение, возникшее при обновлении.
5. Документируйте назначение витрины. Перечень запросов, под которые построена витрина, фиксируется, поскольку изменение запросов требует пересмотра формы.

Практический сценарий: витрина не ускорила отчёт

Контекст: витрина создана для ускорения отчёта, но время формирования не изменилось.

1. Диагностика. План запроса показал полное сканирование витрины. Отчёт фильтровал данные по региону и периоду, а индекс построен по идентификатору категории, который в условии не использовался.
2. Причина. Витрина спроектирована по составу полей, а не по условиям запросов. Агрегация выполнена по всем измерениям, поэтому объём витрины оказался сопоставим с объёмом исходных данных, а индекс не соответствовал фильтрации.
3. Действия. (а) Собрали перечень реальных запросов к отчёту с условиями фильтрации и группировки; (б) перестроили агрегацию до уровня, используемого в отчёте, исключив неиспользуемые измерения; (в) построили индекс по полям, входящим в условие фильтрации; (г) проверили план запроса после изменения и убедились в использовании индекса; (д) сократили объём витрины в несколько раз за счёт исключения неиспользуемых измерений.
4. Результат. Время формирования отчёта снизилось, поскольку запрос использует индекс и читает агрегированные данные требуемого уровня.

Вывод: витрина ускоряет запрос только при соответствии формы и индексации реальным условиям фильтрации. Проектирование по составу полей без анализа запросов даёт объёмную таблицу, которая не ускоряет чтение.

FAQ

Чем витрина отличается от обычного представления? Материализованная витрина хранит результат запроса, поэтому чтение не выполняет соединения и агрегацию. Обычное представление вычисляется при каждом запросе.

Когда агрегация в витрине не подходит? Когда запросы требуют детализации ниже уровня агрегации. Агрегированные данные не позволяют вернуть отдельную запись.

Как обновлять витрину без влияния на читателей? Перестроением в отдельную таблицу с заменой в транзакции, чтобы читатель не видел частично обновлённые данные.

Нужно ли индексировать витрину? Да, под условия фильтрации реальных запросов. Индекс по неиспользуемому полю занимает место без выигрыша.

Источники: PostgreSQL materialized views, ClickHouse views, PostgreSQL index types — ссылки в шапке.