Heroku Postgresでdblinkを使用する。

こんにちは

Heroku Postgresでdblinkを設定したのでその際に行ったことを備忘として残しておきます。

★下準備

「create extension」コマンドでdblinkのextensionをインストールします。

create extension dblink;

一般的にはcreate extensionの前に設定ファイルの変更等が必要なようですが、Herokuの場合はいきなりcreate extensionを実行すればOKです。

大仰に書き始めましたが、実は設定はこれだけで終わりです。(^^;;;

★dblink経由でSELECTしてみる。

dblink経由でSELECTを実行する場合のSQLは以下のようになります。

select * from dblink(
  'host=xxxx.compute-1.amazonaws.com ' ||
  'port=xxxx ' ||
  'dbname=xxxx ' ||
  'user=xxxx ' ||
  'password=xxxx', 
  
  'select id, email from accounts'
  ) as T1(id int, email varchar(255)) 
  where email like '%flect%';

構文よりdblink関数はTABLE句相当になることがわかります。

第1引数が接続情報で第2引数が実行するSQLです。上の例では必要な接続情報を明確にするために「||」でのコンカチを行っていますが、通常はもちろん1ラインで記述すれば良いです。

接続情報は「heroku config」で表示されるDATABASE_URLから取得できます。

dblink経由でSELECTした結果に対してWHERE句も機能しますし、例にはありませんがローカル側のテーブルとのJOINも問題なく行えます。

ちなみにWHERE句は第2引数の中に含めることもできます。

  ...
  'select id, email from accounts where email like ''%flect%'''

この場合、おそらく条件の絞り込みはリモート側のサーバで行われて条件にマッチしたデータのみが転送されてくると思われます。

対して外側にWHERE句を書く方法の場合はすべてのデータが転送されてきた後にローカル側で条件が評価されると想像します。つまり両者では結果が同じでもデータ転送量が異なるためパフォーマンス的には大きな差があることが予想されます。

今回試したテストデータでは顕著なパフォーマンス差は見られませんでしたが、可能な場合はリモートで実行するSQL内にWHERE句を書いた方が良いと思います。(この程度の単純なSQLの場合はオプティマイザの最適化によってどちらでも同じになる可能性もありますが、基本的な考え方としてはこれであっているはずです。)

★dblink接続をセッション内で永続化する。

先の記法はSELECTの実行毎に毎回接続情報を書かなければならないという点でかなり冗長です。

これを回避する方法としてdblinkに名前をつけて接続を開きっぱなしにすることができます。

select dblink_connect('mycon', 'host=xxxx.amazonaws.com port=xxxx ....');

一度接続が確立するとそれ以降は接続情報の代わりに名前を使用してdblink経由のSQLを実行できます。

select * from dblink('mycon',
  'select id, email from accounts'
  ) as T1(id int, email varchar(255)) 
  where email like '%flect%'

ただし定義したdblinkの有効期間はセッションなので接続ごとに再度dblink_connectを実行する必要があります。

同一セッション内で全く同じdblink_connect文を複数回実行した場合には2回目以降は「duplicate connection name」というエラーになるのでコネクションプールのある環境では扱いが面倒な気がします。。。(--

リモートユーザーの権限設定で参照／更新範囲を制限できるのでDBインスタンス単位で有効なdblinkだって作れても良さそうなものですけどね。

まぁ、どの道Heroku Postgresではcreate userできないので権限の制限はできません。。。(--

今回要件にないので更新系のSQL実行は試していませんがドキュメント見る限りHerokuでも特に問題なく実行できると思います。「dblink_exec」などの更新用の関数の実行をSELECT文内で実行するという仕様には最初びっくりしましたけど、SQLに新たな構文を追加することなく実装するために実行結果がSELECT結果として返ってくるようにしたんですかね。(^^;;;