Materialized View в PostgreSQL: кэшируем тяжёлые запросы

Когда запрос занимает несколько секунд и выполняется часто — пора думать о кэшировании на уровне базы. Materialized View сохраняет результат запроса на диск и позволяет читать его мгновенно.

VIEW vs MATERIALIZED VIEW

Обычный VIEW — это просто сохранённый запрос. При каждом обращении к нему PostgreSQL выполняет исходный запрос заново.

-- Обычный VIEW — выполняется при каждом SELECT
CREATE VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue,
    COUNT(*) AS orders_count
FROM orders
GROUP BY 1;

-- Каждый раз пересчитывает по всей таблице orders
SELECT * FROM monthly_revenue WHERE month >= '2026-01-01';

MATERIALIZED VIEW сохраняет результат физически:

-- Materialized VIEW — результат сохранён на диск
CREATE MATERIALIZED VIEW monthly_revenue_mv AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue,
    COUNT(*) AS orders_count
FROM orders
GROUP BY 1;

-- Читает из сохранённых данных — мгновенно
SELECT * FROM monthly_revenue_mv WHERE month >= '2026-01-01';

Создание и обновление

-- Создать с немедленным заполнением
CREATE MATERIALIZED VIEW top_customers_mv AS
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS orders_count,
    SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY lifetime_value DESC;

-- Создать пустым (заполнить позже)
CREATE MATERIALIZED VIEW top_customers_mv AS
SELECT ...
WITH NO DATA;

-- Обновить данные
REFRESH MATERIALIZED VIEW top_customers_mv;

REFRESH пересчитывает весь запрос и заменяет данные. Во время обычного REFRESH представление недоступно для чтения — это проблема для продакшена.

REFRESH CONCURRENTLY — обновление без блокировки

-- Для конкурентного обновления нужен уникальный индекс
CREATE UNIQUE INDEX ON top_customers_mv (id);

-- Теперь REFRESH не блокирует чтение
REFRESH MATERIALIZED VIEW CONCURRENTLY top_customers_mv;

CONCURRENTLY обновляет представление фоново: сначала вычисляет новые данные, затем атомарно заменяет строки. Во время обновления SELECT продолжает читать старые данные. Это занимает больше времени и ресурсов, но не блокирует пользователей.

Важно: CONCURRENTLY требует уникального индекса. Без него — ошибка.

Индексы на Materialized View

Одно из главных преимуществ перед обычным VIEW — возможность создавать индексы:

-- Индекс для быстрой фильтрации
CREATE INDEX ON top_customers_mv (lifetime_value DESC);
CREATE INDEX ON top_customers_mv (name);

-- После REFRESH индексы автоматически перестраиваются
REFRESH MATERIALIZED VIEW CONCURRENTLY top_customers_mv;

Автоматическое обновление через pg_cron или триггеры

PostgreSQL не обновляет Materialized View автоматически. Нужно настроить расписание:

-- Вариант 1: pg_cron (расширение)
SELECT cron.schedule('refresh-revenue', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_mv');

-- Вариант 2: триггер на исходной таблице (осторожно с производительностью)
CREATE OR REPLACE FUNCTION refresh_revenue_mv() RETURNS trigger AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_mv;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_refresh_revenue
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT EXECUTE FUNCTION refresh_revenue_mv();

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

Когда использовать Materialized View

Хорошие кейсы:

• Аналитические дашборды с тяжёлыми агрегациями (выручка по месяцам, топ клиентов)
• Отчёты которые строятся ночью или раз в час
• Сложные JOIN между большими таблицами с редко меняющимися данными
• Предрасчёт рейтингов, скоров, статистики

-- Пример: ежедневная статистика по продуктам
CREATE MATERIALIZED VIEW product_stats_mv AS
SELECT
    p.id,
    p.name,
    p.category,
    COUNT(oi.id) AS times_ordered,
    SUM(oi.quantity) AS total_sold,
    AVG(r.rating) AS avg_rating
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name, p.category;

CREATE UNIQUE INDEX ON product_stats_mv (id);
CREATE INDEX ON product_stats_mv (category, avg_rating DESC);

Плохие кейсы:

• Данные меняются каждую секунду и нужна актуальность в реальном времени
• Запрос уже быстрый — незачем усложнять
• Исходные таблицы маленькие

Materialized View vs TEMP TABLE

-- TEMP TABLE — существует только в рамках сессии
CREATE TEMP TABLE revenue_temp AS
SELECT DATE_TRUNC('month', created_at) AS month, SUM(total)
FROM orders GROUP BY 1;

-- Использовать в запросах сессии
SELECT * FROM revenue_temp;
-- После закрытия соединения — удалится автоматически

Подводные камни

Устаревшие данные. Главная проблема: данные актуальны только на момент последнего REFRESH. Если пользователь видит статистику с задержкой — это нормально, но надо явно показывать время обновления:

-- Добавить метку времени обновления
CREATE MATERIALIZED VIEW stats_mv AS
SELECT *, NOW() AS refreshed_at FROM ...;

Долгий первый REFRESH. При создании большого Materialized View запрос может выполняться минуты. Планируй на период низкой нагрузки.

Место на диске. Materialized View — это копия данных. Для больших агрегаций размер обычно небольшой, но для почти полных копий таблиц — существенный.

Проверь своё понимание оптимизации запросов в нашем тренажёре.