オープンソースデータベース PostgreSQL 最新動向のご紹介

(1)

オープンソースデータベース

PostgreSQL最新動向のご紹介

PostgreSQL最新動向＆活用事例セミナー

2013-06-27 14:10～14:50

(2)

改めて・・・

PostgreSQL とは

 代表的なオープンソース

RDBMS



Ingres(1970～ UCB) を先祖に持つ



PostgreSQL 6.0 (1996 ～) からでも 15年以上の歴史



_{BSDタイプのライセンスで配布}



PostgreSQL Global Development Gruop　と University of

California が著作権を持つ

 ひとつのオーナー企業、オーナー個人を持たない



PostgreSQL開発に時間を割く技術者を提供している企業がいく

つかある／その企業群も少しずつ変遷している

(5)

₅

Contributors

PostgreSQL開発体制

Major Contributors

Core Team

Josh Berkus

Tom Lane

Peter Eisentraut

Magnus Hagander

Bruce Momjian

Dave Page

支援企業

開発コミュニティ

　※www.postgresql.org 記載より

Fujii Masao

Harada Hitoshi

Ishii Tatsuo

Kaigai Kouhei

Suzuki Koichi

：　

30～40名

etc...

(6)

PostgreSQLの歩み

PG 7.0 PG 7.1 PG 7.2 PG 7.3 PG 7.4 PG 8.0 PG 8.1 PG 8.2 PG 8.3 PG 8.4 PG 9.0 PG 9.1 PG 9.2 0 20000 40000 60000 80000 100000 120000

PostgreSQL のコードサイズとリリース

コードサイズ (b yt e )

更新の

高速化

ビットマップ

スキャン

プリペアド

ステートメント

並列実行

VACUUM

外部キー、

JOIN構文

トランザク

ションログ

スキーマ

PITR、

Windows

4 CPU

スケール

|

2001年

4月

|

2005年

1月

|

2009年

7月

|

2006年

12月

|

2002年

11月

ウィンドウ関数

再帰SQL

64 CPU

スケール

|

2011年

9月

同期レプリケーション

外部テーブル

レプリケーション

Windows 64bit

１年１バージョン

１０年以上

安定リリース

コ

ー

ド

サ

イ

ズ

（Ｋ

Ｂ

）

(7)

₇

PostgreSQLはどこで使われているか？

 業務基幹システムの商用データベース製品置き換え



EnterpriseDB 「Postgres Plus Advanced Server」など、

PostgreSQLベースの商用ＤＢ互換製品も



_{Blog、SNS、ゲーム、各種の新しいオンラインサービス}



PostgreSQL も使われているが、MySQL も強い

 オープンソース

Webアプリケーションの標準データベース

 廉価クラウドで標準提供される

DBMS



BI分野の独自データベース製品のベースとして



Netezza、GreenPlum、Yahoo自社内むけデータベース

 地理情報システムで大きな存在感



PostGIS というオープンソースの追加モジュールが強力

(8)

(9)

₉

PostgreSQL 9.3 は盛りだくさん

マテ

リアライズド

ビュー

ページ

チェックサム

ストリーミングレプリケーション

タイムライン追随

ロックタイムアウト

外部キー制約

ロック競合軽減

再帰ビュー

暗黙の

更新可能ビュー

並列pg_dump

postgres_fdwと

書き込みFDW

pg_xlogdump

LOB

サイズ拡張

LATERAL結合

pg_isready

COPY FREEZE

9.2 開発での

機能候補が

シフトした

JSON関数

worker_spi

DDLトリガ

(10)

ストリーミングレプリケーション

タイムライン追随

PostgreSQL

Prim

PostgreSQL

Stby

WAL

ファイル

ストリーミングレプリケーション

ログシッピング



PostgreSQL のレプリケーションはどんなもの？

 タイムライン（時系列）とは？

 リカバリするごとに

_{+1 されるメタ情報}

PostgreSQL

stby

PostgreSQL

stby

カスケード可能

１対多可能

(11)

₁₁

ストリーミングレプリケーション

タイムライン追随

PostgreSQL

Prim

（１）昇格ノードに参照元を切り替え

（２）カスケードで昇格後に継続処理

（３）ノードのスイッチオーバー

 これまでのストリーミングレプリケーションで、

できそうでできなかったことが可能に

PostgreSQL

Prim

↑

昇格

PostgreSQL

stby

障害

×

PostgreSQL

Prim

PostgreSQL

Prim

↑

昇格

PostgreSQL

stby

障害

PostgreSQL

Stby

↑

降格

PostgreSQL

Prim

↑

昇格

↓反転

×

他にも改善項目：

「昇格の高速化」

「タイムアウト設定拡充」

従来は

ログシッピングを使うか、

ベースバックアップ再取得

(12)



FDW = foreign data wrapper （外部データラッパ）

 外部にあるデータをテーブルのようにアクセスする枠組み

 強力なシステム間連携手段

postgres_fdw

と

書き込み FDW

PostgreSQL

CSV

ファイル

LDAP server

MySQL

ODBC

JDBC

Oracle

_{Web service}

mongoDB

redis

couchDB

PostgreSQL

9.3 から

書き込みに対応

postgres_fdw が本体付属

(13)

₁₃

postgres_fdw

と

書き込み FDW

db1=#

CREATE SERVER db02srv FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (port '5432', host 'dbhost02', dbname 'db02');

db1=#

CREATE USER MAPPING FOR user1 SERVER db02srv

OPTIONS (user 'user1', password 'secret');

db1=#

CREATE FOREIGN TABLE remote_t1 (id int, v text)

SERVER db02srv

OPTIONS (schema_name 'public', table_name 't1');

 サーバ、　ユーザマッピング、　外部テーブル、のモデル

 外部テーブルは通常テーブルと同様に読み書き可能

 カスケード可能



COMMIT、ROLLBACK が可能

振る舞いは

各

FDW の実装次第

（postgres_fdw の例）

- 別バージョン対応

- リモートで条件

　絞り込み

- トランザクション

　処理に制限

(14)

マテ

リアライズド

ビュー

 結果を保持するビュー

 基本機能のみ提供

提供されない機能：

×　差分更新

×　自動リフレッシュ

×　プランナでのクエリリライト

table

[view]

SELECT

結果

結果を返す

条件選択、

結合、集約・・・

[view]

SELECT

結果

結果を返す

結果を憶えておく

ビュー参照

「リフレッシュ」

で、元テーブル

から再取得

マテビュー参照

db1=#

CREATE MATERIALIZED VIEW

mv_abalance AS

SELECT aid, abalance

FROM pgbench_accounts

ORDER BY abalance

LIMIT 10;

db1=#

REFRESH MATERIALIZED VIEW

mv_abalance;

(15)

₁₅

並列pg_dump

 ダンプを並列実行する

 以下のとき高速化できる

 複数テーブルがある

 複数ＣＰＵコアがある

 ディスク

I/Oに余裕がある



-j で並列数を指定

 並列でも単一スナップ

ショットが保証される

 ディレクトリ形式

 スタンバイでは不可

pg_dump 所用時間

(sec)

2テーブルのデータベースを2CPUマシンでダンプ

　直列：

pg_dump -j 1 -Fd -f out.d db1

　２並列：

pg_dump -j 2 -Fd -f out.d db1

直列

２並列

0 20 40 60 80 100 120 140 160

se

c

理想的なケース

では所要時間が

１／Ｎになる

(16)

 高速データローディング

を実現するオプション



COPY後の初回参照時の

処理が不要になる

 トランザクション隔離動作

としては例外的な振る舞

いになる



COMMIT前に見えてしまう

COPY FREEZE

db1=#

BEGIN;

db1=#

TRUNCATE t_huge;

db1=#

COPY t_large FROM '/tmp/t_huge.copy' FREEZE;

db1=#

COMMIT;

データロード参照 0 10,000 20,000 30,000 40,000 50,000 60,000 70,000

COPY FREEZE 効果

FREEZE FREEZE 無し実行時間 (m s )

データロード

4000万件

SELECT count(*)

初回参照

FREEZE有

FREEZEなし

(ms)

(17)

₁₇

外部キー制約

ロック競合軽減

ロックタイムアウト

 競合の少ない行ロックが

追加された



SELECT ... FROM ...

FOR KEY SHARE

 主キー以外のカラムに対

する UPDATE とロック競

合しない

 外部キー制約で使われる



SELECT ... FROM ...

FOR NO KEY UPDATE

 「

FOR KEY SHARE」と競

合しない「FOR UPDATE」

 タイムアウト設定が可能に



lock_timeout 設定

 従来は、

WAIT か

NOWAIT の二択

db1=#

BEGIN;

db1=#

SET LOCAL lock_timeout

TO '10s';

db1=#

UPDATE t SET c = 'xx'

WHERE id = 12345;

ERROR: canceling statement

due to lock timeout

(18)

暗黙の

更新可能ビュー

 シンプルなビューは、

作っただけで更新できる



INSERT、 UPDATE、

DELETE は参照元テーブ

ルに適用される

 従来は手動でRULE や

TRIGGER を定義して上

げる必要があった

 ビューで再帰

SQLを記述

再帰

ビュー

CREATE RECURSIVE VIEW v (n)

AS

SELECT 1

UNION ALL

SELECT n+1 FROM v WHERE n < 3;

**db=# SELECT * FROM v;**

n

1

2

3 (3 rows)

WITH RECURSIVE

構文と同様のことが

記述できる

SELECT ≪カラムリスト≫

FROM ≪単一テーブル≫

WHERE ≪検索条件≫

ORDER BY ≪ソートカラム≫

シンプルなビューとは？：

(19)

₁₉



CREATE、DROP、ALTER

にトリガ設定できる



_{DDL操作の一部制限に}

 連動した自動操作に

 テーブル単位レプリケーショ

ンツールでの応用が有力



PostgreSQL死活確認を

するクライアントツール

 「接続できるか？」を確認

 実際には接続しない

 ホスト、ポートを指定

 ユーザ名、パスワード不要

 最大接続数が埋まってい

ても大丈夫

 プロセスが生きているだけ

ではＮＧ扱い

DDLトリガ

(20)

ページ

チェックサム

LOB

サイズ

拡張

 テーブル、インデックス

データのバイナリ破損を

検知できる

 従来は、ヘッダ部分の破

損しか検知できなかった

 ラージオブジェクトの上限

2GB を拡張

 従来は、（圧縮後）２ＧＢ

を超えるとおかしな動作

　　　　　

↓

 最大

4TB に拡張

WARNING: page verification

failed, calculated checksum

61554 but expected 3960

ERROR: invalid page in block

23 of relation

base/12896/16466

（エラーメッセージ例）

弊社（

SRA OSS,Inc.）

(21)

₂₁



ISO SQL:1999 にある

LATERAL構文に対応



FROM句内のサブクエリ

で左側にあらわれた要素

を参照できる



JSON型データむけの　

組み込み関数が追加

 行データを

JSON型として

出力する関数のみ

　　　　　

↓



JSON配列の各種の操作

 表への逆変換

 要素取り出し

LATERAL結合

JSON関数追加

多機能な１カラム

データとして応用

できる

**SELECT * FROM t1,**

LATERAL

**(SELECT * FROM t2**

WHERE c2 = t1.c2) v2,

LATERAL

**(SELECT * FROM t3**

WHERE c3 = v2.c3) v3;

(22)



WALファイル内容を参照

するツールが標準付属

用途としては：



WALファイルが破損してい

ないかを確認

 新たな更新処理が発生した

かどうかをデータベース接

続することなく確認

 トランザクションＩＤを使った、

ＰＩＴＲリカバリ位置決め

pg_xlogdump

$

pg_xlogdump \

0000001300000002000000F5

rmgr: Heap len (rec/tot):

21/ 237, tx: 13238,

lsn: 2/F502A4A8, prev 2/F502A440,

bkp: 1000, desc: insert: rel

1663/12896/16573; tid 0/1

rmgr: Btree len (rec/tot):

18/ 174, tx: 13238,

lsn: 2/F502A598, prev 2/F502A4A8,

bkp: 1000, desc: insert: rel

1663/12896/16579; tid 1/3

rmgr: Transaction len (rec/tot):

12/ 44, tx: 13238,

lsn: 2/F502A648, prev 2/F502A598,

bkp: 0000, desc: commit:

2013-06-06 17:46:32.281513 JST

　

：

（後略）

読み解くには、

それなりに技術が

必要となる

(23)

₂₃

PostgreSQL 9.3 リリース



beta1 時点で 9.3 の大きな機能追加は完結



_{6/15 に 9.3 系 STABLEブランチ}

 まもなく

9.3beta2 がリリース予定



2013年秋にリリースされる見通し

（例年通りなら・・・）

HEAD

9.3STABLE

2013/6/15

Branch

9.3beta2

2013/6/27 ?

2013/5/13

9.3beta1

9.2STABLE

2012/6/14

Branch

9.2.4 2013/4/4

(24)

まとめ



PostgreSQL はオープンソースソフトウェアとして、

安定した開発体制を維持しています



PostgreSQL 9.3 には、多数の機能拡張が含ま

れています

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

ご質問を承ります。

オープンソースデータベース PostgreSQL 最新動向のご紹介

オープンソースデータベース

PostgreSQL最新動向のご紹介

PostgreSQL最新動向＆活用事例セミナー

2013-06-27 14:10～14:50

TOC



PostgreSQL の概要

 次期バージョン

PostgreSQL 9.3 のご紹介

講演者



SRA OSS, Inc. 日本支社マーケテイング部

PostgreSQL技術グループ長 高塚 遙

 主として

PostgreSQL のヘルプデスク、導入構築、

コンサルティング等の業務を担当

3

改めて・・・

PostgreSQL とは

 代表的なオープンソース

RDBMS



Ingres(1970～ UCB) を先祖に持つ



PostgreSQL 6.0 (1996 ～) からでも 15年以上の歴史



BSDタイプのライセンスで配布



PostgreSQL Global Development Gruop と University of

California が著作権を持つ

 ひとつのオーナー企業、オーナー個人を持たない



PostgreSQL開発に時間を割く技術者を提供している企業がいく

つかある／その企業群も少しずつ変遷している

5

Contributors

PostgreSQL開発体制

Major Contributors

Core Team

Josh Berkus

Tom Lane

Peter Eisentraut

Magnus Hagander

Bruce Momjian

Dave Page

支援企業

開発コミュニティ

※www.postgresql.org 記載より

Fujii Masao

Harada Hitoshi

Ishii Tatsuo

Kaigai Kouhei

Suzuki Koichi

：

30～40名

etc...

PostgreSQLの歩み

PostgreSQL のコードサイズとリリース

更新の

高速化

ビットマップ

スキャン

プリペアド

ステートメント

並列実行

VACUUM

外部キー、

JOIN構文

トランザク

ションログ

スキーマ

PITR、

Windows

4 CPU

スケール

|

2001年

4月

|

PostgreSQL技術グループ長　高塚遙

₃

_{BSDタイプのライセンスで配布}

PostgreSQL Global Development Gruop　と University of

₅

　※www.postgresql.org 記載より

：　

₇

_{Blog、SNS、ゲーム、各種の新しいオンラインサービス}

₉