PostgreSQLエンタープライズ・コンソーシアム 技術部会 WG#2

WG2活動報告書
パラレル処理移⾏編

PostgreSQL Enterprise Consortium

⽬次
⽬次
1. 改訂履歴
2. ライセンス2. はじめに2.1. 本資料の⽬的
2.2. 本資料で記載する範囲
2.3. 本資料で扱う⽤語の定義
2.4. 本資料で扱うDBMSおよびツール
3. PostgreSQLのパラレル処理
3.1. OracleとPostgreSQLのパラレル処理について
3.2. パラレル処理の機能差
3.3. パラレル処理の指定⽅法
3.4. パラレル処理の優先度
4. パラレル処理の適⽤
4.1. パラレル指定⽅法の指針
4.2. 運⽤上の考慮点
5. パラレル処理まとめ
5.1. パラレル処理指定の適⽤⽅針案
6. 著者

Page 2 of 15

2456666677789121212141415

PostgreSQL Enterprise Consortium

Page 3 of 15PostgreSQL Enterprise Consortium

1. 改訂履歴版
1.0

改訂⽇

変更内容

2020/03/17 新規作成

Page 4 of 15

PostgreSQL Enterprise Consortium

2. ライセンス
本作品はCC-BYライセンスによって許諾されています。 ライセンスの内容を知りたい⽅は こちら でご確認ください。 ⽂書の内容、表記に関する誤り、ご要望、感想等につきましては、PGEConsのサイト を通じてお寄せいただきますようお願いいたします。
Eclipseは、Eclipse Foundation,Inc.の⽶国、およびその他の国における商標もしくは登録商標です。
IBMおよびDb2は、世界の多くの国で登録されたInternational Business Machines Corporationの商標です。
Intel、インテルおよびXeonは、⽶国およびその他の国における Intel Corporation の商標です。
Javaは、Oracle Corporation 及びその⼦会社、関連会社の⽶国及びその他の国における登録商標です。 ⽂中の社名、商品名等は各社の商標または登録商標である場合があります。
Linux は、Linus Torvalds ⽒の⽇本およびその他の国における登録商標または商標です。
Red HatおよびShadowman logoは、⽶国およびその他の国におけるRed Hat,Inc.の商標または登録商標です。
Microsoft、Windows Server、SQL Server、⽶国 Microsoft Corporationの⽶国及びその他の国における登録商標または商標です。
MySQLは、Oracle Corporation 及びその⼦会社、関連会社の⽶国及びその他の国における登録商標です。 ⽂中の社名、商品名等は各社の商標または登録商標である場合があります。
Oracleは、Oracle Corporation 及びその⼦会社、関連会社の⽶国及びその他の国における登録商標です。 ⽂中の社名、商品名等は各社の商標または登録商標である場合があります。
PostgreSQLは、PostgreSQL Community Association of Canadaのカナダにおける登録商標およびその他の国における商標です。
Windows は⽶国 Microsoft Corporation の⽶国およびその他の国における登録商標です。
TPC, TPC Benchmark,TPC-C, TPC-E, tpmC, TPC-H, QphHは⽶国Transaction Processing Performance Councilの商標です
その他、本資料に記載されている社名及び商品名はそれぞれ各社が 商標または登録商標として使⽤している場合があります 。

Page 5 of 15

PostgreSQL Enterprise Consortium

2. はじめに2.1. 本資料の⽬的
本資料は、以下の参考資料として利⽤されることを想定しています。
Oracle Enterprise Editionでパラレル処理を活⽤し性能要件を満たしている場合にPostgreSQLへデータベースを移⾏する場合
パラレル処理を有効活⽤し性能向上を⾏う場合

2.2. 本資料で記載する範囲
本資料では、パラレル処理におけるOracleとの機能差、PostgreSQLでの指定⽅法と考慮点を中⼼に記載します。パラレル指定時の性能⽐較については本資料では取り扱っておりません。性能⾯に関しては、「2017年度WG1活動報告書」、「2018年度WG1活動報告書」等を参照してください。

2.3. 本資料で扱う⽤語の定義
資料で記述する⽤語について以下に定義します。
表 2.1 ⽤語定義
No.

⽤語意味

1

DBMS

データベース管理システムを指します。

2

Oracleデータベース管理システムの Oracle Database を指します。

2.4. 本資料で扱うDBMSおよびツール
本書のパラレル処理の優先度、考慮点は以下のDBMSを前提にした調査結果を記載します。
表 2.2 本書で扱うDBMS
DBMS名称

バージョン

PostgreSQL 11.4

Page 6 of 15

PostgreSQL Enterprise Consortium

3. PostgreSQLのパラレル処理
3.1. OracleとPostgreSQLのパラレル処理について
PostgreSQLではバージョン9.6よりパラレルクエリがサポートされました。本書ではパラレルクエリに限定せず、パラレル化できる処理として扱います。そのため、表題等も”パラレル処理”としました。Oracleから移⾏を前提にした場合、Oracle Enterprise Editionではパラレル処理がサポートされており、パラレル処理を前提で性能を確保している場合もあります。PostgreSQLに移⾏した場合、パラレル化せずに性能要件を満たしていれば問題ありませんが、満たしていない場合はどのように対応すればよいか。また、パラレル処理がサポートされないOracle Standard
Editionからの移⾏では、PostgreSQLに移⾏することでパラレル処理の恩恵を受ける部分もあり、移⾏後のシステムの性能改善にも繋がります。
本書ではOracleとPostgreSQLのパラレル処理の機能差を⽰し、PostgreSQLでの運⽤における指定⽅法について説明します。

3.2. パラレル処理の機能差
PostgreSQLではバージョン9.6でパラレルクエリがサポートされる以前より⼀部機能についてはパラレル化が可能な処理もありました。ここではPostgreSQLのバージョン毎に単純にOracleとの機能差を⽰します。
パラレル処理の機能差
PostgreSQLとOracleのパラレル処理の機能差を以下表に⽰します。PostgreSQLの機能には⼀部周辺ツールも含みます。脚注も合わせて確認して下さい。
Oracle12c
SE

EE

9.5

9.6

101112－〇－〇〇〇〇Hash Join－〇〇〇〇

Nested Loop Join－〇〇〇〇

集約（Aggregation)

－〇〇〇〇

ヒント句－

〇*1〇*1〇*1〇*1IndexScan(B-tree)－－〇〇〇

Merge Join－－〇〇〇

Bitmap Heap Scan

－－〇〇〇

サブクエリ

－－〇〇〇

ソート－－〇〇〇

Parallel Hash Join*2－－－〇〇

パラレルアペンド－－－〇〇SELECT INTO

－－－〇

〇

分離レベルがSERIALIZABLE

－－－－

〇

外部表（PostgreSQLはFILE_FDW）

－－－－－

機能分類
クエリ

PostgreSQL

⽐較機能Seq ScanDML

INSERT,UPDATE,DELETE－〇－－－－－DDL

CREATE TABLE 〜 AS SELECT－〇－－－〇〇

CREATE MATERIALIZED VIEW－－－〇〇

CREATE INDEX(B-tree)－－－〇〇

ALTER INDEXまたはREINDEX－－－〇〇－－－－－

バックアップ（rman,PITR)

〇*3

〇*3

〇*3

〇*3

〇*3

論理バックアップ(DataPump,pg_dump)

〇*4

〇*4

〇*4

〇*4

〇*4

データロード

〇*5

〇*5

〇*5

〇*5

〇*5

その他

統計取得（DBMS_STATS,ANALYZE）－〇〇：機能あり、－:機能なしまたは対象外*1:pg_hint_plan 1.2以降を使⽤*2:ハッシュ作成もパラレルで実⾏される。バージョン10までのハッシュ結合は結合する2つのテーブルの1つのみパラレルスキャンとなる*3:Barman 2.2以降を使⽤（ただしパラレル化には複数のBarmanサーバが必要）

Page 7 of 15

PostgreSQL Enterprise Consortium*4:pg_dumpはディレクトリ形式で複数のテーブルを処理する場合、pg_restoreはディレクトリ形式およびカスタム形式が対象*5:pg_bulkload 3.1以降を使⽤

3.3. パラレル処理の指定⽅法
1. パラレル処理の指定箇所
以下はOracle,PostgreSQLでパラレル処理の指定箇所を表にしたものです。3.2.パラレル処理の機能差の表の”その他”は除きます（それぞれのユーティリティでパラレル度を指定します）。Oracleに関してはEnterprise Editionのみとなります。移⾏を考慮した場合、移⾏元のパラレル処理の具体的な指定を踏襲するのではなく、”どのような指針で計画されているか”を分析し、新たに指針として計画することを推奨します。多くの場合、数年以上前のハードウェアで稼動するデータベースを最新のハードウェアで稼動させるだけで性能は向上します。以前はパラレル指定が必要であったケースでも不要となる場合もあるからです。
RDBMS
Oralce (EEのみ)

パラレル処理の指定箇所

説明

初期化パラメータ

⾃動、⼿動含め様々なワークロードに対応

リソースマネージャ
表・索引

パラレル実⾏の有無、パラレル度の指定

セッション単位
ヒント句
PostgeSQL

postgresql.conf（パラメータ）

パラレル度の制限、実⾏計画の調整要素を指定

表

parallel_workersで制限（テーブルサイズ依存なし）

ロール

set句でpostgresql.confのパラメータを上書き

セッション

set⽂でpostgresql.confのパラメータを上書き

ヒント句(pg_hint_plan 1.2.0〜)

ヒント句でパラレル度を指定

2. パラメータの指定
PostgreSQLのパラレル処理関連で指定できるパラメータについて、運⽤上考慮すべきものについて以下表に⽰します。デフォルト値はバージョンにより異なる場合があるので該当するバージョンのマニュアルを参照してください。
表 3.1 パラメータの指定値
パラメータ

指定可能バージョン

説明

max_worker_processes

全て・インスタンス内のバックグラウンドプロセスの最⼤値。サーバ内でデータベースインスタンスに割り当てるCPUコア数を指定する。このパラメータはパラレル動作させるためのパラメータではなくパラレルクエリで使うCPUコアを制限するものと考えてよい。
・ロール、セッション指定でも上書きすることはできない。

max_parallel_workers

9.6以降

・インスタンス内のパラレルクエリ操作⽤のワーカー数の最⼤値。
max_worker_processes以下を指定する。
・ロール、セッション指定で上書きできてしまうため、インスタンスでのパラレルクエリで使うCPUコアの制限値とすることはできない。

max_parallel_workers_per_gather

9.6以降

GatherまたはGather Mergeノードに対して起動できるワーカー数の最⼤値。
SQL内での１つの処理で動作するパラレル度と考えた⽅が分かりやすい。複数ユーザが接続し複数のSQLが同時に動作する場合などmax_parallel_workersの限界に達しパラレル実⾏できないケースもある。

max_parallel_maintenance_workers

11以降

create index,reindexなどメンテナンス処理で使⽤するワーカー数の最⼤値。

min_parallel_index_scan_size

10以降

パラレルスキャンが考慮されるために、スキャンされなければならないインデッ
クスデータの最⼩量。インデックスサイズのではないことに注意。

min_parallel_table_scan_size

10以降

パラレルスキャンを考慮する最⼩のテーブルデータのサイズ。SEQ SCANの場合はテーブルサイズと同じです。

min_parallel_relation_size

9.6

min_parallel_table_scan_sizeと役割は同じ。

3. 表、ロール、セッション、pg_dump、pg_restoreでのパラレル度指定⽅法例
表、ロール、セッションでのパラレル度の指定⽅法について例⽂で⽰します。ロール、セッションではすべてのパラメータを上書きできるわけではありません。

Page 8 of 15

PostgreSQL Enterprise Consortium

表での指定⽅法
pgbench_accounts表のパラレル度を3にする。
alter table pgbench_accounts set ( parallel_workers = 3 );ロールでの指定例
postgresロールのmax_parallel_workers_per_gatherを4に上書きする。
alter role postgres set max_parallel_workers_per_gather = 4;
セッションでの指定例
当セッションのmax_parallel_workers_per_gatherを4に上書きする。
set max_parallel_workers_per_gather = 4;
ヒント句での指定例
pgbench_accounts表に対してのパラレル度を4にする。
/*+ Parallel(pgbench_accounts 4) */select count(*) from pgbench_accounts;
pg_dumpでの指定例
test01データベースを/home/postgres/backup/test01_dディレクトリに-jオプションを使⽤して4並列（4つのテーブルを同時）でダンプする。
pg_dump -Fd -v -j 4 -f /home/postgres/backup/test01_d test01
pg_restoreでの指定例/home/postgres/backup/test01_dのバックアップをtest01データベースに-jオプションを使⽤して4並列（4つのテーブルを同時）でリストアする。
pg_restore -c -v -j 4 -d test01 /home/postgres/backup/test01_d

3.4. パラレル処理の優先度
運⽤では”postgresql.confのパラメータで指定し、かつ特定ロールでパラメータを上書き”のように複数の箇所でパラレル度が指定されるケースがあります。その場合、指定したパラレル度は以下の４つのルールで優先度が決まりで実⾏計画が作成されます。実⾏に関してはmax_worker_processes等の制限を受け、実⾏計画通りにパラレル化されない場合もあります。
①基本的な指定優先度
上位の優先度が下位の優先度のパラレル度を拡張または制限できます。
ヒント句＞セッション指定

＞

ロール指定

＞

postgresql.conf②テーブルのパラレル度を指定した場合テーブルのパラレル度を指定した場合は他の指定に対し、より制限される（低い）パラレル度が適⽤されます。

例１ セッション指定のパラレル度よりテーブルのパラレル指定が低いケース
テーブルのパラレル度：3、セッション指定のパラレル度：4のときテーブルのパラレル度で計画される。

Page 9 of 15

PostgreSQL Enterprise Consortium

test01=# alter table pgbench_accounts set ( parallel_workers = 3 );ALTER TABLE
test01=# set max_parallel_workers_per_gather = 4;
SETtest01=# explain select count(*) from pgbench_accounts;
QUERY PLAN-----------------------------------------------------------------------------------------------------Finalize Aggregate (cost=409514.48..409514.49 rows=1 width=8)-> Gather (cost=409514.16..409514.47 rows=3 width=8)
Workers Planned: 3-> Partial Aggregate (cost=408514.16..408514.17 rows=1 width=8)-> Parallel Seq Scan on pgbench_accounts (cost=0.00..392385.13 rows=6451613 width=0)(5 ⾏)
例２ テーブルのパラレル指定よセッション指定のパラレル度が低いケース
テーブルのパラレル度：6、セッション指定のパラレル度：4のときセッション指定のパラレル度で計画される。
test01=# alter table pgbench_accounts set ( parallel_workers = 6 );ALTER TABLE
test01=# set max_parallel_workers_per_gather = 4;
SET
test01=# explain select count(*) from pgbench_accounts;
QUERY PLAN-----------------------------------------------------------------------------------------------------Finalize Aggregate (cost=391369.42..391369.43 rows=1 width=8)-> Gather (cost=391369.00..391369.41 rows=4 width=8)
Workers Planned: 4-> Partial Aggregate (cost=390369.00..390369.01 rows=1 width=8)-> Parallel Seq Scan on pgbench_accounts (cost=0.00..377869.00 rows=5000000 width=0)(5 ⾏)③テーブルのパラレル度を指定しない場合
テーブルのパラレル度を指定しない場合はテーブルのサイズによりパラレル度が制限が決定され、他の指定に対し、より制限される（低い）パラレル度が適⽤されます。
以下の図は 「PGECons 勉強会#2 パラレルクエリ」 からの抜粋になります。

図 3.1 テーブルサイズとパラレル度④パラレル処理の⽅が早いと想定される実⾏計画のとき

Page 10 of 15

PostgreSQL Enterprise Consortium

パラレルでない実⾏計画よりパラレル実⾏計画のコスト推定値が低い場合にパラレル実⾏が計画されます。

Page 11 of 15

PostgreSQL Enterprise Consortium

4. パラレル処理の適⽤
4.1. パラレル指定⽅法の指針
データベースが搭載されるサーバは複数のCPUコアを搭載し運⽤される場合がほとんどです。そして、運⽤されるシステム内でも性能要求が”⾼い処理”と”低い処理”があります。性能要求が”⾼い処理”であっても⼩さなデータ量であれば１つのCPUコアで⼗分に性能要件を満たすこともできます。パラレル処理は性能要求が”⾼い処理”でかつデータ量が多い場合に要件を達成させるための⼿法のひとつとなります。パラレル指定⽅法の指針は、限りあるCPUリソースの中で、必要なときに必要な処理には必要なCPUを割り当てられるようにすることです。以下にいくつかのCPU割当の基準を⽰します。実際の運⽤設計にあたっては、これらを複合的に組合わせて適切なワークロードとなるようにします。
パラレル処理の全体のCPUを制限する
データベースサーバにデータベース以外のサービスが搭載される場合、または複数のデータベースインスタンスを起動させる必要がある場合など対象のデータベースサービスだけでサーバCPUを使い切らないように制限する必要があります。具体的にはpostgresql.confでパラメータのmax_worker_processesを指定します。ただし、このパラメータの変更はインスタンスの再起動が必要となるため、運⽤スケジュールによるワークロード変更が必要な場合かつ運⽤上、再起動をしたくない場合はmax_parallel_workersで指定します（このパラメータはロールやセッションで上書き可能なため、運⽤ルールとして上書きしないようにするなど考慮が必要）。
ロールのSQLで使⽤するCPUを制限する
複数のロール（ユーザ）を使⽤する場合、ロールによっては⾼い性能要求が必要ないものもあるかもしれません。そのようなロールでのSQL実⾏にはCPUの割当を制限します。
オンライン処理で同時実⾏トランザクションが多く、パラレル化でCPUが枯渇するようであれば、その接続ロールではパラレル処理が動作しないようにします。
セッション指定でCPUを確保する
特定のバッチ処理などpostgresql.confの指定より⼗分なCPUを使⽤して性能を確保したい場合もあります。この場合はセッションで⼗分なCPUを割り当てられるようにします。ただし運⽤全体でCPUを枯渇させない考慮を合わせて⾏います。
ヒント句でCPUを確保する
セッション指定と同じ意図ですが、よりピンポイントに指定する⽅法です。
⼀定サイズ以上のテーブル以外はパラレル処理が動作しないようにする
要求性能を満たせる場合はmin_parallel_table_scan_sizeやmin_parallel_index_scan_sizeを指定してパラレル処理が動作しないようにします。運⽤上、効果の低い処理にCPUを使⽤するより必要な処理にCPUを確保可能とするための処置です。

4.2. 運⽤上の考慮点
PostgreSQLはパラレルクエリ以外にも3.2 パラレル処理の機能差の表の”その他”で⽰したように個別のユーティリティでもパラレル処理を実装しているものがあります。この場合、それぞれのユーティリティで指定したパラレル度以外でプロセスを使⽤する場合もあるので設計には注意が必要です。
pg_restoreでの例
pg_restoreではアーカイブ形式がカスタム形式、ディレクトリ形式の場合にパラレル処理で復元できるとなっております。実際には以下の表のように、リストア中のそれぞれの処理でプロセスが起動します。
表 4.1 pg_restoreで発⽣するプロセス
リストアでの処理

pg_restore*1

コピー処理

複数の表を同時にコピー索引の作成

複数の索引を同時に処理

パラレルクエリの機能*2

⾃動バキュームの機能*3

１つの索引を複数のプロセスで処理

表のANALYZE

複数の表を同時にANALYZE*1:pg_restoreの-jパラメータで指定するジョブ数*2:max_parallel_maintenance_workers の指定値
*3:autovacuum_max_workersの指定値
実際に以下に例を⽰します。

Page 12 of 15

PostgreSQL Enterprise Consortium

実⾏コマンド前提として、max_worker_processes=8,max_parallel_maintenance_workers=4, autovacuum_max_workers=2とします。
$ pg_restore -c -j 4 -d test01 backup/test01.dmp
処理起動後の前半
4並列でコピーが実⾏され、終わったものはANALYZEが開始されています。

図 4.1 pg_restore処理前半のプロセス
処理起動後の後半
索引作成がパラレルで動作している。この後、索引の作成が完了すればpg_restoreはANLYZEを待たずに完了する。（ただし、リストアした全てテーブルに対しANLYZEは継続して続⾏される。並列度か⾼く、リソースが枯渇しなければその分早く完了する）

図 4.2 pg_restore処理後半のプロセス

Page 13 of 15

PostgreSQL Enterprise Consortium

5. パラレル処理まとめ
5.1. パラレル処理指定の適⽤⽅針案
まとめとしてPostgreSQLにおけるパラレル処理指定の指針と考え⽅の案を表に⽰します。中規模であっても１つのテーブルで数百GBあるような場合はテーブルのパーティショニング化の考慮も必要でしょう。また、同じく中規模でも⼩さなテーブルばかりで性能要求が低いのであれば運⽤コスト低減のために”指針なし”もあり得ます。運⽤要件によって最適な指針は変わると考えますので参考程度としてして下さい。
表 5.1 パラレル処理指定の適⽤⽅針案
DB規模

前提条件

指針

⼩規模

・DBサイズは50GB未満
・DBに割り当てられるCPU(コア)が4以
下・接続ユーザ数は10未満
・性能要件は規定されない

指針なし。postgresql.confのデフォル
トのまま・管理コストは減らす（管理しない）

中規模

・DBサイズは50GB〜1TB
・DBに割り当てられるCPU(コア)が32以
下・接続ユーザ数は50未満
・性能要件が規定されるパラレル化⽅式を規定し、ワークロードに合せて指定・優先度の⾼い処理の性能を確保

⼤規模・DBサイズは1TB以上・DBに割り当てられるCPU(コア)が潤沢
・接続ユーザ数は50以上
・性能要件が規定される中規模と同様＋必要に応じてパーティショニング含め性能確保を検討

・優先度の⾼い処理の性能を確保・パラレル化のみでは性能を確保できない可能性あり。運⽤が成⽴するための追加⽅式を検討

Page 14 of 15

考え⽅PostgreSQL Enterprise Consortium

6. 著者（企業・団体名順）
版
第1.0版(2019年度WG2)

所属企業・団体名
富⼠通エフ・アイ・ピー株式会社

部署名
ビジネス推進本部

Page 15 of 15

⽒名
多⽥ 明弘