LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@necologicLabs(Rei Yamamoto)

PostgreSQL Foreign Data Wrapper (FDW) を使ったリアルタイムデータ統合手法

Posted at

はじめに

AWS 上でリレーショナルデータベースを運用する際、複数のデータベース間でデータをやり取りする必要が出てくることがあります。たとえば、Aurora PostgreSQL をソースデータベースとし、そこにある製品情報テーブル（Products）を、別 VPC にある PostgreSQL（ターゲットデータベース）側でリアルタイムに参照し、ターゲットにある Transactions テーブルと JOIN したいケースです。

  • データの複製や ETL が不要
  • リアルタイムにソースの最新データを参照
  • 過去の集計やレポーティングにも柔軟に対応

これらを可能にするのが、Foreign Data Wrapper (FDW) という機能です。本記事では、FDW とは何かという概要から始め、メリット・デメリット、ネットワーク設定、そして実際の セットアップ手順 を順を追って説明していきます。

Foreign Data Wrapper (FDW) とは？

Foreign Data Wrapper (FDW) は、PostgreSQL の機能拡張の一つで、外部データソース（他のデータベースなど）のテーブルを、ローカルのテーブルのように透過的に扱える仕組みを提供します。
実際には、FDW 用の拡張機能（postgres_fdw など）をインストールし、**「サーバー」「ユーザーマッピング」「外部テーブル」**という設定を行うことで、リモートにあるテーブルを参照できます。

FDW を使うメリット・デメリット

メリット

  1. リアルタイムアクセス:
    ソースデータベースの最新データを常に取得できるため、ETL バッチ処理不要で簡単に最新情報を活用可能。
  2. 開発効率の向上:
    SQL JOIN を書くだけで、ローカルテーブル＋リモートテーブルを統合的に利用できるため、アプリケーションやデータ分析の実装がシンプルに。
  3. データ重複の低減:
    バッチでデータを複製するアプローチと比較し、ストレージコストやデータの重複を抑制できる。

デメリット

  1. パフォーマンスリスク:
    リモートアクセスなので、ネットワーク遅延やソースデータベースの負荷状況に応じてクエリ応答速度が変動する可能性がある。
  2. ソース DB への依存度が高い:
    ソース側で障害や過負荷が発生すると、FDW を介したクエリにも直接影響が及ぶ。
  3. 一部の機能制限:
    外部テーブル上で一部の PostgreSQL 機能（トランザクション管理やトリガーなど）が使えない場合がある。

ネットワーク設定手順（VPC ピアリングや Transit Gateway）

ソース（Aurora PostgreSQL）とターゲット（PostgreSQL）は同一アカウントであっても別 VPC 上にある場合、以下のいずれかの手法で安全にネットワークを疎通させる必要があります。

  1. VPC ピアリング

    • ソースの VPC とターゲットの VPC の間にピアリング接続を作成
    • それぞれの VPC のルートテーブルに、ピアリング先の IP アドレス宛の経路を追加
    • セキュリティグループのインバウンド／アウトバウンドルールで、5432 (PostgreSQL ポート) を開放

  2. AWS Transit Gateway

    • 複数 VPC や複数リージョンを一元的に接続する必要があるケースでは Transit Gateway を利用
    • 各 VPC を Transit Gateway にアタッチし、ルートを設定
    • こちらも同様にセキュリティグループや NACL のポート開放を忘れずに

いずれの場合も、Aurora へのアクセスを許可するため、正しいセキュリティグループ設定を行う必要があります。

FDW セットアップをステップバイステップで解説

ここからは、以下の構成を想定した具体的な手順を示します。

  • ソース: Aurora PostgreSQL (別 VPC)
    • EndPoint maindb-instance-1.ctqegcgyi6u1.us-east-1.rds.amazonaws.com
    • Products テーブルを保有
  • ターゲット: PostgreSQL (別 VPC)
    • Endpoint subdb-instance-1.ctqegcgyi6u1.us-east-1.rds.amazonaws.com
    • Transactions テーブルを保有し、Products との JOIN が必要

1. Aurora PostgreSQL（ソース）側の準備

  1. VPC ピアリング or Transit Gateway 設定

    • Aurora が属する VPC と、ターゲット PostgreSQL が属する VPC をネットワーク的に疎通できる状態にする
    • Aurora インスタンスのセキュリティグループで、ターゲット側からの 5432 ポートアクセスを許可

  2. ソースデータベースのテーブル定義

    CREATE TABLE Products (
	product_id UUID PRIMARY KEY,
	product_name TEXT NOT NULL,
	category TEXT NOT NULL,
	price NUMERIC(10, 2) NOT NULL,
	stock INTEGER NOT NULL
);
    • 既にデータが入っている想定

2. PostgreSQL（ターゲット）側の準備

  1. ターゲット PostgreSQL インスタンスの起動

    • RDS for PostgreSQL や EC2 上の PostgreSQL、コンテナなど構成は問わないが、同じく 5432 ポートを受け付ける設定が必要

  2. ターゲットデータベースのテーブル定義

    CREATE TABLE Transactions (
	transaction_id UUID PRIMARY KEY,
	product_id UUID NOT NULL,
	quantity INTEGER NOT NULL,  
	total_price NUMERIC(10, 2) NOT NULL,
	transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
    • 将来的に foreign_products テーブル (FDW) と JOIN して使う

3. FDW のインストールと有効化

ターゲットデータベース（PostgreSQL）側で、postgres_fdw を利用できるようにします。

-- ターゲットデータベースに接続して実行 
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

注意: 一部のマネージドサービスやバージョンによっては、拡張機能がデフォルトで無効の場合があるため、管理コンソールなどで有効化を行っておきましょう。

4. サーバー接続 (CREATE SERVER)

ソースデータベース（Aurora PostgreSQL）への接続情報をターゲットデータベースに登録します。

CREATE SERVER source_aurora FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (
	host 'maindb-instance-1.ctqegcgyi6u1.us-east-1.rds.amazonaws.com',
	port '5432',     
	dbname 'postgres'  -- ソースのデータベース名
);
  • source_aurora はサーバー識別子であり、任意の名前で OK
  • host と port は Aurora の接続先情報
  • dbname はソースデータベースの名称

5. ユーザーマッピング (CREATE USER MAPPING)

FDW 経由でソースにアクセスする際の認証情報を設定します。

CREATE USER MAPPING FOR target_user SERVER source_aurora 
OPTIONS ( 
	user 'aurora_user', 
	password 'aurora_password' 
);
  • Sample
CREATE USER MAPPING FOR postgres SERVER source_aurora 
OPTIONS ( 
	user 'postgres', 
	password 'xxx' 
);
  • FOR target_user: ターゲットの PostgreSQL に存在するユーザー名
  • user と password: Aurora PostgreSQL でアクセス可能なユーザーとパスワード

6. 外部テーブル作成 (CREATE FOREIGN TABLE)

ソースデータベース（Aurora）の Products テーブルを、ターゲット上で**「外部テーブル」** として定義します。

CREATE FOREIGN TABLE foreign_products (   
	product_id UUID,  
	product_name TEXT,  
	category TEXT,   
	price NUMERIC(10, 2),     
	stock INTEGER
) 
SERVER source_aurora OPTIONS ( 
	schema_name 'public',  -- ソースDB上のスキーマ   
	table_name 'products'  -- ソースDB上のテーブル名
);

これにより、ターゲットデータベースから SELECT * FROM foreign_products; とするだけで、リモート Aurora の Products テーブルを参照できるようになります。

補足: PostgreSQL 9.5 以降は、IMPORT FOREIGN SCHEMA 機能を使ってソース側のスキーマにあるテーブルを一括でインポートできるため、複数テーブルを扱う場合に便利です。

設定の確認

以下のクエリにより外部テーブル情報を確認

SELECT
    fs.srvname AS server_name,
    fs.srvoptions AS server_options,
    um.umuser AS user_id,
    um.umoptions AS user_options,
    ft.foreign_table_name AS table_name,
    ft.foreign_table_schema AS table_schema
FROM
    pg_foreign_server fs
LEFT JOIN
    pg_user_mappings um ON fs.oid = um.srvid  -- 修正: umserver を srvid に変更
LEFT JOIN
    information_schema.foreign_tables ft ON fs.srvname = ft.foreign_server_name;

実行結果

Attribute Value
server_name source_aurora
server_options {host=maindb-instance-1.ctqegcgyi6u1.us-east-1.rds.amazonaws.com, port=5432, dbname=postgres}
user_id 16400
user_options {user=postgres, password=xxx}
table_name foreign_products
table_schema public

JOIN クエリの実行例

ここまでの設定が正しく行われていれば、ターゲット側の Transactions テーブルと、リモート側の foreign_products を簡単に JOIN できます。

SELECT  
	t.transaction_id,  
	f.product_name,    
	f.category,   
	t.quantity,   
	t.total_price,  
	t.transaction_date 
FROM 
	transactions t 
JOIN
	foreign_products f 
ON
	t.product_id = f.product_id
WHERE
	f.category = 'Electronics';
  • f.product_name や f.category など、Aurora のテーブル情報をリアルタイムに参照
  • t.transaction_date はターゲット側のローカルテーブル（Transactions）のカラム

これだけで、ソースデータベースの最新情報をターゲット側で活用できます。

実行結果（抜粋）

            transaction_id            | product_name |  category   | quantity | total_price |  transaction_date   
--------------------------------------+--------------+-------------+----------+-------------+---------------------
 f8b8b097-75ce-47e6-8fb8-42adbf3456fc | Growth       | Electronics |        4 |      421.16 | 2025-01-02 21:57:27
 8b8a1390-6f24-4d07-9239-adc5df9ab92e | Lose         | Electronics |        6 |      448.08 | 2025-01-03 03:29:54
 86386a4b-4eda-4ff4-9854-87b65d58e3aa | Without      | Electronics |        7 |     2537.57 | 2025-01-01 20:44:28
 203291b1-24af-443e-ad34-092151a99c61 | Wonder       | Electronics |        8 |     2098.00 | 2025-01-02 14:48:15
 fd75b3fa-f564-4599-9faf-c29dd42f82e3 | How          | Electronics |        3 |     1176.63 | 2025-01-02 15:22:55
 42a281a5-de30-4bf2-b528-bd3b942e35b6 | Pattern      | Electronics |        2 |      186.56 | 2025-01-01 06:54:58
 03a7c005-9f1c-46ca-b102-352dd0ed32ae | Win          | Electronics |        6 |     2955.60 | 2025-01-01 13:39:38
 6bc0bdd8-0d9e-47a8-853a-69ac235ca983 | Successful   | Electronics |        2 |      986.18 | 2025-01-02 23:59:29
 aaffdb6e-dbd1-4f33-a788-d62bdbf012bd | Every        | Electronics |        1 |      184.57 | 2025-01-02 07:48:06
 76f0a5db-297a-42fc-b8e6-d0e874031471 | Sing         | Electronics |        3 |     1426.11 | 2025-01-03 02:17:25
 b1dd0e57-4b49-413c-9514-aeb879200138 | Paper        | Electronics |        5 |      481.60 | 2025-01-01 04:53:51
 23f60790-6cb8-4c67-8c2c-60afe30289fb | Teacher      | Electronics |        6 |      429.30 | 2025-01-02 21:37:51
 638f7951-7a70-4da5-b87d-41e681143811 | Society      | Electronics |        3 |      302.22 | 2025-01-04 04:47:11

パフォーマンス

PostgreSQLのForeign Data Wrapper (FDW) では、キャッシュ機能は基本的にありません。外部テーブルにアクセスするたびに、リモートデータベース（FDWで設定されたサーバー）にクエリを送信し、最新データを取得します。

同一リージョンでのデータ取得でのパフォーマンスを計測してみます。
先ほどのクエリでは以下の結果でした。

(5987 行)

時間: 1094.468 ミリ秒(00:01.094)

パフォーマンスとしてはおおむね良好といえるでしょう。十分実用的といえます。

チューニング①

PostgreSQL Foreign Data Wrapper (FDW) では、リモートデータベースとの通信を効率化するために、クエリ計画の一部をリモートデータベースにプッシュダウンする機能があります。これにより、毎回全データを取得するわけではありません。

プッシュダウンとは

FDW は、以下のようなクエリの一部をリモートデータベースに送信して処理を任せることで、不要なデータ転送を最小限に抑えます。

  • プッシュダウンされる例:
    • WHERE 条件
    • LIMIT
    • 一部の JOIN 条件
    • 特定のカラムのみの選択（SELECT column_name）

クエリ最適化チューニング

サンプルクエリ

改善効果を確認するためよくないSQLを試します

SELECT 
	p1.product_name, 
	SUM(t.quantity) AS total_sold
FROM 
	transactions t
JOIN (
    SELECT *
    FROM foreign_products
) p1 ON t.product_id = p1.product_id
WHERE 
	t.transaction_date >= '2022-01-01'
	AND 
	p1.category = 'Electronics'
	AND 
	p1.product_id IN (
		SELECT product_id
		FROM foreign_products
		WHERE stock > 10
	)
GROUP BY p1.product_name;

(830 行)
時間: 531.426 ミリ秒

それほど悪くはないですが改善の余地を検討します。
EXPLAIN VERBOSE (sql) によりクエリ計画を評価します

explain_query01.png

問題点の分析

1. リモートテーブルの二重スキャン

  • リモートテーブル foreign_products が 2 回スキャンされています。

    1. 最初のスキャン:

      SELECT product_id, product_name FROM public.products WHERE ((category = 'Electronics'))
      • category = 'Electronics' で 6 行を取得。

    2. 2 回目のスキャン:

      SELECT product_id FROM public.products WHERE ((stock > 10))
      • stock > 10 で 683 行を取得。

  • 問題点:

    • リモート SQL が 2 回実行されており、リモートデータベースとの通信コストが増加しています。
    • ローカル側での結合（Hash Join）によってデータが無駄に処理されています。

2. 不要なローカル処理

  • ローカル側で Hash Join による結合処理が発生しています。
  • 特に、リモート側で処理可能な結合条件（category と stock）をローカルで行うため、効率が悪くなっています。

3. ローカル transactions テーブルの全表スキャン

  • ローカルテーブル transactions に対して Seq Scan（全表スキャン）が発生しています。

    Seq Scan on public.transactions t  (cost=0.00..685.00 rows=29997 width=20) Filter: (t.transaction_date >= '2022-01-01')

  • 影響:

    • transaction_date にインデックスがないため、全 29,997 行をスキャンしています。

改修ポイント

  1. リモートでの結合と条件適用を徹底
    リモートデータベースで category = 'Electronics' と stock > 10 を組み合わせた条件を適用し、ローカルでの不要な結合を削減
WITH filtered_products AS (
    SELECT 
		product_id, 
		product_name
    FROM 
		foreign_products
    WHERE 
		category = 'Electronics' 
		AND 
		stock > 10
)
SELECT 
	p.product_name, 
	SUM(t.quantity) AS total_sold
FROM 
	transactions t
JOIN 
	filtered_products p 
	ON 
		t.product_id = p.product_id
WHERE 
	t.transaction_date >= '2022-01-01'
GROUP BY 
	p.product_name;
  1. ローカルテーブルへのインデックス追加
    transactions テーブルの transaction_date にインデックスを追加

結果

(830 行)
時間: 387.544 ミリ秒

約30％程度削減できました。

explain_query02.png

クエリ計画でもリモートへのアクセスが１回に集約されていることがわかります。
この例は外部テーブルであるからというわけではないものの、クエリの不備によるパフォーマンス劣化はより顕著になるといえるため EXPLAIN による分析は欠かせません。

チューニング②

パフォーマンスチューニングのもう一つの手段としてキャッシュを考えます。
前述の通りFDWは標準でキャッシュを持ちません。ここではMaterialized Viewによりキャッシュしてみます。

Materialized View を使用することで、リモートデータベースへの頻繁なアクセスや複雑なクエリ処理をローカルにオフロードすることができます。この手法により、リモートデータ取得の負荷を減らし、クエリ実行時間をさらに短縮できます。

Materialized View

ここでは、foreign_products全体をキャッシュ化してみます。

CREATE MATERIALIZED VIEW products AS
SELECT *
FROM foreign_products;

SQL

WITH filtered_products AS (
    SELECT 
		product_id, 
		product_name
    FROM 
		products
    WHERE 
		category = 'Electronics' 
		AND 
		stock > 10
)
SELECT 
	p.product_name, 
	SUM(t.quantity) AS total_sold
FROM 
	transactions t
JOIN 
	filtered_products p 
	ON 
		t.product_id = p.product_id
WHERE 
	t.transaction_date >= '2022-01-01'
GROUP BY 
	p.product_name;

foreign_productsをproductsに変えたのみです。

(830 行)
時間: 353.511 ミリ秒

約10％程度の短縮となりました。
データ数、クエリ、マスターデータベースの場所、そして、ビューへのアクセス回数により、短縮効果は顕著になるでしょう。

今回はテーブル全体をキャッシュしましたが条件を絞れば効果は増すと想定されます。
Materialized Viewではリアルタイムのデータ更新が反映されないため、使いどころを選びますがバッチ処理の前にMaterialized Viewを更新（最新データ取得）し、そのデータを利用してバッチ処理をローカルにデータ同様に処理するようなケースで効果の高いソリューションとなりそうです。

運用上のポイント・注意点

  1. パフォーマンス監視
    • リモート接続なので、クエリによってはネットワーク遅延の影響を受けやすい
    • 頻繁に JOIN が発生する場合は、必要に応じてソース側のインデックス最適化やキャッシュ戦略を検討
  2. ソースデータベースへの負荷
    • FDW はソースの最新データを直接参照するため、ソースに負荷がかかるクエリを連発すると、ソース側のパフォーマンスに影響が出る可能性
    • アクセス頻度やクエリの複雑さをモニタリングし、必要に応じてMaterialized View を用意するなどの対策を
  3. 権限設定・セキュリティ
    • VPC ピアリングやセキュリティグループ設定だけでなく、DB レベルでも適切なロールと権限付与を行い、不要なアクセスを制限する
  4. 部分的なデータ移行との併用
    • リアルタイムで取得しなくてもよいデータは、ETL やスナップショットでターゲットに持ってくるほうが高速化するケースもある
    • FDW は万能ではなく、ユースケースごとに最適な設計を組み合わせるのがおすすめ
  5. PostgreSQLへの依存
    • PostgreSQLが前提となるため強く依存することになります。今後のシステム構築時の制限となりえるため、検討を十分に行うことが重要です

まとめ

Foreign Data Wrapper (FDW) を使うことで、Aurora PostgreSQL（ソース） と ** Aurora PostgreSQL（ターゲット）** が別 VPC にあっても、あたかも同じデータベースのテーブルであるかのように JOIN できます。
ネットワーク設定（VPC ピアリングや Transit Gateway） と FDW の基本設定（サーバー、ユーザーマッピング、外部テーブル） を正しく行えば、リアルタイムなデータ参照が簡単に実現可能です。

  • メリット: リアルタイムアクセス、開発効率向上、データ重複の低減
  • デメリット: パフォーマンスリスク、ソース DB 依存度、機能制限

ポイントは、ネットワークとセキュリティ設定、そしてソース側の負荷やクエリ設計をしっかり考慮すること。
本手法は、オンデマンドで最新データを参照しつつ、ターゲットのローカルテーブルとシームレスに結合したいシーンに特に有効です。ぜひプロジェクト要件に合わせて導入を検討してみてください。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?