Hatena::Grouppostgresql

PostgreSQL 雑記 このページをアンテナに追加 RSSフィード

2013-09-14postgres_fdw このエントリーを含むブックマーク このエントリーのブックマークコメント

PostgreSQL 9.3 にて postgres_fdw が追加されました。外部の PostgreSQL サーバの表を読み書きするための仕組みです。これまでもリモートDBにアクセスする方法には dblink や PL/Proxy がありましたが、FDW: Foreign Data Wrapper を基礎とするため、SQL として自然な構文を使えることが利点です。

機能についての詳しい説明は第26回 しくみ+アプリケーション勉強会 のスライドにありましたが、できることを簡単な表にもまとめてみます:

操作可否補記
参照 (SELECT)インデックスも使われる
追加 (INSERT) 
更新 (UPDATE)SELECT FOR UPDATE + ctid scan
削除 (DELETE)同上
絞込: 組み込みの関数/演算子initdb中に登録されるもの (OID<10000で判定)
絞込: ユーザ定義の関数/演算子×インライン展開されたSQL関数は、組み込み扱いになることも
結合 (JOIN)×リモートから取得後、ローカルで処理される
ソート (ORDER BY, LIMIT)×同上
集約 (GROUP BY)×同上
統計情報 (ANALYZE)リモート or ローカルどちらでも保持できる
トランザクション 

なお、可否=×は「リモートで実行できないため効率が悪い」の意味であり、処理はローカルで行われるので結果自体に間違いはありません。これらの機能を踏まえると、9.3時点での postgres_fdw を使う際に、性能面で気をつけるのは、以下の点でしょうか:

  • 外部表を参照する場合、その外部表単体で完結するような絞り込みを行う。同じリモートサーバ内の表との結合でも効率は悪い。
  • リモート表の大量の更新や削除は避ける。合致する行を1行ごとに ctid を指定して UPDATE/DELETE を繰り返すため。SELECT FOR UPDATE する関係で、候補行の大量取得さえもマズい。
  • ローカル/リモート間および複数のリモート間で処理がパラレルに行われることは期待できないlibpq の同期APIを使っているようなので、処理は併走しない。

シンプルな WHERE 句であれば絞込みもリモートで行われるのは素晴らしいですね。 ただ、小さなマスタ表と結合するだけでも最適化が阻害されるため、本来パラレル化の需要が高いであろうデータウェアハウス用途には、まだ荷が重いと思われます。一方、向いているのは、バッチ処理の分散化といった、生データや集計結果だけをやり取りすればよいケースでしょうか。監査目的に、write-once な外部DBにログを INSERT していくような使い方も考えられます。なんにせよ、postgres_fdw は、複数DB連携に向けてのマイルストーンですね。

2012-04-17SQLでランレングス圧縮 このエントリーを含むブックマーク このエントリーのブックマークコメント

範囲型 (range types) ⇔ 普通のテーブル構成の相互変換は、いわゆるランレングス圧縮 (RLE) になりますね。とりあえずサンプルデータを作成。

=# CREATE TABLE flat (seq serial, v integer);
=# INSERT INTO flat (v) SELECT v FROM (
  SELECT v, generate_series(1, 1 + floor(random() * 10)::integer) FROM (
    SELECT floor(random() * 10)::integer AS v FROM generate_series(1, 1000)
  ) T
) T;
INSERT 0 5594

最初はウィンドウ関数を使えば良いかと思っていたんですが、いまいち上手くいかない……。increment_if() みたいなエッジを検出するようなウィンドウ関数があれば可能かも? 再帰クエリならば簡単ですが、効率は悪い気がします:

=# CREATE TABLE rle AS
WITH RECURSIVE r AS (
  SELECT seq, v, seq AS seq_first FROM flat
UNION ALL
  SELECT flat.*, r.seq_first FROM flat, r WHERE flat.v = r.v AND flat.seq = r.seq + 1
)
SELECT int4range(min(seq), max(seq) + 1) AS seq_range, v FROM (
  SELECT seq, v, min(seq_first) AS seq_first FROM r GROUP BY seq, v
) T
GROUP BY seq_first, v
ORDER BY seq_range;

=# SELECT * FROM rle;
  seq_range  | v
-------------+---
 [1,10)      | 5
 [10,16)     | 0
 [16,23)     | 6
...
(900 rows)

逆に、展開するのは generate_series() を使えば簡単です:

=# SELECT generate_series(lower(seq_range), upper(seq_range) - 1) AS seq, v FROM rle;
 seq  | v
------+---
    1 | 5
    2 | 5
    3 | 5
...
(5594 rows)

2012-04-16PostgreSQL 9.2 - Range Types このエントリーを含むブックマーク このエントリーのブックマークコメント

PostgreSQL 9.2 の新機能 Range Types (範囲型) について調べてみました。

範囲型 = { 下端, 上端 } のペア

範囲型は2つのスカラー値から成るデータ型です。2つのペアで「範囲」や「区間」を表し、これまで2カラム使っていたところを、1カラムで表現できます:

CREATE TABLE reservation (duration_begin timestamptz, duration_end timestamptz);
CREATE TABLE reservation (duration tstzrange);

組み込みの数値型や日時型をペアとする範囲型は予め定義されていますが、CREATE TYPE AS RANGE で独自の範囲型を定義することもできます。浮動小数点型、文字列型の範囲くらいは自分で定義する機会があるかもしれませんね。

int4range, int8range, numrange, daterange, tsrange (timestamp), tstzrange (timestamptz)

物理レイアウトは以下のようになっており、通常6バイトのオーバーヘッドがあります。rangetypid のぶんが気になります。

サイズ名前説明
1vl_lenこの値のサイズ
4rangetypidサブタイプの型
Nlower下端の値
Nupper上端の値
1flags端を含むか, 下限/上限なしか、等

範囲型の作成と、値の取り出し

範囲型の文字列表現は ( ) または [ ] で囲んだ2つのスカラー型です。下端 / 上端は、その端を「含む」か「含まない」かを指定できるため、以下の4パターンがあります:

=# SELECT numrange('[3,8]'), numrange('[3,8)'), numrange('(3,8]'), numrange('(3,8)');
 numrange | numrange | numrange | numrange
----------+----------+----------+----------
 [3,8]    | [3,8)    | (3,8]    | (3,8)

なお、下端 / 上端を別々に与える形式もありますが、この場合は下端は「含む」、上端は「含まない」(以上~未満)になります。また、離散型の範囲型 (int4range, int8range, daterange) の場合は、初期化の際に自動的に「以上~未満」の形式に正規化されました。

=# SELECT numrange(3, 8), int4range('(3,8]');
 numrange | int4range
----------+-----------
 [3,8)    | [4,9)

下端 / 上端の取り出しは lower() / upper() にて、範囲同士の重なりは && 演算子を使います:

=# SELECT lower(r), upper(r), r && numrange(2, 5) AS overlaps FROM numrange(3, 8) AS r;
 lower | upper | overlaps
-------+-------+----------
     3 |     8 | t

インデックスと排他制約

範囲型を使う最大の利点は、排他制約を定義することで「範囲の重なりが無い」制約をデータベース上に表現できることです。以下では、日ごとに価格が変わる商品を定義しています。

=# CREATE TABLE daily_price (item_id integer, duration daterange, price integer);
=# CREATE EXTENSION btree_gist; -- for gist on integer
=# ALTER TABLE daily_price ADD EXCLUDE USING gist (item_id WITH =, duration WITH &&);

うっかり範囲が重なるような更新を行うとエラーになります。商品IDが異なれば範囲が重なっても良いことも表現できています。

=# INSERT INTO daily_price VALUES(1, '[2012-04-16, 2012-04-20]', 10000);
=# INSERT INTO daily_price VALUES(1, '[2012-04-21, 2012-04-30]', 12000);
=# INSERT INTO daily_price VALUES(2, '[2012-04-18, 2012-04-23]', 10000);
→ OK
=# INSERT INTO daily_price VALUES(1, '[2012-04-18, 2012-04-23]', 14000);
ERROR:  conflicting key value violates exclusion constraint "daily_price_item_id_duration_excl"
DETAIL:  Key (item_id, duration)=(1, [2012-04-18,2012-04-24)) conflicts with existing key
         (item_id, duration)=(1, [2012-04-16,2012-04-21)).

範囲型に対してはインデックスを張る場合は gist を使います。なお、排他制約は内部的には gist インデックスを作成するため、上記の定義であれば、そのままインデックスを使った検索ができます:

=# EXPLAIN SELECT * FROM daily_price WHERE item_id = 1 AND duration && '[2012-04-20, 2012-04-23]';
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Index Scan using daily_price_item_id_duration_excl on daily_price  (cost=0.00..8.27 rows=1 width=40)
   Index Cond: ((item_id = 1) AND (duration && '[2012-04-20,2012-04-24)'::daterange))

まとめ

期間の範囲、価格の範囲、面積の範囲などなど、「範囲」を保持するケースは結構多くあると思います。そのときに範囲型を使うと、重なりの検索が自然に表現でき、インデックスを使える利点があります。一方、値ごとに6バイトという見逃せないオーバーヘッドもあります。

使い時としては、そのデータ型にインデックスを張りたいか / 排他制約が必要か、を目安にすることになるでしょう。データサイズは大きくなりますが、インデックスを使えるため検索は速くなります。サイズと性能のトレードオフですね。

参考リンク

2011-12-05JOINの書き方3種 このエントリーを含むブックマーク このエントリーのブックマークコメント

PostgreSQLはテーブルの結合 (ジョイン, JOIN) が得意です。ぶっちゃけ、JOINを使わないのであれば他のDBを使ったほうが良いと思っているくらいです。で、そんなJOINなのですが、書き方に流派がいくつかあるようです。以下のようなテーブルを考えて:

CREATE TABLE item (item_id integer, price integer);
CREATE TABLE list (list_id integer, item_id integer, amount integer);

1. FROM にテーブルをならべて WHERE で条件を書く。Oracle流?

SELECT list_id, item.item_id, amount * price
  FROM item, list WHERE item.item_id = list.item_id

2. JOIN ~ ON 構文。実は括弧は要らない。

SELECT list_id, item.item_id, amount * price
  FROM item JOIN list ON item.item_id = list.item_id

3. JOIN ~ USING () 構文。こちらは括弧が必須。

SELECT list_id, item_id, amount * price
  FROM item JOIN list USING (item_id)

個人的には、3.がキータイプ数を減らせておススメ。「列名はテーブルを跨っても一意な名前を付ける必要がある」という制約もありますが、それはそれでミスを避けやすい気がします。また JOIN を使う 2 と 3 の書き方は、後から LEFT JOIN 等に変更する必要が出た場合でも、SQL文を大幅に書き換えずに済むのが地味に嬉しいです。ただ、SQL Server 等、USING が使えないDBもあるようなので、業界ではマイナーな書き方かもしれません。

L_starL_star2011/12/06 18:204. natural join構文。ただしjoinしたいカラム(例の場合item_id)以外にカラム名重複が無いケースに限る。
SELECT list_id, item_id, amount * price
FROM item natural JOIN list

pgsqlpgsql2011/12/06 18:30そういえば NATURAL JOIN もありますね。想定外の動作をしそうで怖い気はしますが…

s87s872011/12/06 22:16面倒くさがって pgbench のテーブル同士を NATURAL JOIN したら filler 列まで JOIN に使われて、結局 2. の構文にした記憶が…
本文中の列名の制約を守ると、NATURAL JOIN はハマりどころが多そうですね。

2011-12-03SELECT * FROM Web-API : PostgreSQL Advent Calendar #3 このエントリーを含むブックマーク このエントリーのブックマークコメント

PostgreSQL Advent Calendar 12/3 です。

PostgreSQL 9.1ではSQL/MED規格の一部である 外部データラッパ (FDW: Foreign Data Wrapper) がサポートされました。これを使うと、Postgresの普通のテーブル以外の外部リソースもテーブルの形で検索、参照ができます。その拡張機能の一つに www_fdw があります。これは、Web APIの結果をテーブルとして取得するための、汎用フレームワークです。今回は、この www_fdw を使って、Google API のジオコーディング (地名 → 緯度・経度 変換) をしてみます。

環境構築

PostgreSQL 9.2dev で試しましたが、もちろん 9.1 でも動作すると思います。なお、XML機能を使うので、PostgreSQL本体を野良ビルドしている方は configure 時に --with-libxml をお忘れなく。

www_fdw を PGXN から取得するため、pgxn client を先にインストールしています。その後、www_fdw のダウンロード & インストールをし、テスト用に www_db データベースにログインします。

$ sudo easy_install pgxnclient
$ sudo yum install curl-devel
$ pgxn install www_fdw
$ pg_ctl start
$ createdb www_db
$ psql -d www_db

もし pgxn install の際に pg_config が見つからない旨のエラーが出る場合には、PATHが初期値にリセットされてるのかもしれません。自分は一時的にデフォルトパスに pg_config を置いてごまかしました。

$ sudo ln -s $PGDIR/9.2/bin/pg_config /bin/pg_config

Google Geocoding FDW の作成

www_fdw は Web API を使うための汎用フレームワークで、以下の2つをやってくれます。個別のAPIに対応させるには若干のカスタマイズが必要で、特に変換関数を用意するケースは多いと思われます。

  • WHERE句の条件をURLに変換し、HTTPリクエストを投げる。
  • HTTPレスポンスをテーブルにマッピングするために、変換関数を通す。

まずはEXTENSIONをインストールし、外部サーバを定義します。Google ジオコーディングAPIでは結果の形式が XMLJSON を選べますが、今回は、後で値を取り出せるよう xml を指定しておきます。なお、plv8jsを使えば、JSONからでも手軽に値を取り出せるかもしれません。

=# CREATE EXTENSION www_fdw;
=# CREATE SERVER google_geo_server FOREIGN DATA WRAPPER www_fdw OPTIONS (
     uri 'http://maps.google.com/maps/geo?output=xml',
     response_type 'xml',
     response_deserialize_callback 'google_geo_deserialize'
   );
=# CREATE USER MAPPING FOR current_user SERVER google_geo_server;
=# CREATE FOREIGN TABLE google_geo (
     q        text,
     address  text,
     north    float8,
     south    float8,
     east     float8,
     west     float8
   ) SERVER google_geo_server;

さて、マッピングの中心になるのが google_geo_deserialize 関数です。今回はXML出力を想定しているので、(xml型) ⇒ (テーブル型) の変換を行います。若干ごちゃごちゃしていますが、実際には、愚直にXMLからフィールドを取り出しているだけです。

=# CREATE FUNCTION google_geo_deserialize(options WWWFdwOptions, response xml)
    RETURNS SETOF google_geo AS $$
SELECT
  (
    (xpath('/ns:kml/ns:Response/ns:name/text()', $2,
       ARRAY[ARRAY['ns', 'http://earth.google.com/kml/2.0']]))[1]::text,
    (xpath('/Placemark/address/text()', x))[1]::text,
    (xpath('/Placemark/ExtendedData/LatLonBox/@north', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@south', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@east', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@west', x))[1]::text::float8
  )::google_geo
FROM unnest(
  xpath('/ns:kml/ns:Response/ns:Placemark', $2,
    ARRAY[ARRAY['ns', 'http://earth.google.com/kml/2.0']])
) AS x
$$ LANGUAGE sql IMMUTABLE STRICT;

ジオコーディングを使ってみる

それでは実際に google_geo 外部表にアクセスしてみます。列 q に地名がずらりと並んでいるような仮想的なテーブルを想像してください。その中の地名の1つを取り出すイメージです。

=# \x
=# SELECT * FROM google_geo WHERE q = '東京タワー';
-[ RECORD 1 ]---------------------------------------
q       | 東京タワー
address | Tokyo Tower, Minato, Tokyo 105-0011, Japan
north   | 35.6676749
south   | 35.6495431
east    | 139.7614544
west    | 139.7294396

無事、緯度経度が取り出せました。さらに座標で検索を行う場合には point や box 型として扱ってもよいですね。

まとめ

www_fdw を使うと、PostgreSQL から直接 Web API を使うことができます。「入力はURLパラメータ、出力はXML」というタイプのAPIならば手間無く扱えます。また、それら以外の形式でも入出力のマッピング関数をカスタマイズできます。汎用フレームワークのため、ぎりぎりのチューニングは難しいかもしれませんが、HTTPを話せる多くのサービスと繋げられるのは夢が膨らみますね。twitter_fdw on www_fdw や、HTTPサーバモードのあるNoSQL DB (groonga 等) への簡易的なアクセス方法としても使えそうです。

明日は

PostgreSQL Advent Calendar 明日の担当は KaiGai.Kohei さんです。よろしく~

umitanukiumitanuki2011/12/03 15:11どうも最近のpgxn_clientにはloadってコマンドがinstallと分離して二段階になったみたいですよ。あとpg_configは--pg_configオプションがあるみたいです。
https://github.com/dvarrazzo/pgxnclient/blob/devel/pgxnclient/commands/__init__.py#L430
ヘルプ貧弱すぎ