PostgreSQL: материализованные представления и FDW

Вы наверняка знаете, что в Postgres есть материализованные представления (materialized views) и обертки сторонних данных (foreign data wrappers, FDW). Материализованные представления позволяют материализовывать запросы и обновлять их по требованию. Обертки сторонних данных предоставляют функциональность загрузки данных из внешних источников, таких как, например, NoSQL-хранилища или другие серверы Postgres.

Вероятно, что вариант использования материализованных представлений совместно с обертками сторонних данных вы еще не рассматривали. Материализованные представления ускоряют доступ к данным: результаты запросов сохраняются и отпадает необходимость выполнять их еще раз. Доступ к сторонним данным через FDW может быть довольно медленным, поскольку они находятся в других системах. Объединив эти функции, можно в итоге получить быстрый доступ к сторонним данным.

Давайте подтвердим это практикой! Для начала создадим стороннюю таблицу (foreign table):

CREATE DATABASE fdw_test;
connect fdw_test;
CREATE TABLE world (greeting TEXT);
connect test

CREATE EXTENSION postgres_fdw;
CREATE SERVER postgres_fdw_test FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'fdw_test');

CREATE USER MAPPING FOR PUBLIC SERVER postgres_fdw_test
OPTIONS (password '');

CREATE FOREIGN TABLE other_world (greeting TEXT)
SERVER postgres_fdw_test
OPTIONS (table_name 'world');

det
          List of foreign tables
 Schema |    Table    |      Server
--------+-------------+-------------------
 public | other_world | postgres_fdw_test

заполним ее данными:

INSERT INTO other_world
SELECT *
FROM generate_series(1, 100000);

и создадим материализованное представление на основе сторонней таблицы:

CREATE MATERIALIZED VIEW mat_view (first_letter, count) AS
        SELECT left(greeting, 1), COUNT(*)
        FROM other_world
        GROUP BY left(greeting, 1);

Теперь мы можем сравнить время выборки из сторонних таблиц и материализованных представлений:

timing

SELECT left(greeting, 1) AS first_letter, COUNT(*)
FROM other_world
GROUP BY left(greeting, 1);
 first_letter | count
--------------+-------
 1            | 11112
 2            | 11111
 3            | 11111
 4            | 11111
 5            | 11111
 6            | 11111
 7            | 11111
 8            | 11111
 9            | 11111

Time: 354.571 ms

SELECT * FROM mat_view;
 first_letter | count
--------------+-------
 1            | 11112
 2            | 11111
 3            | 11111
 4            | 11111
 5            | 11111
 6            | 11111
 7            | 11111
 8            | 11111
 9            | 11111

Time: 0.783 ms

Материализованное представление оказалось намного быстрее, однако не все так радужно, поскольку его обновление (refresh) занимает практически столько же времени, сколько и выборка из сторонней таблицы:

REFRESH MATERIALIZED VIEW mat_view;
Time: 364.889 ms

Вышеприведенные команды выполнялись в Postgres 9.6. Однако уже в десятой версии появилось вот такое улучшение:

Выполнение агрегатных функций на серверах FDW, когда это возможно (Jeevan Chalke, Ashutosh Bapat).

Благодаря ему удается уменьшить объем передаваемых от FDW-сервера данных, а также снять с запрашивающего сервера нагрузку по агрегированию. Эта оптимизация реализована в обертке сторонних данных postgres_fdw, которая также умеет выполнять соединения (join) на сторонних серверах (используя расширения).

В Postgres 10 агрегаты сторонних таблиц выполняются быстрее, чем в 9.6, но все же пока медленнее, чем выборки из материализованных представлений:

SELECT left(greeting, 1) AS first_letter, COUNT(*)
FROM other_world
GROUP BY left(greeting, 1);
 first_letter | count
--------------+-------
 1            | 11112
 2            | 11111
 3            | 11111
 4            | 11111
 5            | 11111
 6            | 11111
 7            | 11111
 8            | 11111
 9            | 11111

Time: 55.052 ms

Использовать агрегаты в материализованных представлениях совсем не обязательно — можно просто скопировать стороннюю таблицу целиком и обновлять соответствующую вьюшку по необходимости (но логическая репликация в Postgres 10 подходит для этого еще лучше):

CREATE MATERIALIZED VIEW mat_view2  AS
        SELECT *
        FROM other_world;

Теперь мы можем сравнить скорость выполнения запроса к сторонней таблице и ее локальной копии:

o /dev/null

SELECT *
FROM other_world;
Time: 317.428 ms

SELECT * FROM mat_view2;
Time: 34.861 ms

В заключение отмечу, что материализованные представления и обертки сторонних данных отлично работают вместе. С помощью материализованных представлений можно создавать локальные копии (кэши) внешних таблиц целиком или агрегированных данных (выборок) из этих таблиц. Обновить такой кэш очень просто: refresh materialized view. При этом в Postgres 10 появились улучшения, которые ускоряют запросы с агрегатными функциями к сторонним таблицам.

Ссылки:

1. Оригинал: Materialized Views and Foreign Data Wrappers.

Автор: olemskoi

Источник