PostgreSQL 9.1 の新機能

PostgreSQL 9.1 の新機能

FORCIA, Inc. 板垣 貴裕

 

PostgreSQL 9.1.0 が 2011年9月12日にリリースされました。 最新版のバイナリやソースコードは "ダウンロード用ページ" で配布されています。

9.1 では、9.0 で新規に採用されたレプリケーション機能の使い勝手の強化の他、外部のファイルや DB に直接アクセスできる SQL/MED や、拡張モジュールの管理機能など、さらなる強化が行われています。

互換性に関する注意

最初が注意になってしまいますが、以前のバージョンとの互換性の無い設定がデフォルトに変更されています。比較的多くのアプリケーションで問題になる可能性があるため、あえて強調して注意させてもらいます。

standard_conforming_strings のデフォルトが on に変更

standard_conforming_strings = on がデフォルトになりました。E'...' 形式でない文字列内の \ は、エスケープとしては扱われなくなります。具体的には：

WARNING:  nonstandard use of \\ in a string literal at character

の警告が出たものの無視しているか、escape_string_warning = off で無理やり黙らせていたケースで問題になります。SQL文字列中では ' (シングルクォート) や \ (バックスラッシュ, 円記号) はエスケープが必要なため、\ を使ってしまったことがあるのではないでしょうか。 こういったアプリケーションは、9.1 のデフォルト設定だと動作しなくなる恐れがあります。ただ、互換性を維持するオプションも用意されています。

9.1 のデフォルト設定では、文字列中にエスケープが必要な場合は、E'...' の形式の文字列 (先頭に E) を使わなければなりません。E のつかない文字列は、バックスラッシュは単にバックスラッシュ1文字として扱われます。また、シングルクォートは2つ重ねて '' とエスケープします。

本来はきちんと '' と E'' 文字列を使い分けて欲しいのですが、どうしても SQL の改修が難しい場合には、設定パラメータ standard_conforming_strings = off に変更することで、9.0 までと互換性のある動作になります。 9.1 のデフォルトの動作は標準SQLで規定されたものなのですが、残念ながら準拠している DBMS 製品が少なすぎるため、あまり作法として定着していないのは否めません。(個人的には、そこまでストイックにならなくても、互換性を重視して欲しいと思うのですが……。)

レプリケーションとクラスタ構成

さて、ここからは 9.1 の新機能を紹介していきます。 まずは、レプリケーション関連の新機能です。 同期レプリケーションが追加された他、9.0 のユーザからのフィードバックを反映して、運用や監視が大幅に強化されました。 使い方の具体的な手順は、別記事「ストリーミング・レプリケーションの構築」でも解説しています。

同期レプリケーション

ストリーミング・レプリケーションを「同期モード」に設定できるようになりました。 デフォルトの「非同期モード」では、更新処理のコミット直後にマスタサーバが故障すると、スレーブにまだ変更が伝わっていない可能性があります。 この状態のままフェイルオーバーするとコミットの結果が失われる場合があります。 一方、同期モードでは遅延をゼロに抑えられるため、高い信頼性を求められる用途へもレプリケーションの適用範囲が広がりました。 ただし、ここでの「同期」とは、「結果を失わない」という意味であることに注意してください。同期モードであっても、更新直後にスレーブで参照クエリ実行すると、まだ更新が反映されていない場合があります。

同期レプリケーションを使うには、スレーブに対して「名前」を設定し、その名前にマスタの「同期サーバリスト」に加えるだけです。 スレーブに対する名前の設定は、recovery.conf の primary_conninfo パラメータに application_name を加えます。 マスタの設定は、postgresql.conf の synchronous_standby_names に名前をカンマ区切りで並べます。 同期モードのレプリケーションは、どうしても性能へのオーバーヘッドがあるため、例えば「バックアップ用の1台は同期モードで、他の負荷分散用のスレーブは非同期モードで」などの構成も、チューニングとして有効です。

レプリケーションの運用性改善

PostgreSQL のレプリケーションの初期設定をより簡略化し、高可用性を高めるための改善です。

pg_basebackup - ベースバックアップ用ツール
オンライン・バックアップや、レプリケーションの最初のデータベースコピーに利用できるツールです。 pg_dump 並みの簡単さで、オンライン・バックアップを取得できます。 特にテーブルスペースを使っている場合も、自動的にすべてコピーしてくれるので手間がかかりません。
ただし、常にデータベース全体のバックアップを取得することに注意してください。一部のみをバックアップしたり、前回からの差分や増分バックアップをする機能はありません。
pg_ctl promote - フェイルオーバー用コマンド
スレーブは自由にマスタから切り離してフェイルオーバー (更新可能な状態への昇格 = promote) できますが、9.0 の手順は 予め trigger_file で設定したパスに touch 等でファイルを作成するというものでした。9.1 ではそれに加えて、pg_ctl promote コマンドでも昇格するようになり、手順が洗練されました。手順が簡略化される上、このコマンドを使ったほうが昇格するまでの時間を短縮 (秒オーダーですが) できます。
フェイルオーバー後に新たなマスタに再接続
recovery.conf にて recovery_target_timeline='latest' を指定すると、フェイルオーバー後の新しいマスタに、既存のスタンバイサーバが接続できます。 9.0　ではフェイルオーバー / スイッチオーバー後は、全てのスレーブを破棄し、バックアップからやり直して再接続する必要がありましたが、latest の設定を使えば、接続先のマスタを切り替えるだけで、スレーブのデータベースはそのまま使えます。 バックアップを省いてクラスタを再構成できるため、切り替え後、単独のサーバだけで運用しなければならない時間が大幅に短縮できます。
スタンバイでのレプリケーションの停止と再開
スレーブ・サーバにて、レプリケーションを一時停止と再開するオプションが追加されました。 用途としては、スタンバイDBをバックアップ用のスナップショットとして使ったり、アプリケーションのステージングに利用することも考えられます。
  • pg_xlog_replay_pause() : 一時停止
  • pg_xlog_replay_resume() : 再開
restart_after_crash パラメータ
もしデータベースプロセスに異常が発生した場合には、通常は自動的にリカバリ処理を行い、復旧を試みます。 しかし、クラスタ制御ソフトウェアと組み合わせて運用している場合には、自動リカバリが望ましくない場合もあります。 このパラメータにより、クラッシュ時の自動リカバリするか否かを制御できるようになりました。

レプリケーションの状態確認

レプリケーションが正常に行われているか、遅延は無いか等を確認するための機能が追加されました。

pg_stat_replication: マスタサーバでのレプリケーション状態確認
レプリケーションのマスタサーバ側で利用できるビューです。 接続中のスタンバイサーバの一覧と、その状況を確認できます。 例えば pg_current_xlog_location() と sent_location 列に大きな開きがある場合には、転送するまでの遅延が発生しており、マスタサーバの性能不足を疑えます。 一方、sent_location とスタンバイサーバでの pg_last_xlog_receive_location() の開きは、ネットワークの遅延やスタンバイサーバの性能不足を疑えます。
pg_last_xact_replay_timestamp(): スタンバイサーバでの最終変更日時
レプリケーションのスタンバイ側で、最後に反映処理をした時間を返す関数です。今までは xlog 中の位置は取得できましたが、この関数で時間も分かるようになりました。ただ、過去の時間が返却されても、しばらく更新が無い場合には「遅れ無し」ということもあり得るので、よくある質問の「スタンバイの遅れはどれくらい？」の直接の回答ではないことに注意してください。

SQL/MED (外部データ連携)

SQL/MED (Management of External Data) は、DBMS の外部のデータを SQL から管理、アクセスするための SQL 仕様です。 9.1 は、その中の「外部テーブル (Foreign Table)」機能をサポートしました。 「外部データラッパ (Foreign Data Wrapper)」と呼ばれる「ドライバ」を追加することで、外部データをテーブルの形で参照できます。 サポートしているのは読み取りだけで、更新はできませんが、様々な外部データにアクセスできる、興味深い機能です。

9.1 では、まず、タブまたはカンマ区切りのファイルを読み込むことができる file_fdw モジュールが追加されました。COPY FROM コマンドの読み込み部分だけを抜き出したものですが、インポートした後すぐに加工して他のテーブルに移し替えるような必要がある場合には、作業用のテーブルが不要になるため効率的です。

将来的には、リモートサーバのDB (PGでもそれ以外でも) との連携や、ウェブAPIへの直接接続など、ありとあらゆるデータをSQLという柔軟な言語で検索、整形できる可能性を秘めています。 例えば、Oracle Database と接続するための oracle_fdw もリリースされており、他にも数多くのドライバが PGXN: fdw に登録されています。 比較的簡単な API で外部データラッパを作成できますので、開発者の方はぜひ挑戦してみてください！

関連リンク

N-gram全文検索のサポート

pg_trgm モジュールが強化され、GiST または GIN インデックスをサポートすると共に、LIKE 演算子でインデックスが使えるようになりました。 ただし、日本語文字 (アルファベットと数字以外) を扱うには、9.1 の段階では、ソースコード中の設定を一部書き換えて、再コンパイルする必要があります。詳しくは、記事「中間一致検索でインデックスを使うには」を参照してください。

GiST/GIN インデックスをベースにしているため、レプリケーションに対応した N-gram インデックスとして有効です。(textsearch_senna はレプリケーションに対応していません。) 組み込みの全文検索 (+ textsearch_ja) での単語単位のインデックスでは満足できない場合、こちらも選択肢となりえます。

拡張モジュールのサポート

EXTENSION という管理単位が導入されました。 PostgreSQL はユーザ定義関数やデータ型などをサポートしており拡張性に優れますが、これまではパッケージの管理単位が無かったため、関数や型を個別にインストール / 管理する必要がありました。 EXTENSION は、拡張モジュールをパッケージ単位で扱うための機能です。

例えば、hstore モジュールをインストール / アンインストールする場合は、以下のような違いがあります。 シェルにログインする必要がなくなり、環境によって変化する $PGHOME パスも意識せずに済むようになりました。

  • 9.0以前: シェルから psql -f 
    $ psql -d dbname -f $PGHOME/share/contrib/hstore.sql
$ psql -d dbname -f $PGHOME/share/contrib/uninstall_hstore.sql
  • 9.1以降: DBにログインして CREATE EXTENSION 
    =# CREATE EXTENSION hstore;
=# DROP EXTENSION hstore;

また、拡張モジュールをより容易に入手できるよう、PGXN: PostgreSQL Extension Network というサイトもオープンしました。 Perl の CPAN を参考に、PostgreSQL 用のモジュールを集積し、検索しやすく整理することを目標としているそうです。 モジュールの検索からインストールまでの筋道が整理されたことで、今後はますます PostgreSQL の拡張性が活かされるようになると良いですね！ (参考: PGXN in Wiki)

性能強化とチューニング

UNLOGGED TABLE
普通のテーブル、一時テーブル (TEMP TABLE) に加えて、UNLOGGED TABLE というテーブルタイプが追加されました。 UNLOGGED オプションを使うと、挿入 / 更新 / 削除の際にトランザクションログ (WAL) が書かれません。そのため、挿入や更新の多いテーブルを UNLOGGED にすることで、更新処理が大幅に高速化します。 一時テーブルとの違いは、他のセッションからも見えることと、サーバが正常終了する限りはデータが保持されることです。
ただし、信頼性の要であるWALを書かないため、クラッシュの際にはテーブルが空になることに注意が必要です。また、ストリーミング･レプリケーションでは UNLOGGED テーブルは複製されないことに注意してください。逆に、これを利用してマスタの作業用テーブルとして使う手もあります。
KNN GiST: K-近傍検索でインデックスが利用可能に
近傍検索に GiST インデックスが利用できるようになりました。point 型には距離を計算する <-> 演算子がありますが、この演算子を使って、特定の位置から近い順にソートする処理が高速化できます。GPS の緯度/経度情報から、近隣の施設を検索するようなアプリケーションが想定されます。 
=# CREATE TABLE tbl (id serial, location point);
=# CREATE INDEX ON tbl USING gist (location);
=# EXPLAIN (COSTS OFF)
     SELECT * FROM tbl ORDER BY location <-> '(2, 3)' LIMIT 3;
                   QUERY PLAN
-------------------------------------------------
 Limit
   ->  Index Scan using tbl_location_idx on tbl
         Order By: (location <-> '(2,3)'::point)
(3 rows)
パーティション全体のソートの最適化
ひさびさにパーティショニングの強化です。いわゆる ORDER BY の push down ができるようになりました。特にインデックスが張られた列で上位の数件を拾ってくる場合 (ORDER BY + LIMIT) にて、パーティショニングされていても効率よく処理できるようになります。
ただし、9.1 では min() や max() に対して、この最適化が行われません。代わりに ORDER BY [ASC | DESC] + LIMIT 1 を使ってください。
=# EXPLAIN (COSTS OFF) SELECT * FROM parent ORDER BY i LIMIT 1;
                     QUERY PLAN
------------------------------------------------------------------
 Limit
   ->  Result
         ->  Merge Append
               Sort Key: public.parent.i
               ->  Sort
                     Sort Key: public.parent.i
                     ->  Seq Scan on parent
               ->  Index Scan using child_1_idx on child_1 parent
               ->  Index Scan using child_2_idx on child_2 parent
numeric 型のサイズを 2byte 節約
多くの用途で 2byte ずつ節約されます。numeric 型で大量のデータを保持している場合は、結構な差がでます。 また、以前のディスクフォーマットもサポートしているため、pg_upgrade しても問題ありません。ただ、この新機能の恩恵を受けるには、データの再投入が必要です。
wal_buffers の自動チューニング
shared_buffers に連動して設定されます。 shared_buffers の 1/32　が割り当てられますが、上限は 16MB です。

セキュリティ強化

SE-PostgreSQL の統合
SECURITY LABEL を指定できるようになりました。 contrib/sepgsql が追加されました。 データの取得や操作 (SELECT, INSERT, UPDATE, DELETE) の際に、PostgreSQL のロールベースのアクセス制御に加えて、SE-Linux の強制アクセス制御によってもチェックされます。これにより、例えばウェブアプリケーションからはデータを取得できても、コマンドラインからは直接データを取得できなような設定が可能になり、情報漏洩対策になります。
auth_delay contrib モジュール
認証が失敗した際に、失敗のレスポンスを送る前に若干の遅延を挟みます。 総当り攻撃による不正アクセスを抑制するのに有効です。 
# postgresql.conf
shared_preload_libraries = 'auth_delay'

custom_variable_classes = 'auth_delay'
auth_delay.milliseconds = '500'

SQL構文の強化

真の SERIALIZABLE 分離レベル
9.1 の SERIALIZABLE は、述語ロック (predicate locking) を加味した整合性確認をするようになりました。9.0 までの SERIALIZABLE 分離レベルは REPEATABLE READ に降格されたことに注意してください。 9.1 では、トランザクション分離レベルは3種類になります。
～9.0 9.1
(N/A) SERIALIZABLE
SERIALIZABLE / REPEATABLE READ REPEATABLE READ
READ COMMITTED / READ UNCOMMITTED 同左
WITH 句と INSERT, UPDATE, DELETE を同時に
WITH (...) の中や末尾のクエリにて INSERT/UPDATE/DELETE ができるようになりました。WITH RECURSIVE も使えるので、再帰クエリの結果を元にテーブルを更新するようなケースで便利かもしれません。WITH 句の中で使う場合には、RETURNING 句と組み合わせると面白いことができます。実用的な例を挙げてみます：
例1 : DELETE RETURNING + INSERT の組み合わせで、SQL だけで削除したデータを他のテーブルに挿入 (行のテーブル間移動) することができます。 
WITH deleted AS (DELETE FROM test_trgm
   WHERE text_data LIKE  '%hello%' RETURNING text_data)
 INSERT INTO old_text_data SELECT * FROM deleted;
例2 : MERGE 文はまだサポートされていませんが、UPDATE できなかったキーを持つ行のみ後から INSERT を行うと、他DBの MERGE や REPLACE 文と同等の機能を実現できます。 
CREATE TABLE tbl (id integer PRIMARY KEY, v integer);

WITH val AS (SELECT ((1, 1235)::tbl).*),
     upd AS (UPDATE tbl SET v = val.v FROM val
               WHERE tbl.id = val.id RETURNING tbl.id)
 INSERT INTO tbl SELECT * FROM val WHERE id NOT IN (SELECT id FROM upd);
文字列関数 concat(), concat_ws(), left(), right(), reverse()
他のデータベース製品でサポートされている各種文字列関数が追加されました。 concat() は文字列の連結関数で、|| 演算子と同様です。 複数一括して連結できるのと、NULL は空文字として扱われるのが特徴です。 
SELECT concat('ABC', NULL, 123);
 concat
--------
 ABC123
ちなみに、|| 演算子では、NULL と連結すると結果は NULL になることに注意してください。連結前に coalesce(nullable_column, '') で空文字に置換する必要があるかもしれません。
文字列整形関数: format()
プログラム言語によくある sprintf() 風関数です。書式指定は %s (文字列そのまま) の他に %L (文字列リテラルとして) と %I (識別子として) が使えます。FAQ に「PL/pgSQL の EXECUTE 文 で DDL が使えません」というのがありますが、この場合には format() を使うと期待の動作になります。 
=# DO $$ BEGIN EXECUTE
   'CREATE TABLE $1 (id integer)' USING 'tbl'; END; $$;
ERROR:  syntax error at or near "$1" at character 14
=# DO $$ BEGIN EXECUTE
   format('CREATE TABLE %I (id integer)', 'tbl'); END; $$;
DO
ビューに対するトリガ
ビュー (VIEW) に INSTEAD OF トリガを定義できるようになりました。 例えばビューへの UPDATE を置き換えることで、SQL からは更新可能なビューとして扱えるようなことができます。 9.0 までは PostgreSQL 独自の RULE で同様の処理はできましたが、INSTEAD OF トリガは標準SQLで規定された仕様です。 トリガのほうが性能面でも優れているため、これでもう RULE はお役御免になるでしょう。
GROUP BY が MySQL 風に緩めに
GROUP BY にプライマリキーが指定されていれば、それ以外の列は GROUP BY に指定しなくても、SELECT 句に書けるようになりました。具体的には 
CREATE TABLE tbl (pk integer PRIMARY KEY, t text, i integer);
SELECT pk, t, sum(i) FROM tbl GROUP BY pk;
は、9.0 以前ではエラーになりましたが、9.1 では受け付けられます。 9.0 以前では、t 列も GROUP BY に書く必要があります。
標準SQLでは関数従属性を加味した、このような省略が許されており、9.1 ではその部分的な実装 (プライマリキーのみ) が実装されました。キーの一意性がチェックされるため、省略した列がランダムに選択されることはありません。 MySQLではさらに制限が緩く、なんでも許されてしまうため、「なぜ PostgreSQL では受け付けないのか？」という質問が良くありました。ある程度の互換性が向上したと思われます。
CREATE TABLE IF NOT EXISTS
テーブルが存在しない場合のみ、テーブルを作成するオプションです。
ENUM (列挙) 型に新しい値を追加
ENUM型に新しい値を追加できるようになりました。挿入位置も BEFORE, AFTER で指定できます。 
ALTER TYPE enum_type ADD 'new_value' [ (BEFORE|AFTER) 'old_value' ]
COPY TO/FROM の ENCODING オプション
これまで COPY TO/FROM コマンドで、データの一括投入/抽出をする場合、そのデータの文字エンコーディングは接続クライアントのエンコーディング (client_encoding) と一致させる必要がありました。一時的に client_encoding をファイルのエンコーディングに変更することで対処できましたが、接続ドライバによっては client_encoding を変更することが難しい場合もありました。代わりに encoding オプションを使うと、ファイルのエンコーディングを client_encoding とは別個で設定できます。SQL/MED の file_fdw もこの機能を利用して、ファイルのエンコーディングを指定いするオプションを実現していています。 
COPY tbl FROM 'file.sjis.csv' WITH CSV, ENCODING 'sjis';

運用やメンテナンス機能の強化

pg_stat_*_tables システムビューに VACUUM, ANALYZE 回数表示
VACUUM と ANALYZE の回数が pg_stat テーブルに追加されました。 特に autovacuum がきちんと動作しているか / 逆に異常な回数動作していないかの確認に有効です。
log_file_mode パラメータ
サーバログファイルのパーミッションを設定します。 デフォルトでは 600 であるため、postgres ユーザ以外からはログが読めません。 そのため、監視ツールなどがサーバログを読む必要がある際、監視ツールを root や postgres ユーザで動作させる必要があり、セキュリティ的に好ましくありませんでした。 このパラメータを例えば 640 に指定すれば、postgres グループのユーザであればログの読み取りができるため、運用設定の自由度が増します。
CLUSTER が必要に応じてインデックス・スキャンとシーケンシャル・スキャンを使い分け
CLUSTER の際に、断片化が進んでいる場合は SeqScan してくれるようになりました。 特に断片化が進んでいる大きなテーブルだと数倍高速です。
外部ツールにも pg_reorg という類似機能を持つ製品がありますが、性能面では差は無くなったと言えます。もちろん、再編成中にも参照や更新できる pg_reorg の利点は残っています。
トランザクション内で行った操作の統計情報ビュー
これまではインスタンス一括で情報が表示されていましたが、この新しいビューでは自身のトランザクション内での統計情報カウンタのみを表示します。 pg_stat_xact_xxx ビューで確認できます。 注意が必要なのは、「トランザクション内」なので、COMMIT の発行前にビューを確認する必要があることです。 
=# BEGIN;
=# SELECT count(*) FROM tbl;
=# SELECT * FROM pg_stat_xact_xxx;
-----
   1
=# COMMIT;
=# SELECT * FROM pg_stat_xact_xxx;
-----
   0  ← リセットされている
ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX
既存のユーニク・インデックスを再利用して、プライマリキーやユニーク制約を定義できるようになりました。 特に便利なのは、CREATE INDEX CONCURRENTLY と組み合わせるケースです。
インデックスのメンテナンス (再構成) を行う REINDEX では、実質的に対象テーブルの排他ロックが必要となり、処理中は参照も更新もできません。 サービスを止めないままメンテナンスをするために、CREATE INDEX CONCURRENTLY で新しいインデックスを作成し、完了後に古いインデックスを DROP することが良く行われます。 しかし、9.0 まではプライマリキーに対しては同じ方法を使えませんでした。
もちろん、列に対して NOT NULL 制約をつけておき、ユニークインデックスを付与することでプライマリキーの代わりにはなるのですが、テーブルの定義に基づいて動作するようなツールでは、プライマリキーが求められることもあります。 この新しい構文を使うと、既存のインデックスをプライマリキー用のインデックスとして割り付けることができるため、以下のようなSQL でオンラインのままプライマリキーを張り替えられます。 
=# CREATE UNIQUE INDEX CONCURRENTLY tbl_pkey_new ON tbl (id);
=# ALTER TABLE tbl
     DROP CONSTRAINT tbl_pkey,
     ADD PRIMARY KEY USING INDEX tbl_pkey_new;

まとめ

PostgreSQL 9.1 は、より洗練されたレプリケーションを初めとする性能と機能の強化により、適用範囲をさらに拡大してくれそうです。 また、SQL/MED や EXTENSION による拡張性の強化は、本体の他にもサードパーティを巻き込んた今後の発展を期待できます。 ぜひ、最新の PostgreSQL を試してください！

リリース履歴
リリース名 リリース日
Alpha 1 2010-09-07
Alpha 2 2010-11-01
Alpha 3 2010-12-30
Alpha 4 2011-02-24
Alpha 5 2011-03-29
Beta 1 2011-05-01
Beta 2 2011-06-12
Beta 3 2011-07-11
RC 1 2011-08-22
正式版 2011-09-12

PostgreSQL 9.1 に関する情報の収集には、以下のサイトが参考になります。

(2011年4月14日 更新)