PostgreSQL 9.3 выйдет с довольно-таки крутой фичей, называющейся материализованные представления. Фича была разработан Кевином Гриттнером и не так давно закоммичена:
commit 3bf3ab8c563699138be02f9dc305b7b77a724307
Дата: Воскресенье 4 Марта 18:23:31 2013 -0600
Автор: Кевин ГриттнерДобавлены материализованные представления
У материализованного представления есть правило, так же как и у обычного представления, и куча, а также другие физические свойства, как у таблицы. Правило используется только для наполнения таблицы, ссылки в запросах указывают на материализованные данные.
Реализована минимальная функциональность, но и она может быть полезной во многих случаях. В настоящее время данные загружаются только “по требованию” инструкциями CREATE MATERIALIZED VIEW и REFRESH MATERIALIZED VIEW. Ожидается, что в будущих релизах будут добавлены инкрементальные обновления данных с различными настройками времени обновления, и будет дано более четкое определение самому понятию “свежие” данные. В какой-то момент даже запросы смогут использовать материализованные данные вместо данных самих таблиц, но это требует реализации описанного выше функционала в первую очередь.
Большая часть работы по составлению документации проделал Robert Haas. Ревью: Noah Misch, Thom Brown, Robert Haas, Marko Tiikkaja. Ревью по вопросам безопасности, включающее решение о том, как лучше реализовать sepgsql, ожидается от KaiGai Kohei.
Что такое материализованное представление? Если коротко, то это мутант таблицы и обычного представления. Представление это проекция данных с помощью заданного отношения, не имеющее хранилища. Таблица это… таблица!
Материализованное представление лежит где-то посредине – это проекция табличных данных, имеющее собственное хранилище. Оно использует запрос для получения своих данных, как представление, но данные хранятся как в обычной таблице. Материализованное представление может быть обновлено свежими данными с помощью повторного выполнения запроса, использованного на этапе его создания. Кроме того, оно может быть очищено (truncated). В последнем случае оно остается в состоянии, не допускающем сканирования. Также, так как материализованное представление имеет свое собственное полноценное хранилище, оно может использовать табличные пространства (tablespace) и свои собственные индексы. Обратите внимание, на то, что оно может быть беспротокольным (unlogged) (прим. перев.: то есть данные не пишутся в write-ahead log).
Вместе с данной фичей вводятся 4 новые SQL-команды:
• CREATE MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW
• DROP MATERIALIZED VIEW
• REFRESH MATERIALIZED VIEW
CREATE, ALTER и DROP – в данном случае это привычные DDL-команды для манипулирования определением представления. Наиболее же интересна команда REFRESH (по поводу ее названия были долгие споры внутри комьюнити). Эта команда может быть использована для обновления материализованного представления свежими данными повторным запуском сканирующего запроса. Обратите внимание на то, что REFRESH также может быть использован для очистки данных (truncate), хотя и не настоящей, с помощью запуска с опцией WITH NO DATA.
Материализованные представления имеют множество преимуществ в различных ситуациях: быстрый доступ к данным, которые должны быть получены с удаленного сервера (чтение файла на сервере postgres через file_fdw, и т.д.), использование периодически обновляемых данных (система кеширования), проекция данных с ORDER BY из больших таблиц, периодическое выполнение дорогих “JOIN”-ов в фоне и т.д.
Я уже могу представить себе несколько замечательных комбинаций из процедур обновления данных и фоновых воркеров. Кто вообще сказал, что автоматическое обновление данных в материализованных представлениях невозможно?
А теперь, давайте посмотрим как это работает:
postgres=# CREATE TABLE aa AS SELECT generate_series(1,1000000) AS a;
SELECT 1000000
postgres=# CREATE VIEW aav AS SELECT * FROM aa WHERE a <= 500000;
CREATE VIEW
postgres=# CREATE MATERIALIZED VIEW aam AS SELECT * FROM aa WHERE a <= 500000;
SELECT 500000
Размеры по каждому из отношений:
postgres=# SELECT pg_relation_size('aa') AS tab_size, pg_relation_size('aav') AS view_size, pg_relation_size('aam') AS matview_size;
tab_size | view_size | matview_size
----------+-----------+--------------
36249600 | 0 | 18137088
(1 row)
Материализованное представление использует хранилище (в данном случае, 18Мб) в объеме, необходимом для хранения данных, выбранных из родительской таблицы (размером 36Мб) во время выполнения запроса на создание представления.
Обновление полученного представления осуществляется очень легко.
postgres=# DELETE FROM aa WHERE a <= 500000;
DELETE 500000
postgres=# SELECT count(*) FROM aam;
count
— 500000
(1 row)
postgres=# REFRESH MATERIALIZED VIEW aam;
REFRESH MATERIALIZED VIEW
postgres=# SELECT count(*) FROM aam;
count
— 0
(1 row)
Изменения в родительской таблицы отразились на материализованном представлении только после выполнения команды REFRESH. Обратите внимание, что на момент написания этой статьи, REFRESH использовал эксклюзивную блокировку (эх…).
Материализованное представление может быть переведено в несканируемое состояние с помощь опции WITH NO DATA команды REFRESH.
postgres=# REFRESH MATERIALIZED VIEW aam WITH NO DATA;
REFRESH MATERIALIZED VIEW
postgres=# SELECT count(*) FROM aam;
ERROR: materialized view "aam" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
Появилась новая системная таблица matviews, которая содержи информацию о текущем состоянии материализованных представлений.
postgres=# SELECT matviewname, isscannable FROM pg_matviews;
matviewname | isscannable
-------------+-------------
aam | f
(1 row)
Над материализованным представлением нельзя осуществлять DML-запросы, поскольку данные представления могут не соответствовать текущему значению родительской таблицы. Обычные представления же, наоборот, выполняют соответствующий им запрос каждый раз, когда это необходимо, поэтому через них возможна модификация родительских таблиц (updatable views).
postgres=# INSERT INTO aam VALUES (1);
ERROR: cannot change materialized view "aam"
postgres=# UPDATE aam SET a = 5;
ERROR: cannot change materialized view "aam"
postgres=# DELETE FROM aam;
ERROR: cannot change materialized view "aam"
Сейчас несколько слов об улучшении и ухудшении производительности, которое вы можете получить с использованием материализованных представлений (с учетом того, что вы можете манипулировать и их индексами). Например, можно очень легко улучшить производительность запросов на выборку в материализованных представлениях, совершенно не беспокоясь о схеме данных в родительской таблице:
postgres=# EXPLAIN ANALYZE SELECT * FROM aam WHERE a = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on aam (cost=0.00..8464.00 rows=1 width=4) (actual time=0.060..155.934 rows=1 loops=1)
Filter: (a = 1)
Rows Removed by Filter: 499999
Total runtime: 156.047 ms
(4 rows)
postgres=# CREATE INDEX aam_ind ON aam (a);
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM aam WHERE a = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Only Scan using aam_ind on aam (cost=0.42..8.44 rows=1 width=4) (actual time=2.096..2.101 rows=1 loops=1)
Index Cond: (a = 1)
Heap Fetches: 1
Total runtime: 2.196 ms
(4 rows)
Обратите внимание на то, что индексы и ограничения (материализованные представления могут иметь constraints!) родительской таблицы не копируются в материализованные представления. Например, быстрый запрос сканирующий первичный ключ таблицы может закончиться смертельно долгим последовательным перебором, будучи запущенным на материализованном представлении.
postgres=# INSERT INTO bb VALUES (generate_series(1,100000));
INSERT 0 100000
postgres=# EXPLAIN ANALYZE SELECT * FROM bb WHERE a = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using bb_pkey on bb (cost=0.29..8.31 rows=1 width=4) (actual time=0.078..0.080 rows=1 loops=1)
Index Cond: (a = 1)
Heap Fetches: 1
Total runtime: 0.159 ms
(4 rows)
postgres=# CREATE MATERIALIZED VIEW bbm AS SELECT * FROM bb;
SELECT 100000
postgres=# EXPLAIN ANALYZE SELECT * FROM bbm WHERE a = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on bbm (cost=0.00..1776.00 rows=533 width=4) (actual time=0.144..41.873 rows=1 loops=1)
Filter: (a = 1)
Rows Removed by Filter: 99999
Total runtime: 41.935 ms
(4 rows)
Такие анти-паттерны безусловно не рекомендованы к применению на промышленных системах!
В целом, материализованные представления – замечательная фича, особенно для применения в приложениях, требующих кеширования. Наслаждайтесь!
Автор: zavg
