Что такое материализованное представление (materialized view)?

Question

Материализованное представление — это объект базы данных, который хранит результаты SQL-запроса как физическую таблицу. В отличие от обычного представления, которое выполняет запрос при каждом обращении, материализованное представление сохраняет данные на диске и требует явного обновления. Это полезно для сложных запросов с агрегациями, которые выполняются долго, но данные в которых меняются редко. Обновление данных происходит командой REFRESH MATERIALIZED VIEW.

YeaHub · Accepted Answer

Материализованные представления сочетают в себе преимущества представлений и таблиц.1. Отличия от обычных представленийОбычное представление: Виртуальная таблица, запрос выполняется при каждом SELECTМатериализованное представление: Физическое хранение данных, запрос выполняется только при обновлении2. Создание и использование-- Создание материализованного представления
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    date_trunc('month', order_date) as month,
    product_category,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY month, product_category;

-- Создание индекса для ускорения запросов
CREATE UNIQUE INDEX idx_monthly_sales_month_category 
ON monthly_sales (month, product_category);

-- Обновление данных
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- Использование как обычной таблицы
SELECT * FROM monthly_sales 
WHERE month >= '2023-01-01' 
ORDER BY total_sales DESC;3. Сценарии примененияАгрегированные отчеты: Суммы, средние, количества по периодамСложные денормализованные данные: Результаты JOIN нескольких таблицМедленно меняющиеся данные: Справочники, исторические данныеГеографические данные: Предварительно рассчитанные расстояния или области4. Преимущества и ограниченияПреимущества:Высокая производительность для сложных запросовСнижение нагрузки на основную базу данныхВозможность создания индексовОграничения:Данные могут быть неактуальнымиТребует дополнительного места на дискеОбновление может занимать значительное времяВывод: Используйте материализованные представления для сложных отчетов и агрегаций, где допустима некоторая задержка в актуальности данных. Они особенно полезны в системах бизнес-аналитики и для кеширования результатов дорогостоящих запросов.