PostgreSQL 9.5 の IMPORT FOREIGN SCHEMA を使ってみた

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください

【5/21開催】Azure OpenAI ServiceによるRAG実装ガイドを公開しました
生成AIを活用したユースケースで最も一番熱いと言われているRAGの実装ガイドを公開しました。そのガイドの紹介をおこなうイベントです!!
https://tech-lab.connpass.com/event/315703/

こんにちは。サイオステクノロジー OSS サポート担当 W です。

今回は PostgreSQL 9.5 の目玉機能でもある IMPORT FOREIGN SCHEMA を紹介します。以前から CREATE FOREIGN TABLE は利用できましたが、SCHEMA としてインポートできるようになり、より便利になりました。

1 CREATE FOREIGN TABLE
2 IMPORT FOREIGN SCHEMA
3 IMPORT FOREIGN SCHEMA の導入
4 postgres_fdw の導入
5 CREATE EXTENSION
6 CREATE SERVER
7 CREATE USER MAPPING
8 IMPORT FORGIN SCHEMA
9 定義やインポートの削除
10 外部テーブルのベンチマーク
11 まとめ
12 参考

CREATE FOREIGN TABLE

以前から IMPORT FOREIGN TABLE により、リモート PostgreSQL のテーブルをインポート可能でした。しかし、テーブルをインポートするには長い SQL を書く必要がありました。

CREATE FOREIGN TABLE の書式

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

このような書式をもっており、カラム名やデータ型を指定してインポートする必要がありました。

CREATE FOREIGN TABLEの使用例

 CREATE FOREIGN TABLE remote.customers (
     id int NOT NULL,
     name text,
     company text,
     registered_date date,
     expiry_date date,
     active boolean,
     status text,
     account_level text) SERVER dest_server OPTIONS (schema_name 'public');

この例では dest_server に定義されている customers テーブルを public のテーブルとして利用できるように定義しています。

外部サーバからテーブルのインポートはできるのですが、一つ一つのテーブルに対して設定するのは手間もかかり、間違いも起きやすかったです。

IMPORT FOREIGN SCHEMA

IMPORT FOREIGN SCHEMA は CREATE FOREIGN TABLE に比べとてもシンプルにテーブルをインポートできます。

IMPORT FOREIGN SCHEMA の書式

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

実際の使用例を見ると CREATE FOREIGN TABLE との違いは一目瞭然です。

IMPORT FOREIGN SCHEMA の使用例

 IMPORT FOREIGN SCHEMA public
   FROM SERVER dest_server INTO remote;

この例では dest_server の public スキーマをローカルの remote スキーマにインポートしています。dest_server の public スキーマ内のテーブル定義が丸ごと remote にインポートされます！今まで CREATA FOREGIN TABLE は面倒すぎて使っていない方も多かったと思います。IMPORT FOREIGN SCHEMA ならとても簡単です。

LIMIT TO でインポートするテーブルを限定したり、EXCEPT で除外するテーブルを指定することも可能です。

customers, purchases だけをインポート

 IMPORT FOREIGN SCHEMA public
   LIMIT TO (customers, purchases)
   FROM SERVER dest_server INTO remote;

reports, audit を除外

 IMPORT FOREIGN SCHEMA public
   EXCEPT (reports, audit)
   FROM SERVER dest_server INTO remote;

IMPORT FOREIGN SCHEMA の導入

IMPORT FOREIGN SCHEMA はデフォルトで有効な機能ではありません。外部データラッパーが必要で、利用できるようにするには準備が必要です。今回はリモートデータベースに PostgreSQL を使用するので postgres_fdw が必要です。対応する外部データラッパーが在れば他のデータベースなどにも利用できます。手順の概要は以下の通りです。

必要条件
– ローカルの PostgreSQL は 9.5 以降であること（リモートの PostgreSQL は古いバージョンでも構わない）

手順

postgres_fdw をローカルサーバに導入する
CREATE EXTENSION postgres_fdw をローカルデータベースで実行する
CREATE SERVER でリモートデータベースサーバの定義を作成する
CREATE USER MAPPING でリモートデータベースユーザとローカルユーザのマッピングを定義する
IMPORT FOREIGN SCHEMA でリモートデータベースをインポートする

順番に解説します。

postgres_fdw の導入

postgres_fdw は contrib モジュールです。ソースから PostgreSQL をインストールした場合、contrib/postgres_fdw ディレクトリで make install を実行するとインストールできます。RPM を利用している場合、yum install postgresql-contrib で導入できます。

CREATE EXTENSION

psql コマンドでリモートデータベースをインポートするローカルのデータベースに接続します。

例： psql -h 127.0.0.1 -p 5495 mydb

psql から

CREATE EXTENSION postgres_fdw;

を実行します。

CREATE EXTENSION の実行例

pg_user@localhost ~=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

問題がなければエラーメッセージがなく終了します。

CREATE SERVER

CREATE SERVER の書式

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

詳しいオプションの説明はマニュアルを参照してください。

CREATE SERVER の実行例

pg_user@localhost ~=# CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', dbname 'pg_user', port '5495');
CREATE SERVER

この例では remote という名前のサーバを定義しています。外部データラッパーに postgres_fdw を使用し、リモートデータベースサーバの接続オプションを OPTIONS で host, dbname, port を指定しています。

CREATE USER MAPPING

実際にリモートデータベースに接続するにはローカルデータベースのユーザとのユーザのマッピングを作成しなければなりません。CREATE USER MAPPING で定義します。

CREATE USER MAPPING の書式

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ , ... ] ) ]

CREATE USER MAPPING の実行例

pg_user@localhost ~=# CREATE USER MAPPING FOR pg_user SERVER remote OPTIONS (user 'pg_user');
CREATE USER MAPPING

この例ではリモートユーザの pg_user をローカルの pg_user にマッピングしています。

IMPORT FORGIN SCHEMA

最後に IMPORT FORGIN SCHEMA でリモートスキーマをインポートします。

IMPORT FORGIN SCHEMA の実行例

pg_user@localhost ~=# IMPORT FOREIGN SCHEMA public FROM SERVER remote INTO public;
IMPORT FOREIGN SCHEMA

この例では remote のサーバの public スキーマをローカルの public にインポートしています。

インポート後のテーブル一覧の例

pg_user@localhost ~=# \d
                    リレーションの一覧
┌──────────┬────────────────────┬──────────────┬─────────┐
│ スキーマ │        名前        │      型      │ 所有者  │
├──────────┼────────────────────┼──────────────┼─────────┤
│ public   │ counter            │ 外部テーブル │ pg_user │
│ public   │ myusers            │ 外部テーブル │ pg_user │
│ public   │ pgbench_accounts   │ 外部テーブル │ pg_user │
│ public   │ pgbench_branches   │ 外部テーブル │ pg_user │
│ public   │ pgbench_history    │ 外部テーブル │ pg_user │
│ public   │ pgbench_tellers    │ 外部テーブル │ pg_user │
│ public   │ test               │ テーブル     │ pg_user │
│ public   │ url_access_counter │ 外部テーブル │ pg_user │
│ public   │ user_master        │ 外部テーブル │ pg_user │
└──────────┴────────────────────┴──────────────┴─────────┘
(9 行)

このスキーマではローカルテーブルの test 以外は全てインポートされた外部テーブルであることがわかります。pgbench を使ったベンチマークをした事がある方は、pgbench のテーブルがあることも分かると思います。後にベンチマークを行います。

定義やインポートの削除

作成したサーバ定義やスキーマを削除したくなる場合もあります。

DROP SERVER remote CASCADE;

を実行すると関連するユーザマッピングやインポートしたテーブルを含めて削除してくれます。個別に削除するには DROP FOREIGN TABLE で削除します。テーブル自体は削除されず、外部テーブル定義のみが削除されます。外部テーブルは削除できません。削除しようとすると

pg_user@localhost ~=# DROP TABLE user_master;
ERROR:  42809: "user_master" is not a table
HINT:  Use DROP FOREIGN TABLE to remove a foreign table.
LOCATION:  DropErrorMsgWrongType, tablecmds.c:791

のようなエラーが発生します。

外部テーブルのベンチマーク

リモートのデータベースは別のホストではなく、同じホスト上の別サーバとして別ポートで待機している PostgreSQL です。ローカル／リモート共に執筆時点（2015/2/25）の git リポジトリの PostgreSQL 9.5 です。

ローカル側ポート： 5499
リモート側ポート： 5495

pgbench プログラムを使ってローカル／リモートの PostgreSQL をベンチマークします。-N オプションを付けて一部の更新を無くしています。これは外部テーブルへのアクセスがローカルテーブルと同じようには動作せずエラーになるからです。

以下は更新を含む pgbench 結果です。

外部テーブル経由

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5499 -c 40 -s 2000 -n -N
Scale option ignored, using pgbench_branches table count = 1
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 40
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 400/400
latency average: 0.000 ms
tps = 401.474616 (including connections establishing)
tps = 417.503413 (excluding connections establishing)

直接アクセス

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5495 -c 40 -s 2000 -n -N
Scale option ignored, using pgbench_branches table count = 1
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 40
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 400/400
latency average: 0.000 ms
tps = 379.430323 (including connections establishing)
tps = 393.706988 (excluding connections establishing)

更新が入っている場合、遜色ない速度で実行されていることが分かります。次は参照のみの結果です。

外部テーブル経由

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5499 -c 90 -s 2000 -n -S
scale option ignored, using count from pgbench_branches table (1)
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 90
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 900/900
latency average: 0.000 ms
tps = 4749.240122 (including connections establishing)
tps = 4772.880985 (excluding connections establishing)

直接アクセス

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5495 -c 90 -s 2000 -n -S
scale option ignored, using count from pgbench_branches table (1)
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 90
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 900/900
latency average: 0.000 ms
tps = 8238.514138 (including connections establishing)
tps = 8311.517311 (excluding connections establishing)

参照のみは更新有りに比べ、何十倍も速いです。TPS が増えるほど、オーバーヘッドの微妙な差が大きな差として現れます。外部テーブル経由はかなり遅いことが分かります。このため外部テーブルは参照先の分散などには適しないことが分かります。

まとめ

IMPORT FORGIN SCHEMA はテーブルをコピーしたい場合にはとても便利です。外部テーブルをインポートしてローカルのテーブルにコピーするだけです。

pg_user@localhost ~=# SELECT * INTO new_table FROM pgbench_accounts ;
SELECT 100000

外部テーブルはトランザクションでも使えるのですが、pgbench とは相性が悪いようです。並列実行されたトランザクションでエラーになり、ベンチマークは取れませんでした。

client 69 aborted in state 11: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: SELECT bid, bbalance, filler, ctid FROM public.pgbench_branches WHERE ((bid = 1)) FOR UPDATE

このことからも分るように、今のところは外部テーブルを速度の遅いローカルテーブル、のように利用するには注意が必要です。