Привет, Хабр! Меня зовут Денис, я инженер-программист в Контуре.

TL;DR:

У нас был долгий REFRESH MATERIALIZED VIEW: один запуск мог идти около часа, а повторные запуски вставали в очередь и держали соединения. CONCURRENTLY помогал не блокировать чтение из materialized view, но не решал проблему очереди одинаковых REFRESH.

Мы сделали механизм в PostgreSQL: триггерами отмечаем изменения в зависимых таблицах, храним зависимости каждой MV в служебной таблице, а перед обновлением берём pg_try_advisory_xact_lock по конкретной MV. Если lock не удалось взять — значит, обновление уже идёт, и второй REFRESH не ждёт в очереди, а пропускается.

Несколько лет назад, пока проект, на котором я работаю, был совсем молодым и быстро рос, у нас была единственная база данных — PostgreSQL. Специфика проекта такая, что нам нужно строить сложные запросы и отдавать результат напрямую пользователю. Проект развивался, количество документов и пользователей росло, и в какой-то момент наши запросы с кучей джойнов и функций начали заметно замедляться. При этом это была не внутренняя аналитика, а реальные данные, которые пользователь запрашивал через UI, поэтому время выполнения запроса играло большую роль.

Все простые способы оптимизации на тот момент уже не работали, и мы рассматривали два решения. Первое — глобально изменить подход: использовать другую БД или изменить схему хранения данных. Второе — использовать материализованное представление. Мы понимали, что данных будет всё больше, и вариант с глобальным изменением архитектуры всё равно неизбежен, но проблему нужно было решать прямо сейчас. Поэтому какое-то время нас спасал materialized view. Далее для простоты я буду называть его просто MV.

В PostgreSQL можно сохранить запрос как view 1, который живёт прямо в БД. Вместо того чтобы постоянно писать один и тот же запрос, мы сохраняем его под определённым именем и выполняем простой SELECT * FROM view_name. В этот момент PostgreSQL выполняет запрос, лежащий в основе view.

А вот materialized view 2 — это, по сути, закешированный результат выполнения запроса. Результат сохраняется как таблица в базе данных. К этой таблице можно обращаться точно так же: SELECT * FROM materialized_view_name. Такой запрос обычно выполняется намного быстрее, потому что результат уже заранее сохранён.

Хранимые процедуры и view иногда считают анти-паттерном, если они хранят бизнес-логику: в таком случае логика оказывается распределена между приложением и базой. Но в некоторых сценариях это осознанный компромисс, особенно когда нужно быстро решить проблему производительности.

Подвох MV в том, что при обновлении данных в таблицах, из которых построено представление, данные в самой MV останутся старыми. Чтобы обновить их, нужно выполнить команду REFRESH MATERIALIZED VIEW materialized_view_name 3. REFRESH заново выполняет запрос целиком, и после этого MV содержит новые данные. При этом нельзя просто взять и обновить в MV только то, что поменялось: весь объём данных будет заново вычитан и записан в MV.

CONCURRENTLY позволяет обновлять MV без блокировки параллельных SELECT по ней. Но есть важное условие: для CONCURRENTLY нужен хотя бы один UNIQUE-индекс на materialized view, причём индекс должен быть по обычным колонкам, без expression index и без WHERE. Также CONCURRENTLY можно использовать только тогда, когда materialized view уже заполнена. Нельзя выполнить два REFRESH CONCURRENTLY параллельно для одной MV — вторая команда будет ожидать завершения первой из‑за несовместимости блокировок.

Моя задача — сделать обновление MV так, чтобы данные были насколько возможно свежими, но без лишних REFRESH.

Первая мысль была такой: просто сделаю background service, который будет периодически ходить в базу и выполнять команду REFRESH MATERIALIZED VIEW. Очень просто и быстро.

После теста я понял, что не всё так просто: данных было так много, что REFRESH длился около часа. Повторные запросы на REFRESH просто вставали в очередь, и после окончания первого запроса начинался следующий. Так продолжалось до тех пор, пока все запросы либо не выполнялись, либо не отваливались по таймауту. Получался бесконечный REFRESH с кучей висящих соединений в очереди. Отдельный неприятный эффект был с миграциями — пока REFRESH выполнял запрос к базовым таблицам, он держал на них ACCESS SHARE-блокировки. Для обычных DML-операций это обычно не проблема, но некоторые DDL-операции требуют ACCESS EXCLUSIVE и поэтому вынуждены ждать окончания долгого REFRESH 4.

В конечном итоге сформировались следующие требования:

• если изменились данные в зависимых таблицах, нужно обновить MV;

• каждую MV нужно обновлять в одном потоке;

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

Хотелось оставить всю логику обновления MV в базе данных, чтобы можно было вызвать refresh_mv_if_need(mv_name) и получить результат, а не тащить всю логику в приложение, которое вызывает REFRESH.

Схема получилась следующая:

Самое сложное здесь — сама процедура: она собирает все наши данные и принимает решение, нужно ли запускать REFRESH.

Работу можно разделить на три части:

1. Следим за обновлением зависимых таблиц.

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

2. Создаём таблицу со всеми MV, для которых должна работать эта механика.

   1. Сохраняем полное имя MV.

   2. Сохраняем список зависимых таблиц, от которых зависят данные в нашей MV.

3. Создаём процедуру, которая будет собирать данные из всех источников и запускать REFRESH MATERIALIZED VIEW.

Code time

Создаём таблицы

mv.table_updates_time — таблица с датами последнего изменения зависимых таблиц.

CREATE SCHEMA IF NOT EXISTS mv;

CREATE TABLE IF NOT EXISTS mv.table_updates_time
(
    id SERIAL PRIMARY KEY,
    table_name regclass NOT NULL UNIQUE,
    data_changed_time_utc TIMESTAMP without time zone DEFAULT (clock_timestamp() AT TIME ZONE 'utc')
);

Код 1. Скрипт для создания таблицы mv.table_updates_time

mv.materialized_views_updated_time — таблица со списком всех MV, списком зависимых таблиц у каждой MV и временем последнего успешного REFRESH.

CREATE TABLE IF NOT EXISTS mv.materialized_views_updated_time
(
    id SERIAL PRIMARY KEY,
    materialized_view_name regclass NOT NULL UNIQUE,
    depending_tables regclass[] NOT NULL,
    view_refreshed_time_utc TIMESTAMP without time zone DEFAULT NULL
);

Код 2. Скрипт для создания таблицы mv.materialized_views_updated_time

Фиксируем время обновления зависимых таблиц

Создаём функцию add_table_updating_time, которая обновляет время последнего изменения таблицы. Эту функцию будет вызывать триггер update_date_for_table_trigger на каждый STATEMENT.

CREATE OR REPLACE FUNCTION mv.add_table_updating_time()
  RETURNS trigger
  LANGUAGE plpgsql
AS $BODY$
DECLARE
    table_nm regclass := TG_ARGV[0]::regclass;
BEGIN
    INSERT INTO mv.table_updates_time (table_name, data_changed_time_utc)
    VALUES (table_nm, clock_timestamp() AT TIME ZONE 'utc')
    ON CONFLICT (table_name)
    DO UPDATE SET data_changed_time_utc = EXCLUDED.data_changed_time_utc;

    RETURN NULL;
END;
$BODY$;

Код 3. Функция для обновления времени последнего изменения таблицы

CREATE TRIGGER update_date_for_table_trigger
AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE
    ON users.users
FOR EACH STATEMENT
    EXECUTE FUNCTION mv.add_table_updating_time('users.users');

Код 4. Пример добавления триггера для зависимой таблицы

Создаём процедуру с основной логикой

На вход принимаем имя MV, проверяем, заполнена ли она и есть ли подходящий UNIQUE-индекс для CONCURRENTLY, после чего пытаемся захватить transaction-level advisory lock. Если lock удалось захватить, запускаем REFRESH. При этом lock освободится автоматически после завершения транзакции.

CREATE OR REPLACE PROCEDURE mv.refresh_mv_if_need(mv_name text)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    mv_oid regclass;

    max_table_updating_date TIMESTAMP;
    mv_updating_date TIMESTAMP;
    depending_tables_v regclass[];
    mv_populated BOOLEAN;
    mv_can_refresh_concurrently BOOLEAN;
    time_at_start TIMESTAMP;

    materialized_view_id INTEGER;
    materialized_view_full_name TEXT;
    advisory_lock_ns CONSTANT INTEGER := 7788; -- произвольный идентификатор
    advisory_lock_taken BOOLEAN;
BEGIN
    mv_oid := to_regclass(mv_name);

    IF mv_oid IS NULL THEN
        RAISE EXCEPTION 'The relation % is not found.', mv_name;
    END IF;

    SELECT id
        INTO materialized_view_id
        FROM mv.materialized_views_updated_time
        WHERE materialized_view_name = mv_oid;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'The MV % is not found in materialized_views_updated_time.', mv_name;
    END IF;

    -- Берём transaction-level advisory lock.
    -- Он автоматически освободится в конце транзакции.
    SELECT pg_try_advisory_xact_lock(advisory_lock_ns, materialized_view_id)
        INTO advisory_lock_taken;

    IF NOT advisory_lock_taken THEN
        RAISE NOTICE 'The MV % is already being refreshed. Skipped.', mv_name;
        RETURN;
    END IF;

    -- После получения lock перечитываем актуальное состояние MV из служебной таблицы.
    SELECT view_refreshed_time_utc, depending_tables
        INTO mv_updating_date, depending_tables_v
        FROM mv.materialized_views_updated_time
        WHERE materialized_view_name = mv_oid;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'The MV % is not found in materialized_views_updated_time.', mv_name;
    END IF;

    SELECT c.relispopulated, format('%I.%I', n.nspname, c.relname)
        INTO mv_populated, materialized_view_full_name
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'm'
          AND c.oid = mv_oid;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'The MV % is not found in pg_class or is not a materialized view.', mv_name;
    END IF;

    SELECT EXISTS (
        SELECT 1
        FROM pg_index i
        WHERE i.indrelid = mv_oid
          AND i.indisunique
          AND i.indimmediate
          AND i.indisvalid
          AND i.indpred IS NULL
          AND i.indexprs IS NULL
          AND i.indnkeyatts > 0
    )
        INTO mv_can_refresh_concurrently;

    SELECT MAX(data_changed_time_utc)
        INTO max_table_updating_date
        FROM mv.table_updates_time
        WHERE table_name = ANY(depending_tables_v);

    IF NOT COALESCE(mv_populated, FALSE)
       OR mv_updating_date IS NULL
       OR (
            max_table_updating_date IS NOT NULL
            AND max_table_updating_date > mv_updating_date
          )
    THEN
        time_at_start := clock_timestamp() AT TIME ZONE 'utc';

        RAISE NOTICE 'The % should be updated. Updating...', mv_name;

        IF mv_populated AND NOT mv_can_refresh_concurrently THEN
            RAISE EXCEPTION
                'Materialized view % is populated but has no suitable UNIQUE index for REFRESH CONCURRENTLY.',
                mv_name;
        END IF;

        IF mv_populated THEN
            EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY ' || materialized_view_full_name;
        ELSE
            RAISE NOTICE 'Materialized view is not populated. Doing initial non-concurrent refresh...';
            EXECUTE 'REFRESH MATERIALIZED VIEW ' || materialized_view_full_name;
        END IF;

        UPDATE mv.materialized_views_updated_time
        SET view_refreshed_time_utc = time_at_start
        WHERE materialized_view_name = mv_oid;
    ELSE
        RAISE NOTICE 'The % is up to date.', mv_name;
    END IF;
END;
$BODY$;

Код 5. Создание процедуры для вызова обновления MV по имени

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

INSERT INTO mv.materialized_views_updated_time  
    (materialized_view_name, depending_tables)  
VALUES  
    ('schema.my_materialized_view'::regclass, ARRAY['users.users'::regclass, 'documents.documents'::regclass]);

Код 6. Пример запроса на добавление новой MV

В Oracle существует развитая система материализованных представлений, где можно задать режим REFRESH FAST ON COMMIT — инкрементальное обновление сразу после фиксации изменений. Представленное решение — это полный рефреш по требованию с самодельной проверкой необходимости, что в терминах Oracle примерно соответствует REFRESH COMPLETE ON DEMAND с пользовательским механизмом отслеживания изменений.

Ограничения такого подхода

• Если есть долгие транзакции, возможна гонка: триггер фиксирует изменение до COMMIT, а REFRESH MATERIALIZED VIEW видит только данные, закоммиченные к началу своего выполнения. Поэтому timestamp здесь не строгая гарантия консистентности. В случае длинных транзакций нужна отдельная очередь событий или другой маркер, который не позволяет считать изменение обработанным раньше его commit.

• Statement-level trigger срабатывает даже если statement затронул ноль строк. Это значит, что DML-запрос, не изменивший ни одной строки, всё равно обновит data_changed_time_utc и может вызвать лишний REFRESH.

• Проблема с блокировками зависимых таблиц так и остаётся, но за счёт того, что DDL операции у нас не такие частые, стреляло это довольно редко.

• Помимо добавления новых MV нужно следить за тем, чтобы не забывали добавлять MV в таблицу mv.materialized_views_updated_time, а также навешивать триггеры и следить за зависимыми таблицами.

• Данный подход никак не ограничивает возможность вызвать REFRESH напрямую.

Результат

Внедрив такой подход мы на продолжительное время забыли о проблемах обновления MV. Это позволило нам сконцентрироваться на глобальном решении.

Альтернативные решения

• Существует расширение pg_ivm 5 для PostgreSQL. Оно реализует Incremental View Maintenance. Но это не замена обычному REFRESH MATERIALIZED VIEW во всех сценариях. У него есть свои ограничения, требования к запросам. Наши запросы были слишком сложными.

• Простая периодическая задача с advisory lock, но без отслеживания изменений. Это упрощённая версия текущего подхода — меньше служебных таблиц и триггеров. Из минусов — лишние запуски REFRESH даже если данные не изменились.

Используете ли вы materialized view в PostgreSQL? Как поддерживаете их актуальность?