オープンソースカンファレンス2020大阪

PostgreSQL”超”入門
SRA OSS, Inc. 日本支社

OSS事業本部 技術部 PostgreSQL技術グループ
北山 貴広© 2020 SRA OSS, Inc. Japan1

目次• 自己紹介• PostgreSQLの概要• PostgreSQLへのマイグレーション概要© 2020 SRA OSS, Inc. Japan

2

自己紹介
• 北山 貴広 (きたやま たかひろ)• 2019年6月入社の新入社員（社会人経験31年目）• PostgreSQLとZabbix他種々のOSSサポート業務がメイン• データベーススキルを武器に金融公共系でシステム開発運用経験• 過去にSoftware Designでコラム「温故知新 ITむかしばなし」を連載© 2020 SRA OSS, Inc. Japan3

PostgreSQLの概要© 2020 SRA OSS, Inc. Japan

4

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. Japan5

PostgreSQLのライセンス• The PostgreSQL License

COPYRIGHTファイル• https://www.postgresql.org/about/license/• 広告条項が無い修正BSDライセンスに類似• 使用、複製、改変、配布の自由• 複製においては著作権表示、ライセンス条文、免責事項を含めることが条件• GPLと比較すると、派生物を再配布する際にソースコード公開、ライセンス継承の義務がない

PostgreSQL Database Management System(formerly known as Postgres, then as Postgres95)
Portions Copyright (c) 1996-2019, 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 FOR
DIRECT, 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 ADVISED OFTHE POSSIBILITY OF SUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OFMERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWAREPROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OFCALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

© 2020 SRA OSS, Inc. Japan

6

PostgreSQLの開発体制• 特定のオーナー企業をもたない方式• 単独の企業に独占されることなく、幅広い層の厚い開発体制

コントリビュータ
メジャー
コントリビュータ

Peter Eisentraut (2ndQuadrant)
Magnus Hagander (Redpill Linpro)
Tom Lane (Crunchy Data)
Bruce Momjian (EnterpriseDB)
Dave Page (EnterpriseDB)

支援企業

コアチーム

開発コミュニティ

Etsuro Fujita (NTT)Amit Langote (NTT)Fujii Masao (NTT Data)
Michael Paquier (VMWare)⋮30名など…
※PostgreSQLサイトより (2020年1月時点)© 2020 SRA OSS, Inc. Japan7

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

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

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

PostgreSQLの生い立ち
1977～1985

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

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

1994～1995

Postgres95

Informix
1996～

PostgreSQL

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

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

バージョン10以降

9.6.14メジャーバージョン

12.1

マイナーバージョン

メジャーバージョン

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

10

3,500,000

PostgreSQLの歴史

3,000,000

PostgreSQL各バージョンの行数とリリース日、主な追加機能

2,500,000
2,000,000

2011.92019.10
12が登場！

2012.92018.102016.92016.12017.102014.122013.92010.92008.2 2009.7
2006.122005.1 2005.11

1,500,000
1,000,000

BitmapScan

500,000
PITR

hot (fast)
UPDATE

4core
scale

many
core
scale

Replication

OLAP,
CTE

SSI,
FDW

JSONB

Mview,
UView

Parallel

JIT
compilelogicalRepli.

RLS,UPSERTTable
AM I/F© 2020 SRA OSS, Inc. Japan

08.0 8.1 8.2 8.3 8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6

1011

1211

PostgreSQLで出来ること／出来ないこと
• 機能面

• 性能面
–
–
–

多数のCPUコアによるOLTP読み書き性
能スケール（単純ベンチマーク条件）
パラレル問い合わせ／JITに対応（OLAPむけに）[×]更新でWALボトルネック有• 運用機能–SQL SQL:2011 機能をほぼサポート–

JSON対応での優越–

PostGIS による GIS対応–[×]マテビュー差分更新など• クラスタ構成–

論理・物理のレプリケーション–

PITRバックアップ

–

–

監査ログ

アクティブ＝スタンバイ構成–

参照負荷分散／シャーディング–

クラウドによるマネージドDB–

両方向論理レプリケーション–[×]共有ストレージを用いたアクティブ＝アクティブ構成（いわゆるRAC型）–[×]フラッシュバック／時系列© 2020 SRA OSS, Inc. Japan

12

PostgreSQLの導入事例
国内の導入事例• NTT OSSセンタ

海外の導入事例• CAIXA (ブラジル連邦貯蓄銀行)• PostgreSQLの開発まで手掛けるユーザ企業• 社内の80%強のシステムにPostgreSQLが採用できると分析• システムリプレイスに応じて順次入れ替え• 中国電力• ATMおよびオンラインシステムで採用• 開発者も抱える• フランス社会保障システム• 汎用機のシステムからリプレイス• Zalando• スマートメータのシステムに採用• PGConf.ASIA 2016で講演• ヨーロッパ最大のオンライン服飾販売サイト• 5TB、90台のデータベース

PGConf.ASIA 2016公開資料: http://www.pgconf.asia/JP/material/

Zalandoのオンライン通販サイト: https://www.zalando.de/© 2020 SRA OSS, Inc. Japan13

PostgreSQLの派生製品• ライセンス制約が少ないため、商用/OSS製品のベースとして広く利用されている• PostgreSQLサイトに記載のものだけで42 (開発終了も含む)• おもな派生製品• SRA OSSのPowerGres• EnterpriseDBのEDB Postgres AdvancedServer• AWSのAmazon Aurora / Redshift• 富士通のFUJITSU Enterprise Postgres• Microsoft (Citus Data)のCitus
PostgreSQL derived databases
https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases© 2020 SRA OSS, Inc. Japan14

クラウドでのPostgreSQL利用• マネージドサービスも増え、利用しやすくなってきている• Microsoft Azure Database for PostgreSQL• Amazon RDS for PostgreSQL• Google Cloud SQL for PostgreSQL• クラウドのメリット• 資材の調達が不要• 従量課金制なので使った分しか費用が発生しない• マネージドサービスの利点• 運用管理を任せられる• 耐障害性、可用性構成の作成を任せられる• スケールアップ/ダウンが容易© 2019 SRA OSS, Inc. Japan

15

RDBMS市場でのPostgreSQLの位置づけ• DB-Engines Rankingによる人気ランキングでは• Oracle、MySQL、MS SQL Serverに次いで第4位のスコア• スコアの伸びでは2017年から2年連続で第1位• Oracleの移行先として• SE1/SEの販売終了、他社クラウドでの値上げもあって、Oracle離れが進んでいる• MySQLに比べると、機能が豊富で移行しやすく、システム構造が似ていて技術者の転換がしやすい• Oracle以外とは棲み分け• MySQLはシンプルなWebシステム向け• Microsoft SQL ServerはWindows向け© 2020 SRA OSS, Inc. Japan※DB-Engines Rankingサイトより (2020年1月時点)
16PostgreSQLへのマイグレーション概要© 2020 SRA OSS, Inc. Japan17

ITシステムのコスト構造
Web層

アプリケーション層• ITシステムのコストはデータベースの選択がカギを握る• データベースの重要性は高い• データベースのコストが占める比率も高い• データベースにオープンソースを使ってコストを削減
データベース層© 2020 SRA OSS, Inc. Japan18

データベースのコスト• 商用データベースではライセンス費、サポート費は必須• オープンソースデータベース
ではライセンス費は基本的に不要、サポート費は必要に応じて選択できる• オープンソースデータベース
を使えばライセンス費を削減できる

サポート費
サポート費
ライセンス費
オープンソース
データベース
商用データベース© 2020 SRA OSS, Inc. Japan19

ベンダロックインのリスク• 特定のベンダ製品に依存するあまり、ほかの製品への乗り換えが難しくなり、ベンダの言いなりになってしまう状態• 社内標準としてデータベースデメリットを1つに絞っていると…
メリット• ノウハウがたまりやすい• まとめ買いで値引きしてもらえるかも• システムに適したデータベースがほかにあっても選べない• ベンダの都合で不要なバージョンアップを強いられたり、突然サポートを打ち切られたりする• システムに応じてオープンソースデータベースを使い分ければベンダロックインから抜け出せる© 2020 SRA OSS, Inc. Japan20

マイグレーション先に選択されるPostgreSQL
マイグレーション元の RDBMS

マイグレーション先の RDBMS

100%

100%

80%80%60%60%40%40%20%

20%

0%

0%
OracleSQL Server

DB2

PostgreSQL

MySQLFirebird

https://lets.postgresql.jp/documents/tutorial/UserSurvey/1© 2020 SRA OSS, Inc. Japan21

PostgreSQLが選択される理由

Oracleとは構造がけっこう似ている• エンジニアをOracleから転換しやすい
Oracleの構造

PostgreSQLの構造

SGA

共有メモリ
共有バッファ

REDOバッ
ファ

LGWR

CKPT

専用サーバプロセス

DBWR

MMAN

専用サーバプロセス

SMON

PSP0

専用サーバプロセス

PMON

⋮PGA

FSM

共有プール

データベース
バッファキャッシュ

マスタープロセス
バックエンドプロセス
バックエンドプロセス
バックエンドプロセスデータベース

VM

WALバッファwriterstats collector

loggerautovacuum
launchercheckpointerarchiver⋮wal writer

データベースクラスタ

表領域
データ
ファイル

データ
ファイル

REDOログファイル

各種
設定ファイルデータ
ベース© 2020 SRA OSS, Inc. Japan

データ
ベース

WAL
ファイル

各種
設定ファイル

22

Oracleからのマイグレーション• マイグレーションにコストがかかる場合• ストアドプロシージャを多用している場合• OCI (Oracle Call Interface) など、独自APIを使用している場合• 基本手順

方針策定

機械変換

手動補正

テスト

• 手動補正の過程で変換方針に追加すべきことが加わっていく• 一次手動補正 (意図の理解なし) の後に二次手動補正 (意図の理解あり)を行うか？

© 2020 SRA OSS, Inc. Japan23

OracleとのおもなSQL特性の違い• デフォルトでAUTOCOMMIT/明示トランザクション• DDLは暗黙にCOMMIT実行が含まれない• DDLはデフォルトでロックタ
イムアウトなし• トランザクション中のエラー
後にCOMMITしても、成功した命令も含めてロールバックされる=> UPDATE t1 SET val = 1 WHERE id = 10;
UPDATE 1=> BEGIN;
BEGIN=> DELETE FROM t1 WHERE id > 100;
DELETE 9900=> COMMIT;COMMIT=> BEGIN;
BEGIN=> DROP TABLE t1;
DROP TABLE=> ROLLBACK;ROLLBACK© 2020 SRA OSS, Inc. Japan24

OracleとのおもなSQLの違い• SELECTにFROMが不要 (DUAL)• VARCHAR2型、NUMBER型がな
い (NUMERIC型がある)• DATE型に時分秒が含まれない• NULLと'' (空文字列) に区別がある• VARCHAR(N) 型やLENGTH関数の長さが文字数単位• NVL関数がない• ROWNUM列がない

SELECT * FROM (SELECT * FROM tbl
ORDER BY c) WHERE rownum < 10;
SELECT * FROM tbl
ORDER BY c LIMIT 10;
SELECT NVL(c, 0)
FROM tbl;© 2020 SRA OSS, Inc. Japan

SELECT
CASE WHEN c IS NULL
THEN 0 ELSE c END
FROM tbl;

25

利用ツール: Ora2Pg• OralceからPostgreSQLへの移行ツール• http://ora2pg.darold.net/• Oracleに接続して、スキーマやデータを抽出し、PostgreSQLで読み込めるように変換• 移行時の設定を柔軟に指定できる• NUMERICの桁数に応じてSMALLINT、INTEGER、BIGINTを使い分け• 移行できない/移行が完璧でないオブジェクトも多い• 移行コスト見積もりのレポートも作成できる© 2020 SRA OSS, Inc. Japan26

利用ツール: orafce• Oracle互換の関数とパッケージ• https://github.com/orafce/orafce
算術関数

データ型書式設定関数

BITAND、COSH、SINH、TANH

TO_CHAR、TO_DATE、TO_MULTIBYTE、TO_NUMBER、
TO_SINGLE_BYTE

文字列関数

条件式

INSTR、LENGTH、LENGTHB、LPAD、LTRIM、NLSSORT、RPAD、RTRIM、SUBSTR、UBSTRB

DECODE、LNNVL、NANVL、NVL、NVL2

日付/時刻関数パッケージ

ADD_MONTHS、DBTIMEZONE、LAST_DAY、MONTHS_BETWEEN、NEXT_DAY、ROUND、SESSIONTIMEZONE、SYSDATE、TRUNC

DBMS_ALERT、DBMS_ASSERT、DBMS_OUTPUT、DBM
S_PIPE、DBMS_RANDOM、DBMS_UTILITY、UTL_FILEなど© 2020 SRA OSS, Inc. Japan27

参考ドキュメント

PostgreSQLエンタープライズ・コンソーシアム成果物• 成果物総索引: https://www.pgecons.org/works_index/• データベース移行• アセスメント、システム構成移行、異種DB連携、定義移行、データ移行、アプリケーション移行、移行評価、運用移行、チューニング、機能要件、非機能要件、サポートツール

データ型の移行© 2020 SRA OSS, Inc. Japan

ストアドプロシージャの移行
28

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

29