Представления: запрос или сохранённый результат

Представления: сохранённый запрос или сохранённый результат

Представление (view) — запрос, сохранённый в базе под именем. К нему можно обращаться почти как к таблице, но обычное представление не хранит собственные строки: исходный запрос выполняется при чтении.

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

#Обычное представление

CREATE VIEW reporting.order_summary AS
SELECT
    o.id AS order_id,
    o.org_id,
    o.status,
    o.total_amount,
    o.created_at
FROM marketplace.orders AS o;

Теперь клиент может выполнить:

SELECT order_id, total_amount
FROM reporting.order_summary
WHERE org_id = $1
ORDER BY created_at DESC, order_id DESC;

PostgreSQL обычно планирует запрос представления вместе с внешним условием. ORDER BY внутри определения не гарантирует порядок внешнего результата: клиент всё равно должен задавать свой порядок.

#Можно ли изменять данные через представление

Простое представление над одной таблицей часто автоматически принимает INSERT, UPDATE и DELETE. Если оно показывает только часть строк, WITH CHECK OPTION не позволяет создать строку, которая сразу исчезнет из этой части:

CREATE VIEW app.open_tickets AS
SELECT id, org_id, title, status
FROM support.tickets
WHERE status <> 'closed'
WITH LOCAL CHECK OPTION;

Попытка вставить через это представление билет со статусом closed будет отклонена. Это правило именно представления, а не полная политика доступа к таблице. Для ограничения строк во всех путях нужна RLS, которую разберём позже.

#Чьи права действуют

Представление может выполнять запрос с правами владельца или вызывающей роли. Для представления поверх RLS часто нужны права вызвавшего пользователя:

CREATE VIEW app.tenant_notes
WITH (security_invoker = true, security_barrier = true) AS
SELECT id, org_id, body
FROM private.tenant_notes;

security_invoker = true означает, что доступ к исходной таблице и её RLS проверяются для вызывающей роли. security_barrier = true ограничивает опасное переставление пользовательских условий относительно защитного фильтра, но может уменьшить свободу оптимизации.

Такую границу нужно проверять отрицательными тестами от имени настоящей роли приложения: пользователь одной организации не должен увидеть чужую строку.

#Материализованное представление

Материализованное представление (materialized view) физически хранит результат запроса. Чтение становится дешевле, но сохранённые данные устаревают.

CREATE MATERIALIZED VIEW reporting.org_sales AS
SELECT
    org_id,
    count(*) AS order_count,
    sum(total_amount) AS revenue,
    max(updated_at) AS last_order_change
FROM marketplace.orders
GROUP BY org_id;

CREATE UNIQUE INDEX org_sales_org_key
ON reporting.org_sales (org_id);

Изменение orders не обновляет org_sales автоматически. Для полного пересчёта используется:

REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.org_sales;

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

#Как договориться о свежести

До материализации определите:

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

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

#Изменение представления без поломки клиентов

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

SELECT pg_describe_object(classid, objid, objsubid)
FROM pg_depend
WHERE refobjid = 'reporting.order_summary'::regclass;

Для несовместимого изменения безопаснее:

создать order_summary_v2 с новым набором столбцов;
перевести клиентов на новую версию;
понаблюдать за ошибками;
удалить старое представление отдельной миграцией.

CREATE OR REPLACE VIEW полезен для совместимых изменений, но не превращает несовместимый договор в безопасный.

#Практика

В лаборатории 15 вы сохраните продажи по организации в материализованном представлении, создадите уникальный индекс и выполните конкурентное обновление.

Подробнее: CREATE VIEW, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW.