SQL Materialized Views: Caching Expensive Query Results

Иногда в базе есть запрос, который нужен часто, но выполняется тяжело.

Например:

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

Сам запрос может быть правильным, но дорогим:

много строк;
несколько JOIN;
GROUP BY;
SUM, COUNT, AVG;
фильтры по большим таблицам;
сортировки.

Если выполнять такой запрос каждый раз заново, дашборд или отчёт может открываться медленно.

Материализованное представление решает эту проблему просто:

База один раз выполняет тяжёлый запрос и сохраняет его результат на диск.

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

По сути, материализованное представление — это кэш внутри базы данных.

---

Обычное VIEW и MATERIALIZED VIEW: в чём разница

Начнём с обычного представления.

VIEW — это сохранённый SQL-запрос.

Например:

CREATE VIEW paid_orders AS
SELECT *
FROM orders
WHERE status = 'paid';

Теперь можно писать:

SELECT *
FROM paid_orders;

Но важно понимать: обычное VIEW не хранит данные отдельно.

Когда вы обращаетесь к paid_orders, PostgreSQL подставляет исходный запрос и выполняет его заново.

То есть VIEW — это скорее удобный ярлык для запроса.

А MATERIALIZED VIEW работает иначе.

Оно не просто хранит текст запроса. Оно хранит результат запроса физически на диске.

Можно представить так:

Инструмент	Что хранит
VIEW	Текст запроса
MATERIALIZED VIEW	Готовый результат запроса

Это главное отличие.

---

Пример задачи: выручка по странам

Допустим, у нас есть таблица пользователей:

CREATE TABLE users (
    id      bigint PRIMARY KEY,
    email   text NOT NULL,
    country text
);

И таблица заказов:

CREATE TABLE orders (
    id      bigint PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users(id),
    amount  numeric(12,2) NOT NULL,
    status  text NOT NULL
);

Мы хотим часто показывать отчёт:

страна;
количество оплаченных заказов;
общая выручка.

Обычный запрос может выглядеть так:

SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

Если таблицы маленькие, проблем нет.

Но если в orders миллионы строк, а отчёт открывают постоянно, каждый запуск такого запроса может быть дорогим.

Особенно если это дашборд, который открывают менеджеры, аналитики и админы.

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

---

Создаём MATERIALIZED VIEW

Создадим материализованное представление:

CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

Теперь PostgreSQL выполнит этот запрос и сохранит результат.

После этого можно читать:

SELECT *
FROM revenue_by_country;

И это уже не будет каждый раз заново джойнить orders и users, фильтровать оплаченные заказы и считать агрегаты.

PostgreSQL прочитает готовые строки из revenue_by_country.

Условно там будет лежать такая таблица:

country	orders_cnt	revenue
US	1200	85000.00
DE	740	43000.00
KZ	310	18000.00

Для чтения это очень быстро.

---

Материализованное представление — почти как таблица

С материализованным представлением можно работать похоже на обычную таблицу.

Например:

SELECT country, revenue
FROM revenue_by_country
ORDER BY revenue DESC
LIMIT 10;

Можно фильтровать:

SELECT *
FROM revenue_by_country
WHERE revenue > 10000;

Можно сортировать:

SELECT *
FROM revenue_by_country
ORDER BY orders_cnt DESC;

Можно создавать индексы.

Но есть важное отличие от обычной таблицы: данные в материализованном представлении нельзя просто обновлять как обычные строки.

Вы не делаете так:

UPDATE revenue_by_country
SET revenue = 999999
WHERE country = 'US';

Потому что это не самостоятельные данные. Это результат запроса.

Чтобы изменить данные в материализованном представлении, нужно изменить исходные таблицы и потом обновить само представление через REFRESH.

---

Главный нюанс: данные не обновляются сами

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

Допустим, мы создали:

CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

В этот момент PostgreSQL посчитал выручку и сохранил результат.

Потом пользователь сделал новый заказ:

INSERT INTO orders (id, user_id, amount, status)
VALUES (1001, 10, 500.00, 'paid');

В таблице orders заказ появился.

Но в revenue_by_country он сам по себе не появится.

Материализованное представление всё ещё показывает старую картину — на момент последнего пересчёта.

Это очень важный компромисс:

Читаем быстро,
но данные могут быть не самыми свежими.

---

Обновление через REFRESH MATERIALIZED VIEW

Чтобы материализованное представление подтянуло свежие данные, его нужно обновить:

REFRESH MATERIALIZED VIEW revenue_by_country;

После этой команды PostgreSQL заново выполнит исходный запрос и перезапишет результат в материализованном представлении.

Теперь новые оплаченные заказы попадут в отчёт.

То есть жизненный цикл такой:

1. Создали MATERIALIZED VIEW.
2. Быстро читаем готовый результат.
3. Периодически вызываем REFRESH.
4. Результат снова становится свежим.

Можно воспринимать REFRESH как пересборку кэша.

---

Проблема обычного REFRESH

Обычный REFRESH MATERIALIZED VIEW может блокировать чтение из представления.

Например:

REFRESH MATERIALIZED VIEW revenue_by_country;

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

На маленьком отчёте это незаметно.

Но если пересчёт идёт 30 секунд, 2 минуты или 10 минут, это уже проблема. Дашборд может зависнуть именно в момент обновления.

Для таких случаев в PostgreSQL есть более аккуратный вариант:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

---

REFRESH CONCURRENTLY: обновить без блокировки чтения

CONCURRENTLY позволяет обновлять материализованное представление так, чтобы обычные SELECT могли продолжать читать старую версию данных, пока новая версия пересчитывается.

То есть во время обновления пользователи всё ещё видят старый результат.

Когда PostgreSQL досчитает новую версию, он аккуратно подменит данные.

Пример:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

Это полезно для работающих сервисов и дашбордов, где нельзя блокировать чтение отчёта на время пересчёта.

Но у CONCURRENTLY есть условия.

Главное из них: на материализованном представлении должен быть уникальный индекс, который однозначно определяет строки результата.

Например, если в нашем отчёте одна строка на страну, можно создать уникальный индекс по country:

CREATE UNIQUE INDEX revenue_by_country_country_uniq
ON revenue_by_country (country);

После этого можно запускать:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

---

Почему CONCURRENTLY требует уникальный индекс

Чтобы обновить материализованное представление конкурентно, PostgreSQL должен понять, какие строки изменились, какие появились, а какие исчезли.

Для этого ему нужен способ однозначно сравнивать старую и новую версию результата.

Уникальный индекс как раз говорит:

Вот набор колонок, по которому каждая строка результата уникальна.

В нашем примере:

CREATE UNIQUE INDEX revenue_by_country_country_uniq
ON revenue_by_country (country);

означает:

На каждую country приходится не больше одной строки.

И это совпадает с запросом, потому что мы группируем по стране:

GROUP BY u.country

Если уникального индекса нет, REFRESH MATERIALIZED VIEW CONCURRENTLY завершится ошибкой.

---

Обычный REFRESH или CONCURRENTLY

У обоих вариантов есть свои плюсы.

Обычный REFRESH:

REFRESH MATERIALIZED VIEW revenue_by_country;

обычно проще и может быть быстрее, но на время обновления блокирует чтение из материализованного представления.

REFRESH CONCURRENTLY:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

позволяет читать старую версию, пока строится новая, но работает сложнее и может быть медленнее.

Упрощённо:

Вариант	Чтение во время обновления	Что нужно
REFRESH MATERIALIZED VIEW	Может блокироваться	Ничего особенного
REFRESH MATERIALIZED VIEW CONCURRENTLY	Продолжается	Уникальный индекс

Для продакшена, где отчёт читают пользователи, чаще выбирают CONCURRENTLY.

Для ночных пересчётов, когда никто не читает отчёт, иногда достаточно обычного REFRESH.

---

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

Материализованное представление хранит данные физически, поэтому на него можно создавать индексы.

Например, мы часто хотим показывать топ стран по выручке:

SELECT country, revenue
FROM revenue_by_country
ORDER BY revenue DESC
LIMIT 10;

Для этого можно создать индекс:

CREATE INDEX revenue_by_country_revenue_idx
ON revenue_by_country (revenue DESC);

Теперь запрос с сортировкой по revenue может работать быстрее.

Можно создать индекс и по количеству заказов:

CREATE INDEX revenue_by_country_orders_cnt_idx
ON revenue_by_country (orders_cnt DESC);

Или уникальный индекс для CONCURRENTLY:

CREATE UNIQUE INDEX revenue_by_country_country_uniq
ON revenue_by_country (country);

Но важно помнить: индексы ускоряют чтение, но делают обновление тяжелее.

Когда вы вызываете REFRESH, PostgreSQL должен обновить не только данные материализованного представления, но и его индексы.

Поэтому не стоит создавать индексы «на всякий случай». Индексируйте только то, что реально нужно для запросов.

---

Пример: ежедневная выручка

Материализованные представления часто используют для отчётов по дням.

Например:

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
    date_trunc('day', o.created_at)::date AS day,
    count(*) AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
WHERE o.status = 'paid'
GROUP BY date_trunc('day', o.created_at)::date;

Теперь можно быстро получить выручку по дням:

SELECT *
FROM daily_revenue
ORDER BY day DESC
LIMIT 30;

Добавим уникальный индекс по дню:

CREATE UNIQUE INDEX daily_revenue_day_uniq
ON daily_revenue (day);

Теперь можно обновлять конкурентно:

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

И добавить индекс для сортировки или фильтрации по выручке, если это нужно:

CREATE INDEX daily_revenue_revenue_idx
ON daily_revenue (revenue DESC);

---

Как запускать REFRESH по расписанию

Материализованное представление не обновляется само.

Поэтому нужно решить, когда запускать REFRESH.

Частые варианты:

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

Например, для управленческого дашборда может хватить обновления раз в час.

Для ежедневного отчёта — раз в сутки ночью.

Для почти real-time аналитики — каждые несколько минут, если нагрузка позволяет.

В PostgreSQL для расписания можно использовать внешние инструменты:

• cron на сервере;
• планировщик в приложении;
• Airflow;
• Kubernetes CronJob;
• CI/CD job;
• расширение pg_cron.

Сам SQL будет простым:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

Главное — не забыть, что это полноценный тяжёлый пересчёт. Его частоту нужно подбирать под нагрузку и требования бизнеса.

---

Staleness: данные могут устаревать

У материализованных представлений есть главный компромисс — устаревание данных.

По-английски это часто называют staleness.

Допустим, вы обновляете revenue_by_country раз в час.

В 12:00 выполнился REFRESH.

В 12:10 пользователь сделал заказ.

В 12:15 менеджер открыл отчёт.

Будет ли новый заказ в отчёте?

Нет, если следующий REFRESH будет только в 13:00.

Это нормально, если бизнес согласен с такой задержкой.

Например, для аналитического дашборда задержка в час часто приемлема.

Но для баланса пользователя, остатков на складе или доступного лимита — нет.

Нельзя показывать пользователю старый баланс просто потому, что материализованное представление ещё не обновилось.

Поэтому правило такое:

MATERIALIZED VIEW подходит там, где допустима задержка данных.

---

Где материализованное представление использовать нельзя

Материализованное представление плохо подходит для данных, где нужна точность прямо сейчас.

Плохие кандидаты:

• баланс пользователя;
• остатки товара на складе;
• лимит по карте;
• доступные деньги для вывода;
• количество свободных мест, если они быстро меняются;
• критичные проверки перед оплатой.

Почему?

Потому что между двумя REFRESH данные могут быть устаревшими.

Например, если пользователь только что оплатил заказ, а отчёт ещё не обновился, в материализованном представлении этого заказа может не быть.

Для отчёта это нормально.

Для финансовой проверки — опасно.

---

Где материализованное представление подходит хорошо

Хорошие кандидаты:

• дашборды;
• отчёты;
• агрегаты по дням, неделям, месяцам;
• статистика по странам;
• топы товаров;
• аналитика продаж;
• витрины данных для BI;
• предварительно посчитанные тяжёлые JOIN и GROUP BY.

Например:

Топ-10 стран по выручке за месяц.

Количество регистраций по дням.

Средний чек по категориям.

Выручка по менеджерам.

Число активных пользователей за каждый день.

Во всех этих случаях обычно допустимо, что данные обновляются не каждую секунду, а по расписанию.

---

VIEW, MATERIALIZED VIEW или сводная таблица

Есть три похожих инструмента, и их легко перепутать.

VIEW

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

CREATE VIEW revenue_by_country_view AS
SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

Данные всегда свежие, потому что запрос выполняется заново при каждом чтении.

Но если запрос тяжёлый, чтение будет медленным.

MATERIALIZED VIEW

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

CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

Чтение быстрое, потому что результат уже сохранён.

Но данные свежие только на момент последнего REFRESH.

Сводная таблица

Сводная таблица — это обычная таблица, которую вы сами наполняете и обновляете.

Например:

CREATE TABLE revenue_summary (
    day     date NOT NULL,
    country text NOT NULL,
    revenue numeric(12,2) NOT NULL,
    orders_cnt bigint NOT NULL,
    PRIMARY KEY (day, country)
);

А потом сами пишете логику обновления:

INSERT INTO revenue_summary (day, country, revenue, orders_cnt)
SELECT
    current_date,
    u.country,
    sum(o.amount),
    count(*)
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
  AND o.created_at >= current_date
GROUP BY u.country
ON CONFLICT (day, country) DO UPDATE
SET revenue = EXCLUDED.revenue,
    orders_cnt = EXCLUDED.orders_cnt;

Сводная таблица даёт больше контроля. Можно обновлять только один день, один регион, одну категорию, а не пересчитывать весь результат целиком.

Но за это вы платите ручной логикой.

---

Сравнение вариантов

Инструмент	Данные	Плюсы	Минусы
VIEW	Всегда свежие	Просто, не хранит данные отдельно	Каждый раз выполняет исходный запрос
MATERIALIZED VIEW	На момент последнего REFRESH	Быстро читается, просто создать	Нужно обновлять, данные могут устаревать
Сводная таблица	Как вы сами обновите	Максимальный контроль, можно обновлять инкрементально	Нужно писать и поддерживать логику

Проще запомнить так:

VIEW — когда нужна свежесть.
MATERIALIZED VIEW — когда нужен быстрый кэш тяжёлого запроса.
Сводная таблица — когда нужна своя тонкая логика обновления.

---

Почему материализованное представление — не всегда лучший вариант

Материализованное представление в PostgreSQL при REFRESH пересчитывает результат.

То есть если у вас огромный запрос по всем заказам за несколько лет, каждый REFRESH может быть тяжёлым.

Например:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

может заново обработать большие объёмы данных.

Если вам нужно обновлять только маленькую часть результата, например только текущий день, иногда лучше сделать отдельную сводную таблицу и обновлять её инкрементально.

Например:

Сегодня пересчитать только сегодняшние продажи.
Исторические дни не трогать.

Для такой логики обычная таблица с INSERT ... ON CONFLICT DO UPDATE может быть лучше, чем полный REFRESH MATERIALIZED VIEW.

---

WITH NO DATA: создать структуру без первичного наполнения

Иногда материализованное представление создают без немедленного выполнения запроса.

Для этого есть:

CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
WITH NO DATA;

В этом случае PostgreSQL создаст само материализованное представление, но не заполнит его данными.

Пока оно не заполнено, читать из него нельзя.

Потом нужно выполнить:

REFRESH MATERIALIZED VIEW revenue_by_country;

Это может быть полезно, если вы хотите сначала создать объект, индексы и подготовить миграцию, а наполнение запустить отдельно в подходящее время.

---

Как удалить материализованное представление

Удаляется оно просто:

DROP MATERIALIZED VIEW revenue_by_country;

Если есть зависимые объекты, PostgreSQL может попросить сначала удалить их или использовать CASCADE.

Например:

DROP MATERIALIZED VIEW revenue_by_country CASCADE;

Но с CASCADE нужно быть осторожным: он может удалить зависимые представления или другие объекты.

---

MySQL: материализованных представлений нет

В MySQL нет встроенного аналога PostgreSQL CREATE MATERIALIZED VIEW.

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

Например:

CREATE TABLE revenue_by_country (
    country varchar(100) PRIMARY KEY,
    orders_cnt bigint NOT NULL,
    revenue decimal(12,2) NOT NULL
);

А потом обновляют её:

• по расписанию;
• через event scheduler;
• через приложение;
• через ETL-процесс;
• иногда через триггеры.

То есть в MySQL чаще используют ручную сводную таблицу.

Идея похожа на материализованное представление, но база сама не даёт такой же удобной команды REFRESH MATERIALIZED VIEW.

---

ClickHouse: MATERIALIZED VIEW — это другое

В ClickHouse тоже есть MATERIALIZED VIEW, но оно работает иначе, чем в PostgreSQL.

В PostgreSQL материализованное представление — это сохранённый результат запроса, который вы периодически пересчитываете через REFRESH.

В ClickHouse materialized view больше похоже на автоматический обработчик вставок.

Когда в исходную таблицу вставляются новые данные, materialized view может преобразовать их и записать результат в другую таблицу.

Например, сырые события вставляются в одну таблицу, а агрегаты по дням автоматически попадают в другую.

То есть в ClickHouse это не просто «кэш запроса», который обновляется командой REFRESH.

Это скорее часть пайплайна записи данных.

Поэтому важно не путать одинаковое название в разных СУБД.

---

Короткая шпаргалка

Создать материализованное представление:

CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country;

Прочитать данные:

SELECT *
FROM revenue_by_country;

Обновить данные:

REFRESH MATERIALIZED VIEW revenue_by_country;

Обновить без блокировки чтения:

CREATE UNIQUE INDEX revenue_by_country_country_uniq
ON revenue_by_country (country);

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_country;

Добавить индекс для быстрых запросов:

CREATE INDEX revenue_by_country_revenue_idx
ON revenue_by_country (revenue DESC);

Создать без первичного наполнения:

CREATE MATERIALIZED VIEW revenue_by_country AS
SELECT
    u.country,
    count(*)      AS orders_cnt,
    sum(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
WITH NO DATA;

Удалить:

DROP MATERIALIZED VIEW revenue_by_country;

---

Главное, что нужно запомнить

Материализованное представление — это сохранённый результат SQL-запроса.

Обычный VIEW каждый раз выполняет запрос заново.

MATERIALIZED VIEW один раз считает результат, сохраняет его на диск и потом отдаёт быстро.

Но за скорость мы платим свежестью данных.

Пока вы не вызвали:

REFRESH MATERIALIZED VIEW revenue_by_country;

материализованное представление показывает старую картину.

Поэтому главный вопрос перед использованием такой конструкции:

Можно ли показывать пользователю данные с задержкой?

Если нельзя — лучше обычный запрос, VIEW или другая более актуальная модель.

Если можно — MATERIALIZED VIEW отлично подходит для тяжёлых отчётов, аналитики и дашбордов.

Хорошее правило:

Нужна актуальность — используйте VIEW или обычный запрос.
Запрос тяжёлый и допустима задержка — используйте MATERIALIZED VIEW.
Нужна сложная инкрементальная логика — делайте сводную таблицу.

Материализованные представления особенно полезны там, где пользователь не должен ждать, пока база каждый раз заново перемалывает миллионы строк ради одного и того же отчёта.