Вы наверняка знаете, что в 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.

Комментарии (3)


  1. Varim
    12.09.2017 09:08
    +1

    А в Postgres 10, агрегаты в материализованных представлениях, по локальным таблицам, обновляются автоматически, как например это делают индексированные вью в ms sql server?
    То есть любой SELECT вернёт обновленные агрегаты?


    1. angel_zar
      12.09.2017 11:39

      Нет


  1. arturgspb
    12.09.2017 15:18
    +1

    Используем это уже год точно на 9.5, но есть проблемы — часто какие-то непонятные блокировки запросов при работе с этими вьюхами, даже при конкурентном пересчёте. Не очень то довольны, конечно, но это реально удобно в целом. Закатал запрос к апи 1с например во вьюхами и пересчитываешь список чего нить раз в 15 минут. ))) Позволяет быстро задачи решать, о недостатках сказал.