Когда один и тот же сложный SELECT с десятком JOIN кочует из отчёта в отчёт, его хочется спрятать за коротким именем. Для этого в postgresql есть представления (VIEW) - сохранённый запрос, к которому обращаются как к таблице. А когда такой запрос тяжёлый и его результат нужно отдавать быстро и часто, на сцену выходят материализованные представления, которые хранят уже посчитанные строки на диске. В этом уроке разберём, чем VIEW отличается от MATERIALIZED VIEW, какие представления можно обновлять напрямую, зачем нужен WITH CHECK OPTION, как работает REFRESH (и почему CONCURRENTLY спасает прод), и как выбрать одно или другое для отчётности и кэширования.
Как это работает
Обычное представление - это не данные, а имя для запроса. Когда вы пишете CREATE VIEW, постгрес просто запоминает текст SELECT в системном каталоге. Никакие строки при этом не копируются. В момент, когда вы делаете SELECT из представления, планировщик подставляет его определение в ваш запрос и выполняет всё заново на актуальных таблицах. Поэтому VIEW всегда показывает свежие данные, но и стоит ровно столько, сколько стоит исходный запрос - каждый раз.
Технически VIEW в PostgreSQL реализовано через систему правил (rules): под капотом создаётся таблица-пустышка и правило _RETURN, которое переписывает обращение к ней в исходный SELECT. Знать это полезно, чтобы понимать сообщения об ошибках, но в повседневной работе можно думать о представлении просто как о виртуальной таблице.
Некоторые представления обновляемые: в них можно делать INSERT, UPDATE и DELETE, и изменения уйдут в базовую таблицу. Чтобы это сработало автоматически, представление должно быть простым - один источник в FROM (таблица или другое обновляемое представление), без DISTINCT, GROUP BY, HAVING, оконных функций, агрегатов, UNION, LIMIT и без вычисляемых выражений в нужных столбцах. Как только появляется JOIN или агрегат, представление становится только для чтения, и тогда запись через него делают вручную через INSTEAD OF триггеры (тема урока про триггеры).
Здесь же пригодится WITH CHECK OPTION. Допустим, представление отбирает только строки бизнес-класса. Без этой опции через него можно вставить или перевести строку в экономкласс - она просто исчезнет из видимости представления, но в таблице останется. WITH CHECK OPTION запрещает такие операции: любая записанная через представление строка обязана удовлетворять его условию WHERE, иначе будет ошибка.
Материализованное представление - другая история. Это физическая таблица, в которую один раз записали результат запроса. Читается оно мгновенно, как обычная таблица, и под него можно строить индексы. Расплата в том, что данные застывают на момент последнего обновления: чтобы пересчитать содержимое, нужно вручную или по расписанию выполнить REFRESH MATERIALIZED VIEW. Это идеальный инструмент для тяжёлой аналитики и кэширования витрин, где небольшая задержка данных допустима, а скорость чтения критична.
Обычный REFRESH полностью пересоздаёт данные и на время работы держит блокировку ACCESS EXCLUSIVE - читатели ждут. Вариант REFRESH ... CONCURRENTLY пересчитывает результат в сторонке и аккуратно применяет разницу, не блокируя SELECT. За удобство берут плату: для CONCURRENTLY обязателен уникальный индекс на материализованном представлении, и сам пересчёт идёт медленнее обычного.
SQL и примеры
Простое обновляемое представление поверх таблицы aircrafts - только дальнемагистральные самолёты:
Код: Выделить всё
CREATE VIEW longhaul_aircrafts AS
SELECT aircraft_code, model, range
FROM aircrafts
WHERE range > 6000
WITH CHECK OPTION;Витрина для отчётности - выручка по рейсам. Это уже не обновляемое представление, потому что есть JOIN и агрегат:
Код: Выделить всё
CREATE VIEW flight_revenue AS
SELECT f.flight_id,
f.flight_no,
f.scheduled_departure::date AS dep_date,
count(tf.ticket_no) AS seats_sold,
sum(tf.amount) AS revenue
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
GROUP BY f.flight_id, f.flight_no, dep_date;Перенесём тяжёлую агрегацию пассажиропотока по аэропортам в материализованное представление:
Код: Выделить всё
CREATE MATERIALIZED VIEW airport_traffic AS
SELECT dep.airport_name AS airport,
count(*) AS departures
FROM flights f
JOIN airports dep ON dep.airport_code = f.departure_airport
GROUP BY dep.airport_name
WITH DATA;Код: Выделить всё
CREATE UNIQUE INDEX airport_traffic_uq ON airport_traffic (airport);
REFRESH MATERIALIZED VIEW CONCURRENTLY airport_traffic;Частые грабли
- Материализованное представление не обновляется само. Если данные в нём устарели - значит, никто не запустил REFRESH. Ставьте его на cron или pg_cron, а не надейтесь на автомагию.
- REFRESH без CONCURRENTLY блокирует SELECT на всё время пересчёта. На большой витрине это заметный простой для отчётов - на проде почти всегда нужен вариант CONCURRENTLY.
- Для CONCURRENTLY забыли уникальный индекс - команда падает с ошибкой. Индекс должен однозначно идентифицировать строку.
- Свежесозданное матпредставление с WITH NO DATA нельзя читать до первого REFRESH - SELECT вернёт ошибку, что данные ещё не сформированы.
- Изменили тип столбца или удалили колонку в базовой таблице - представление, зависящее от неё, ломается, а DROP COLUMN вообще не даст удалить столбец, на который смотрит VIEW (нужен CASCADE).
- CREATE OR REPLACE VIEW не даёт переименовать, удалить или переставить местами существующие столбцы - можно только добавить новые в конец. Для остального нужен DROP VIEW и пересоздание.
- Через сложное представление (с JOIN или агрегатом) нельзя писать напрямую - INSERT/UPDATE вернут ошибку. Запись делают через INSTEAD OF триггеры.
- Подключитесь к демобазе: psql -d demo.
- Создайте обычное представление business_seats со схемой мест бизнес-класса: SELECT по таблице seats с условием fare_conditions = 'Business', добавьте WITH CHECK OPTION.
- Попробуйте через него вставить место с fare_conditions = 'Economy' и убедитесь, что check option это запрещает.
- Создайте материализованное представление route_load: количество проданных мест (count по ticket_flights) с группировкой по flight_no через JOIN с flights.
- Замерьте время чтения: \timing on, затем SELECT из исходного агрегирующего запроса и SELECT из route_load - сравните.
- Постройте уникальный индекс на route_load и выполните REFRESH MATERIALIZED VIEW CONCURRENTLY route_load.
- Через \d+ route_load посмотрите определение и размер, затем уберите всё: DROP MATERIALIZED VIEW route_load и DROP VIEW business_seats.
- Чем принципиально отличается VIEW от MATERIALIZED VIEW по хранению данных и актуальности?
- Какие условия делают представление обновляемым, а что превращает его в read-only?
- Что именно проверяет WITH CHECK OPTION и какую проблему оно решает?
- Зачем для REFRESH ... CONCURRENTLY нужен уникальный индекс и чем этот режим лучше обычного REFRESH?
- В каком случае под отчёт лучше взять обычное представление, а в каком - материализованное?
- Почему после WITH NO DATA нельзя сразу читать материализованное представление?
