PostgreSQL: Materialized Views and FDW / Southbridge Blog

You probably know that Postgres has materialized views and foreign data wrappers (FDW) . Materialized views allow you to materialize queries and update them on demand. Third-party data wrappers provide functionality for loading data from external sources, such as, for example, NoSQL storage or other Postgres servers.

It is likely that you have not yet considered the option of using materialized views in conjunction with third-party data wrappers. Materialized views speed up data access: query results are saved and there is no need to execute them again. Accessing third-party data through FDW can be quite slow, as it resides on other systems. By combining these functions, you can end up with quick access to third-party data.

Let's confirm this with practice! First, create a 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

fill it with data:

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

and create a materialized view based on a third-party table:

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

Now we can compare the sampling time from third-party tables and materialized views:

\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

The materialized view turned out to be much faster, but not everything is so rosy, since its refresh takes almost as much time as the selection from a third-party table:

REFRESH MATERIALIZED VIEW mat_view;
Time: 364.889 ms

The above commands were executed in Postgres 9.6. However, in the tenth version , such an improvement appeared:

Perform aggregate functions on FDW servers whenever possible (Jeevan Chalke, Ashutosh Bapat).

Thanks to it, it is possible to reduce the amount of data transmitted from the FDW server, as well as to remove aggregation load from the requesting server. This optimization is implemented in a third-party data wrapper postgres_fdw, which also can join on third-party servers (using extensions).

In Postgres 10, aggregates of third-party tables are faster than in 9.6, but still slower than selections from materialized views:

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

Using aggregates in materialized representations is not necessary at all - you can simply copy the entire foreign table and update the corresponding view as necessary (but logical replication in Postgres 10 is even better for this):

CREATE MATERIALIZED VIEW mat_view2  AS
        SELECT *
        FROM other_world;

Now we can compare the query execution speed to a foreign table and its local copy:

\o /dev/null
SELECT *
FROM other_world;
Time: 317.428 ms
SELECT * FROM mat_view2;
Time: 34.861 ms

In conclusion, I note that materialized views and third-party data wrappers work great together. Using materialized views, you can create local copies (caches) of entire external tables or aggregated data (samples) from these tables. Reload this cache very simple refresh materialized view. At the same time, improvements have appeared in Postgres 10 that speed up queries with aggregate functions to third-party tables.

References:

1. Original: Materialized Views and Foreign Data Wrappers .