PostgreSQL再入門

PostgreSQLの全体像と最新バージョン13の紹介
2020/11/19
SRA OSS, Inc. 日本支社
OSS事業本部 技術部
佐藤 友章
sato@sraoss.co.jp© 2020 SRA OSS, Inc. Japan

1

自己紹介
• 佐藤 友章（さとう ともあき）• SRA OSSで技術部門の責任者を務める• PostgreSQLのサポートやトレーニング、コンサルなど、今もな
おエンジニアとして15年以上の経験あり• PostgreSQLとの出会いは大学院時代、当時のバージョンは7.2• 趣味はお酒と海外旅行© 2020 SRA OSS, Inc. Japan2

PostgreSQLの全体像© 2020 SRA OSS, Inc. Japan3

PostgreSQLとは• ポストグレスキューエルと読む• 単にポストグレス、日本ではポスグレとも呼ばれる• 標準SQLの大部分とそのほかの先進的な機能をサポートする本格的なオープンソースRDBMS• 20年以上の歴史をもち、現在も活発な開発体制• 豊富なプラットフォームに対応• Unix系OS全般、Windows、macOS• 豊富なサポート言語• C、ECPG、C++、Java、Tcl/TK、Python、Perl、PHP、Ruby、ODBC、.NET Data Providerなど© 2020 SRA OSS, Inc. Japan

4

PostgreSQLのライセンス• The PostgreSQL License

COPYRIGHTファイル• https://www.postgresql.org/about/license/• 広告条項はなしの修正BSDライ
センスに類似• 使用、複製、改変、配布の自由• 複製においては著作権表示、ライセンス条文、免責事項を含めることが条件

PostgreSQL Database Management System(formerly known as Postgres, then as Postgres95)
Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
Portions Copyright (c) 1994, The Regents of the University of CaliforniaPermission to use, copy, modify, and distribute this software and its documentation
for any purpose, without fee, and without a written agreement is hereby granted,
provided that the above copyright notice and this paragraph and the following twoparagraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISEDOF THE POSSIBILITY OF SUCH DAMAGE.

• GPLと比較すると、派生物を再UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
配布する際にソースコード公開、 THE
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWAREPROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
ライセンス継承の義務がない
CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

© 2020 SRA OSS, Inc. Japan5PostgreSQLの開発体制• 特定のオーナー企業をもたない方式• 単独の企業に独占されることなく、幅広い層の厚い開発体制
コントリビュータ
メジャー
コントリビュータ

Peter Eisentraut (EDB)
Andres Freund (Microsoft)
Magnus Hagander (Redpill Linpro)
Jonathan Katz (Crunchy Data)
Tom Lane (Crunchy Data)
Bruce Momjian (EDB)
Dave Page (EDB)

支援企業

コアチーム

開発コミュニティ

Etsuro Fujita (NTT)Amit Langote (EDB)
Fujii Masao (NTT Data)
Michael Paquier (VMWare)⋮
約30名など…
※Contributor Profiles、Sponsorsより (2020年11月現在)© 2020 SRA OSS, Inc. Japan6

日本のPostgreSQLコミュニティ
ユーザコミュニティ

日本PostgreSQLユーザ会 (JPUG)• 1999年設立、2006年NPO法人化• 5,000人強のML会員• 全国各地の支部
日本におけるOSSコミュニ
ティのロールモデルでもある• 普及活動を中心にビジネスコミュニティ

PostgreSQLエンタープライズ・コンソーシアム (PGECons)• 2012年設立• 正会員企業19社、一般会員企業46社 (2020年11月現在)• エンタープライズ用途に向けた共同検証やプロモーション活動© 2020 SRA OSS, Inc. Japan

7

PostgreSQLの生い立ち
1977～1985

Ingresアメリカカリフォル
ニア大学 (UCB) で開発された、最も初期のRDBMSCA-Ingres II
Illustra1986～1994

POSTGRESUCBで研究目的で開発。ユーザ増加。
バージョン4.2で打ち切り

1994～1995

Postgres95

Informix
1996～

PostgreSQL

ソースコードの見なおしと改良。SQLに対応© 2020 SRA OSS, Inc. Japan8

PostgreSQLのバージョン番号• PostgreSQLのバージョンは6.0から始まる• POSTGRESがバージョン4.2まで、Postgres95をバージョン5と見なしてバージョン9.6以前

バージョン10以降

9.6.19メジャーバージョン

13.1マイナーバージョン

メジャー マイナーバージョン
バージョン• 1年に1度のメジャーバージョンアップでは仕様の追加・変更• バージョンアップにはデータ移行が必要• 1年に数回のマイナーバージョンアップではおもにバグ修正© 2020 SRA OSS, Inc. Japan

9PostgreSQLの歴史
JSON, レ
プリケー
ションス
ロット

1,400,000
1,200,000

ソースコードの行数

1,000,000
800,000

ビット
マップス
キャン

HOT更新

レプリ
ケーショ
ン

600,000
400,000

Window,
PITR

4CPUス
ケール

ウィンド
ウ関数,
再帰SQL

パラレル
クエリ
テーブル
AM I/F

多CPUス
ケール

同期レプ
リケーション,
FDW

マテビュー,
更新
ビュー?

JITコンパイル

BRIN, 行セキュリ
ティ,
UPSERT

論理レプ
リケー
ション

200,000
0
8.0
8.1
8.2
8.3
8.4
9.0
9.1
9.2
9.3
9.4
9.5
9.6
10111213
2005/1 2005/11 2006/12 2008/2 2009/7 2010/9 2011/9 2012/9 2013/9 2014/12 2016/1 2016/9 2017/10 2018/10 2019/10 2020/9※ソースコードの行数はclocコマンドで取得© 2020 SRA OSS, Inc. Japan10

PostgreSQLの派生製品• ライセンス制約が少ないため、商用/OSS製品のベースとして広く利用されている• PostgreSQLサイトに記載のものだけで41 (開発終了も含む)• おもな派生製品• SRA OSSのPowerGres• EnterpriseDBのEDB Postgres• AWSのAmazon Aurora• 富士通のFUJITSU Enterprise Postgres• OSSのPostgres XC/XL/X2※PostgreSQL derived databasesより (2020年11月現在)© 2020 SRA OSS, Inc. Japan

11

RDBMS市場でのPostgreSQLの位置づけ• DB-Engines Rankingによる人気ランキングでは• Oracle、MySQL、MS SQL Serverに次いで第4位のスコア• ここ数年、高い伸び率を見せている
MariaDB• Oracleの移行先として

Microsoft 2%
Access2%
SQLite2%
IBM Db2
3%• SE1/SEの販売終了、他社クラウドでの値上げもあって、Oracle離れが進んでいる• MySQLに比べると、機能が豊富で移行しやすく、システム構造が似ていて技術者の転換がしやすい

PostgreSQL
10%• Oracle以外とは棲み分け• MySQLはシンプルなWebシステム向け• Microsoft SQL ServerはWindows向け

その他
15%

Microsoft
SQL Server
19%

Oracle
24%

MySQL
23%

※DB-Engines Ranking (Relational DBMS) より (2020年11月時点)© 2020 SRA OSS, Inc. Japan

12

PostgreSQL 13の紹介© 2020 SRA OSS, Inc. Japan13

Btreeインデックスの重複排除 (1)• 格納データ構造の変更
キー値

ヒープTID

データ1TID 1

データ2

TID 2

データ2

TID 3

データ2

TID 4

データ3

TID 5

• 遅延処理方式• ページ分割のときに重複排除を行う• 重複排除が効かない場合• データ型：

格納サイズ：減処理オーバヘッド：増キー値

ヒープTID

データ1

TID 1

データ2

TID 2, 3, 4

データ3

TID 5

• 非決定的なCOLLATEを使った文字列型• real、double precision• numeric• コンテナ型 (配列、複合型、範囲型、JSONB)• INCLUDEを伴うインデックス© 2020 SRA OSS, Inc. Japan

14

Btreeインデックスの重複排除 (2)onがデフォルト
db1=# CREATE TABLE btree_test (f1 text COLLATE "C");
db1=# CREATE INDEX ON btree_test (f1) WITH (deduplicate_items=on);
db1=# INSERT INTO btree_text SELECT 'foo' FROM generate_series(1,100000);
600
500

2000

重複多数の場合

1500
1000
500

実行時間 (ms)

インデックスサイズ (kB)

2500

400

差異が小さい→遅延処理方式のため

300
200
100

00off

onoff

© 2020 SRA OSS, Inc. Japanon

15新たな計画タイプ (1)インクリメンタルソート• 高速化、使用メモリの節約• enable_incremental_sort (デフォルトon)

ソート項目のうちdt列はインデックスで
db1=# EXPLAIN ANALYZE SELECT * FROM t_log WHERE dt > ' 2020-05-25 14:10'
すでにソート済み
ORDER BY dt, severity, message LIMIT 1000;
Limit (cost=0.38..74.06 rows=1000 width=46) (省略)-> Incremental Sort (cost=0.38..735.25 rows=9974 width=46) (省略)
Sort Key: dt, severity, message
Presorted Key: dt
Full-sort Groups: 32 Sort Method: quicksort
全列ソートのとき127kB
Average Memory: 27kB Peak Memory: 27kB-> Index Scan using t_log_dt_idx on t_log(cost=0.29..379.83 rows=9974 width=46) (省略)
Index Cond: (dt > '2020-05-25 14:10:00'::timestamp without time zone)
Planning Time: 0.107 ms
全列ソートのとき3.3ms
Execution Time: 0.649 ms© 2020 SRA OSS, Inc. Japan16

新たな計画タイプ (2)ストレージ上のハッシュ集約• ハッシュをストレージ書き出しできるように• GroupAggregateより速く、メモリHashAggregateより遅い
db1=# EXPLAIN ANALYZE SELECT uid, count(1) FROM t_login GROUP BY uid;
HashAggregate (cost=36995.00..41912.19 rows=101094 width=12) (省略)Group Key: uid
Planned Partitions: 4work_mem = 4MBの場合
Peak Memory Usage: 4145 kB
Disk Usage: 30376 kB
HashAgg Batches: 4-> Seq Scan on t_login (cost=0.00..16370.00 rows=1000000 width=4) (省略)
Planning Time: 0.092 ms
Execution Time: 301.152 ms© 2020 SRA OSS, Inc. Japan17

その他• パーティションテーブル改良• ロジカルレプリケーション対応• パーティション毎の結合の改良• 行単位BEFOREトリガ対応• 並列VACUUM• 進捗報告ビューの拡張• pg_stat_progress_basebackup• pg_stat_progress_analyze• SQL機能の追加• FETCH FIRST … WITH TIES
• JSON Pathのdatetimeメソッド• pg_rewindの改良• 自動クラッシュリカバリ
• リカバリ設定を自動作成• WALアーカイブ適用• pg_verifybackup© 2020 SRA OSS, Inc. Japan18

PostgreSQL 13のまとめ• PostgreSQL 13は変更が少なめのリリース• 非互換が少なく、バージョンアップしやすい• 次バージョンに持ち越しの様々な開発が継続中• 詳しくは「PostgreSQL 13検証報告」を参照• https://www.sraoss.co.jp/tech-blog/pgsql/pg13report/• またはpostgresql 13 sraoss

検索© 2020 SRA OSS, Inc. Japan

19

オープンソースとともに© 2020 SRA OSS, Inc. Japan

20