Materialized views и стратегии refresh

Опирается на правила: PG-MV-001 … PG-MV-041 из PostgreSQL Style Guide → раздел Materialized views.

Важно знать

• MV = закэшированный snapshot тяжёлого запроса на диске.
• Подходит для: тяжёлых агрегаций, отчётов, dashboards, pre-computed search.
• Не подходит: real-time, часто меняющиеся данные, простые запросы.
• UNIQUE-индекс обязателен для REFRESH CONCURRENTLY.
• REFRESH MATERIALIZED VIEW CONCURRENTLY — стандарт. Не блокирует SELECT.
• REFRESH MATERIALIZED VIEW — ACCESS EXCLUSIVE, только для маленьких в окне обслуживания.
• Инкрементальный refresh — не из коробки, через pg_ivm или триггеры.
• @Scheduled каждые 5 минут — для аналитики с допустимой латентностью.
• Debouncing через Redis-флаг — лучше триггера AFTER на каждое изменение.
• MV vs Read Model в коде: MV — SQL, секунды-минуты; Read Model — Java, микросекунды event-driven.

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

Когда оправдан

PG-MV-001..002:

Подходит для:

• Тяжёлые агрегации, частые reads, задержка ОК (отчёты, dashboards).
• Сложные JOIN по нескольким таблицам.
• Pre-computed search-индексы (см. FTS).
• Денормализация для read-heavy.

Не подходит:

• Real-time данные (refresh имеет latency).
• Часто меняющиеся данные, где cost refresh > выигрыш.
• Простые запросы без агрегаций — обычный VIEW или индекс.

Базовый паттерн

PG-MV-010..012:

CREATE MATERIALIZED VIEW order_stats_mv AS
SELECT
    customer_id,
    count(*) AS orders_count,
    sum(total_amount) AS total_spent,
    max(created_at) AS last_order_at
FROM order_doc
WHERE status != 'CANCELLED'
GROUP BY customer_id
WITH DATA;

WITH NO DATA — создать пустой, заполнить позже через REFRESH. Полезно для миграций.

Запросы — как к обычной таблице:

SELECT * FROM order_stats_mv WHERE customer_id = 42;

Индексы на MV — обязательны для частых WHERE:

CREATE INDEX ix_order_stats_customer ON order_stats_mv (customer_id);
CREATE UNIQUE INDEX uk_order_stats_customer ON order_stats_mv (customer_id);
-- UNIQUE нужен для REFRESH CONCURRENTLY

Стратегии refresh

PG-MV-020..023:

REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW order_stats_mv;

• Перевычисляет с нуля.
• ACCESS EXCLUSIVE — блокирует SELECT во время refresh.
• На большой MV — минуты блокировки.

PG-MV-021: только если MV маленькая (< 100K строк), в окне обслуживания, без онлайн-чтения.

REFRESH MATERIALIZED VIEW CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv;

• Не блокирует SELECT.
• Использует UNIQUE-индекс для diff'а.
• Обновляет только изменившиеся строки.
• Медленнее обычного REFRESH (diff).
• Требует UNIQUE-индекс.

PG-MV-022: CONCURRENTLY — стандарт для прода.

Инкрементальный refresh

PG-MV-023: PG не поддерживает из коробки.

Варианты:

• pg_ivm — extension, incremental view maintenance.
• Триггеры на исходных таблицах + ручной UPDATE MV.
• TimescaleDB continuous aggregates (если в стеке).

Cron vs Реактивно

PG-MV-030..033:

@Scheduled — для отчётов

@Scheduled(fixedDelay = 300_000)   // 5 минут
public void refreshOrderStats() {
    jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
}

5 минут латентности — приемлемо для аналитики.

Триггер AFTER — дорого

CREATE OR REPLACE FUNCTION refresh_order_stats()
RETURNS trigger AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_order_doc_refresh_stats
AFTER INSERT OR UPDATE OR DELETE ON order_doc
FOR EACH STATEMENT EXECUTE FUNCTION refresh_order_stats();

PG-MV-032: refresh всей MV на каждый INSERT — уничтожает throughput. Только когда:

• Изменения редкие.
• MV маленькая.
• Альтернативы нет.

Debouncing через Redis

PG-MV-033: лучшая практика.

@Component
class OrderStatsRefresh {
    @EventListener
    public void onOrderChanged(OrderChangedEvent e) {
        redis.set("mv:order_stats:dirty", "1");
    }

    @Scheduled(fixedDelay = 60_000)
    public void refreshIfDirty() {
        if (redis.delete("mv:order_stats:dirty")) {
            jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
        }
    }
}

Refresh не чаще раза в минуту + только если что-то изменилось.

MV vs Read Model в коде

PG-MV-040..041:

	MV	Read Model в коде
Логика	SQL	Java
Гранулярность	вся MV	по строкам
Латентность	секунды-минуты	микросекунды (event-driven)
Сложность	низкая (один SQL)	высокая (eventual consistency)
Для	отчёты, агрегации	CQRS

Выбор:

• Сложный SELECT ... GROUP BY с JOIN, читается часто, задержка ОК → MV.
• Read model с минимальной задержкой, обновляется поэлементно → отдельная таблица + event handler (см. CQRS → read-model).

Что запрещено

Антипаттерн	Правило	Что взамен
REFRESH MV без CONCURRENTLY на проде	PG-MV-021	CONCURRENTLY
MV без UNIQUE-индекса для CONCURRENTLY	PG-MV-012	CREATE UNIQUE INDEX
Триггер AFTER на каждое изменение	PG-MV-032	debouncing через Redis
MV для real-time	PG-MV-002	Read Model event-driven
MV для простого SELECT * без агрегаций	PG-MV-002	обычный индекс
Без индексов на MV	PG-MV-012	CREATE INDEX
Refresh из миграции	PG-MV-030	@Scheduled job

Куда дальше

• PG → Materialized views — нормативные формулировки.
• Композитные индексы — индексы на MV.
• Полнотекстовый поиск — MV для search-индекса.
• Партиционирование — альтернатива MV для архивов.
• CQRS → read-model — Read Model в коде.
• Caching → patterns — refresh-ahead аналогия.