PostgreSQL入門
2022/6/25 OSC2022 Online/Hokkaido
SRA OSS, Inc. 日本支社
佐藤 友章
sato@sraoss.co.jp© 2022 SRA OSS, Inc. Japan

1

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

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

PostgreSQLの特徴• 対応アーキテクチャ• x86, x86_64, SPARC, POWER, ARMなど• メモリ容量の制限はなし• バージョンが新しくなるほど効率的にメモリを使用• ファイルシステムレベルのI/Oアクセス• 論理的な容量制限はほぼない• 追記型による性能特性• INSERT、DELETEは (UPDATEに比べて) 速い• VACUUMが必要• 同時実行でロック待ち少ない• マルチプロセスモデル© 2022 SRA OSS, Inc. Japan

項目制限

データベースサイズ

制限なし

テーブルサイズ

32TB

行サイズ

400GB

フィールドサイズ

1GB

テーブルの行数

制限なし

テーブルの列数

250-1600
4

PostgreSQLのライセンス• The PostgreSQL License• 広告条項はなしの修正BSDライ
センスと同様• 使用、複製、改変、配布の自由• 複製においては著作権表示、ライセンス条文、免責事項を含めることが条件

COPYRIGHTファイル
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 California
Permission 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 FORDIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITSDOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISEDOF THE POSSIBILITY OF SUCH DAMAGE.

• GPLと比較すると、派生物を再THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF配布する際にソースコード公開、 INCLUDING,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.

© 2022 SRA OSS, Inc. Japan5

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

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

支援企業

コアチーム

開発コミュニティ

Etsuro Fujita (NTT)Kyotaro Horiguchi (NTT)Fujii Masao (NTT Data)
Michael Paquier (VMWare)Masahiko Sawada (EDB)⋮約40名など…

※PostgreSQL公式サイト Contributor Profiles、Sponsorsより (2022年6月現在)© 2022 SRA OSS, Inc. Japan6

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

日本PostgreSQLユーザ会 (JPUG)• 1999年設立、2006年NPO法人化• 全国各地に支部• 普及活動を中心に、マニュアル日本語訳やカンファレンスを実施

ビジネスコミュニティ

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

7

PostgreSQLの生い立ち
1977～1985

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

1986～1994

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

1994～1995Postgres95

Informix
1996～

PostgreSQL

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

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

バージョン10以降

9.6.24
メジャー
バージョン

14.4

マイナー
バージョン

メジャー マイナー
バージョン
バージョン

• メジャーバージョンでは機能追加・仕様変更、マイナーバー
ジョンではバグ修正© 2022 SRA OSS, Inc. Japan9

2022/6/16
インデックス破損
14.4リリースあり

PostgreSQLのリリースサイクル• マイナーバージョンは四半期ごと (5, 8, 11, 2月) + 緊急時• メジャーバージョンは毎年第3四半期 (7-9月)• メジャーバージョンはリリースから5年間サポート
バージョン

サポート可否

初リリース

最終リリース14○2021/9/30

2026/11/1213○

2020/9/24

2025/11/1312○

2019/10/3

2024/11/14

11○

2018/10/18

2023/11/9

10○

2017/10/5

2022/11/10

9.6

×2016/9/29

2021/11/11※PostgreSQL公式サイト Versioning Policyより (2022年6月現在)© 2022 SRA OSS, Inc. Japan

10PostgreSQLの歴史
1,500,000

JSON, レ
プリケー
ションス
ロット

ソースコードの行数

1,250,000

1,000,000

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

500,000

250,000

JITコンパ
イル

パラレル
クエリ

多CPUス
ケール

レプリ
ケーショ
ン

マテビュー,
更新
ビュー

BRIN, 行セキュリ
ティ,
UPSERT

Btree重複
排除

論理レプ
リケー
ション

?

テーブル
AM I/F

09.0
2010/99.1
2011/99.2
2012/99.3
2013/99.4
2014/129.5
2016/1

9.6
2016/9102017/10112018/10

122019/10

13
2020/9142021/9※ソースコードの行数はclocコマンドで取得© 2022 SRA OSS, Inc. Japan11

PostgreSQLの最新バージョン14• LZ4によるデータ圧縮• 同値項目を自動整理• HOTが効かないケースに有用• VACUUM処理を軽減■ REINDEX直後■ pgbench実行後
autovacuum = on
■ pgbench実行後
autovacuum = off20
15101540%12

検索時間 (秒)インデックスサイズ (MB)25

• 圧縮アルゴリズムを選択可能に

30%

9圧縮率

• Btreeインデックス肥大化防止

610%

300%
pglz

50
バージョン13バージョン14© 2022 SRA OSS, Inc. Japan

20%

lz4

pglz

lz4

ほかにも多数改善あり
12

RDBMS市場でのPostgreSQLの位置づけ• DB-Engines Rankingによる人気ランキングでは• Oracle、MySQL、SQL Serverに次ぐ第4位• ここ数年、高い伸び率を見せている• Oracleの移行先として• SE1/SEの販売終了、他社クラウドでの値上げもあって、Oracle離れが進んでいる• MySQLに比べると、機能が豊富で移行しやすく、システム構造が似ていて技術者の転換がしやすい• Oracle以外とは棲み分け• MySQLはシンプルなWebシステム向け• SQL ServerはWindows向け

Oracle
23%

MySQL
21%

その他
25%

PostgreSQL
11%

Db2
3%

SQL Server
17%※DB-Engines Ranking Relational DBMSより (2022年6月時点)© 2022 SRA OSS, Inc. Japan13

PostgreSQLの導入事例
国内の導入事例• NTT OSSセンタ• 社内の80%強のシステムにPostgreSQLが採用できると分析• システムリプレイスに応じて順次入れ替え• 中国電力• スマートメータのシステムに採用

海外の導入事例• CAIXA (ブラジル連邦貯蓄銀行)• フランス社会保障システム• Zalando• ヨーロッパ最大のオンライン服飾販
売サイト• 5TB、90台のデータベース© 2022 SRA OSS, Inc. Japan

14

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

15

PowerGres• PostgreSQLに安心のサポート
サービスと使いやすいGUI管理ツールを付属した製品• サポート期間は販売開始から7年• 別メニューで24時間365日サポートも提供• WebベースのGUI管理ツールでデータベースを簡単に操作• PowerGres PlusはPostgreSQLにない独自の機能追加• 15,000本以上ライセンスを出荷

PowerGresファミリーのラインアップ
PowerGres Plus

PowerGres HA

信頼性とセキュリティ

可用性

WAL二重化PowerGres
使いやすさと安心

PostgreSQL© 2022 SRA OSS, Inc. Japan

HAクラスタ
透過的データ暗号化

GUI
管理ツール

サポート
サービス

16

PowerGres Plusの透過的データ暗号化• 暗号化を意識する必要がなく、アプリケーションを修正しなくていい• 暗号化による性能への影響が小さく、とくにAES-NI対応のCPUではごくわずか、性能低下はOLTP性能では1%以下、ロード性能では10%以下
250

非暗号化
テーブル空間

データが見えてしまう

200
データが見えない

150
10050

アプリ
ケーション

0

PowerGres
Plus

OLTP性能 (100TPS)
暗号化
テーブル空間暗号化なし© 2022 SRA OSS, Inc. Japan

ロード性能 (秒)暗号化あり (AES256/AES-NI有効)
17

PostgreSQLの周辺ツール (1)• pgAdmin 4• GUI管理ツール• pgAdmin IIIの後継• Windows版PostgreSQLに付属• おもな機能• SQL実行機能• データグリッド表示・編集• 実行計画グラフィカル表示• アクセス権限対話型設定• 手続き言語PL/pgSQLデバッグ© 2022 SRA OSS, Inc. Japan18

PostgreSQLの周辺ツール (2)• pg_statsinfo/pg_stats_reporter • Barman• 統計情報を定期的に収集し、レポートを作成するツール• トラブル発生時の調査に役立つ• バックアップ/リカバリを管理するツール• バックアップの世代管理や圧縮、複数サーバの一元管理が行える• pgAudit• 監査ログを取得するツール• セッション/オブジェクト単位にログ取得を詳細に制御できる© 2022 SRA OSS, Inc. Japan19

PostgreSQLの一般的なクラスタ構成
1台では実現できない信頼性や性能を複数台で実現
共有ディスク構成

レプリケーション構成

パーティショニング構成• ディスク以外の障害にスタンバイが対応• 共有ディスクの代わりにディスクミラーリング、NASを使う場合あり• 例: Pacemaker, LifeKeeper• 参照を負荷分散し、縮退運転でサーバ障害に対応• 例: ストリーミングレプリ
ケーション, Pgpool-IIのネ
イティブレプリケーショ
ン, Slony-I• データを複数サーバに分割し、分散処理を行う• 例: Postgres-X2

共有
ディスク

データを複製© 2022 SRA OSS, Inc. Japan

2021年のデータ

2022年のデータ

20

PostgreSQL組み込みのレプリケーション機能
ストリーミングレプリケーション

論理レプリケーション• データの変更内容が記録されたWALをレコード単位で転送• WALからデコードされた論理的な変更内
容を転送• データベースクラスタ単位• 対象は更新を伴うすべての操作• 複製先は参照のみ可• 異バージョン/アーキテクチャ間は不可• テーブル単位• 対象はテーブルのINSERT/UPDATE/
DELETE/TRUNCATE• 複製先でも更新可• 異バージョン/アーキテクチャ間でも可論理的な変更内容

WAL
レコード
参照
/更新

プライマリ

参照
のみ

スタンバイ

参照
/更新

© 2022 SRA OSS, Inc. Japan

パブリ
ケーション

参照
/更新

サブスクリプション

21

PostgreSQLのクラスタ構成例• ストリーミングレプリケーションを利用• Pgpool-IIで負荷分散、自動フェイルオーバを実現• Watchdog機能でPgpool-II自体を冗長化
VIP: PostgreSQL

参照
/更新

参照
/更新

: Pgpool-II
プライマリ

参照
アクティブ

クライアント

相互監視

参照

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

スタンバイ

スタンバイ
© 2022 SRA OSS, Inc. Japan

22

参考情報
• Tech Blog (技術ブログ)• PostgreSQL 14検証レポートやリリース情報を公開中
sraoss tech blog検索• YouTubeチャンネル• 過去セミナーの動画を公開中• PostgreSQLの操作方法など、より技術的な説明を聞きたければ、「OSSDB Exam Silver技術解説セミナー」がお勧め
sraoss youtube

検索© 2022 SRA OSS, Inc. Japan

23

オープンソースとともに製品・サービスに関するお問い合わせ:sales@sraoss.co.jp© 2022 SRA OSS, Inc. Japan

03-5979-2701
24