Тема 4. Проектирование структур баз данных — Средства проектирования структур БД: объекты

1. Архитектурная иерархия объектов каталога СУБД

Многоуровневая структура каталога:

Кластер СУБД (Cluster): единый экземпляр СУБД (фоновые процессы, Shared Buffers, WAL, общий сетевой сокет).
База данных (Database): логически изолированное хранилище данных внутри кластера.
Схема (Schema / Namespace): пространство имен внутри БД для модульного разделения сущностей.
Объекты схемы: таблицы, представления, последовательности, типы данных, индексы, триггеры.

-- Иерархия пространств имен
CREATE SCHEMA billing;
CREATE SCHEMA imaging;

-- Приоритет разрешения имен:
SET search_path TO billing, public;

-- Квалифицированное обращение:
SELECT * FROM billing.subscribers;

Изоляция модулей и безопасность

2. Пространства имен (SCHEMAS): декомпозиция монолита

Проблемы монолитной схемы public:

Коллизии имен при росте проекта (например, таблица users нужна и в модуле биллинга, и в модуле авторизации).
Невозможность гранулярного разграничения прав доступа между сервисами.
Сложность поддержки и визуализации схемы в CASE-системах.

Архитектурные преимущества схем:

Логическая группировка таблиц по доменам (hr, finance, audit).
Масштабирование многоарендных (Multi-tenant) приложений.
Гибкое управление путями поиска через search_path.

3. Последовательности (SEQUENCE): атомарные генераторы ключей

Физический смысл SEQUENCE:

Специальный объект СУБД, генерирующий монотонно растущие числа в разделяемой памяти (Shared Memory).

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

CREATE SEQUENCE billing.receipt_seq
    START WITH 100000
    INCREMENT BY 1
    MINVALUE 100000
    MAXVALUE 999999999
    CACHE 20;

-- Получение уникального значения:
SELECT nextval('billing.receipt_seq');

4. Поведение SEQUENCE при откатах транзакций (ROLLBACK)

Фундаментальный принцип генератора:

SEQUENCE не откатывается назад! Если транзакция вызвала nextval() и затем завершилась оператором ROLLBACK, выданное число теряется навсегда.

В первичных ключах неизбежно появляются пропуски (Gaps): 101, 102, 105...
Попытка сделать монотонную нумерацию без пропусков заблокировала бы всю систему в однопоточный режим.

Инженерный вывод:

Суррогатный первичный ключ (ID) предназначен только для уникальности и ссылочной целостности.
Номера строгой бухгалтерской отчетности (где пропуски запрещены законом) реализуются через отдельные сервисы с пессимистической блокировкой.

5. Доменные типы данных: ENUM и COMPOSITE TYPES

Перечислимый тип (ENUM):

Фиксированный набор строковых меток на уровне системного каталога.
Защита от опечаток и некорректных статусов в бизнес-логике.
Внутреннее компактное хранение (4 байта в памяти вместо строк).

CREATE TYPE imaging.modality_type 
AS ENUM ('CT', 'MRI', 'XRAY', 'US');

Составной тип (COMPOSITE):

Пользовательская структура из нескольких именованных атрибутов.
Может выступать типом столбца таблицы или аргументом процедуры.

CREATE TYPE logistics.geo_point AS (
    lat NUMERIC(9, 6),
    lon NUMERIC(9, 6)
);

6. Динамические представления (VIEW): виртуальные таблицы

Механизм работы CREATE VIEW:

0 байт на диске: сохраняется только SQL-запрос в каталоге pg_views.
Оптимизатор СУБД на лету объединяет дерево разбора представления с клиентским запросом (Query Rewrite).
Всегда актуальные данные (Real-time).

-- Защита персональных данных через VIEW
CREATE VIEW imaging.v_patient_public AS
SELECT 
    patient_id,
    study_id,
    'PATIENT_' || patient_id AS anon_code,
    study_date,
    modality
FROM imaging.patients;

Security through Views: скрытие ПДн

7. Материализованные представления (MATERIALIZED VIEW)

Физический снимок данных:

Результат сложного аналитического запроса однократно рассчитывается и сохраняется на диске в виде таблицы.

Мгновенный отклик: агрегация миллионов строк уже выполнена заранее.
Поддержка индексов: по столбцам витрины можно строить B-Tree и GIN индексы!
Контролируемое отставание: данные статичны до вызова команды REFRESH.

CREATE MATERIALIZED VIEW billing.mv_debt_report AS
SELECT 
    district_id,
    sum(accrual_amount) AS total_accrued,
    sum(paid_amount) AS total_paid
FROM billing.accruals
GROUP BY district_id;

-- Создание индекса по витрине:
CREATE INDEX idx_mv_debt_district 
ON billing.mv_debt_report(district_id);

8. Архитектурный батл: VIEW vs MATERIALIZED VIEW

Критерий	Обычное VIEW	MATERIALIZED VIEW
Место на диске	0 байт (только текст запроса)	Физические страницы данных + индексы
Свежесть данных	100% реальное время	Статичный снимок на момент REFRESH
Собственные индексы	Не поддерживаются	Поддерживаются в полном объеме
Скорость чтения	Зависит от базового запроса	Мгновенно (O(log N))
Основная роль	Безопасность и сокрытие полей	Тяжелая аналитика, витрины данных (OLAP)

9. Регламент актуализации: REFRESH CONCURRENTLY

Проблема эксклюзивной блокировки:

Базовый оператор REFRESH MATERIALIZED VIEW matview_name; накладывает замок AccessExclusiveLock — на время обновления чтение витрины полностью блокируется!

Решение: CONCURRENTLY (без простоя):

Клиенты продолжают свободно читать старый снимок.
СУБД создает временную копию и накладывает дифференциальный патч.
Обязательное условие: наличие UNIQUE INDEX по полям витрины!

CREATE UNIQUE INDEX idx_mv_pk 
ON billing.mv_debt_report(district_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY 
billing.mv_debt_report;

10. Разграничение доступа: Роли, схемы и представления

Принцип наименьших привилегий (Least Privilege):

Пользователям и прикладным микросервисам запрещается прямой доступ к таблицам схем (REVOKE ALL).
Доступ открывается только к специально спроектированным представлениям (GRANT SELECT ON view_name).
Скрытие конфиденциальных столбцов (хэши паролей, персональные данные, коммерческая тайна).

-- Создание роли с логином
CREATE ROLE doctor_read WITH LOGIN PASSWORD '***';

-- Разрешение входа в схему:
GRANT USAGE ON SCHEMA imaging TO doctor_read;

-- Доступ только к безопасному представлению:
GRANT SELECT ON imaging.v_patient_public 
TO doctor_read;

11. CASE-инструменты проектирования структур баз данных

pgModeler

Нативный Open Source инструмент для PostgreSQL. Поддержка всех типов объектов, наглядные ERD, идеальный чистый DDL.

DBeaver

Универсальный инструмент аналитика и разработчика. Визуализация ERD на лету, редактор объектов схемы и связей.

dbForge Studio

Промышленная среда моделирования, визуального конструирования запросов и версионирования схем.

ERwin Data Modeler

Корпоративный эталон сквозного моделирования: от концептуальной модели к физической структуре СУБД.

12. Forward & Reverse Engineering: двунаправленная DDL-инженерия

Forward Engineering (Прямое проектирование):

Визуальная ERD-модель → DDL-скрипт (schema.sql)

Проектировщик расставляет сущности, связи, представления и типы в GUI.
CASE-пакет компилирует модель в набор операторов CREATE SCHEMA, CREATE TABLE, CREATE VIEW.
Скрипт накатывается на чистый сервер СУБД.

Reverse Engineering (Обратное проектирование):

Работающая БД → Визуальная ERD-диаграмма

CASE-инструмент подключается к боевой БД через JDBC-драйвер.
Интроспекция системного каталога считывает все ограничения и типы.
Генерируется наглядная диаграмма для аудита и рефакторинга архитектуры.

13. Инженерные правила проектирования структур БД

1. Модульность

Разделяйте подсистемы по схемам (billing, logistics, auth). Не используйте схему public как свалку таблиц.

2. Целевые представления

Используйте VIEW для сокрытия чувствительных данных и упрощения JOIN. Используйте MATERIALIZED VIEW для тяжелых отчетов.

3. Атомарность ключей

Генерируйте первичные ключи через SEQUENCE. Не бойтесь пропусков номеров — это плата за параллелизм и производительность.

14. Архитектурная строгость: цена ошибок в объектах БД

xkcd 327: Exploits of a Mom (Little Bobby Tables)

Тематический интернет-мем: «Exploits of a Mom» (культовый веб-комикс про «Little Bobby Tables», xkcd №327).
Автор: Randall Munroe. Первоисточник: xkcd.com/327. Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Связь с темой занятия: Проектирование структур и объектов БД требует абсолютной архитектурной строгости. Отказ от прямого доступа к таблицам в пользу защищенных представлений (VIEW), строгая типизация (ENUM) и гранулярная настройка прав доступа (DCL) исключают внедрение SQL-инъекций и катастрофическую потерю системных объектов каталога при атаках вида Robert'); DROP TABLE Students;--.