Чтобы гарантировать, что pg_ivm правильно поддерживает IMMVs, добавьте его в shared_preload_libraries или session_preload_libraries в postgresql.conf:

# Add pg_ivm to preload libraries
shared_preload_libraries = 'pg_ivm'
# OR
session_preload_libraries = 'pg_ivm'

После внесения этого изменения перезапустите PostgreSQL, чтобы конфигурация вступила в силу.

Каталог pgivm.pg_ivm_immv хранит информацию IMMV.

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

Например, предположим, что обычное материализованное представление определено как ниже:

test=# CREATE MATERIALIZED VIEW mv_normal AS
        SELECT a.aid, b.bid, a.abalance, b.bbalance
        FROM pgbench_accounts a JOIN pgbench_branches b USING(bid);
SELECT 10000000

Обновление кортежа в базовой таблице этого материализованного представления происходит быстро, но команда REFRESH MATERIALIZED VIEW для этого представления занимает много времени:

test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
UPDATE 1
Time: 9.052 ms

test=# REFRESH MATERIALIZED VIEW mv_normal ;
REFRESH MATERIALIZED VIEW
Time: 20575.721 ms (00:20.576)

С другой стороны, после создания IMMV с тем же определением представления, как показано ниже:

test=# SELECT pgivm.create_immv('immv',
        'SELECT a.aid, b.bid, a.abalance, b.bbalance
         FROM pgbench_accounts a JOIN pgbench_branches b USING(bid)');
NOTICE:  created index "immv_index" on immv "immv"
 create_immv 
-------------
    10000000
(1 row)

Обновление кортежа в базовой таблице занимает больше времени, чем в обычном представлении, но его содержимое обновляется автоматически, и это быстрее, чем команда REFRESH MATERIALIZED VIEW.

test=# UPDATE pgbench_accounts SET abalance = 1234 WHERE aid = 1;
UPDATE 1
Time: 15.448 ms

test=# SELECT * FROM immv WHERE aid = 1;
 aid | bid | abalance | bbalance 
-----+-----+----------+----------
   1 |   1 |     1234 |        0
(1 row)

Необходим соответствующий индекс на IMMV для эффективного IVM, потому что нам нужно искать кортежи для обновления в IMMV. Если индексов нет, это займет много времени.

Поэтому, когда IMMV создается с помощью функции create_immv, уникальный индекс создается на нем автоматически, если это возможно. Если в запросе определения представления есть предложение GROUP BY, уникальный индекс создается на столбцах выражений GROUP BY. Также, если в представлении есть предложение DISTINCT, уникальный индекс создается на всех столбцах в целевом списке. В противном случае, если IMMV содержит все атрибуты первичного ключа своих базовых таблиц в целевом списке, уникальный индекс создается на этих атрибутах. В других случаях индекс не создается.

В предыдущем примере уникальный индекс “immv_index” создается на столбцах aid и bid таблицы “immv”, и это позволяет быстро обновлять представление. Удаление этого индекса делает обновление представления более длительным.

test=# DROP INDEX immv_index;
DROP INDEX

test=# UPDATE pgbench_accounts SET abalance = 9876 WHERE aid = 1;
UPDATE 1
Time: 3224.741 ms (00:03.225)

Вы можете создать IMMV, который включает агрегатные функции.

test=# SELECT pgivm.create_immv('immv_agg',
        'SELECT bid, count(*), sum(abalance), avg(abalance)
         FROM pgbench_accounts JOIN pgbench_branches USING(bid) GROUP BY bid');
NOTICE:  created index "immv_agg_index" on immv "immv_agg"
 create_immv 
-------------
         100
(1 row)

Time: 5772.625 ms (00:05.773)

Создание этого представления занимает около пяти секунд, и обычная операция обновления требует аналогичного количества времени. В следующем примере демонстрируется обновление IMMV с использованием refresh_immv. Время выполнения будет примерно таким же, если бы вы использовали REFRESH MATERIALIZED VIEW на обычном материальном представлении с тем же определением.

test=# SELECT pgivm.refresh_immv('immv_agg',true);
 refresh_immv
--------------
          100
(1 row)

Time: 5766.292 ms (00:05.766)

Когда базовая таблица обновляется, IMMV также автоматически обновляется инкрементно, как и ожидалось.

test=# SELECT bid, count, sum, avg FROM immv_agg WHERE bid = 42;
 bid | count  |  sum  |          avg
-----+--------+-------+------------------------
  42 | 100000 | 38774 | 0.38774000000000000000
(1 row)

Time: 3.123 ms

test=# UPDATE pgbench_accounts SET abalance = abalance + 1000 WHERE aid = 4112345 AND bid = 42;
UPDATE 1
Time: 16.616 ms

test=# SELECT bid, count, sum, avg FROM immv_agg WHERE bid = 42;
 bid | count  |  sum  |          avg
-----+--------+-------+------------------------
  42 | 100000 | 39774 | 0.39774000000000000000
(1 row)

Time: 1.987 ms

После обновления строки в таблице pgbench_accounts вы можете увидеть, что агрегированные значения в immv_agg обновляются автоматически.

Вы можете найти все IMMVs в каталоге pg_ivm_immv и просмотреть их определяющие запросы, выполнив функцию get_immv_def.

test=# SELECT immvrelid AS immv, pgivm.get_immv_def(immvrelid) AS immv_def FROM pgivm.pg_ivm_immv;
   immv   |                  immv_def
----------+--------------------------------------------
 immv_agg |  SELECT pgbench_accounts.bid,             +
          |     count(*) AS count,                    +
          |     sum(pgbench_accounts.abalance) AS sum,+
          |     avg(pgbench_accounts.abalance) AS avg +
          |    FROM (pgbench_accounts                 +
          |      JOIN pgbench_branches USING (bid))   +
          |   GROUP BY pgbench_accounts.bid
 immv     |  SELECT a.aid,                            +
          |     b.bid,                                +
          |     a.abalance,                           +
          |     b.bbalance                            +
          |    FROM (pgbench_accounts a               +
          |      JOIN pgbench_branches b USING (bid))
(2 rows)

IMMV можно удалить, используя команду DROP TABLE. После удаления IMMV его запись автоматически удаляется из каталога pg_ivm_immv.

test=# DROP TABLE immv;
DROP TABLE

test=# SELECT immvrelid AS immv, pgivm.get_immv_def(immvrelid) AS immv_def FROM pgivm.pg_ivm_immv;
   immv   |                  immv_def
----------+--------------------------------------------
 immv_agg |  SELECT pgbench_accounts.bid,             +
          |     count(*) AS count,                    +
          |     sum(pgbench_accounts.abalance) AS sum,+
          |     avg(pgbench_accounts.abalance) AS avg +
          |    FROM (pgbench_accounts                 +
          |      JOIN pgbench_branches USING (bid))   +
          |   GROUP BY pgbench_accounts.bid
(1 row)

Поддерживаемые агрегатные функции: count, sum, avg, min и max. В настоящее время поддерживаются только встроенные агрегатные функции, и пользовательские агрегаты не могут быть использованы.

Когда создается IMMV, включающий агрегат, некоторые дополнительные столбцы, имена которых начинаются с __ivm, автоматически добавляются в целевой список. __ivm_count__ содержит количество кортежей, агрегированных в каждой группе. Кроме того, для каждого столбца агрегированного значения добавляется более одного дополнительного столбца для поддержания значения. Например, столбцы с именами, такими как __ivm_count_avg__ и __ivm_sum_avg__, добавляются для поддержания среднего значения. Когда базовая таблица изменяется, новые агрегированные значения вычисляются инкрементально, используя старые агрегированные значения и значения связанных дополнительных столбцов, хранящихся в IMMV.

Обратите внимание, что для min или max, новые значения могут быть пересчитаны из базовых таблиц с учетом затронутых групп, когда кортеж, содержащий текущие минимальные или максимальные значения, удаляется из базовой таблицы. Поэтому обновление IMMV, содержащего эти функции, может занять много времени.

Также обратите внимание, что использование sum или avg для типа real (float4) или double precision (float8) в IMMV небезопасно, потому что агрегированные значения в IMMV могут отличаться от результатов, рассчитанных из базовых таблиц, из-за ограниченной точности этих типов. Чтобы избежать этой проблемы, используйте тип numeric.

Простые подзапросы в FROM и подзапросы с EXISTS в ‘WHERE’ поддерживаются.

Простые CTE (WITH запросы) поддерживаются.

DISTINCT разрешен в определяющих запросах IMMV. Предположим, что IMMV определен с DISTINCT на базовой таблице, содержащей дублирующиеся кортежи. Когда кортежи удаляются из базовой таблицы, кортеж во представлении удаляется, если и только если кратность кортежа становится равной нулю. Более того, когда кортежи вставляются в базовую таблицу, кортеж вставляется в представление только в том случае, если такой же кортеж уже не существует в нем.

Физически, IMMV, определенный с DISTINCT, содержит кортежи после устранения дубликатов, и кратность каждого кортежа хранится в дополнительной колонке с именем __ivm_count__, которая добавляется при создании такого IMMV.

Когда базовая таблица усечена, IMMV также усечен, и содержимое становится пустым, если запрос определения представления не содержит агрегат без GROUP BY клаузы. Агрегатные представления без GROUP BY клаузы всегда имеют одну строку. Поэтому в таких случаях, если базовая таблица усечена, IMMV просто обновляется вместо усечения.

Инкрементальные обновления представления в основном выполняются последовательно, даже при выполнении параллельных транзакций.

Предположим, что IMMV определен на двух базовых таблицах, и каждая таблица модифицируется в разных параллельных транзакциях одновременно. В транзакции, которая была зафиксирована первой, IMMV может быть обновлен, учитывая только изменения, сделанные в этой транзакции. Однако, чтобы правильно обновить IMMV в транзакции, которая фиксируется позже, нам нужно учитывать изменения, сделанные в обеих транзакциях.

По этой причине ExclusiveLock удерживается на IMMV сразу после изменения базовой таблицы на уровне изоляции READ COMMITTED. Это гарантирует, что IMMV обновляется в последующей транзакции только после того, как предыдущая транзакция была зафиксирована. На уровне изоляции REPEATABLE READ или SERIALIZABLE ошибка возникает немедленно, если не удается получить блокировку, так как изменения, внесенные другими транзакциями, не видны на этих уровнях, и IMMV не может быть обновлен корректно в таких ситуациях.

Однако, как исключение, если IMMV имеет только одну базовую таблицу, не использует DISTINCT или GROUP BY, и модифицируется с помощью INSERT, то блокировка, удерживаемая на IMMV, является RowExclusiveLock.

Даже если мы можем получить блокировку, параллельная транзакция могла уже инкрементно обновить и зафиксировать представление до того, как мы сможем получить блокировку. На уровне изоляции REPEATABLE READ или SERIALIZABLE это может привести к неконсистентному состоянию представления. Поэтому возникает ошибка, чтобы предотвратить аномалии, когда эта ситуация обнаружена.

Если некоторые базовые таблицы имеют политику безопасности на уровне строк, строки, которые не видны владельцу материализованного представления, исключаются из результата. Кроме того, такие строки также исключаются, когда представления поддерживаются инкрементно. Однако, если новая политика определена или политики изменены после создания материализованного представления, новая политика не будет применена к содержимому представления. Чтобы применить новую политику, необходимо воссоздать IMMV.

IVM эффективен, когда мы хотим поддерживать IMMV в актуальном состоянии, и небольшая часть базовой таблицы изменяется нечасто. Из-за накладных расходов на немедленное обслуживание, IVM неэффективен, когда базовая таблица изменяется часто. Также, когда большая часть базовой таблицы изменяется или в базовую таблицу вставляются большие объемы данных, IVM неэффективен, и стоимость обслуживания может быть выше, чем обновление с нуля.

В такой ситуации мы можем использовать функцию refresh_immv с with_data = false, чтобы отключить немедленное обслуживание перед изменением базовой таблицы. После изменения базовой таблицы вызовите refresh_immv с with_data = true, чтобы обновить данные представления и включить немедленное обслуживание.