The EDB Blog
October 31, 2017

You might know that Postgres supports materialized views and foreign data wrappers (fdw). Briefly, materialized views allow for queries to be materialized and refreshed on demand. Foreign data wrappers allow data to be pulled from foreign data sources, like Nosql stores and other Postgres servers.

What you might not have considered is that materialized views and foreign data wrappers can be used together. Materialized views speed data access by summarizing data so it isn't necessary to query the base tables that are referenced by materialized views. Foreign data wrappers, because they are remote, can be slow to access. By combining the two features, you can get fast access to remote data. 

Transparent data integration for Postgres-based databases. Download Now.

 

Let's see this in action! First, let's set up the 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

populate it with some data:

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

and create a materialized view on the foreign 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 select times for foreign 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 is much faster, but also a contrived example. It is interesting that refreshing the materialized view takes a similar time to selecting from the foreign table:

REFRESH MATERIALIZED VIEW mat_view;
Time: 364.889 ms

The above output is from Postgres 9.6. Thanks to this improvement in Postgres 10:
Push aggregates to foreign data wrapper servers, where possible (Jeevan Chalke, Ashutosh Bapat)

これにより、外部データラッパーサーバーから渡す必要があるデータ量が減り、集計計算が要求元のサーバーからオフロードされます。postgres_fdw、fdwは、この最適化を実行できます。拡張を伴う結合のプッシュダウンも改善されています。

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

要約すると、マテリアライズド・ビューと外部データラッパーは、うまく機能する2つの機能です。マテリアライズド・ビューでは、リモート・データをテーブル全体または集計要約のいずれかに、ローカルにキャッシュできます。キャッシュは「リフレッシュ・マテリアライズド・ビュー」を使用してリフレッシュできます。また、Postgres 10は外部テーブルに対する集計クエリーを高速化します。

Bruce Momjian氏は、EnterpriseDBの上級データベース設計者です。 

このポストはもともとBruceの個人的な投稿です。blog.

bruce.momjian's picture

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business...