Модуль 2. Проектирование хранилища данных на Postgres Pro
Одним из ключевых этапов построения хранилища данных является грамотное проектирование его логической и физической структуры. От того, насколько правильно спроектирована модель данных, зависит не только производительность запросов, но и надежность, масштабируемость и удобство сопровождения всей системы. В данном модуле мы разберем основные подходы к моделированию хранилищ данных, сравним модели Inmon, Kimball и Data Vault, рассмотрим, как их реализовать с учетом специфики Postgres Pro, и сформируем набор практических рекомендаций по выбору архитектуры под задачи аналитики.
1. Роль моделирования в архитектуре хранилища данных
Хранилище данных предназначено для консолидации, хранения и анализа информации, поступающей из различных операционных систем предприятия. В отличие от транзакционных баз данных, в которых основное внимание уделяется быстрой записи и точности, в аналитическом хранилище основная задача — обеспечить быстрый доступ к историческим данным, агрегациям, отчетности и прогнозированию.
Правильное моделирование хранилища позволяет:
- структурировать данные так, чтобы аналитикам было удобно с ними работать;
- минимизировать избыточность данных и повысить их согласованность;
- сократить издержки на хранение и обслуживание;
- упростить интеграцию с BI-системами;
- повысить скорость и предсказуемость выполнения аналитических запросов.
2. Классические подходы к моделированию DWH
Существует несколько методологических подходов к проектированию хранилищ данных. Наиболее известны три: подход Билла Инмона, подход Ральфа Кимбола и методология Data Vault.
2.1 Подход Инмона (Corporate Information Factory)
Билл Инмон — один из основателей концепции хранилищ данных. Его подход предполагает построение центрального нормализованного корпоративного хранилища, в которое собираются все данные организации.
Главные черты модели:
- использование третьей нормальной формы (3NF);
- ориентация на долгосрочную стабильность модели;
- создание витрин данных (data marts) на основе корпоративного хранилища для отдельных бизнес-подразделений;
- акцент на интеграцию данных из разнородных источников и обеспечение их качества.
Преимущества:
- гибкость при изменении структуры источников;
- единый источник истины для всей организации;
- высокая степень согласованности данных.
Недостатки:
- длительное внедрение;
- сложность изменения модели;
- значительные требования к качеству исходных данных.
2.2 Подход Кимбола (Dimensional Modeling)
Ральф Кимбол предложил другой путь: создание хранилища данных как набора витрин, построенных по принципу многомерного моделирования (звезд и снежинок). Ключевые особенности:
- использование денормализованных таблиц фактов и измерений;
- ориентация на потребности бизнеса и простоту запросов;
- быстрый старт с построения витрин для отдельных направлений;
- постепенное расширение хранилища по мере необходимости.
Преимущества:
- простота построения и масштабирования;
- высокая производительность аналитических запросов;
- удобство для BI-систем.
Недостатки:
- возможна дубликация данных;
- трудности с обеспечением целостности при большом количестве витрин;
- проблемы с интеграцией изменений в источниках.
2.3 Data Vault
Методология Data Vault сочетает в себе достоинства обоих подходов. Она делит данные на три типа сущностей:
- хабы — ключевые бизнес-объекты (например, клиент, продукт);
- ссылки — связи между хабами;
- сателлиты — атрибуты, связанные с хабами или ссылками, включая исторические изменения.
Преимущества:
- отличная масштабируемость;
- прозрачное отслеживание истории изменений;
- удобство автоматизации и документирования.
Недостатки:
- сложность структуры, непривычная для бизнес-пользователей;
- необходимость создания витрин для большинства аналитических задач.
3. Выбор модели под Postgres Pro
Postgres Pro позволяет реализовать любую из вышеописанных моделей, однако с учетом особенностей отечественной платформы и практики внедрения наиболее часто применяются два варианта:
- подход Кимбола — для компактных хранилищ, быстрых запусков и BI-ориентированных архитектур;
- Data Vault — для корпоративных хранилищ, где важна прослеживаемость, историзация и стандартизация процессов.
Нормализованная модель по Инмону применяется реже, поскольку требует высокого уровня зрелости данных в источниках и длительной фазы подготовки. Однако Postgres Pro поддерживает все средства для её реализации — от декларативного секционирования до представлений, агрегатов и журналирования изменений.
4. Практическое моделирование в Postgres Pro
4.1 Работа со звездной схемой
В типичной реализации подхода Кимбола в Postgres Pro:
- таблицы фактов секционируются по времени;
- измерения загружаются с поддержкой медленно изменяющихся атрибутов;
- используются индексы BTree и BRIN в зависимости от плотности и объема данных;
- данные периодически агрегируются в материализованных представлениях.
Пример: таблица fact_sales хранит информацию о продажах с ежедневной секцией, таблица dim_customer содержит информацию о клиентах с историей изменений.
4.2 Реализация Data Vault
При использовании Data Vault:
- хабы строятся как таблицы с бизнес-ключом и техническими метками;
- ссылки реализуются как отдельные таблицы со связями между хабами;
- сателлиты хранят атрибуты и дату действия, что обеспечивает историзацию.
Postgres Pro позволяет использовать уникальные и частично уникальные индексы, работать с представлениями и секционированными таблицами, что критически важно для реализации Data Vault в масштабах предприятия.
4.3 Историзация и слежение за изменениями
Postgres Pro предоставляет механизмы для фиксации изменений, в том числе:
- логическая репликация и лог изменений (logical decoding);
- расширения для CDC (Change Data Capture);
- пользовательские триггеры и правила для захвата изменений.
Это позволяет строить полноценные сателлиты Data Vault и управлять версиями данных.
5. Использование специфики Postgres Pro при проектировании
Некоторые возможности Postgres Pro, которые особенно важны на этапе проектирования:
- Секционирование: автоматическое управление секциями, поддержка вставки в правильный раздел.
- Поддержка расширений: pg_pathman для динамического секционирования, pgpro_scheduler для регулярной загрузки.
- Индексы: использование BRIN для больших исторических таблиц, GIN и GiST для работы с текстами и массивами.
- Материализованные представления: позволяют кешировать сложные агрегаты.
- Механизмы безопасности: аудит доступа, шифрование, контроль версий данных.
6. Рекомендации по выбору архитектуры
Выбор модели зависит от задач, зрелости источников и доступных ресурсов. Общие рекомендации:
- для проектов с жесткими сроками и задачами построения отчетности разумно начинать с подхода Кимбола;
- для долгосрочных проектов с высокой степенью автоматизации и требованиями к хранению истории — лучше использовать Data Vault;
- для зрелых компаний с централизованными данными, где важна единая модель — возможна реализация подхода Инмона.
Postgres Pro одинаково хорошо поддерживает все три подхода, особенно в версии Enterprise, где расширены средства управления секционированием, мониторингом и безопасностью.
Пример 1. Звездная схема (Kimball)
1. Таблица фактов: Продажи
CREATE TABLE fact_sales (
SaleDate DATE NOT NULL,
CustomerID INT,
ProductID INT,
StoreID INT,
Amount NUMERIC(14,2),
Quantity INT
) PARTITION BY RANGE (SaleDate);
Пример секции:
CREATE TABLE fact_sales_2024_01
PARTITION OF fact_sales
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
Индексы:
CREATE INDEX idx_sales_date ON fact_sales_2024_01 (SaleDate); CREATE INDEX idx_sales_customer ON fact_sales_2024_01 (CustomerID);
2. Таблица измерения: Клиенты
CREATE TABLE dim_customer (
CustomerID INT PRIMARY KEY,
FullName TEXT,
BirthDate DATE,
City TEXT,
Region TEXT
);
3. Таблица измерения: Продукты
CREATE TABLE dim_product (
ProductID INT PRIMARY KEY,
ProductName TEXT,
Category TEXT,
Price NUMERIC(10,2)
);
4. Материализованное представление: Аггрегаты по месяцам
CREATE MATERIALIZED VIEW mv_sales_monthly AS
SELECT
DATE_TRUNC('month', SaleDate) AS Month,
ProductID,
SUM(Amount) AS TotalAmount,
SUM(Quantity) AS TotalQuantity
FROM fact_sales
GROUP BY Month, ProductID;
Обновление:
REFRESH MATERIALIZED VIEW mv_sales_monthly;
Пример 2. Data Vault
1. Хаб: Клиент
CREATE TABLE hub_customer (
CustomerHash TEXT PRIMARY KEY,
BusinessKey TEXT NOT NULL,
LoadDate TIMESTAMP NOT NULL,
RecordSource TEXT NOT NULL
);
2. Сателлит: Атрибуты клиента
CREATE TABLE sat_customer (
CustomerHash TEXT NOT NULL,
FullName TEXT,
BirthDate DATE,
City TEXT,
LoadDate TIMESTAMP NOT NULL,
EffectiveFrom TIMESTAMP NOT NULL,
EffectiveTo TIMESTAMP,
RecordSource TEXT,
PRIMARY KEY (CustomerHash, LoadDate)
);
Индексдля поиска последней версии:
CREATE INDEX idx_sat_customer_latest ON sat_customer (CustomerHash, EffectiveFrom DESC);
3. Хаб: Магазин
CREATE TABLE hub_store (
StoreHash TEXT PRIMARY KEY,
BusinessKey TEXT NOT NULL,
LoadDate TIMESTAMP NOT NULL,
RecordSource TEXT NOT NULL
);
4. Линк: Клиент–Магазин
CREATE TABLE link_customer_store (
LinkHash TEXT PRIMARY KEY,
CustomerHash TEXT NOT NULL,
StoreHash TEXT NOT NULL,
LoadDate TIMESTAMP NOT NULL,
RecordSource TEXT NOT NULL
);
Пример 3. Нормализованная модель (Inmon)
Предположим, что хранилище построено по предметной области "Финансы".
1. Таблица операций
CREATE TABLE fin_operation (
OperationID UUID PRIMARY KEY,
OperationDate DATE,
AccountID UUID,
CounterpartyID UUID,
Amount NUMERIC(14,2),
Currency CHAR(3),
OperationType TEXT
);
2. Справочник счетов
CREATE TABLE ref_account (
AccountID UUID PRIMARY KEY,
AccountName TEXT,
Currency CHAR(3),
OwnerID UUID
);
3. Справочник контрагентов
CREATE TABLE ref_counterparty (
CounterpartyID UUID PRIMARY KEY,
Name TEXT,
INN TEXT,
Region TEXT
);
4. Представление для аналитики
CREATE VIEW vw_operation_summary AS
SELECT
o.OperationDate,
a.AccountName,
c.Name AS Counterparty,
o.OperationType,
SUM(o.Amount) AS TotalAmount
FROM fin_operation o
JOIN ref_account a ON o.AccountID = a.AccountID
JOIN ref_counterparty c ON o.CounterpartyID = c.CounterpartyID
GROUP BY o.OperationDate, a.AccountName, c.Name, o.OperationType;Вы можете подключить:
- pg_pathman — для продвинутого секционирования;
- pgpro_scheduler — для автоматического обновления витрин;
- pg_stat_statements — для анализа запросов;
- pgpro_stats — расширенная статистика по нагрузке.
8. Нормализация и денормализация данных в аналитическом хранилище
Что такое нормализация
Нормализация — это процесс структурирования данных в базе с целью минимизации избыточности и обеспечения целостности. Применяется в транзакционных (OLTP) базах, но также может использоваться в хранилищах при построении ядра (Core DWH), особенно при использовании подхода Инмона.
Типичные признаки нормализованной модели:
- каждая сущность описана своей таблицей;
- данные не повторяются;
- связи реализованы через внешние ключи;
- используется третья нормальная форма (3NF) или выше.
Плюсы нормализации:
- уменьшение объема хранения;
- простота сопровождения и изменения модели;
- высокий уровень согласованности данных.
Минусы:
- большое количество JOIN-запросов;
- снижение производительности аналитических операций;
- усложнение витрин данных.
Что такое денормализация
Денормализация — это объединение связанных сущностей в одну таблицу, чаще всего ради производительности и удобства аналитических запросов. Она характерна для витрин данных (data marts), а также для моделей Кимбола и частично Data Vault.
Типичные признаки денормализованной модели:
- таблицы содержат агрегированные или повторяющиеся данные;
- измерения могут встраиваться в таблицы фактов;
- часто используются представления и материализованные витрины.
Плюсы денормализации:
- высокая производительность запросов;
- удобство построения отчетов в BI-инструментах;
- простота анализа для конечных пользователей.
Минусы:
- возможна дубликация данных;
- рост объема хранения;
- сложность внесения изменений в структуру.
Где применяется в Postgres Pro
Postgres Pro позволяет эффективно использовать обе стратегии:
- для нормализованной модели — поддержка внешних ключей, проверок и транзакционной целостности;
- для денормализованной — секционирование, материализованные представления, индексы BRIN, параллельные запросы.
Хорошей практикой считается комбинация: на нижних уровнях хранилища (ODS и Core DWH) использовать нормализованные структуры, а для отчетности и BI — денормализованные витрины.
9. Выбор подходящей модели для конкретных бизнес-задач
Как выбирать архитектуру хранилища
Выбор архитектурной модели зависит от:
- целей проекта;
- зрелости организации;
- объема и качества исходных данных;
- требований к историчности;
- степени централизации управления данными;
- доступных ресурсов и компетенций.
Подход Кимбола: для чего подходит
Рекомендуется, если:
- требуется быстрое внедрение;
- пользователи хотят сразу видеть отчеты;
- источники стабильны;
- объем данных умеренный;
- BI-система — основной потребитель.
Типичные задачи:
- управленческая отчетность;
- маркетинговая аналитика;
- контроль продаж;
- построение витрин.
Пример: торговая компания хочет еженедельно анализировать продажи и эффективность рекламных кампаний. Реализация: таблица фактов продаж, таблицы измерений (товар, магазин, клиент), представления по неделям.
Подход Инмона: для чего подходит
Рекомендуется, если:
- хранилище строится централизованно;
- требуется единая версия данных для всего холдинга;
- есть множество источников с разнородными структурами;
- высокая ценность согласованности данных;
- в компании приняты строгие процедуры ИТ.
Типичные задачи:
- финансовая консолидация;
- централизованное планирование;
- исторический учет и аудит.
Пример: крупный холдинг собирает данные из нескольких ERP-систем. Требуется согласованная модель учета контрактов, оплат и начислений. Реализация: нормализованная модель ядра хранилища, витрины строятся на втором уровне.
Подход Data Vault: для чего подходит
Рекомендуется, если:
- данные нестабильны и часто меняются;
- важна историзация, отслеживание изменений;
- есть желание автоматизировать генерацию моделей;
- требуется масштабируемость и модульность;
- проект должен быть живучим при изменении требований.
Типичные задачи:
- построение корпоративного DWH;
- регламентированная отчетность;
- аудит данных и lineage.
Пример: банк строит хранилище, где фиксируется весь путь клиента: от входа в мобильное приложение до расчетов и платежей. Реализация: хабы клиентов, транзакций и продуктов, сателлиты с историей изменений, ссылки между сущностями.
10. Практические советы по выбору модели
- Не существует универсального подхода — модель должна отражать логику бизнеса и потребности пользователей.
- На ранних этапах, когда важно быстро показать результат — подойдёт схема Кимбола.
- При высоких требованиях к качеству и контролю данных — стоит применять подход Инмона.
- Если архитектура должна быть гибкой, масштабируемой и пригодной к автоматизации — лучше выбрать Data Vault.
- В реальных проектах часто комбинируют методы: ядро строится по Data Vault или Инмону, а витрины по Кимболу.
Заключение
Проектирование хранилища данных — это критический этап, влияющий на успех всей аналитической платформы. Независимо от выбранной методологии, необходимо учитывать:
- структуру и объемы исходных данных;
- требования к отчетности и аналитике;
- необходимость хранения истории и управления изменениями;
- возможности платформы Postgres Pro, включая секционирование, индексацию, расширения и безопасность.
В следующем модуле мы рассмотрим, как подготовить инфраструктуру и развернуть Postgres Pro в виде высокопроизводительного и отказоустойчивого хранилища.
Postgres Professional — это российская промышленная СУБД, созданная на базе открытого PostgreSQL, но значительно расширенная для корпоративного применения. В отличие от классического PostgreSQL, решения от Postgres Professional включают в себя поддержку российских ГОСТов и сертификацию ФСТЭК, повышенную надёжность, оптимизации под высоконагруженные системы (в том числе 1С и DWH), инструменты резервного копирования, мониторинга и отказоустойчивости. За платформой стоит команда ядра PostgreSQL в России, что гарантирует актуальность, стабильность и экспертную техническую поддержку 24/7.
Для компаний, которым важно не просто использовать PostgreSQL, а внедрить его на уровне корпоративных стандартов — с гарантией, сопровождением, документированными улучшениями и адаптацией под российское законодательство — Postgres Pro Enterprise становится логичным выбором. Это не просто бесплатная база данных, а полноценный продуктовый стек, совместимый с BI, аналитикой, ERP, 1С и другими системами, в том числе импортозамещёнными.
