Как я могу гарантировать, что материализованное представление всегда актуально?

Мне нужно будет вызывать REFRESH MATERIALIZED VIEW при каждом изменении соответствующих таблиц, верно?

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

Как я должен это делать?

Много способов достичь этого. Прежде чем привести несколько примеров, имейте в виду, что REFRESH MATERIALIZED VIEW команда блокирует представление в режиме AccessExclusive, поэтому, пока она работает, вы даже не можете сделать SELECT на стол.

Хотя, если вы находитесь в версии 9.4 или новее, вы можете дать ему опцию CONCURRENTLY:

REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;

Это приобретет исключительную блокировку и не будет блокировать запросы SELECT, но может иметь большие накладные расходы (зависит от количества измененных данных, если несколько строк изменились, то это может быть быстрее). Хотя вы все еще не можете запустить две команды REFRESH одновременно.

Обновить вручную

Это вариант, который следует рассмотреть. Особенно в случаях загрузки данных или пакетной обработки обновления (например, система, которая загружает только тонны информации / данных после длительных периодов времени) обычно есть операции в конце для изменения или обработки данных, поэтому вы можете просто включить операцию REFRESH В конце ее.

Планирование операции обновления

Первый и широко используемый вариант-использовать некоторую систему планирования для вызова обновления, например, вы можете настроить подобное в задании cron:

*/30 * * * * psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv"

И тогда ваш материализованный взгляд будет обновляется каждые 30 минут.

Соображения

Эта опция действительно хороша, особенно с опцией CONCURRENTLY, но только если вы можете принять данные, которые не всегда на 100% актуальны. Имейте в виду, что даже с CONCURRENTLY или без REFRESH команда REFRESH должна выполнить весь запрос, поэтому вам нужно потратить время, необходимое для выполнения внутреннего запроса, прежде чем рассматривать время для планирования REFRESH.

Обновление с помощью триггера

Другой вариант-позвонить REFRESH MATERIALIZED VIEW в триггерной функции, например:

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

Затем в любой таблице, которая включает изменения в представлении, вы делаете:

CREATE TRIGGER tg_refresh_my_mv AFTER INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH STATEMENT EXECUTE PROCEDURE tg_refresh_my_mv();

Соображения

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

1. любая операция вставки / обновления / удаления должна будет выполнить запрос (что возможно медленно, если вы рассматриваете MV);
2. даже с CONCURRENTLY, один REFRESH по-прежнему блокирует другой, поэтому любая вставка / обновление / удаление в задействованных таблицах будет быть сериализованным.

Единственная ситуация, которую я могу считать хорошей идеей, - это если изменения действительно редки.

Обновить с помощью LISTEN/NOTIFY

Проблема с предыдущим вариантом заключается в том, что он является синхронным и накладывает большие накладные расходы на каждую операцию. Чтобы исправить это, вы можете использовать триггер, как и раньше, но это вызывает только NOTIFY операция :

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, 'my_mv';
    RETURN NULL;
END;
$$;

Таким образом, вы можете построить приложение, которое поддерживает связь и использует LISTEN операция для определения необходимости вызова REFRESH. Один хороший проект, который вы можете использовать для тестирования, это pgsidekick , с этим проектом вы можете использовать shell script для выполнения LISTEN, поэтому вы можете запланировать REFRESH как:

pglisten --listen=refresh_mv --print0 | xargs -0 -n1 -I? psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY ?;"

Или используйте pglater (также внутри pgsidekick), чтобы убедиться, что вы не звоните REFRESH очень часто. Например, вы можете использовать следующий триггер, чтобы сделать это REFRESH, но в течение 1 минуты (60 секунд):

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, '60 REFRESH MATERIALIZED VIEW CONCURRENLTY my_mv';
    RETURN NULL;
END;
$$;

Так что он не будет звонить REFRESH менее чем через 60 секунд., а также если Вы NOTIFY много раз менее чем за 60 секунд, то REFRESH будет срабатывать только один раз.

Соображения

Как вариант cron, этот вариант также хорош, только если вы можете обнажить немного устаревших данных, но это имеет то преимущество, что REFRESH вызывается только тогда, когда это действительно необходимо, поэтому у вас меньше накладных расходов, а также данные обновляются ближе к тому, когда это необходимо.

OBS: я еще не пробовал коды и примеры, так что если кто-то найдет ошибку, опечатку или попробуйте и работает (или нет), пожалуйста, дайте мне знать.