待望のPostgreSQL8.3完全ガイド
SRA OSS, Inc. 日本支社
石井 達夫

PostgreSQLの歴史
800000

マルチプロセッサ
対応

750000700000650000600000

WALログ
TOAST

550000500000450000400000Windows対応

PL/pgSQL
トリガ

Concurrent
Vacuum

350000300000250000

行ロック
MVCC

2000001 50000
1 00000

副問い合わせマルチバイト50000
06 .1 .1

2007/7/27

6 .2 .1

6 .3 .1

6 .4 .2

6 .5 .3

7 .0 .3

7 .1 .3

7 .2 .8

7 .3 .1 9

Copyright(c)2007 SRA OSS, Inc. Japan

7 .4 .1 7 8 .0 .1 3

8 .1 .9

8 .2 .4

8 .3

2

PostgreSQLの性能向上
PostgreSQL 8.3以降

VACUUM性能向上

2007/7/27

PostgreSQL 8.2

更新性能向上マルチプロセッサ
性能向上

マルチプロセッサ
性能向上

Copyright(c)2007 SRA OSS, Inc. Japan

3

Part1: 特に注目度の高い新機能
HOT
●GIT
●
Synchronized Scan●
負荷分散チェックポイント●
更新可能カーソル●
インデックスアドバイザー● ソート処理のモニタリング●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

4

HOT(1)
従来の更新処理の問題点
更新を繰り返すと更新連鎖(update chain)が長くなる●
更新されないインデック
スも追加される●
VACUUMをしないとどんどん遅くなる●version 0

a100

version 1

b100

version 2

c100

最新版

d100

テーブル
2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

インデックス5HOT(2): HOTとは
HOT: Heap Only Tupleの略●
更新対象列にインデックスカラムが含まれていない
場合に効果的●VACUUMの必要性を減らす●

局所的なVACUUM処理をリアルタイムで実行●
UPDATEのみならず，SELECT時にもdead tuple回収●●

UPDATEを繰り返してもテーブル，インデックスが肥大化しない

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

6

HOT(3): HOTの動作原理
Root tuple
version 0
Heap Only Tuple

100X

version 1
Heap Only Tuple

X

version 2
Heap Only Tuple
最新版
テーブル
2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

インデックス7

HOT(4): HOTの効果
MLに流れた開発者自身によるベンチマーク●
pgbenchによる900万行のデータ●
メモリ2GB，共有バッファ128MB
●
更新＋挿入
●
autovacuumあり●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan8HOT(5): トランザクション性能比較
TPS

2200
2000
1 800
1 600
1 400
1 200

H O T ありH O T なし

1 000800600400200
2007/7/27

0Copyright(c)2007 SRA OSS, Inc. Japan

9

HOT(6): DBサイズの比較
Block数
2200002000001 80000
1 60000
1 40000

オリジナルサイズ
H O T ありH O T なし

1 20000
1 00000
80000
60000
40000
20000
0
2007/7/27

テーブルサイズインデックスサイズ

Copyright(c)2007 SRA OSS, Inc. Japan

10

HOT(7): HOTの開発状況
現在パッチの最終レビュー●
現時点では8.3に入るかどうかは未確定●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan11

Grouped Index Tuples(GIT)
B-Treeインデックスを効果的に圧縮する技術●
CLUSTERコマンドと併用すると効果的●時には100倍の圧縮効果も●
CREATE INDEXに新しいオプション●●●

CREATE INDEX ... (groupthreshold=2)

8.3に入るか，それとも8.4に持ち越しかは現時点で未定

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan12

インデックスA(1:1)
B(1:2)
C(1:3)
D(2:1)

GITの動作
テーブル
A
100
B
200
C300

D400

ブロック1
ブロック2

インデックスページが一杯になったら圧縮開始
A(1:1,2,3)
D(2:1)

2007/7/27

A
B

100
200

C300

D

400

Copyright(c)2007 SRA OSS, Inc. Japan13

Synchronized Scan(1)
●

問題点
●

共有バッファに入りきらないような大きなテーブルを複数セッションが順スキャンすると，キャッシュヒット率が低下する
23

4

5

6

セッション1はブロック6までアクセス済

バッファキャッシュ

3456

バッファキャッシュにはブロック6までキャッシュ済

1

3

4

5

6

セッション2はブロック1からスキャンをスタート．キャッシュ
ヒット率01

2007/7/27

2

Copyright(c)2007 SRA OSS, Inc. Japan14

Synchronized Scan(2)
●

解決策
●

常にテーブルの先頭からスキャンするのではなく，バッファキャッシュにキャッシュされているブロックからスキャン開始
2

3

4

5

6

セッション1はブロック6までアクセス済

バッファキャッシュ

3

4

5

6

バッファキャッシュにはブロック6までキャッシュ済

1

3

4

5

6

セッション2はすでにキャッシュされているブロック3からスキャン開始

1

2007/7/27

2

Copyright(c)2007 SRA OSS, Inc. Japan15

Synchronized Scan(3)●Synchronized Scan利用上の注意点
常にテーブルの先頭からスキャンするわけではないので，行の返却順序が一定にならない●
行の返却順序が問題になる場合は，ORDER BYを使って明示的にソートする●●

2007/7/27

実際にregression testの一部に変更が加えられている

Copyright(c)2007 SRA OSS, Inc. Japan16

負荷分散チェックポイント(1)
●

問題点
●

チェックポイント時に一時的に性能が低下する●●

background writerでは代用できない●●

dirty bufferの掃出し
トータルのI/Oが増えてしまうため解決策
チェックポイントを負荷分散し，少しずつdirty bufferを掃出す●
checkpoint_completion_target
●

●

効果

●
2007/7/27

性能が一定にCopyright(c)2007 SRA OSS, Inc. Japan17

更新可能カーソル(1)●

従来のカーソルは「更新不可能」だった
BEGIN
DECLARE c CURSOR FOR SELECT * FROM T1;
FETCH 2 FROM c;i| j---+----1 | foo
2 | bar(2 rows)
DELETE FROM t1 WHERE i = 1;
MOVE BACKWARD ALL IN c;
FETCH ALL FROM c;i| j---+----1 | foo
2 | bar(2 rows)

SELECT * FROM t1;
i| j---+----1 | foo
2007/7/27(1 row)

削除されていないように見える

実際には削除されている
Copyright(c)2007 SRA OSS, Inc. Japan18

更新可能カーソル(2)●「FOR UPDATE」を付けて更新可能カーソルへ
BEGIN
DECLARE c CURSOR FOR SELECT * FROM T1 FOR UPDATE;FETCH 2 FROM c;i| j---+----1 | foo
2 | bar(2 rows)
DELETE FROM t1 WHERE CURRENT OF c;
MOVE BACKWARD ALL IN c;
FETCH ALL FROM c;i| j---+----削除されている1 | foo(1 rows)

SELECT * FROM t1;
i| j---+----1 | foo(1 row)
2007/7/27

新しい構文

実際に削除されている
Copyright(c)2007 SRA OSS, Inc. Japan19

インデックスアドバイザー●「インデックスアドバイザー」とは？
必要なインデックスがあれば指摘してくれるような機能●
多くの商用DBMSに見られるツール●●

PostgreSQLでの実装●「インデックスアドバイザー」の機能自体はユーザが実装
実際にはベンダーやpgfoundryで開発されることを期待？
●
実装的にはPostgreSQLからダイナミックロードされるモジュールになる●●

PostgreSQLにはそのためのインターフェイス（フック）を設ける●

2007/7/27

EXPLAINコマンドから呼び出されるフックなど
Copyright(c)2007 SRA OSS, Inc. Japan20

インデックスアドバイザーの実装例
ユーザ定義インデックス
regression=# load '/home/tgl/pgsql/advisor';アドバイザーのロードLOAD
regression=# explain select * from fooey order by unique2,unique1;
QUERY PLAN---------------------------------------------------------------Sort (cost=809.39..834.39 rows=10000 width=8)
Sort Key: unique2, unique1-> Seq Scan on fooey (cost=0.00..145.00 rows=10000 width=8)
Plan with hypothetical indexes:
Index Scan using <hypothetical index> on fooey (cost=0.00..376.00 rows=10000 width=8)(6 rows)

ユーザ定義インデックスアドバイザーの出力

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan21ソート処理のモニタリング

test=# set trace_sort to on;
test=# explain analyze select * from accounts order by abalance;
LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
LOG: switching to external sort with 7 tapes: CPU 0.02s/0.00u sec elapsed 0.03 sec
LOG: performsort starting: CPU 0.51s/0.00u sec elapsed 0.52 sec
LOG: finished writing final run 1 to tape 0: CPU 0.52s/0.00u sec elapsed 0.52 sec
LOG: performsort done: CPU 0.52s/0.00u sec elapsed 0.52 sec
LOG: external sort ended, 1331 disk blocks used: CPU 0.96s/0.00u sec elapsed 0.96 sec
QUERY PLAN----------------------------------------------------------------Sort (cost=16191.82..16441.82 rows=100000 width=97) (actual time=527.619..771.426
rows=100000 loops=1)
Sort Key: abalance
Sort Method: external sort Disk: 10648kB-> Seq Scan on accounts (cost=0.00..2588.00 rows=100000 width=97) (actualtime=0.021..213.239 rows=100000 loops=1)
Total runtime: 969.161 ms
2007/7/27Copyright(c)2007 SRA OSS, Inc. Japan
22(5 rows)

Part2: そのほかの改良点●

JIS 2004(JIS X 0213)対応●

文字種の増大，Windows Vistaとの親和性

ディスク領域の節減●
WALログの省略●
Async Commit●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

23

JIS 2004とは？
●従来の日本語文字コード規格(JIS X 0208+JIS X0212)を拡張した文字コード規格(JIS X 0213)の
通称●●

ただし，JIS 2004はJIS X 0208+JIS X 0212の完全上位互換ではない

多数の漢字や符合を追加●

一部の「機種依存文字」も取り込み

Windows Vistaなどが採用し，注目される● 人名などが充実しているため，官公庁や地方公共団
体での採用が期待される●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

24

JIS 2004で使えるようになった
機種依存文字の一部(NEC特殊文字)①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳
ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ㍉㌔㌢㍍㌘㌧㌃㌶㍑㍗㌍㌦㌣㌫㍊㌻㎜㎝㎞㎎㎏㏄㎡㍻〝〟№㏍℡㊤㊥㊦㊧㊨㈱㈲㈹㍾㍽㍼≒≡∫∮√⊥∠∟⊿∵∩∪

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

25

JIS 2004対応の実装●

JIS 2004対応のEUC(EUC_JIS_2004)，シフトJIS(SHIFT_JIS_2004)をエンコーディングとして追加●

JIS 2004はEUC_JP, SJISの上位互換ではないためUNICODEはそのまま利用可能●
UNICODE <--> EUC_JIS_2004,
SHIFT_JIS_2004の変換が可能●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

26

JIS 2004利用上の注意
クライアントはUNICODE中心●BMP(2バイトのUCS)の範囲以外のUNICODEが利用できることが必要(Unicode 3.1以降のサポート)● 字形が変っている文字がある●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan27

ディスク領域の節約●

可変長データの内部表現(varlena)の変更によるデータ領域の削減●

126バイト長以下の場合にサイズを表す領域を4バイトから1バイトに削減

タプルヘッダーを4バイト縮小(27->23バイト，15%の削減)●
スキーマ定義や投入データにもよるが，場合によっては大きな効果●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan28

WALログの省略●

不必要なWALログの出力がなくなったことにより，ログ領域の削減，処理の高速化●

pgbenchでは，COPYの前にTRUNCATEを実行することにより，COPYでログ出力されない●

1000万件COPYの場合
8.2では6分32秒●
8.3では3分18秒●

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

29

Async Commit(評価中)
コミットした後同期書き込みが終わるまで待たずにクライアントにコミット完了を返す●
fsync=offと同等の高速化●
再投入可能なデータやセンサーデータを扱う場合に効果的●
fsync=offとの違い●

GUCの「synchronous_commit」でセッション中にオン・オフ可能● クラッシュしても最近のトランザクションが消えるだけで，データ不整合は起きない●
fsync=offではどこまでデータが消えるか予測できない
2007/7/27
Copyright(c)2007 SRA OSS, Inc. Japan
30●

そのほかの改良点(1)●CREATE FUNCTIONの改良●●

実行コスト，想定返却行数の指定が可能に

DISCARD ALL
セッションデータの全初期化をセッションを維持したまま実行可能●
pgpoolが待っていた機能！
● 初期化対象データ●

prepared plan●
一時テーブル●SETで一時的に変更したデータ●
カーソル
Copyright(c)2007 SRA OSS, Inc. Japan●
LISTEN/NOTIFY●

2007/7/27

31

そのほかの改良点(2)tsearch2の本体への取り込み(評価中)●
CSV形式のログ(評価中)●CREATE TABLE LIKE●●●

warm standby用のcontribコマンド●●

SQL標準，パーティショニング
pg_standby

XML対応

主にSQL:2003対応●
XMLデータ型
2007/7/27
Copyright(c)2007 SRA OSS, Inc. Japan●
テーブル定義やデータのXMLとの相互変換●

32そのほかの改良点(3)●

ログ項目の追加
log_autovacuum●
log_lock_waits●

LOG: process 12274 still waiting forAccessExclusiveLock on relation 16467 of database
16384 after 1003.966 ms
STATEMENT: lock table t1;●

アーカイブログの圧縮インターフェイス●●

実際の圧縮はexternal projectにて複合型の配列サポート

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

33

そのほかの改良点(4)●ヒープページ調査ツール(contrib)

test=# SELECT * FROM heap_page_items(get_raw_page('pg_class',0));-[ RECORD
1 ]---------------------------------------------------------------------lp|1
lp_off| 5744
lp_flags | 1
lp_len| 205
t_xmin|2t_xmax|0t_field3 | 497
t_ctid| (0,1)t_infomask2 | 27
t_infomask | 10507
2007/7/27
Copyright(c)2007 SRA OSS, Inc. Japan34t_hoff| 32t_bits|

そのほかの改良点(5)●

リカバリの高速化●

上書きされるページのデータを読まない

LIKEの高速化●
ENUMデータ型●●●

MySQLからのマイグレーションが容易に

並列CREATE INDEX

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan35

そのほかの改良点(6)●マージジョインの高速化●

不必要なソートを避ける

explain select * from accounts t1, accounts t2 where t1.aid = t2.aid order by t1.aid desc;

8.2のプラン

Sort (cost=6012239.09..6037239.09 rows=10000000 width=200)
Sort Key: t1.aid-> Merge Join (cost=0.00..953070.25 rows=10000000 width=200)
Merge Cond: (t1.aid = t2.aid)-> Index Scan using accounts_pkey on accounts t1 (cost=0.00..401535.13 rows=10000000 width=100)-> Index Scan using accounts_pkey on accounts t2 (cost=0.00..401535.13 rows=10000000 width=100)

8.3のプランMerge Join (cost=0.00..942662.25 rows=10000000 width=194)
Merge Cond: (t1.aid = t2.aid)-> Index Scan Backward using accounts_pkey on accounts t1 (cost=0.00..396331.13 rows=10000000
width=97)-> Index Scan Backward using accounts_pkey on accounts t2 (cost=0.00..396331.13 rows=10000000width=97)
2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

36

そのほかの改良点(7)●ORDER BY ...LIMITの高速化
Webアプリケーションに良く使われるクエリ●
入力行全体をソートするのではなく，LIMIT分だけソートする● 場合によっては非常に効果的●●SELECT * FROM accounts ORDER BY abalance LIMIT
10; (1000万件)
PostgreSQL 8.2: 113.7秒●
PostgreSQL 8.3: 7.8秒●●

2007/7/27“OFFSET”付には適用されないので注意
Copyright(c)2007 SRA OSS, Inc. Japan37

PostgreSQL 8.3のリリース時期●

10月頃を予定

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

38

8.4以降で取り込むもの●VACUUM性能の向上●●DSM(Dead Space Map)

検索性能の向上●2007/7/27

ビットマップインデックス

Copyright(c)2007 SRA OSS, Inc. Japan39

参考URL•http://developer.postgresql.org/index.php/Todo:Wis
hlistFor83•http://developer.postgresql.org/index.php/Feature_Matrix•http://developer.postgresql.org/index.php/Todo:PatchStatus

2007/7/27

Copyright(c)2007 SRA OSS, Inc. Japan

40