板垣 貴裕

この記事では、PostgreSQL 9.0 の概要記事だけでは紹介しきれなかった、9.0のその他の新機能についてまとめています。

SQL 構文の強化

string_agg

文字列を連結して集約する string_agg 関数が追加されました。 以前のバージョンでも array_agg + array_to_string で同じ結果が得られますが、より高速です。 他のデータベースにも同様の関数はありますが、MySQL では group_concat()、Oracle Database では listagg() と名前は異なります。

=# SELECT array_to_string(array_agg(t), '+'),
          string_agg(t, '+')
     FROM tbl;
 array_to_string | string_agg
-----------------+------------
 a+b+c           | a+b+c
(1 row)

function( 'value' AS name )

関数のデフォルト引数を名前与えて値を設定できるようになりました。複数のデフォルト引数を持つ場合に、その一部のみに値を設定したい場合に役立ちます。

=# CREATE FUNCTION func(
     a integer,
     b integer DEFAULT 2,
     c integer DEFAULT 3
   ) RETURNS text AS
     $$ SELECT 'a=' || $1 || ', b=' || $2 || ', c=' || $3 $$
   LANGUAGE sql IMMUTABLE STRICT;

=# SELECT func(1);         ⇒ a=1, b=2, c=3
=# SELECT func(1, 8, 9);   ⇒ a=1, b=8, c=9
=# SELECT func(1, 9 AS c); ⇒ a=1, b=2, c=9

CREATE INDEX でインデックス名の省略

CREATE INDEX ON table と、インデックスの名前を省略できるようになりました。PostgreSQL が自動的に名前を決めてくれます。

CREATE TABLE name OF type

CREATE TYPE で作成した型をテンプレートにしてテーブルを作成できます。

CREATE TABLE ... (LIKE parent INCLUDING { COMMENTS | STORAGE | ALL } )

テーブル定義の際、LIKE INCLUDING オプションに COMMENTS と STORAGE が追加されました。それぞれ、コメントやストレージパラメータをコピーします。また、ALL を指定するとすべての INCLUDING オプションを指定したのと同じ効果になります。パーティションを定義する際には、この ALL オプションを使うと手間が省けるでしょう。

DROP { COLUMN | CONSTRAINT } IF EXISTS

列や制約に対して DROP 対象が存在しなくてもエラーにしない IF EXISTS オプションが追加されました。

COPY tbl TO 'file' WITH CSV FORCE QUOTE *

全ての列をダブルクォート " " で囲むオプション FORCE QUOTE * が追加されました。

性能の改善とチューニング

ALTER TABLESPACE seq_page_cost, random_page_cost

テーブルスペースごとにオプティマイザ用の I/O コストを設定できるようになりました。 異なる性能のディスクを組み合わせている場合のチューニング要素です。 たとえば、頻繁にアクセスするデータを SSD 上のテーブルスペースに配置した場合、random_page_cost を seq_page_cost と同じ 1 まで引き下げても良いでしょう。 SSD はランダム・リードにシークが必要ないためです。

インデックスでの非NULLの検索

"x IS NOT NULL" の条件での検索でインデックスが使えるようになりました。ちなみに "x IS NULL" での検索は 8.4 から可能です。

継承テーブルの統計収集強化

継承の親テーブルを ANALYZE すると、子テーブルも同時に ANALYZE されるようになりました。

ALTER TABLE でのテーブル再構成で WAL をスキップ

archive_mode が off の場合に限られますが、ALTER TABLE で列の型変更など、テーブルを再構成する際に無駄な WAL を出力しないようになりました。再構成の高速化が期待できます。

ログとプロファイラ

Windows のイベントログやコンソール出力の文字化け修正

PostgreSQL で日本語を扱う場合、サーバエンコーディングには UTF-8 または EUC_jp を選ばなければなりません。しかし、Windows のイベントログやコンソールではエンコーディングを SJIS にして出力する必要があるため、サーバログが文字化けしていました。v9.0 ではログをユニコードで出力するようになり、この問題が解消されています。

pg_ctl reload した際に変更があったパラメータをログに出力

どのパラメータが変更されたかが分かりやすくなりました。

log_line_prefix に %e (SQLSTATE) が追加

エラーコードをログに出力できます。8.4 以前では log_error_verbosity = verbose にしなければなりませんでした。

セキュリティとアクセス管理

ALTER ROLE role IN DATABASE database SET param TO value

ユーザとデータベースの組み合わせごとに設定パラメータを指定できるようになりました。設定値は psql からならば、メタコマンド \drds で確認できます。

pg_hba.conf で samehost, samenet をサポート

サーバと同じアドレスからの認証を一括で設定します。サーバが動的なアドレスを持つ場合に役立ちます。

便利な機能

pg_table_size(), pg_indexes_size()

それぞれ「テーブル＋TOAST 領域」「インデックスの合計」のサイズを取得します。pg_relation_size() の姉妹品です。 ちなみに、バージョン 8.4 でも、同じ機能を実現するために下記のような関数を定義できます。

CREATE FUNCTION pg_table_size(regclass) RETURNS bigint AS
$$
SELECT pg_relation_size(c.oid)
     + pg_relation_size(c.oid, 'fsm')
     + pg_relation_size(c.oid, 'vm')
     + coalesce(pg_relation_size(t.oid), 0)
     + coalesce(pg_relation_size(t.oid, 'fsm'), 0)
     + coalesce(pg_relation_size(t.oid, 'vm'), 0)
     + coalesce(pg_relation_size(t.reltoastidxid), 0)
     + coalesce(pg_relation_size(t.reltoastidxid, 'fsm'), 0)
  FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 WHERE c.oid = $1
$$
LANGUAGE sql;

CREATE FUNCTION pg_indexes_size(regclass) RETURNS bigint AS
$$
SELECT sum(pg_relation_size(indexrelid)
         + pg_relation_size(indexrelid, 'fsm'))::bigint
  FROM pg_index
 WHERE indrelid = $1
$$
LANGUAGE sql;

PL/Python 3 の追加

手続き関数で Python 3 がサポートされました。これまでの Python 2系 (plpythonu) とは別の、plpython3u の名前で登録されています。

pg_ctl init[db] モードの追加

pg_ctl に init 及び initdb モードが追加されました。以下は同じ意味になります。

$ pg_ctl initdb -o "--locale=C --encoding=UTF8"
$ initdb --locale=C --encoding=UTF8

現時点では initdb コマンドと全く同じですが、将来的に名前が pg_ から始まるコマンドに統一される可能性があるため、この書式に移行を始めても良いかもしれません。

pgbench に \shell, \setshell メタコマンドが追加

pgbench からシェルコマンドを実行し、結果を変数に格納するメタコマンドが追加されました。複雑な機能試験や負荷試験に利用できるでしょう。