Давайте обсудим, какие есть варианты обновления материализованного представления или MV (Materialized View) и какие при этом у нас есть проблемы.
Огромным преимуществом MV является хранение результатов выполнения запроса – не нужно заново его выполнять. Особенно ощутимо это дает преимущество при сохранении результатов сложных запросов. Также мы можем создавать индексы на наши MV.
Но при этом есть и большой недостаток – при поступлении новых данных, изменении набора существующих – эти изменения не отразятся в MV, так как там хранится результат первого выполнения запроса, создающий набор данных.
Для актуализации данных у нас есть команда REFRESH MATERIALIZED VIEW и у ней есть ряд параметров, а именно:
[NO] DATA – оставлять ли старые данные при обновлении MV для доступа на чтение. Тут небольшое отхождение от доки – и при NO DATA мы просто просто переводим MV в unscannable состояние и удаляем данные – доступа туда нет, нужно залить новые данные
CONCURRENTLY – чтобы мы могли не эксклюзивно заблокировать MV и на время создания он будет недоступен, а позволять получать из него старые данные и актуализировать новые. Для этого нам нужно создать уникальный индекс коррелированный с уникальным индексом основной таблицы, по которой мы строили запрос, чтобы PostgreSQL мог сопоставить измененные или новые строки.
Вот здесь и начинается самое интересное.
Для тестов будем использовать ВМ 4 ядра 16 ОЗУ 100 Гб ССД
Воспользуемся моим набором данных по перевозкам в Тайланде и зальем 60 млн.записей:
wget https://storage.googleapis.com/thaibus/thai_medium.tar.gz && tar -xf thai_medium.tar.gz && psql < thai.sql
Подключимся к БД:
sudo -u postgres psql -d thai
Включим подсчет времени выполнения:
\timing
И создадим MV просто полную выборку из таблицы билетов – 60 млн. записей:
thai=# create materialized view ms as select * from book.tickets;
SELECT 53997475
Time: 73514.510 ms (01:13.515)
Сделаем выборку 1 записи:
thai=# select * from ms limit 1;
id | fkride | fio | contact | fkseat
----+--------+--------------------+---------------------------+--------
1 | 310552 | AFANASEVA SVETLANA | {"phone": "+79298378114"} | 1
(1 row)
Создадим индекс для обновления MV:
thai=# CREATE UNIQUE INDEX ui ON ms(id);
CREATE INDEX
Time: 48958.914 ms (00:48.959)
И попробуем обновить конкурентно:
thai=# refresh materialized view CONCURRENTLY ms WITH DATA;
REFRESH MATERIALIZED VIEW
Time: 383257.527 ms (06:23.258)
И видим как фантастическое время выполнения, так и то, что запрос выполняется в 1 !!! поток:
Какие же есть варианты, чтобы и читать данные и получить обновленный набор данных по запросу?
refresh materialized view ms WITH DATA;
не подойдет – все запросы SELECT будут ждать окончания выполнения рефреша…
Один из вариантов – создать рядом второе MV, потом удалить первое и переименовать второе:
thai=# create materialized view ms2 as select * from book.tickets;
SELECT 53997475
Time: 63799.503 ms (01:03.800)
thai=# drop materialized view ms;
DROP MATERIALIZED VIEW
Time: 1402.371 ms (00:01.402)
alter materialized view ms2 rename to ms;
ALTER MATERIALIZED VIEW
Time: 1005.176 ms (00:01.005)
Итого запросы к MV будут недоступны всего 2 секунды. Ну или вариант конкурентной перестройки за овертайм.
Добавить комментарий