Урок 04

Кэш на уровне БД: материализованные представления

Что это

Materialized View (MV) — сохранённый результат запроса.

Что даёт:

  • Быстрое чтение.
  • Тяжёлый расчёт один раз.
  • Агрегация без пересчёта.
  • JOIN без выполнения.

Что стоит:

  • Место на диске.
  • Обновление вручную.
  • Не realtime.

Правило: MV — для тяжёлых агрегатов.

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

Хорошо:

  • Дашборды.
  • Отчёты.
  • Аналитика.
  • Топ-N.
  • Сводки по дням.

Плохо:

  • Realtime данные.
  • Мелкие выборки.
  • Часто меняющиеся.

Правило: если данные можно обновить раз в 5–60 мин — MV.

MV vs VIEW

VIEW — виртуальный, считается каждый раз.

MV — физический, сохранён.

Правило:

  • VIEW — упростить SQL.
  • MV — ускорить.

Создание MV

CREATE MATERIALIZED VIEW post_stats AS
SELECT
    p.id,
    p.title,
    COUNT(c.id) AS comments_count,
    COALESCE(SUM(c.likes), 0) AS likes_sum,
    MAX(c.created_at) AS last_comment_at
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title;

Что даёт: сохранённая таблица.

Правило: MV может иметь индексы.

Индексы на MV

CREATE UNIQUE INDEX mv_post_stats_id ON post_stats (id);
CREATE INDEX mv_post_stats_comments ON post_stats (comments_count DESC);

Что даёт: быстрый поиск.

Правило: MV без индексов — бесполезно.

Обновление MV

REFRESH:

REFRESH MATERIALIZED VIEW post_stats;

Что даёт: пересчёт всей таблицы.

Блокирует чтение.

Concurrent refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats;

Что даёт: не блокирует чтение.

⚠️ Требует уникальный индекс.

Правило: в проде — всегда concurrent.

Расписание

Cron:

*/15 * * * * psql -d skills -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats;'

Что даёт: обновление раз в 15 мин.

Правило: частота — по бизнес-требованиям.

Из Laravel

Scheduler:

Schedule::call(function () {
    DB::statement('REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats');
})->everyFifteenMinutes();

Что даёт: в расписании Laravel.

Правило: логировать длительность.

Чтение MV

Query builder:

$stats = DB::table('post_stats')
    ->orderByDesc('comments_count')
    ->limit(10)
    ->get();

Что даёт: быстрый результат.

Модель

class PostStat extends Model
{
    protected $table = 'post_stats';
    public $timestamps = false;
    protected $primaryKey = 'id';
    public $incrementing = false;
}

Что важно: $timestamps = false, $incrementing = false.

Правило: MV — read-only модель.

Incremental refresh

Проблема: полный refresh дорогой.

Что делать:

  • Только изменённые строки.
  • Триггеры на изменения.
  • Очереди.

Пример:

CREATE OR REPLACE FUNCTION refresh_post_stats(p_id int)
RETURNS void AS $$
BEGIN
    INSERT INTO post_stats (id, title, comments_count)
    SELECT p.id, p.title, COUNT(c.id)
    FROM posts p
    LEFT JOIN comments c ON c.post_id = p.id
    WHERE p.id = p_id
    GROUP BY p.id, p.title
    ON CONFLICT (id) DO UPDATE SET
        title = EXCLUDED.title,
        comments_count = EXCLUDED.comments_count;
END;
$$ LANGUAGE plpgsql;

Что даёт: обновление по строке.

Правило: incremental — в разы быстрее.

Материализованная таблица

Не MV, а своя таблица:

Schema::create('post_stats', function (Blueprint $table) {
    $table->foreignId('id')->primary();
    $table->string('title');
    $table->integer('comments_count')->default(0);
    $table->timestamp('refreshed_at');
});

Что даёт:

  • Полный контроль.
  • Обновление из Laravel.
  • События модели.

Правило: своя таблица — гибче MV.

Обновление своей таблицы

PostStat::query()->upsert(
    DB::table('posts')
        ->leftJoin('comments', 'comments.post_id', '=', 'posts.id')
        ->selectRaw('posts.id, posts.title, COUNT(comments.id) as comments_count')
        ->groupBy('posts.id', 'posts.title')
        ->get()
        ->map(fn ($r) => (array) $r + ['refreshed_at' => now()])
        ->all(),
    ['id'],
    ['title', 'comments_count', 'refreshed_at']
);

Что даёт: атомарное обновление.

Правило: upsert — лучше TRUNCATE+INSERT.

Триггеры в Postgres

Проблема: MV не обновляется автоматически.

Решение:

  • Триггер на INSERT/UPDATE/DELETE.
  • Очередь задач.
  • Debounce — не на каждое изменение.

Пример:

CREATE TRIGGER comments_stats_trigger
AFTER INSERT OR UPDATE OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION refresh_post_stats_trigger();

Что даёт: MV обновляется сама.

⚠️ Триггеры замедляют запись.

Правило: триггеры — осторожно.

TimescaleDB

Continuous aggregates:

CREATE MATERIALIZED VIEW daily_stats
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', created_at) AS day, COUNT(*)
FROM orders GROUP BY day;

Что даёт: авто-обновление агрегатов.

Правило: для временных рядов — TimescaleDB.

ClickHouse

Для аналитики:

  • Колонковая БД.
  • Быстрые агрегаты.
  • Real-time вставки.
  • Материализованные представления авто.

Правило: аналитика > 100M строк — ClickHouse.

MV и репликация

Что важно:

  • MV реплицируется как таблица.
  • REFRESH на праймари.
  • На репликах — уже готовое.

Правило: MV + read-replica — хорошо.

Мониторинг

Что смотреть:

  • Размер MV.
  • Время REFRESH.
  • Актуальность (refreshed_at).
  • Ошибки refresh.

Правило: REFRESH > 5 мин — инкрементальный.

Практика

1. Найдите 3 тяжёлых агрегата.

2. Создайте MV для каждого.

3. Добавьте уникальный индекс.

4. Настройте concurrent refresh.

5. Настройте cron каждые 15 мин.

6. Замерьте до/после.

7. Сделайте инкрементальный refresh.

8. Мониторьте refreshed_at.

9. Проверьте размер.

10. Сравните с TimescaleDB.

Частые ошибки

1. Без уникального индекса:

CONCURRENTLY не работает

Решение: UNIQUE INDEX.

2. Частый REFRESH:

CPU БД

Решение: реже / incremental.

3. REFRESH без CONCURRENTLY:

Блокирует

Решение: CONCURRENTLY.

4. Полный refresh на большой:

Минуты

Решение: incremental.

5. Нет индексов на MV:

Seq Scan

Решение: индексы.

6. Триггеры на всё:

Замедляет запись

Решение: debounce.

7. Без мониторинга:

Устаревшее

Решение: refreshed_at.

8. MV как источник правды:

Данные теряются

Решение: MV — только кэш.

Итог

  • MV — сохранённый результат.
  • Только для тяжёлых агрегатов.
  • Уникальный индекс обязателен.
  • CONCURRENTLY refresh.
  • Cron или scheduler.
  • Incremental — для больших.
  • Своя таблица — гибче.
  • TimescaleDB — для time-series.
  • ClickHouse — для аналитики.
  • Мониторить refreshed_at.

Дальше — connection pooling: PgBouncer, ProxySQL.