PostgreSQL入門
2023年6月17日
OSC2023 Online/Hokkaido
SRA OSS LLC
佐藤 友章
sato@sraoss.co.jp© 2023 SRA OSS LLC

1

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

2

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

3

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

項目制限

データベースサイズ

制限なし

テーブルサイズ

32TB

行サイズ

400GB

フィールドサイズ

1GB

テーブルの行数

制限なし

テーブルの列数

250-1600
4

PostgreSQLのライセンス• The PostgreSQL License• 広告条項はなしの修正BSDライセンスと同様• 使用、複製、改変、配布の自由• 複製においては著作権表示、ライセンス条文、免責事項を含めることが条件• GPLと比較すると、派生物を再配布する際にソースコード公開、ライセンス継承の義務がない

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 writtenagreement is hereby granted, provided that the above copyright noticeand this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANYPARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, ORCONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OFTHE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE
UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OFSUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANYWARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIEDWARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULARPURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS"
BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TOPROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
MODIFICATIONS.

© 2023 SRA OSS LLC

5

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 (AWS)
Masahiko Sawada (AWS)⋮約40名など…

※PostgreSQL公式サイト Contributor Profiles、Sponsorsより (2023年5月現在)© 2023 SRA OSS LLC

6

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

日本PostgreSQLユーザ会 (JPUG)

ビジネスコミュニティ

PostgreSQLエンタープライズ・コンソーシアム (PGECons)• 1999年設立、2006年NPO法人化• 2012年設立• 全国各地に支部• 正会員企業17社、一般会員企業59
社 (2023年5月現在)• 普及活動を中心に、マニュアル日本語訳やカンファレンスを実施• エンタープライズ用途に向けた共同検証やプロモーション活動© 2023 SRA OSS LLC

7

PostgreSQLの生い立ち

1977～1985

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

Illustra1986～1994

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

1994～1995

Postgres95

Informix
1996～

PostgreSQLソースコードの見な
おしと改良。SQLに対応© 2023 SRA OSS LLC

8

PostgreSQLのバージョン番号• PostgreSQLのバージョンは6.0から始まる• POSTGRESがバージョン4.2まで、Postgres95をバージョン5と見なす• メジャーバージョンでは機能追加・仕様変更、マイナーバー
ジョンではバグ修正

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

© 2023 SRA OSS LLC

9

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

サポート可否

初リリース

最終リリース15○2022/10/13

2027/11/11

14○2021/9/30

2026/11/12

13○

2020/9/24

2025/11/13

12○

2019/10/3

2024/11/14

11○

2018/10/18

2023/11/910×2017/10/5

2022/11/10※PostgreSQL公式サイト Versioning Policyより (2023年5月現在)© 2023 SRA OSS LLC

10PostgreSQLの歴史
1,600,000

1,400,000

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

ソースコードの行数

1,200,000
1,000,000
800,000

MERGE

Btree重複排除

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

400,000
200,000

パラレル
クエリ

Btree改良
テーブル
AM I/F

多CPUス
ケール

レプリ
ケーショ
ン

JITコンパイル

BRIN, 行セキュリ
ティ,
UPSERT

マテ
ビュー,
更新
ビュー

論理レプ
リケー
ション

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

9.6
2016/9101112
2017/10 2018/10 2019/10

13
2020/9142021/9152022/10※ソースコードの行数はclocコマンドで取得© 2023 SRA OSS LLC

11

PostgreSQLの最新バージョン15
MERGE文対応

NOT IN句性能改善• 条件に応じてINSERT/UPDATE/DELETEを行うSQL文• 内部的にハッシュテーブルを使うように変更• いわゆるUPSERTを実現• 要素が大量の場合に効果あり
5,000要素をもつNOT IN句の場合
180150

時間 (ミリ秒)

MERGE INTO items AS i
USING (VALUES (1, 'foo'))AS i2 (id, name) ON i.id = i2.id
WHEN MATCHED THENUPDATE SET name = i2.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (i2.id, i2.name);

12090

1460

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

Oracle
22%

MySQL
21%

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

その他
26%

PostgreSQL
11%
SQL Server
17%※DB-Engines Ranking Relational DBMSより (2023年5月時点)© 2023 SRA OSS LLC

13

Db2
3%

PostgreSQLの導入事例
国内の導入事例

海外の導入事例• NTT OSSセンタ• CAIXA (ブラジル連邦貯蓄銀行)• 社内の80%強のシステムにPostgreSQLが採用できると分析• システムリプレイスに応じて順次入れ替え• 中国電力• スマートメータのシステムに採用• フランス社会保障システム• Zalando• ヨーロッパ最大のオンライン服飾販
売サイト• 5TB、90台のデータベース© 2023 SRA OSS LLC

14

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

15

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

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

PowerGres HA

信頼性とセキュリティ

可用性

WAL二重化PowerGres

使いやすさと安心

PostgreSQL• 15,000本以上ライセンス出荷© 2023 SRA OSS LLC

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

GUI
管理ツール

サポート
サービス

16

PowerGres Plusの透過的データ暗号化• 暗号化を意識する必要がなく、アプリケーション修正が不要• 暗号化による性能への影響がごくわずか
250
非暗号化
テーブル空間

データが見えてしまう

200

データが見えない

150
100
50

アプリ
ケーション

PowerGres
Plus

0
OLTP性能 (100TPS)

暗号化
テーブル空間暗号化なし© 2023 SRA OSS LLC

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

17

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

18

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

19

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

レプリケーション構成

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

共有
ディスクデータを複製© 2023 SRA OSS LLC

2021年のデータ

2022年のデータ

20

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

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

WAL
レコード参照/更新

プライマリ

参照
のみ

スタンバイ

参照
/更新

© 2023 SRA OSS LLC

パブリ
ケーション

参照
/更新

サブスク
リプション

21

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

参照
/更新

参照
/更新

: Pgpool-II
プライマリ

参照
アクティブ

クライアント

相互監視

参照

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

スタンバイ

スタンバイ

© 2023 SRA OSS LLC

22

参考情報
• Tech Blog (技術ブログ)• PostgreSQL 15検証レポートやリリース情報を公開中
sraoss tech blog

検索

• YouTubeチャンネル• 過去セミナーの動画を公開中• PostgreSQLの操作方法など、より技術的な説明を聞きたければ、「OSS-DB Exam Silver技術解説
セミナー」がお勧め
sraoss youtube

検索© 2023 SRA OSS LLC

23

www.sraoss.co.jp

sales@sraoss.co.jp© 2023 SRA OSS LLC

03-5979-2701
24