PostgreSQL 9.6 の postgres_fdw について検証してみた

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

PostgreSQL 9.6 から postgres_fdw がリモートでの結合、ソート、UPDATE、DELETE に対応しました。
https://www.postgresql.jp/document/9.6/html/release-9-6.html

とあるようにリモートの postgres_fdw が JOIN, SORT, UPDATE, DELETE に対応しています。今回は PostgreSQL 9.6.1 の postgres_fdw を pgbench で試用してみます。

備考：postgres_fdw はリモートの PostgreSQL データベースのテーブルをローカルテーブルと同じように扱える拡張です。

PostgreSQL 9.6 と postgres_fdw のインストール

今回はソースコードから PostgreSQL 9.6 を Fedora 25 にインストールします。RHEL などでもほぼ同様の手順でインストール可能です。ビルドに必要なコンパイラなどのツール及びライブラリは既にインストール済みであるとします。

ダウンロード URL

https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.bz2

PostgreSQL のビルドとインストール手順

$ tar jxvf postgresql-9.6.1.tar.bz2
$ cd postgresql-9.6.1
$ ./configure --prefix=/usr/local/pgsql-9.6
    使用しているユーザが /usr/local/pgsql-9.6 に対して読み書きできること
    PostgreSQL は root では動作しないことに注意してください
$ make -j 8
$ make install

postgres_fdw のビルドとインストール手順

$ cd contrib/postgres_fdw
$ make
$ make install

これでインストールは完了です。

テスト用の PostgreSQL データベース作成

新しくインストールした PostgreSQL 9.6 のコマンドが使えるように、/usr/local/pgsql-9.6/bin にパスを通します。

$ export PATH=/usr/local/pgsql-9.6/bin:$PATH

データベースファイルを作成する適当なディレクトリに移動します。ディレクトリはどこでも構いません。

$ initdb --no-locale -E UTF-8 local
$ initdb --no-locale -E UTF-8 remote

local と remote ディレクトリがカレントディレクトリに作成されます。データベースが待機するデフォルトのポートは 5432 です。他の PostgreSQL と同じだと起動できないので local, remote 両方とも別のポートに変更します。筆者は

local: 5488
remote: 5499

で待機させるように

local/postgresql.conf

port = 5488             # (change requires restart)

remote/postgresql.conf

port = 5499             # (change requires restart)

と編集しました。

これで 2つの PostgreSQL を起動できるようになりました。pg_ctl コマンドで起動します。

$ pg_ctl -D local/ -l local.logfile start
$ pg_ctl -D remote/ -l remote.logfile start

この手順でインストールすると現在のユーザが PostgreSQL のスーパーユーザとして登録されますが、スーパーユーザ用のデータベースは作成されません。必要なデータベースを local/remote に作成します。

local

$ createdb -h 127.0.0.1 -p 5488

remote

$ createdb -h 127.0.0.1 -p 5499

remote データベースで pgbench テーブルを初期化する

以下のようなコマンドで pgbench テーブルを初期化します。

pgbench -i -s 20 -h 127.0.0.1 -p 5499

local データベースに remote の pgbench テーブルを定義する

psql コマンドを使って local データベースに接続します。

$ psql -h 127.0.0.1 -p 5488

リモートのデータベーステーブルをローカルで利用できるようにするには以下の手順を実行します。

postgres_fdw を使用したリモートアクセスを準備するには:

1. CREATE EXTENSION を使用して postgres_fdw 拡張をインストールしてください。

2. CREATE SERVER を使用して、接続しようとする各リモートデータベースを定義する外部サーバオブジェクトを作成してください。user および password を除く接続パラメータを、外部サーバオブジェクトのオプションとして指定します。

3. CREATE USER MAPPING を使用して、外部サーバへのアクセスを許可するデータベースユーザごとにユーザマッピングを作成します。 ユーザマッピングの user および password オプションを使用してリモートユーザのためのユーザ名とパスワードを指定します。

4. CREATE FOREIGN TABLE もしくは IMPORT FOREIGN SCHEMA を使用して、アクセスしたいリモートテーブルごとに外部テーブルを作成します。 外部テーブルのカラム定義は被参照側のリモートテーブルに一致していなければなりません。 しかしながら、外部テーブルのオプションとして正しいリモートの名前を外部テーブルのオプションに指定すれば、テーブルおよびカラム名はリモートのものと異なった名前を付ける事ができます。

https://www.postgresql.jp/document/9.6/html/postgres-fdw.html

今回は 4. の手順でスキーマごと一度にインポートします。

psql のコマンドプロンプトから postgres_fdw 拡張をインストールします。

# CREATE EXTENSION postgres_fdw;

エラーとなる場合は postgres_fdw 拡張がインストールされていません。この場合、拡張のインストール手順を確認してください。

リモートサーバを定義します。

CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '127.0.0.1', dbname 'username', port '5499');

username には現在のログイン名（ユーザ名）を利用します。ここの手順ではパスワードの設定は必要ありませんが、実際の運用時の設定ではパスワードの設定も必要でしょう。

リモートユーザのマッピングを定義します。

CREATE USER MAPPING FOR username SERVER remote OPTIONS (user 'username');

先ほどと同様に username には現在のログイン名（ユーザ名）を利用します。パスワードが必要な場合はパスワードも設定してください。

remote のスキーマをインポートします。

IMPORT FOREIGN SCHEMA public FROM SERVER remote INTO public;

remote の public スキーマを local の public スキーマにインポートできました。エラーが起きる場合は、local の public スキーマに既に同じ名前のテーブルがある、パスワードを利用している場合は間違っているなどが考えられます。

正しくインポートされると remote のテーブルが local から参照できるようになっています。

# \d
                  List of relations
 Schema |       Name       |     Type      |  Owner  
--------+------------------+---------------+---------
 public | pgbench_accounts | foreign table | username
 public | pgbench_branches | foreign table | username
 public | pgbench_history  | foreign table | username
 public | pgbench_tellers  | foreign table | username
(4 rows)

ローカルテーブルの場合、Type に “table” と表示されるところが “foreign table” と表示され、remote のテーブルだと判ります。

pgbench のベンチマーク

このベンチマークに利用しているシステムは以下の通りです。postgresql.conf のチューニングなどはありません。

　CPU: Intel core i7 4770s
　MEM: DDR4 32GB
　SSD: Intel 530 SSD
　OS: Fedora25 - Kernel 4.9.5-200.fc25.x86_64

まずテーブル本体を定義している remote データベースに直接 pgbench を実行したベンチマークを行います。

$ pgbench -c 20 -T 10 -p 5499 -h 127.0.0.1
starting vacuum...end.
transaction type: 
scaling factor: 20
query mode: simple
number of clients: 20
number of threads: 1
duration: 10 s
number of transactions actually processed: 2810
latency average = 71.790 ms
tps = 278.591907 (including connections establishing)
tps = 278.612747 (excluding connections establishing)

次に local データベースの外部テーブルに対して pgbench を実行します。

$ pgbench -c 20 -T 10 -p 5488 -h 127.0.0.1
starting vacuum...WARNING:  skipping "pgbench_branches" --- cannot vacuum non-tables or special system tables
WARNING:  skipping "pgbench_tellers" --- cannot vacuum non-tables or special system tables
ERROR:  "pgbench_history" is not a table
(ignoring this error and continuing anyway)
end.
client 18 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-4491)) WHERE ((bid = 10))
client 15 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-4330)) WHERE ((bid = 14))
client 11 aborted in state 7: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_tellers SET tbalance = (tbalance + (-3292)) WHERE ((tid = 4))
client 12 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 3669) WHERE ((bid = 6))
client 16 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-4082)) WHERE ((bid = 1))
client 8 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 779) WHERE ((bid = 1))
client 19 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 2514) WHERE ((bid = 2))
client 3 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-2704)) WHERE ((bid = 2))
client 7 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 2411) WHERE ((bid = 2))
client 14 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 3657) WHERE ((bid = 2))
client 9 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-2908)) WHERE ((bid = 12))
client 0 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 4039) WHERE ((bid = 14))
client 2 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 2167) WHERE ((bid = 7))
client 17 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-3042)) WHERE ((bid = 14))
client 6 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-2132)) WHERE ((bid = 14))
client 5 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-1994)) WHERE ((bid = 3))
client 1 aborted in state 7: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_tellers SET tbalance = (tbalance + (-652)) WHERE ((tid = 83))
client 13 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 2957) WHERE ((bid = 7))
client 10 aborted in state 7: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_tellers SET tbalance = (tbalance + (-1286)) WHERE ((tid = 136))
transaction type: 
scaling factor: 20
query mode: simple
number of clients: 20
number of threads: 1
duration: 10 s
number of transactions actually processed: 453
latency average = 442.085 ms
tps = 45.240165 (including connections establishing)
tps = 45.243502 (excluding connections establishing)

ベンチマーク結果の考察

外部テーブルを pgbench でベンチマークすると明らかな違いがあります。

• 外部テーブルは VACUUM できないためエラーが発生している
• pgbench_history がテーブルでないとエラーが発生している
• SERIALIZABLE トランザクション分離レベルのエラーが発生している
• 性能が 1/5 になっている

まずリモートテーブルに対して VACUUM は行えないのでこのエラーは正常です。

pgbench_history は外部テーブルとして定義されていない、というエラーはなぜ発生しているのかエラーメッセージからだけでは判りません。SERIALIZABLE トランザクション分離レベルのエラーはエラーメッセージ通りのエラーです。しかし、これは直接 pgbench を実行した場合にはエラーになりません。性能が大幅に低下しているのは仕組み上仕方ありません。

local データベースのログを確認してみたところ、記録されたエラーは SERIALIZABLE トランザクション分離レベルのエラーのみで他のエラーはありませんでした。念のため postgresql.conf で log_statement=’all’ に設定して確認したところ、クエリは受信され実行されていました。

SERIALIZABLE トランザクション分離レベルのエラーがリモートテーブルのみで発生する原因を調べる為、postgres_fdw のソースコードを参照したところ、以下のようなコードがありました。

/*
 * Start remote transaction or subtransaction, if needed.
 *
 * Note that we always use at least REPEATABLE READ in the remote session.
 * This is so that, if a query initiates multiple scans of the same or
 * different foreign tables, we will get snapshot-consistent results from
 * those scans.  A disadvantage is that we can't provide sane emulation of
 * READ COMMITTED behavior --- it would be nice if we had some other way to
 * control which remote queries share a snapshot.
 */
static void
begin_remote_xact(ConnCacheEntry *entry)
{
 int   curlevel = GetCurrentTransactionNestLevel();

 /* Start main transaction if we haven't yet */
 if (entry->xact_depth conn);

  if (IsolationIsSerializable())
   sql = "START TRANSACTION ISOLATION LEVEL SERIALIZABLE";
  else
   sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
  do_sql_command(entry->conn, sql);
  entry->xact_depth = 1;
 }

IsolationIsSerializable() の実態はトランザクション分離レベルの値をチェックする PostgreSQL 本体のマクロでした。しかし、log_statement=’all’ にしても

START TRANSACTION ISOLATION LEVEL SERIALIZABLE

がログに記録されておらず、ログに記録されていたのは

START TRANSACTION ISOLATION LEVEL REPEATABLE READ

でした。

pgbench の実行方法をトランザクション数を指定する方法に変えてみます。

$ pgbench -c 10 -t 50 -p 5488 -h 127.0.0.1
starting vacuum...WARNING:  skipping "pgbench_branches" --- cannot vacuum non-tables or special system tables
WARNING:  skipping "pgbench_tellers" --- cannot vacuum non-tables or special system tables
ERROR:  "pgbench_history" is not a table
(ignoring this error and continuing anyway)
end.
client 7 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-4428)) WHERE ((bid = 2))
client 0 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-1093)) WHERE ((bid = 16))
client 9 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-1724)) WHERE ((bid = 1))
client 8 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-2925)) WHERE ((bid = 8))
client 1 aborted in state 7: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_tellers SET tbalance = (tbalance + (-185)) WHERE ((tid = 96))
client 3 aborted in state 7: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_tellers SET tbalance = (tbalance + 3083) WHERE ((tid = 161))
client 6 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 503) WHERE ((bid = 1))
client 5 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + 2836) WHERE ((bid = 1))
client 2 aborted in state 8: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: UPDATE public.pgbench_branches SET bbalance = (bbalance + (-3710)) WHERE ((bid = 10))
transaction type: 
scaling factor: 20
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 50
number of transactions actually processed: 88/500
latency average = 159.365 ms
tps = 62.748990 (including connections establishing)
tps = 62.786521 (excluding connections establishing)

注目すべき箇所は

number of transactions actually processed: 88/500

です。クエリエラーはこれほど多く記録されていませんが、実際に実行できたとするトランザクションは大幅に少ない値でした。この理由はまとめに記載します。

まとめ

postgres_fdw はトランザクションがない単純なクエリでは問題なく動作しますが、トランザクションを利用すると「ローカルテーブルと同様に利用できる」とまでは言えません。先に紹介した postgres_fdw のコメントにあるように

 * Note that we always use at least REPEATABLE READ in the remote session.
 * This is so that, if a query initiates multiple scans of the same or
 * different foreign tables, we will get snapshot-consistent results from
 * those scans.  A disadvantage is that we can't provide sane emulation of
 * READ COMMITTED behavior --- it would be nice if we had some other way to
 * control which remote queries share a snapshot.

リモートテーブルに対するクエリは常に REPEATABLE READ になり、READ COMMITTED 分離レベルは使えない、と記載されています。リモートテーブルの場合、クエリに失敗するのは REPEATABLE READ と READ COMMITTED 分離レベルの違いが原因です。

参考：13.2. トランザクションの分離

https://www.postgresql.jp/document/9.6/html/transaction-iso.html

トランザクションエラーでロールバックしたトランザクションはクエリエラーとして記録されないので、PostgreSQL のログを見ただけでは判らなかったのです。

REPEATABLE READ では「反復不能読み取り」でトランザクションが失敗する可能性があります。ローカルと同じ（READ COMMITTEDの）ようには動作しない点に十分注意する必要があります。これは重要な違いですが、注意してマニュアルを読まないと見落としがちです。

https://www.postgresql.jp/document/9.6/html/postgres-fdw.html

ローカルトランザクションが SERIALIZABLE 隔離レベルを用いている時、リモートトランザクションも SERIALIZABLE 隔離レベルを使用します。 それ以外の場合には REPEATABLE READ 隔離レベルを使用します。 これは、あるクエリが複数のテーブルスキャンをリモート側で行う際に、確実に全てのスキャンにおいて一貫したスナップショットで結果を取り出すためです。 その結果、別の要求によってリモートサーバ側で競合する更新が発生したとしても、あるトランザクション内の問い合わせはリモートサーバからの一貫したデータを参照する事となります。 ローカルのトランザクションが SERIALIZABLE あるいは REPEATABLE READ 隔離レベルを用いている場合、この動作は期待通りのものでしょう。 一方、ローカルのトランザクションが READ COMMITTED 隔離レベルを使用している場合には、予想外の動作かもしれません。 将来の PostgreSQL リリースではこれらのルールに変更が加えられるかもしれません。

トランザクション利用時には十分注意が必要です。「ローカルと同じ」と過信せずに使えば postgres_fdw はとても便利な機能です。くれぐれも外部テーブルは常に REPEATABLE READ 分離レベル（少なくとも上記コメントの問題が解決するまでは）である点に十分注意して使いましょう。