外部データとの連携 ～FDWで様々なデータソースとつなぐ～
PostgreSQLインサイド

デジタル技術の進化により、ビジネスは大きく変わりつつあります。IoTにより取得できる多種多様なデータと、既存の業務データとを組み合わせて、新たな価値へとつなげるなど、システムは様々なデータ（システム）と連携できることが求められています。PostgreSQLには、Foreign Data Wrapper（日本語では「外部データラッパー」と呼ばれ、以降「FDW」と略します）という機能があり、RDBやNoSQLなど様々な外部データにアクセスできます。ここでは、FDWの概要と仕組み、利用時のポイントについて説明します。なお、この記事は、PostgreSQL 11.1で検証しています。

1. FDWとは

FDWとは、SELECT文やUPDATE文などのSQL文を使用して、外部にあるデータにアクセスできるようにするための、PostgreSQLの拡張機能です。
FDWは、PostgreSQLが公開しているライブラリーを利用し、RDBやNoSQLなど、連携したいデータに合わせて独自に作成できます。この仕組みを利用して、これまでに多くの企業、プロジェクト、個人がFDWを作成しています。現在では約120種類近くものFDWが公開されていますので、それらを利用することで多様なデータにアクセスできます。

PostgreSQLからアクセスできる外部データ

PostgreSQLからアクセスできる外部データの主なカテゴリーには、以下のようなものがあります。

• PostgreSQL
• PostgreSQL以外のRDB（Oracle、SQL Server、MySQLなど）
• NoSQLデータベース（MongoDB、Redis、Cassandraなど）
• カラムナデータベース（MonetDB、Citusなど）
• ファイル（CSVファイル、プレーンテキストなど）
• 地理空間情報（PostGISなど）
• LDAP
• ビッグデータ（Apache Hadoop、BigQueryなど）
• SNS（Facebook、Twitterなど）

これら外部データのアクセス先を、データソースと呼びます。

参考

以下のサイトに、FDWがまとめて掲載されています。目的に見合ったFDWを選ぶうえでの参考にしてください。

• PostgreSQL Wiki
  データソースの分野によって13のカテゴリーに分類されたFDWが掲載されています。
• PGXN（PostgreSQL Extension Network）
  PostgreSQLの拡張モジュールがFDWを含め多数公開されています。トップページで「fdw」「foreign data wrapper」といったタグを選択することでFDWに絞り込むことができます。

FDWの活用シーン：IoTデータを活用して新たなサービスを提供

FDWの様々なデータとつながる特性を活かし、既存のシステムを利用して新たなサービスを拡張できます。例えば、すでにある基幹データベースにある利用者情報と、位置情報やセンサー情報などのIoTデータを組み合わせて、新たなサービスを提供するようなシーンへの適用が期待できます。

2. FDWの仕組みと利用について

FDWは外部データを参照できることに加え、FDWによっては更新系のSQLを実行できたり、データの絞り込みやソートの処理などを、連携先で実行させる「プッシュダウン」機構を使えるものもあります。ここでは、FDWの仕組みと利用方法についての概要を説明します。

2.1 FDWの仕組み

FDWが外部のデータソースと連携するための仕組み、およびPostgreSQL内部での処理の流れについて、説明します。

外部のデータソースにアクセスする仕組み

外部のデータソースにアクセスするためには、対応するFDWをPostgreSQLの拡張機能としてインストール、および外部データを「外部テーブル」として定義しておきます。外部テーブルに対して、SQL文で問い合わせることで、FDWを介して外部データにアクセスできます。

PostgreSQL内部での処理の流れ

クライアントから外部テーブルを含むSQL文を発行すると、PostgreSQLの内部では、以下の流れで処理が実行されます。

1. クライアントからFROM句に外部テーブルを指定したSQL文を実行
2. パーサが構文を解析
3. プランナ（オプティマイザ）がFDWに実行プランを問い合わせて最適なプランを取得
4. エクゼキュータが、FROM句に指定された外部テーブルに対応するFDWに処理を依頼
5. FDWが外部データにアクセス
6. 実行した結果をPostgreSQLの内部表現に変換してエクゼキュータに返却
7. クライアントに実行結果を返却

2.2 FDWの利用の流れ

FDWを利用する際は、関連するオブジェクトを作成します。オブジェクトを作成するためには、連携先のデータソースに対応するFDWのインストール、接続先のサーバー定義、ユーザーのマッピング定義、および外部テーブルの作成を行います。ここでは、その概要を説明します。

以下は、外部にあるPostgreSQLと連携するためのFDW「postgres_fdw」を利用した場合の例を示したものです。postgres_fdwの場合、利用するための準備として下図のローカル側で上から示した順番にオブジェクトを作成します。

• 備考
  CREATE SERVER、CREATE USERMAPPING、CREATE FOREIGN TABLEのOPTIONSに指定できる項目や書式は、利用するFDWごとに違いがあります。

外部テーブルが作成されたことを確認する方法の1つとして、「\det+」コマンドを実行し外部テーブルの一覧から確認する方法があります。実行例に関しては、以下の記事をご覧ください。

• Oracleデータベースにアクセスする ～oracle_fdwの基本的な使い方～

参考

外部テーブルの作成は、CREATE FOREIGN TABLE以外に、SQL文のIMPORT FOREIGN SCHEMAを使用することで、接続先の表定義を取り込むことができます。リモート側のスキーマ単位にローカル側で一括して外部テーブルが作成されるため、同一スキーマ内に複数の外部テーブルを作成する場合に便利です。

2.3 プッシュダウンの利用

FDWにはプッシュダウン（pushdown）という機構があります。プッシュダウンは、クライアントから問い合わせのあったSQL文に含まれるWHERE句（絞り込み処理）、ORDER BY句（ソート処理）などをリモート側で実行させる機構です。例えば、WHERE句のプッシュダウンは、リモート側で対象データの絞り込み処理が行われるため、ローカルとリモートとの間のデータ転送量を抑え、通信におけるボトルネックを減少させる効果があります。具体的には、1000行の外部データに対してWHERE句で絞り込み条件を指定して5行が抽出される処理において、プッシュダウンなしの場合と、プッシュダウンありの場合では、以下のようにデータ転送量が200分の1に抑えられます。

なお、FDWの種類によっては、プッシュダウンの対象となる句が異なります。また、プッシュダウン対象の句の組み合わせによっては、プッシュダウンされないケースもありますので、利用するFDWのドキュメントなどで仕様を確認してください。

2.4 利用時のポイント

ここでは、主にpostgres_fdwを例に挙げ、FDWを利用するうえでのポイントについて、いくつかご紹介します。なお、他のFDWではポイントが異なる場合がありますので、別途FDWのドキュメントを確認してください。

更新トランザクションの制御

外部テーブルに対する更新トランザクションの利用において、留意する点があります。

• リモート側のCOMMITのタイミング

  ローカル側で外部テーブルに対して実施された更新は、ローカル側でCOMMITを発行するとリモート側に反映されます。リモート側で更新が成功してCOMMITされると、ローカル側でもCOMMITされます。ロールバックも同様です。なお、FDWは、2相コミットやプリペアステートメントに対応していませんので、注意してください。

• トランザクション分離レベル

  外部テーブルに対して発行したSQL文が単一のSQL文であっても、FDWを介してリモート側に発行される際に、複数のSQL文となる場合があります。このとき、リモート側ではトランザクションの一貫性を保つためにREPEATABLE READやSERIALIZABLEで動作します。ローカル側とリモート側のトランザクション分離レベルを一致させない場合、ローカル側とリモート側でトランザクションの実行結果が異なる可能性があります。

統計情報の最新化

統計情報を最新化することは、最適な実行計画作成のために重要です。通常のテーブルでは、自動バキュームによりANALYZEコマンドも自動的に実行され、統計情報が最新化されます。しかし、外部テーブルに対しては、自動バキュームによるANALYZEコマンドが実行されません。したがって、別途、ANALYZEコマンドを実行する必要があります。なお、オプションの指定により、外部テーブルにアクセスするタイミングで、統計情報の最新化を同時にできるFDWもあります。

連携先へのアクセス時のデータ型変換

連携先がPostgreSQL以外のデータソースの場合、PostgreSQLとはサポートしているデータ型の種類や仕様の違いがあります。FDWでは、外部テーブルと連携先のテーブルとのデータ受け渡し時に、FDWがデータを自動で変換しますが、正しく変換されない場合があります。例えば、oracle_fdwにおいて、文字データ型のVARCHAR(n)を利用する場合、PostgreSQLではnを文字数として扱い、Oracleではnをバイト数として扱うという違いがあります。そのため、データ変換が行われた際に容量を超えると、エラーが発生します。

パーティショニングと組み合わせた利用形態

FDWと、テーブルのパーティショニング機能とを組み合わせることで、複数サーバーに分散して格納された外部データを、外部テーブル（パーティション）を介してパーティションテーブルに集約するといった使い方ができます。例えば、各支店にある売上情報を本店のサーバーにあるパーティションテーブルに集約して、効率よく参照したり、分析したりするようなユースケースが考えられます。

パーティショニングは、テーブルを分割することで検索性能やメンテナンス性を向上させる機能です。パーティショニングの設定方法などについて知りたい方は、「パーティショニングの概要」で説明していますので、併せてお読みください。

データ操作の性能

ネットワークのレイテンシーと通信回数が原因となり、FDWによる外部テーブルのデータ操作が遅いという課題があります。そのため、FDWを使用する場合は、性能に問題がないかを検証することが重要です。
PostgreSQLでは、バージョンを追うごとにFDWの性能が向上しています。例えば、PostgreSQL 14では、postgres_fdwによる外部テーブルに対する一括挿入の高速化が実現しました。そのため、PostgreSQLの最新バージョンの使用も検討することが重要です。

• 外部テーブルに対する一括挿入の高速化 – PostgreSQL 14でコミットされた機能の先行紹介

外部テーブルに対するインデックス

外部テーブルに対してインデックスを作成することはできません。例えば、「CREATE INDEX ON remote_mem (カラム名);」を実行した場合、以下のようなエラーメッセージが出力されることがあります。

ERROR:  cannot create index on foreign table "remote_mem"
ERROR:  cannot create index on relation "remote_mem"

日本語環境では以下のようなメッセージが出力されることがあります。

ERROR:  外部テーブル"remote_mem"のインデックスを作成できません
ERROR:  リレーション"remote_mem"のインデックスを作成できません

3. まとめ

ここまで、FDWによって様々なデータソースにアクセスし、外部データの参照や更新系のSQLを実行できることを説明しました。ただし、更新系SQLやプッシュダウンの対応は、FDWごとに異なります。
以下に、代表的なFDWの例をまとめました。表中「レ」はサポートあり、「N/A」はサポートなしを表しています。

• 出典
  https://www.pgecons.org/
  http://creativecommons.org/licenses/by/2.1/jp/

上記に示した更新系SQLやプッシュダウン以外にも、FDWによって利用可能な操作やオプションが異なります。また、機能以外にもライセンス形態やサポート体制も異なりますので、FDWを利用する際は、業務で使用するのに問題ないライセンス形態であることや、トラブルやQAが生じた際の対応を、事前に検討することをお勧めします。

2023年9月1日更新

こちらもおすすめ

• Oracleデータベースにアクセスする ～oracle_fdwの基本的な使い方～
• Oracleデータベースにアクセスする ～oracle_fdwを使いこなすために～

本コンテンツに関するお問い合わせ