🧔 👉🏾 🤢 PostgreSQL：マテリアライズドビューとFDW 🌻 ✌️ 👨‍🎓

Postgresにはマテリアライズドビューと外部データラッパー（FDW）があることをご存知でしょう。マテリアライズドビューを使用すると、クエリをマテリアライズし、オンデマンドで更新できます。サードパーティのデータラッパーは、NoSQLストレージや他のPostgresサーバーなどの外部ソースからデータをロードする機能を提供します。

マテリアライズドビューをサードパーティのデータラッパーと組み合わせて使用するオプションをまだ検討していない可能性があります。マテリアライズドビューにより、データアクセスが高速化されます。クエリ結果は保存され、再度実行する必要はありません。 FDWを介したサードパーティデータへのアクセスは、他のシステムにあるため、非常に遅くなる可能性があります。これらの機能を組み合わせることにより、サードパーティのデータにすばやくアクセスできるようになります。

練習でこれを確認しましょう！まず、外部テーブルを作成します。

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 MATERIALIZED VIEW mat_view; Time: 364.889 ms

上記のコマンドはPostgres 9.6で実行されました。ただし、第10バージョンでは、このような改善が現れました。

可能な限りFDWサーバーで集約関数を実行します（Jeevan Chalke、Ashutosh Bapat）。

これにより、FDWサーバーから送信されるデータの量を減らすことができ、また要求サーバーから集約の負荷を取り除くことができます。この最適化は、 postgres_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

結論として、実体化された表現とサードパーティのデータのラッパーがうまく機能することに注意してください。マテリアライズドビューを使用すると、外部テーブル全体のローカルコピー（キャッシュ）またはこれらのテーブルからの集計データ（サンプル）を作成できます。このようなキャッシュのrefresh materialized view

は非常に簡単です。 refresh materialized view

。同時に、Postgres 10には、サードパーティテーブルへの集計関数を使用したクエリを高速化する改善点があります。

参照：

オリジナル：マテリアライズドビューと外部データラッパー。

PostgreSQL：マテリアライズドビューとFDW

More articles: