PostgreSQL 9.3 新機能：postgres_fdw
現在開発が佳境を迎えている PostgreSQL 9.3 ですが、先日「postgres_fdw」という外部データラッパ関連の機能がコミットされましたので、ご紹介します。なお、現在開発中の機能ですので、正式リリースまでに変更されることも考えられます（というか、きっと変更が入る）ので、その点はご承知おきください。
postgres_fdwとは？
「postgres_fdw」はcontribモジュールとして追加された外部データラッパで、外部のPostgreSQLのデータを外部テーブル経由で参照できるようにする機能です。これまではfile_fdwが唯一の標準の外部データラッパでしたが、ようやくPostgreSQL自身に接続する機能が公式にサポートされました。以前からcontribにあるdblinkと違って事前に外部テーブルなどを定義しておく必要がありますが、SQL文中で通常のテーブルと同じように記述できるので、アプリケーション開発での使いやすさはこちらの方がよいでしょう。
最新版ソースの取得
今回紹介するpostgres_fdwは9.3向けに追加されたものなので、開発中のソースコードからビルドしたPostgreSQLでしか利用できません。gitサーバからmasterブランチを取得するか、スナップショットダウンロードサイトからソースtarボールをダウンロードしましょう。
ダウンロード後は、ソースコードをビルドしてインストールしていきますが、contribも別途インストールする必要があるので注意して下さい。あくまでお試しなので、今回はホームディレクトリにインストールしてみます。コマンド例のパスについては適宜読み替えて下さい。
$ tar zxvf ~/Downloads/postgresql-9.3devel.tar.gz ... $ cd postgresql-9.3devel $ ./configure --prefix=$HOME/pgsql-9.3devel ... $ make && make install ... $ make -C contrib && make -C contrib install ...
インストールが終わったら、9.3develを使えるようにPATHなどを設定しましょう。
$ export PATH=$HOME/pgsql-9.3devel/bin:$PATH $ export LD_LIBRARY_PATH=$HOME/pgsql-9.3devel/lib:$LD_LIBRARY_PATH
また、同じ環境で別のPostgreSQLを使っている場合は別のポート番号を使うなどの対処をしてください。とりあえず、ここではそのままイケる前提で進めます。DBクラスタを作成したら、サーバを起動します。
$ initdb --no-locale -E UTF-8 $ pg_ctl start -w -l log
postgres_fdwのインストール
contribディレクトリでmake installしたじゃん、という方もいるかもしれませんが、postgres_fdwを使うにはもう一手間必要です。実はpostgres_fdwは（他のほとんどの外部データラッパと同様に）EXTENSION形式になっています。このため、postgres_fdwを使いたいデータベースごとにCREATE EXTENSION文を実行して、関連するデータベースオブジェクトを作成する必要があります。
$ psql postgres=# SELECT * FROM pg_available_extensions WHERE name LIKE '%_fdw'; name | default_version | installed_version | comment --------------+-----------------+-------------------+---------------------------------------------------- file_fdw | 1.0 | | foreign-data wrapper for flat file access postgres_fdw | 1.0 | | foreign-data wrapper for remote PostgreSQL servers (2 rows)
利用可能なEXTENSIONを見ると、確かにpostgres_fdwが入っています。さっそくこのデータベースで利用できるようにEXTENSIONを作成しましょう。psqlの\dxコマンドを使うと、インストールされているEXTENSIONが確認できます。
postgres=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------+---------+------------+---------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers (2 rows)
参照先テーブルの作成
postgres_fdwは外部にあるPostgreSQLのデータを参照するための機能ですので、どこかに実表が必要です。ここでは、簡単に用意できるpgbench*1の環境を使います。pgbenchというデータベースを新しく作って、そこに環境を構築します。
$ createdb pgbench $ pgbench -i -s 5 pgbench NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping creating tables... 100000 of 500000 tuples (20%) done (elapsed 0.10 s, remaining 0.40 s). 200000 of 500000 tuples (40%) done (elapsed 0.20 s, remaining 0.29 s). 300000 of 500000 tuples (60%) done (elapsed 0.29 s, remaining 0.20 s). 400000 of 500000 tuples (80%) done (elapsed 0.39 s, remaining 0.10 s). 500000 of 500000 tuples (100%) done (elapsed 0.49 s, remaining 0.00 s). vacuum... set primary keys... done.
さっそくDBに接続して、外部テーブルを作ってみましょう…と言いたいところですが、やはりここでも準備が必要です。postgres_fdwは、外部のPostgreSQLに接続するのに必要な情報を外部サーバやユーザマッピングというDBオブジェクトの「FDWオプション」から取得します。ここでは、postgresユーザで同じDBクラスタ内の別のデータベース（さきほど作成したpgbenchデータベース）に接続することにします。なお、接続情報のうちユーザー名とパスワードはユーザマッピングに、それ以外は外部サーバに設定することになっています。FDWオプションの名前はlibpqの接続文字列と同じですので、詳細はlibpqのドキュメントを参照して下さい。
postgres=# CREATE SERVER pgbench FOREIGN DATA WRAPPER postgres_fdw postgres-# OPTIONS (host 'localhost', dbname 'pgbench'); CREATE SERVER postgres=# CREATE USER MAPPING FOR public SERVER pgbench postgres-# OPTIONS (user 'postgres', password 'secret'); CREATE USER MAPPING
ここまでくると、あとは外部テーブルを定義して検索するだけです。元のテーブルの定義を確認して、同じデータ型で外部テーブルを定義しましょう。なお、NOT NULL制約も可能な限り定義した方が、プランナーの最適化が期待できます。ここでは、pgbench_branchesテーブルとpgbench_accountsテーブルだけを作成しています*2。
postgres=# CREATE FOREIGN TABLE pgbench_branches ( postgres(# bid int NOT NULL, postgres(# bbalance int, postgres(# filler char(88)) postgres-# SERVER pgbench; CREATE FOREIGN TABLE postgres=# CREATE FOREIGN TABLE pgbench_accounts ( postgres(# aid int NOT NULL, postgres(# bid int, postgres(# abalance int, postgres(# filler char(83)) postgres-# SERVER pgbench; CREATE FOREIGN TABLE
\dコマンドで表示されるテーブル一覧に表示されるのに加えて、外部テーブル一覧を表示する\detコマンドでは使用するサーバなどの関連情報も参照できます。
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+---------------+----------
public | pgbench_accounts | foreign table | postgres
public | pgbench_branches | foreign table | postgres
(2 rows)
postgres=# \det+
List of foreign tables
Schema | Table | Server | FDW Options | Description
--------+------------------+---------+-------------+-------------
public | pgbench_accounts | pgbench | |
public | pgbench_branches | pgbench | |
(2 rows)
検索してみましょう
これでようやく外部のテーブルを検索する準備ができました。まずは単純なクエリから。
postgres=# SELECT * FROM pgbench_branches ; bid | bbalance | filler -----+----------+-------- 1 | 0 | 2 | 0 | 3 | 0 | 4 | 0 | 5 | 0 | (5 rows)
結果だけを見ると、普通のテーブルと区別がつきませんね。実際に外部のテーブルを参照しているか、EXPLAINコマンドで実行計画を見てみましょう。
postgres=# EXPLAIN (VERBOSE) SELECT * FROM pgbench_branches ; QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.pgbench_branches (cost=100.00..116.33 rows=211 width=364) Output: bid, bbalance, filler Remote SQL: SELECT bid, bbalance, filler FROM public.pgbench_branches (3 rows)
通常のテーブルであれば「Seq Scan」と表示される場所が「Foreign Scan」となっていて、外部データを参照していることが分かります。また、この例ではVERBOSEオプションを指定しているので、外部サーバに投げているクエリも表示されています。それでは、検索条件を追加してみましょう。
postgres=# SELECT * FROM pgbench_branches WHERE bid = 1; bid | bbalance | filler -----+----------+-------- 1 | 0 | (1 row) postgres=# EXPLAIN (VERBOSE) SELECT * FROM pgbench_branches WHERE bid = 1; QUERY PLAN ------------------------------------------------------------------------------------------- Foreign Scan on public.pgbench_branches (cost=100.00..112.66 rows=1 width=364) Output: bid, bbalance, filler Remote SQL: SELECT bid, bbalance, filler FROM public.pgbench_branches WHERE ((bid = 1)) (3 rows)
きちんと条件に一致する行だけが検索できました。EXPLAIN結果を見てみると、外部サーバにWHERE句の付いたクエリを投げていますので、外部サーバからのデータ転送は実際にヒットした一件分だけです。このように、postgres_fdwは外部サーバ側で評価できる条件をリモートクエリに含めることで、データ転送量の削減を図ります。この最適化が効く条件はやや複雑なのですが、基本ルールは「リモート側とローカル側で式のセマンティクス（意味）が変わらないこと」です。このルールは列のCOLLATE（照合順）にも適用されるので、残念ながら文字列系のデータは基本的にリモートでの評価ができません。
それでは、次に外部テーブル同士を結合してみます。
postgres=# SELECT a.aid, b.bbalance postgres-# FROM pgbench_branches b JOIN pgbench_accounts a ON (b.bid = a.bid) postgres-# WHERE aid < 100 postgres-# ORDER BY aid LIMIT 10; aid | bbalance -----+---------- 1 | 0 2 | 0 3 | 0 4 | 0 5 | 0 6 | 0 7 | 0 8 | 0 9 | 0 10 | 0 (10 rows) postgres=# EXPLAIN (VERBOSE) postgres-# SELECT a.aid, b.bbalance postgres-# FROM pgbench_branches b JOIN pgbench_accounts a ON (b.bid = a.bid) postgres-# WHERE aid < 100 postgres-# ORDER BY aid LIMIT 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Limit (cost=936.28..936.30 rows=10 width=8) Output: a.aid, b.bbalance -> Sort (cost=936.28..963.57 rows=10918 width=8) Output: a.aid, b.bbalance Sort Key: a.aid -> Merge Join (cost=532.31..700.34 rows=10918 width=8) Output: a.aid, b.bbalance Merge Cond: (a.bid = b.bid) -> Sort (cost=200.59..202.72 rows=853 width=8) Output: a.aid, a.bid Sort Key: a.bid -> Foreign Scan on public.pgbench_accounts a (cost=100.00..159.06 rows=853 width=8) Output: a.aid, a.bid Remote SQL: SELECT aid, bid, NULL, NULL FROM public.pgbench_accounts WHERE ((aid < 100)) -> Sort (cost=331.72..338.12 rows=2560 width=8) Output: b.bbalance, b.bid Sort Key: b.bid -> Foreign Scan on public.pgbench_branches b (cost=100.00..186.80 rows=2560 width=8) Output: b.bbalance, b.bid Remote SQL: SELECT bid, bbalance, NULL FROM public.pgbench_branches (20 rows)
マージ結合のためのソートが入って実行計画が複雑になっていますが、通常のテーブルと同じように結合できています。また、SELECT句に列を明示すると、SELECT句やORDER BY句、結合などのローカル側で必要となる列以外は代わりにNULLを取得します。これも、データ転送量を削減するための最適化です。
まとめ
このように、postgres_fdwを使用すると外部のPostgreSQLにあるテーブルを通常のSELECT文で参照することができるようになります。複数システムが共存している場合などに、いちいちデータをダンプ＆ロードすることなく最新のデータが直接参照できますので、事前に外部テーブルを作成する必要がある点を差し引いても使いでのある機能ではないかと思います。
