PostgreSQL Update
9.1リリース＆9.2の展望
INSIGHT OUT 20112011-10-19 15:00～15:50
SRA OSS, Inc. 日本支社
高塚 遥 harukat@sraoss.co.jp
Copyright © 2011 SRA OSS, Inc. Japan All rights reserved.

1

PostgreSQLとは何ぞや 代表的なオープンソースRDBMS Ingres(1970～ UCB) を先祖に持つ
 PostgreSQL 6.0 (1996 ～) から １０年以上の歴史

 BSDタイプのライセンスで配布
 PostgreSQL Global Development Gruop と UCBが著作権を持つ ひとつのオーナー企業、オーナー個人を持たない PostgreSQL開発に時間を割く技術者を提供している企業
がいくつかある／その企業群も少しずつ変遷している
Copyright © 2011 SRA OSS, Inc. Japan All rights reserved.

2

PostgreSQLの歩みPostgreSQL のコードサイズ
120000

レプリケーション
Windows 64bit
100000

コードサイズ (KB)

PostgreSQLの80000

プリペアド
ステートメント

60000

40000

外部キー、
JOIN構文並列実行
VACUUM
PITR、
Windows

CPU
スケール

スキーマ

20000

トランザク
ションログ

ウィンドウ関数
再帰SQL

ビットマップ
スキャン

更新の高速化0PG 7.0|
2001年
4月

PG 7.1

PG 7.2|
2002年
11月

PG 7.3

PG 7.4|
2005年
1月

PG 8.0PG 8.1|
2006年
12月

PG 8.2

PG 8.3|
2009年
7月

PG 8.4

PG 9.0|2011年
9月
3位置づけと使われ方の変貌
2001年

2011年

 おもちゃ、社内システム
携帯DLサイト Accessからの移行先
自治体
 Webバックエンド
パッケージ 「小規模なら」公共・エンタープライズ ゲームSNS 「大規模」「ミッションクリティカル」でも マンモス校頑張れば公共・エンタープライズ
教務 パッケージ製品内部の基盤部分
地図/ナビ Oracleからの移行先
オンライン証券
 OSS Webアプリの基盤部分
銀行
基幹
4

PostgreSQL 9.1 リリース
HEAD

2010.72011.6

2010.99.0リリース

2011.99.0.5リリース

2011.92011.99.1リリース

9.1.1リリース

例によって１年毎メジャーバージョンリリース

 9.0に入りそびれた大物機能
 運用、開発を便利にする機能
5レプリケーション関連

6同期レプリケーションが選択可能（１）
APサーバ リクエストなど
（３）

レスポンス

マスターサーバ

Postgres

スタンバイサーバ
WAL
WAL
データデータ（２）

Postgres（４）
適用

WAL情報転送

スタンバイ接続のapplication_namesynchronous_standby_names = 'syncrep' 9.0 では非同期のみ（＝障害直近データ損失あり）
 WALデータを転送完了後にコミット完了とできる
 「スタンバイに問い合わせた内容がマスタと一致」を保障するわけではない
 逆にスタンバイが止まっているとマスタがブロック7

再全同期なしのスイッチ可能
 9.0はスイッチ困難
 ベースバックアップの取り直しが必要になることが多い

 正常停止した旧マスタのデータを利用可能
 PaceMaker の MasterSlave型リソースにも適用可能
 試作RA有マスターサーバ
Postgres
レプリ

スタンバイサーバ
Postgresケーション

マスターストップ
スタンバイ昇格 / 同期指定解除
旧マスターに recovery.conf 設置
旧マスターを新スタンバイとして起動

新スタンバイ
Postgres

レプリ
ケーション

新マスター
Postgresrecovery.conf

recovery_target_timeline = leastest
8

pg_stat_replicationビュー マスターでレプリケーション状況を一覧できる=# SELECT * FROM pg_stat_replication;-[ RECORD 1 ]----+-----------------------------procpid| 2604
usesysid| 10usename| postgres
application_name | syncrep
client_addr| 127.0.0.1
client_hostname |client_port| 56573backend_start| 2011-10-12 13:13:12.121657+09
state| streaming
sent_location| 0/12C5E9B4 ただし進度の単位はWALファイル位置
write_location| 0/12C5E9B4
flush_location| 0/12C5E7D0
replay_location | 0/12C5E7D0
sync_priority| 1
sync_state| sync9pg_basebackupコマンド
pg_ctl promoteコマンド
 ベースバックアップがコマンドひとつで完了
 SELECT pg_start_backup('hoge'); ⇒ tarとか ⇒ SELECTpg_stop_backup(); の手順が不要に テーブルスペースにも対応
 PostgreSQLの通常接続を使うので scp や NFS を用意しなくてよい スタンバイのレプリケーションを終了させて通常起動（＝マスタ昇格）させるコマンド これまではトリガーファイルを置く方式のみ
10

拡張

11

SQL/MED FOREIGN TABLE 外部データをテーブルとして読み込む枠組み
 contrib/file_fdw はCSV形式などのファイルをテーブルとして読み込む外部データラッパーの実装例
 さまざまな外部データ連携のモジュールが作れる

 9.1 では参照だけ、更新は未対応
 mysql_fdw や twitter_fdw などいろいろ開発されている
12

CREATE EXTENSION
 拡張モジュールをインストールする命令
 所定の作法を守って書かれた拡張モジュールを、ＳＱＬコマンドで管理できる
 ビルドと.dll / .soのインストールは予め必要です 拡張モジュールのバージョンアップに対応=# CREATE EXTENTION hstore;=# DROP EXTENSION hstore;
13

性能14

UNLOGGEDテーブル
 トランザクションログ（WAL）に記録しないテーブルを作れる 書き込みが速い クラッシュしたらデータ消えてしまうかも レプリケーションされない=# CREATE UNLOGGED TABLE
tbl01 ( … )pgsnaga.blogspot.comより

15

ORDER BY pushdown プランナが改善
 Merge Append プランタイプ
 テーブルパーティショニングされたテーブルでソートを行うとき各々の子テーブルにあるインデックスが使われる=> SELECT id, v1 FROM t_partitioned_oya
ORDER BY v1 LIMIT 10;
16賢いCLUSTERコマンド
 テーブル統計情報をみて、最適な再編成の方式を自動的に選択
 新方式の「Seq Scan + Sort」 は、
 速い、ただし、ほぼクラスタ済みデータなら旧方式有利
 一時ファイルを大きく使う

テーブル状態
断片化が少ない
断片化が多い処理方式
Index Full Scan
（従来方式）Seq Scan + Sort（新方式）17

性能…大事な話
 PCサーバ上、単純なpgbench 標準テスト、デフォルト設
定では PostgreSQL 9.1 は速くない そもそも PostgreSQL 8.4 ～9.0 も速くない 単純テストはPostgreSQL 8.3 が速い

 「状況がハマるとき」「しかるべく設定したとき」初めて性能アップの成果が得られる

18

機能19

KNN GiST インデックス
 k-NN(k Nearest Neighbor) 検索：空間上に指定された地点に近接するオブジェクトを，空間データベースの中からk 個求める
 GiSTインデックスを距離が近いものトップリストを出す検索に使える
 POINT型、Geometry型など

SELECT coordinates,(coordinates <-> '5.0,5.0'::point) AS dist
FROM spots ORDER BY dist ASC LIMIT 10;
20

述語ロック対応SERIALIZABLE
 Serializable Snapshot Isolation 9.0 までのSERIALIZABLEは、不完全順に片方ずつ行った場合

 マニュアルにも但し書きがついている
以下を 旧SERIALIZABLE で同時実行すると直列実行と異なる結果になる／9.1 SERIALIZABLEでは 直列化失敗エラー

INSERT INTO tbl (val, grp)
SELECT SUM(val), 'b' FROM tbl WHERE grp = 'a';
INSERT INTO tbl (val, grp)
SELECT SUM(val), 'a' FROM tbl WHERE grp = 'b';
旧SERIALIZABLE ⇒ (9.1) REPEATABLE READ
新SERIALIZABLE ⇒ (9.1) SERIALIZABLE
互換性注意
21

contrib/sepgsql
 データベースオブジェクトに強制アクセス制御
 アクセス可否のチェックをSE-Linuxと統合
 各SQLについてSE Linux に内部的に問い合わせるのでSELinux Enabled が大前提 SELinuxのポリシーとして設定を行う

 SE-Linux同様のラベルベース強制アクセス制御

SECURITY LABEL FOR selinux
ON TABLE mytable
IS 'system_u:object_r:sepgsql_table_t:s0';22さまざまな文字列関数が追加
 format('%s and %s', 'abc', '123')
 printf タイプのフォーマッタ
 SQLリテラル、SQL識別子のエスケープに対応 concat(a, b, c, ...)、concat_ws()
 || 演算子と同じだけど、某ＤＢとの互換性的に

 reverse()
 left()、right()
 右から何文字、左から何文字

23

更新を行えるWITH句 Writable CTE (Common Table Expressions) 処理行データ返す「RETURNING 」と組み合わせ、 WITHクエリ
でINSERT、UPDATE、DELETE 記述可 例：DELETEした内容を別テーブルにINSERTWITH t_tmp (id, v) AS (DELETE FROM t RETURNING *)INSERT INTO t_new SELECT * FROM t_tmp;
 例：MERGE代替(UPDATE で該当なければINSERT）
WITH val AS (SELECT 100 as id, 'AAA' as v),
upd AS (UPDATE t SET v = val.v FROM val
WHERE t.id = val.id RETURNING t.id)
INSERT INTO t
SELECT * FROM val WHERE id NOT IN (SELECT id FROM upd);24

ビューに対するトリガー 更新可能ビューを作るのに RULE でなくトリガーも使えるようになった RULEは PostgreSQL独自概念だし、書き方も独特 トリガーの方が複雑なことも書ける

CREATE TRIGGER mytrig
INSTEAD OF UPDATE ON myview
FOR EACH ROWEXECUTE PROCEDURE myupdate();25

カラム単位ロケール指定
 検索毎、カラム毎で COLLATE（言語を考慮した文字比較）設定ができる
 8.3 まで データベースクラスタ単位
 8.4 データベース単位

 glibc の日本語ロケールは例によって使えない
CREATE COLLATION c_en (LOCALE = 'en_US.UTF-8');
SELECT t FROM t_coll ORDER BY t COLLATE c_en;
SELECT t FROM t_coll ORDER BY t;
ABC
XYZ
abc
xyz

abc
ABCxyz26
XYZ

contrib/pg_tramの拡張
 LIKE検索で中間一致にもインデックス検索
 もともとは類似度一致の演算子やインデックス機能を提供するもの
 9.1 で LIKE、ILIKE に対応
 実はマルチバイト対応にはソース上のフラグ変更必要
gist も可CREATE INDEX ON docs
USING gin (doc gin_trgm_ops);
SELECT * FROM docs WHERE doc LIKE '%foo%';27

9.1 その他・・・
 非互換！
 standard_conforming_strings = on がデフォルト
エスケープ利用で E'xxx' を使っていないなら要注意
 複合型に対する、関数スタイルのキャストが禁止 PL/pgSQL の RAISE命令と例外処理の振る舞い変更
 例外補足のされ方が（Oracle PL/SQL に似せるように）変わ SERIALIZABLEの定義変更
 その他、細かな非互換はリリースノートを参照

 修正点は以上紹介したものの他にも多数

28

9.2 以降

29

レンジデータ型
 範囲を表現するデータ型
 重なり検出する演算子 && 8.4 で導入された排他制約と 組み合わせ
 重なりがあったら制約違反
 これまでは BOX型くらい しか使える対象がなかった

データ型要素データ型

int4range

int

int8range

bigintnumrange

numerictsrange

timestamp
without
timezonetstzrange

timestampwith timezone

daterange

date

SELECT range(11.1, 22.2) && range(20.0, 30.0);
ALTER TABLE reservation
ADD EXCLUDE USING gist (during WITH &&);
30

Index Only Scan min/max、Sort/Limit、Hash-Join、Merge-Join など
で使える=# EXPLAIN select min(unique1) from tenk1;
QUERY PLAN-------------------------------------------------------Result
InitPlan 1 (returns $0)-> Limit-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)

31

レプリケーションのカスケード対応
マスターサーバ
Postgres
レプリ

スタンバイサーバ
Postgres

ケーション

レプリ
ケーション

スタンバイサーバ
Postgres

レプリ
ケーション

スタンバイサーバ
Postgres カスケード構成の障害となっている細かな問題を修正 ホットスタンバイサーバからのベースバックアップ取得対応
32

commitfest 2011-09 より
 Inserting heap tuples in bulk in COPY バックグラウンドジョブの改善
 Separating bgwriter and checkpointer Autovacuum polling loop elimination

 pg_last_xact_insert_timestamp()
 トランザクション状態を時刻で知りたい

未確定のものも含めて紹介しています。

他にも多数挙がっています。

 recovery.conf が postgresql.confに統合
 ロック～排他制御まわりの改善多数

33

新バージョン情報は
 コミットフェスト管理Webサイト
https://commitfest.postgresql.org/
 アルファ版が数ヶ月おきに
http://www.postgresql.org/developer/alpha HEADのマニュアルとか 各種イベントの講演など
34