PostgreSQLの最新情報
日本PostgreSQLユーザ会
石井 達夫

すみません．カナダに行けませんでした

2009/05/30

Copyright(c)2009 Tatsuo Ishii

2

コンファレンスについて●

スケジュールはこちら●

http://www.pgcon.org/2009/schedule/

今回の目玉は右の方だったようです●“How to Get Your
PostgreSQL Patch
Accepted”●

2009/05/30

Copyright(c)2009 Tatsuo Ishii

3

2009/05/30

Copyright(c)2009 Tatsuo Ishii

4

順調に成長を続けるPostgreSQL

再帰SQLWindow関数

マルチプロセッサ
最適化

9 00000
8 00000
7 00000

WALログ
TOAST

6 00000
5 00000

PL/pgSQL
Concurrent
Vacuumトリガ

4 00000

HOT
全文検索
autovacuumWindows対応

3 00000

行ロック
MVCC

2 00000

副問い合わせマルチバイト1 00000
0

Copyright(c)2009 Tatsuo Ishii

7 .4

8 .0

8 .1

8 .2

8 .3

8 .4

2009

2002

7 .3

2008

7 .2

2006

7 .1

2005

7 .0

2003

6 .5

2001

6 .4

2000

6 .3

1998

1997
2009/05/30

6 .2

1999

6 .1

5

PostgreSQLの主要な機能●

標準装備の機能
行ロック●
読み取り一貫性(MVCC)●
コストベース・オプティマイザ●
パーティショニング
●
ストアドプロシジャ，トリガ●
オンライン・バックアップ●
アーカイブログ
●
全文検索
●

●

オプション機能●

2009/05/30

レプリケーション，クラスタリング，GIS対応
Copyright(c)2009 Tatsuo Ishii6この５年間でPostgreSQLの性能はどれだけ向上したか？
●

PostgreSQL 7.4(2003年)からPostgreSQL8.3(2008年)の間に...
検索性能164%向上●
一括ロード性能200%向上●
更新性能300%向上●

2009/05/30

Copyright(c)2009 Tatsuo Ishii

7検索性能の向上
164%の性能向上

7.4(2003/11)から8.3(2008/2)の間に2009/05/30

Copyright(c)2009 Tatsuo Ishii

8

一括ロード性能の向上
200%の性能向上

7.4(2003/11)から8.3(2008/2)の間に1(15GB)

億件のロード2009/05/30

Copyright(c)2009 Tatsuo Ishii

9更新性能の向上
300%の性能向上

7.4(2003/11)から8.3(2008/2)の間に2009/05/30

Copyright(c)2009 Tatsuo Ishii

108.4注目の新機能：再帰SQLのサポート●

リスト，木構造などのデータ構造から再帰的にデータを取得できる●●

住友電工情報システムとSRA OSSの協力により開発●●

今はアプリ側で処理するか，PL/pgSQLなどの関数を使わなければならず，不便で効率も悪かった

ユーザ企業が積極的にPostgreSQLの開発に関与する新しい形態(エコシステム)

実装はSQL標準の共通SQL式(Common Table
Expression: CE)句を採用

2009/05/30

Copyright(c)2009 Tatsuo Ishii

11単純な再帰SQL
WITH RECURSIVE t(n) AS (VALUES (1) – 非再帰項
UNION ALLAL
SELECT n+1 FROM t WHERE n < 100 -- 再帰項)
SELECT sum(n) FROM t;sum-----5050

2009/05/30

Copyright(c)2009 Tatsuo Ishii

12サンプルデータ
神奈川支部
東日本営業本部

横浜営業所
関内営業所

厚木工場
東京本社

戸塚営業所

製造事業本部
千葉工場
西日本営業本部

大阪支部

梅田営業所
難波営業所

2009/05/30

Copyright(c)2009 Tatsuo Ishii13RDBでの表現
事業所名
東京本社
東日本営業本部
神奈川支部
横浜営業所
関内営業所
戸塚営業所
製造事業本部
厚木工場
千葉工場
西日本営業本部
大阪支部
梅田営業所
難波営業所
2009/05/30

管轄事業所名東京本社
東日本営業本部
神奈川支部
横浜営業所
横浜営業所
東京本社
製造事業本部
製造事業本部
東京本社
西日本営業本部
大阪支部
大阪支部
Copyright(c)2009 Tatsuo Ishii

人員数
1 00
5050
20
15
1050200
200
5050
2020
14

東日本営業本部以下の人員数を求める
再帰SQLの定義
WITH RECURSIVE 東日本営業人員(事業所名, 人員数) AS(SELECT 事業所名, 人員数 FROM 人員構成表
WHERE 事業所名 = '東日本営業本部'
UNION ALL
SELECT PEN.事業所名, PEN.人員数 FROM 人員構成表 ASPEN,
東日本営業人員 AS EBP
WHERE PEN.管轄事業所名 = EBP.事業所名)
SELECT SUM(人員数) FROM 東日本営業人員;
SELECT本体
2009/05/30

Copyright(c)2009 Tatsuo Ishii

15組織階層の「深さ」を表示
WI TH RECURSI VE 東日本営業人員(事業所名, レベル, 人員数) AS(SEL ECT 事業所名, 1 , 人員数 F ROM 人員構成表
WHERE 事業所名 = ' 東日本営業本部'
UNI ON AL L
SEL ECT PEN. 事業所名, EBP. レベル+1, PEN. 人員数 F ROM 人員構成表 ASPEN,
東日本営業人員 AS EBP
WHERE PEN. 管轄事業所名 = EBP. 事業所名)
SEL ECT * FROM 東日本営業人員;
事業所名 | レベル | 人員数-------------+--------+-------東日本営業本部 |
1 |50
神奈川支部
|2 |50
横浜営業所
|
3 |20
関内営業所|4 |15
戸塚営業所
|4 |
10(5r o ws )2009/05/30
Copyright(c)2009 Tatsuo Ishii

16

PostgreSQLの新機能：Window関数●●●

SQL標準のデータ解析関数●
順序付けなどに利用●行を集約しない(集約関数の一種ではない)
主な関数●row_number: 行番号●
rank, dense_rank, percent_rank, cume_dist: 順位●
ntile: N個のグループに分類●
first_value, last_value, nth_vaue: ある順位のデータ
OVER●関数() OVER(PARTITION BY...): 区間に分割●
関数() OVER(ORDER BY...): 区間毎にソート

2009/05/30

Copyright(c)2009 Tatsuo Ishii

17

Window関数: row_number
te s t =# SEL ECT row_number( ) OVER ( ) , * FROM e mp s a l a r y ;r o w_ n u mb e r | d e p n a me | e mp n o | s a l a r y | e n r o l l _ d a t e------------+-----------+-------+--------+------------1 | devel op|
10 |5200 | 2007-08-01
2 | sal es|
1 |5000 | 2006-1 0-01
3 | per sonnel |5 |3500 | 2007-1 2-1 0
4 | sal es|
4 |4800 | 2007-08-08
5 | per sonnel |2 |3900 | 2006-1 2-23
6 | devel op|7 |4200 | 2008-01 -01
7 | devel op|
9 |4500 | 2008-01 -01
8 | sal es|
3 |4800 | 2007-08-01
9 | devel op|
8 |6000 | 2006-1 0-01
1 0 | devel op|
11 |5200 | 2007-08-1 5(1 0 r o ws )出力結果に対して，1から連番を振る
2009/05/30

Copyright(c)2009 Tatsuo Ishii

18

Window関数: rank
test=# s e l e c t * f r o m e mp s a l a r y o r d e r b ye mp n o ;d e p n a me | e mp n o | s a l a r y | e n r o l l _ d a t e-----------+-------+--------+------------s al es|
1 |5000 | 2006-1 0-01
per s onnel |2 |3900 | 2006-1 2-23s al es|
3 |4800 | 2007-08-01
s al es|
4 |4800 | 2007-08-08
per s onnel |5 |3500 | 2007-1 2-1 0
dev el op|
7 |4200 | 2008-01 -01
dev el op|
8 |6000 | 2006-1 0-01
dev el op|
9 |4500 | 2008-01 -01
dev el op|
10 |5200 | 2007-08-01
dev el op|
11 |5200 | 2007-08-1 5(1 0 r o ws )t e s t =# SEL ECT d e p n a me , e mp n o , s a l a r y , rank( )OVER ( PARTI TI ON BY depname ORDER BY sal aryDESC) F ROM e mp s a l a r y ;d e p n a me | e mp n o | s a l a r y | r a n k-----------+-------+--------+-----devel op|
8 |6000 |
1devel op|
10 |5200 |
2devel op|
11 |5200 |
2devel op|
9 |4500 |
4devel op|
7 |4200 |
5
per s onnel |2 |3900 |
1
per s onnel |5 |3500 |2sal es|
1 |5000 |
1sal es|
4 |4800 |
2sal es|
3 |4800 |
2(1 0 r o ws )元データ
部門の中で，給料の高い順に並べる

2009/05/30

Copyright(c)2009 Tatsuo Ishii

19

Window関数: dense_rankt e s t =# SEL ECT d e p n a me , e mp n o , s a l a r y ,dense_rank( ) OVER ( PARTI TI ON BY depname ORDER BY sal ary DESC)
FROM e mp s a l a r y ;d e p n a me | e mp n o | s a l a r y | d e n s e _ r a n k-----------+-------+--------+-----------devel op|
8 |6000 |
1devel op|
10 |5200 |
2devel op|
11 |5200 |
2devel op|
9 |4500 |
3devel op|
7 |4200 |
4
per sonnel |2 |3900 |
1
per sonnel |5 |3500 |
2
sal es|1 |5000 |
1
sal es|4 |4800 |
2
sal es|3 |4800 |
2(1 0 r o ws )rankと違って，順位番号の抜けがない
2009/05/30

Copyright(c)2009 Tatsuo Ishii

20

Window関数: percent_rank
te s t =# SEL ECT d e p n a me , e mp n o , s a l a r y ,percent _rank( ) OVER ( PARTI TI ON BY depname ORDER BY sal ary DESC)
FROM e mp s a l a r y ;d e p n a me | e mp n o | s a l a r y | p e r c e n t _ r a n k-----------+-------+--------+-------------devel op|
8 |6000 |
0
順位を割合（パーセント）
devel op|
10 |5200 |
0. 25
で表します
devel op|
11 |5200 |
0. 25
devel op|
9 |4500 |
0. 75
devel op|
7 |4200 |
1
per sonnel |2 |3900 |
0per sonnel |5 |3500 |
1sal es|
1 |5000 |
0sal es|
4 |4800 |
0. 5
sal es|3 |4800 |
0. 5(1 0 r o ws )2009/05/30

Copyright(c)2009 Tatsuo Ishii

21

Window関数: ntilet e s t =# SEL ECT d e p n a me , e mp n o , s a l a r y ,nt i l e( 3) OVER ( PARTI TI ON BY depname ORDER BY sal ary DESC)
FROM e mp s a l a r y ;d e p n a me | e mp n o | s a l a r y | n t i l e-----------+-------+--------+------devel op|
8 |6000 |
1
部門毎に給与をdevel op|
10 |5200 |
1
できるだけdevel op|
11 |5200 |
2
3分割しますdevel op|
9 |4500 |
2devel op|
7 |4200 |
3
per sonnel |2 |3900 |
1
per sonnel |5 |3500 |
2
sal es|1 |5000 |
1
sal es|4 |4800 |
2
sal es|3 |4800 |
3(1 0 r o ws )2009/05/30

Copyright(c)2009 Tatsuo Ishii

22

Window関数: cume_dist
te s t =# SEL ECT d e p n a me , e mp n o , s a l a r y F ROM(SEL ECT *, cume_di st ( ) OVER ( ORDER BY sal ary DESC) AS r a n k FROMe mp s a l a r y )AS f o o WHERE r a n k <= 0. 3;
d e p n a me | e mp n o | s a l a r y---------+-------+-------devel op |8 |6000
devel op |
10 |
5200
devel op |
11 |
5200
(3 r o ws )cume_distは「累積分散（パーセンタイル）」を表します．上の問合わせは，給与の上位3割を示します．

2009/05/30

Copyright(c)2009 Tatsuo Ishii23Window関数： 集約関数との組み合わせSEL ECT d e p n a me , e mp n o , s a l a r y , sum( sal ary)
OVER w FROM empsal ary WI NDOW w AS ( PARTI TI ON BYdepname) ;d e p n a me | e mp n o | s a l a r y | s u m-----------+-------+--------+------devel op|
11 |5200 | 251 00
devel op|
7 |4200 | 251 00
devel op|
9 |4500 | 251 00
devel op|
8 |6000 | 251 00
devel op|
10 |5200 | 251 00
per sonnel |5 |3500 | 7400
per sonnel |2 |3900 | 7400
sal es|3 |4800 | 1 4600
sal es|1 |5000 | 1 4600
sal es|4 |4800 | 1 4600
(1 0 r o ws )sumは部門内の給料の合計を示します
2009/05/30

Copyright(c)2009 Tatsuo Ishii

24

PostgreSQL 8.4: その他の改良
●

●

●

●

VACUUMの高速化●
Visibility Mapにテーブルの不用領域の位置を記録し，無駄なテーブルのスキャンを防ぐ
Free Space Map(FSM)のオンディスク化●
ディスク上にFSMを持つことにより，FSMの溢れが発生しない>追跡不能な再利用可能領域が発生しなくなる
ディスク先読みの並列実行●
effective_io_concurrencyで並列度を指定(=ドライブの物理台数)●
一部プランのみ．Linux/UNIXで利用可能
並列リストア
●DBのリストア処理を並列に実行できるようになった

2009/05/30

Copyright(c)2009 Tatsuo Ishii

25

VACUUMの効率化(Visibility Map)
8.3以前では，テーブルをすべて読まないと，不用領域を見つけることができなかった

VisibilityMap
2009/05/30

8.4では，Visibility Mapを使って必要なところだけを読んで不要領域を発見できる
Copyright(c)2009 Tatsuo Ishii

26

PostgreSQL 8.4: その他の改良
●
●
●
●
●

●
●

●
●

列単位でのアクセス権限設定
可変引数関数，デフォルト引数関数
前方一致による全文検索
ハッシュインデックスの高速化
SELECT DISTINCTの高速化(GROUP BYへの書き換え必要なし)
EXISTS, NOT EXISTSの高速化
統計情報ファイルの指定が可能（メモリファイル利用による高速化も可能)
デッドロックの際に原因となったSQLを表示
関数単位の実行回数，実行時間のログ

2009/05/30

Copyright(c)2009 Tatsuo Ishii

27

パラレルリストアの効果●●

PostgreSQL 8.4(4/10版)
遅い(秒)チューニングあり●

チューニング内容

1400

1200

DBサイズ2.7GB

●

1600

1000

800

600

–

shared_buffers = 256MB–

wal_buffers = 256

400

200–

check_point_segments = 16

速い

0PostgreSQL 8.3 チューニ
PostgreSQL 8.4
ング無し
PostgreSQL 8.3
PostgreSQL 8.4+ パラレル
リストア

2009/05/30

Copyright(c)2009 Tatsuo Ishii28pgpool-II 2.2リリース！
●

pgpool-IIとは●
●
●
●
●
●

●

PostgreSQL専用のオープンソースクラスタソフト
PostgreSQL 7.4から8.3まで対応
レプリケーション，負荷分散，パラレルクエリなどの機能管理用GUIツールあり
20万のダウンロード実績
他のレプリケーションソフトとの連携も可能●
Slony-I, warm standbye

pgpool-II 2.2●●●

2009/05/30

SERIALIZABLEトランザクションへの対応
オンラインリカバリの改善
信頼性の向上Copyright(c)2009 Tatsuo Ishii29pgpool-IIを導入するメリット
レプリケーションによりデータのリアルタイムバックアップ●
故障DBの自動切り放し●
HAよりも短いフェイルオーバ時間●
同期型レプリケーションなので，複数サーバ間でデータの一時的なずれが起きない●
負荷分散機能により，検索系の性能アップが可能●
PostgreSQLアプリケーションの修正は最小限●運用を止めずにDBの切り離し，復帰，追加が可能●
PostgreSQLの可用性，性能を向上可能●

2009/05/30

Copyright(c)2009 Tatsuo Ishii30pgpool-IIのアーキテクチャ

PostgreSQL

Shared Nothing型の分散クラスタ構成
問合わせ

PostgreSQL

問合わせ
問合わせ
DBクライアント

pgpool-II

PostgreSQL
問合わせ2009/05/30

Copyright(c)2009 Tatsuo Ishii

31

コネクションプールの効果
DBクライアントとpgpoolIIの間の接続が切れても，pgpool-IIとDBの間のコネクションを維持●
次回DBクライアントからの接続があったときに再利用するので性能が向上●
コネクションプールをしない
場合に比べ，数倍の性能向上が見られるケースも性能(TPS)●

2009/05/30

800
700
600
500
400
300
200
100
0
コネクションプールなし

コネクションプールあり

pgbenchを使い，検索問合わせを10000回実行した結果
データ件数: 10万件
ハードウェア: Centrino 1.1GHz
ソフトウェア: Linux 2.6/PostgreSQL 8.3/pgpool-II 2.1

Copyright(c)2009 Tatsuo Ishii32pgpool-IIの構成例（１）
アプリケーションサーバそれぞれにpgpool-IIを配置
pgpool-II自体の可用性を向上

PostgreSQL

PostgreSQL

PostgreSQL
DBクライアントTomcat+pgpool-II
2009/05/30

Copyright(c)2009 Tatsuo Ishii33pgpool-IIの構成例（２）pgpool-IIで負荷分散とフェイルオーバ
Slony-Iでレプリケーション
更新問合わせと検索問合わせの切り分けをpgpool-IIが担当

Slony-I
マスタ

更新問合わせ問合わせ

Slony-I
スレーブ

検索問合わせDBクライアント

pgpool-II
検索問合わせ2009/05/30

Copyright(c)2009 Tatsuo Ishii

Slony-I
スレーブ

34

pgpool-II事例
オープンドア様事例●
国内最大級規模の携帯電話向けSNS/ゲームサイト●
月間4億PV，トランザクシ
ョンの2割が更新系●
pgpool-II + Slony-Iで20台以上のPostgreSQLを管理．スケールアウトする大規模システムを構築●

http://members.techtarget.itmedia.co.jp/tt/members/0802/28/news01.html
ITMedia techTarget記事より引用
2009/05/30

Copyright(c)2009 Tatsuo Ishii35pgpool-IIの関連情報●pgpool-II開発サイト●●

pgpool-II日本語メーリングリスト●●

http://pgfoundry.org/projects/pgpool/
http://www.sraoss.jp/mailman/listinfo/pgpoolgeneral-jp「PostgreSQLには絶対pgpool-II」(Think IT)●

2009/05/30

http://www.thinkit.co.jp/article/98/1/

Copyright(c)2009 Tatsuo Ishii36

ご清聴ありがとうございました

2009/05/30

Copyright(c)2009 Tatsuo Ishii

37