OSS-DB Exam Silver
技術解説無料セミナー
2020/10/17 開催

主題

開発/SQL （出題範囲

32%）

副題

トランザクションの概念 【重要度︓１】
SQLコマンド 【重要度︓13】

本⽇の講師

NTT テクノクロス株式会社
上原 ⼀樹© LPI-Japan / EDUCO all rights reserved.

OSS-DB／オープンソースデータベース技術者認定試験#OSS-DBnOSS-DBとは
オープンソースのデータベースソフトウェア「PostgreSQL」を扱うことができる技術⼒の認定です。様々な分野でPostgreSQLの利⽤拡⼤が進む中でOSS-DBの認定を持つことは、⾃分のキャリアのアピールにもつながります。

üOSS-DB Goldは設計やコンサルティングができる技術⼒の証明

PostgreSQLについての深い知識を持ち、データベースの設計や開発のほか、パフォーマン
スチューニングやトラブルシューティングまで⾏えることが証明できますüOSS-DB Silverは導⼊や運⽤ができる技術⼒の証明

PostgreSQLについての基本的な知識を持ち、データベースの運⽤管理が⾏えるエンジニアとしての証明ができますü対象のバージョンはPostgreSQL 11© LPI-Japan all rights reserved.

2

OSS-DB 技術者認定はなぜ必要か︖#OSS-DBn⼀般論を学んだだけでは現場で活躍することは難しい 使う前に設定が必要
デフォルトの状態では、業務要件に対応することは難しい。
- ユーザ- アクセス権- テーブルの作成- プログラムの開発

 重要な⽤途 複雑な⽤途- 基幹業務での利⽤に向けた設計- バックアップ- パフォーマンスチューニング
- トラブルシューティング- セキュリティ

認定試験を通して、実際の運⽤に則した技術、知識を習得できる© LPI-Japan all rights reserved.

3認定試験への傾向と対策#OSS-DBn公式ドキュメントは使⽤するバージョンのドキュメントを読むべき バージョンによって、機能の違いがあるため、採⽤するバージョンとあったものを読む。
 本セミナー資料でも参考として、バージョン11のPostgreSQL⽂書のリンクを記載する。
nGUCパラメータやシステムテーブル・ビューは、単純に意味を覚えるのではなく、影響まで理解しなければならない
例 deadlock_timeout
意味:

ロック状態になった時にデッドロック検出処理を開始するまでの
待機時間

影響:

値を⼩さくすると、デッドロックの検出は早くなるが、 実際には
デッドロックが発⽣していないのに検出処理が 動くことが多くなるため、CPUに無駄な負荷がかかる可能性も⾼くなる

n実機での動作確認は極めて重要 ⾃分が予想した通りに動作しなければ、何かしら理解不⾜があるということ。
© LPI-Japan all rights reserved.

4

本セミナーについて#OSS-DBnテーマ
 本⽇は過去に実施されたオンラインセミナーでは扱っていない分野について解説する。
 過去のオンラインセミナーの内容について- どちらも試験対策としてだけではなく、PostgreSQLを運⽤する上で重要な項⽬です。
- 7⽉︓「PostgreSQLのバックアップ⽅法」– https://oss-db.jp/__/download/5f0fb75b37471d78510554e4/20200719-silver-01.pdf- 9⽉︓「VACUUM、ANALYZEの⽬的と使い⽅」と「⾃動バキュームの概念と動作」– https://oss-db.jp/__/download/5f3de5a37ea72b5fa4113218/20200905-silver-01.pdf

n本セミナーの進め⽅について
 資料での説明に加え、デモを交えながら解説する。
 実⾏するコマンドは資料の内容を参照。

© LPI-Japan all rights reserved.

5

本⽇のテーマ#OSS-DB

データベースの導⼊⼿順や設定等の基本的な情報に加え、OSS-DB Silver(ver.2.0)で試験範囲として追加されたマテリアライズドビュー、宣⾔的パーティショニング等の機能を解説する。
n運⽤管理（52%）
 インストール⽅法 【重要度︓2】- initdb

 設定ファイル 【重要度︓5】- postgresql.conf- pg_hba.conf- pg_settingsビュー

n開発/SQL (32%)
 トランザクションの概念 【重要度︓1】- トランザクション分離レベル- デッドロック SQL コマンド 【重要度︓13】- マテリアライズドビュー- 宣⾔的パーティショニング© LPI-Japan all rights reserved.

6

事前準備︓環境構築#OSS-DBnVMを⽤意する CentOS 8[root@ossdb ~]# cat /etc/redhat-release
CentOS Linux release 8.2.2004 (Core)nPostgreSQLのインストール
 リポジトリ登録[root@ossdb ~]# rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-8x86_64/pgdg-redhat-repo-latest.noarch.rpm

 PostgreSQL 11のインストール[root@ossdb ~]# dnf -qy module disable postgresql[root@ossdb ~]# dnf install -y postgresql11-server postgresql11-devel※CentOS 7では、dnfコマンドの代わりにyumコマンドを使⽤する。なお、moduleはCentOS 8でのパッケージ管理の概念として新たに追加されたものである。

© LPI-Japan all rights reserved.

7

事前準備︓環境構築#OSS-DBnOSユーザを切り替える postgresユーザはRPMを⽤いたインストールで⾃動で作成される。
[root@ossdb ~]# su - postgres

n環境変数の設定
 postgresユーザの .bash_profile は、~/.pgsql_profile を読み込む仕様になっている。
[postgres@ossdb ~]$ cat << EOF > ~/.pgsql_profile
PATH=/usr/pgsql-11/bin:$PATH
PGDATA=/var/lib/pgsql/local/11/dataexport PATH PGDATA
EOF

 再ログインまたは、sourceコマンドで環境変数を反映する。
[postgres@ossdb ~]$ source ~/.pgsql_profile© LPI-Japan all rights reserved.

8

運⽤管理編#OSS-DB

n運⽤管理（52%）
 インストール⽅法 【重要度︓2】- initdb
 設定ファイル 【重要度︓5】- postgresql.conf- pg_hba.conf- pg_settingsビュー

n開発/SQL (32%)
 トランザクションの概念 【重要度︓1】- トランザクション分離レベル- デッドロック SQL コマンド 【重要度︓13】- マテリアライズドビュー- 宣⾔的パーティショニング© LPI-Japan all rights reserved.

9データベースクラスタの作成（initdb）#OSS-DBnデータベースクラスタ（DBクラスタ）を作成する[postgres@ossdb ~]$ initdb -D ~/local/11/data --no-locale --encoding=utf8

 DBクラスタとは- データベースの格納領域を指し、⼀つのインスタンスによって管理される。
- データベースの集合体であり、デフォルトでは「postgres」、「template0」、「template1」という3つのデータベースが作成される。
- ファイルシステムとしては、DBクラスタは全てのデータが格納される1つのディレクトリとなる。

nDBクラスタは、pg_ctlコマンドでも作成可能[postgres@ossdb ~]$ pg_ctl initdb -D ~/local/11/data
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/creating-cluster.html
https://www.postgresql.jp/document/11/html/app-pg-ctl.html© LPI-Japan all rights reserved.

10データベースクラスタの作成（initdb）の補⾜#OSS-DBn-Dオプションは省略可能
 環境変数PGDATAを定義すれば、-Dオプションを省略できる。
[postgres@ossdb ~]$ initdb --no-locale --encoding=utf8

 環境変数が定義済みであれば、pg_ctlコマンド等、他でも同様に-Dオプションを省略可能となる。

nデフォルトで作成されるデータベースについて
 template0、template1は、テンプレートデータベースと呼ばれており、CREATE DATABASE実⾏時のベースとなる特別なデータベースである。
 CREATE DATABASE実⾏時、デフォルトでは、template1が新しいデータベースのコピー元となる。
- データベース作成時にオプションで任意のデータベースをテンプレートとして指定することも可能。
 template0は削除不可、オブジェクトの追加もできない。
ninitdbを実⾏したOSユーザがデータベースの管理者（スーパーユーザ）となる
 ただし、以下のように-Uオプションを指定した場合は指定したユーザ名が管理者となる。
[postgres@ossdb ~]$ initdb -D ~/local/11/data --no-locale --encoding=utf8 -U admin© LPI-Japan all rights reserved.

11pg_hba.confによる認証⽅式の設定#OSS-DB

nPostgreSQLでは、クライアント認証を $PGDATA/pg_hba.conf で管理する
 フォーマットは以下のとおり。
local
host
hostssl
hostnossl
host
hostssl
hostnossl

database
database
database
database
database
database
database

user
user
user
user
user
user
user

auth-method [auth-options]address auth-method [auth-options]address auth-method [auth-options]address auth-method [auth-options]
IP-address IP-mask auth-method [auth-options]
IP-address IP-mask auth-method [auth-options]
IP-address IP-mask auth-method [auth-options] 設定ファイルでは、接続先DB（どこに）、接続するDBMSユーザ（誰が）、接続元（どこから）に対してDBへの接続の許可/拒否を定義する。
- 書かれていない内容の接続は全て拒否される。
- テーブル単位での制御はできない。
– データベース内の個別テーブルの制御はGRANT⽂、REVOKE⽂を使⽤する。
- databaseに「replication」を指定した場合は物理レプリケーション接続の制御として解釈される。
 デフォルトではローカル接続が許可された状態となっているので、動作確認程度であれば変更不要。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/auth-pg-hba-conf.html© LPI-Japan all rights reserved.

12

PostgreSQLの起動/停⽌（pg_ctl）#OSS-DBn起動 環境変数PGDATAが定義済みであれば、-Dオプションは省略可能。
[postgres@ossdb ~]$ pg_ctl -D ~/local/11/data start

n停⽌[postgres@ossdb ~]$ pg_ctl -D ~/local/11/data stop

 停⽌には以下の3つのモードが存在する。-mオプションで指定可能。
パラメータ名説明

smart

全てのクライアントが切断し、オンラインバックアップ実⾏中の場合はそれが終了するまで待つ。

fast（デフォルト）

クライアントが切断するまで待たず、実⾏中のオンラインバックアップを終了させる。
全ての実⾏中のトランザクションをロールバックし、サーバを停⽌する。

immediateクリーンアップ処理なしで、全てのサーバプロセスを即座に中断する。
次回起動時にクラッシュリカバリが⾏われる。

参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/app-pg-ctl.html© LPI-Japan all rights reserved.

13

設定ファイル（postgresql.conf）の設定#OSS-DB

nコメントアウトされている場合はデフォルト値が採⽤される
 同じパラメータを複数設定した場合、ファイルの後ろに定義されたものが最終的に反映される。
n設定項⽬の詳細はpg_settingsで確認可能 パラメータの値が反映の条件や変更可能なDBMSユーザはそれぞれ異なるので注意が必要。
- 反映の条件（データベースサーバ起動時/reload実⾏時等）はcontextで確認可能。
postgres=# SELECT name,setting,context FROM pg_settings WHERE name = 'work_mem';name| setting | context----------+---------+--------contextの⼀部を紹介する。
work_mem | 4096
| userpostmaster︓起動時に反映(1 row)sighup︓pg_ctl reload時に反映
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/view-pg-settings.html

user︓任意のユーザが変更可能...等々、詳細はドキュメントを参照。

nSET⽂を⽤いた設定⽅法
 postgresql.confに設定した場合、DBクラスタ全体に反映されてしまう。全体だと影響が⼤きくなりすぎるパラメータや、特定の処理にだけ適⽤させたい設定については、SET⽂を使⽤して、影響範囲を絞る。
postgres=# SET work_mem TO '64MB';© LPI-Japan all rights reserved.

14pg_settingsビューの補⾜#OSS-DB

npg_settingsビューはSHOW⽂、SET⽂の代わりとして使⽤可能
 パラメータを参照することはもちろん、設定内容を更新することができる。
- 更新の有効範囲はSET⽂と同様で、実⾏したセッションでのみ有効。
 なお、INSERT、DELETEは実⾏できない。

npg_settingsを⽤いた設定⽅法（SET⽂相当）
postgres=# UPDATE pg_settings SET setting = '64MB' WHERE name = 'work_mem';
set_config-----------64MB(1 row)© LPI-Japan all rights reserved.

15

設定ファイル（postgresql.conf）の設定の補⾜#OSS-DB

n動作確認等の環境で利⽤頻度の⾼いパラメータの⼀部をご紹介
 対象のパラメータ及び、デフォルトでの設定内容は以下のとおり。
#listen_addresses = 'localhost'#port = 5432
#shared_buffers = 128MB
logging_collector = on#log_checkpoints = off#log_lock_waits = off#log_autovacuum_min_duration = -1

 パラメータ概要
パラメータ名概要

listen_addresses

PostgreSQLが接続を待ち受けるIPアドレスを設定する。「*」で全てのIPアドレスを受け付ける。

port

PostgreSQLが使⽤するport番号を設定する。複数DBクラスタを起動する場合等に使⽤する。

shared_buffers

PostgreSQLの処理で主に使⽤されるメモリ量を設定する。

logging_collector

サーバログの出⼒を制御。10以前はデフォルトが無効となっていた。

log_checkpoints

チェックポイント処理をサーバログに出⼒する。解析に使⽤する。デフォルトでは無効。

log_lock_waits

ロック待ち情報をサーバログに出⼒する。解析に使⽤する。デフォルトでは無効。

log_autovacuum_min_duration

autovacuum処理をサーバログに出⼒する。解析に使⽤する。デフォルトでは「-1（無効）」。
© LPI-Japan all rights reserved.

16

サンプル問題（インストール⽅法）#OSS-DBnOSS-DB Silver サンプル問題/例題解説 から本テーマに関するものをいくつか紹介します。
 本セミナーでは1題のみ、時間を取って実施していただき、その後解説を⾏います。
- 残りの問題は⾃習でご活⽤ください。
 時間︓2分間© LPI-Japan all rights reserved.

17

サンプル問題

1.60#OSS-DBnホストdbserver(192.168.1.11)で稼働しているデータベースに、クライアント
dbclient(192.168.1.12)上のユーザtest1がpsqlを使って接続する。pg_hba.confの内容が次の通りであるとして、以下の記述から適切なものを2つ選びなさい。
local all all peer
host test all 192.168.1.12/32 trust
host all all 192.168.1.0/24 md5

A)
B)
C)
D)
E)

test1というデータベースにtest1というユーザで接続するときは、パスワードなしで認証される。
testというデータベースにtest1というユーザで接続するときは、パスワードなしで認証される。
test1というデータベースにtestというユーザで接続するときは、パスワードで認証する。
testというデータベースにtestというユーザで接続するときは、パスワードで認証する。
test2というデータベースにtest1というユーザで接続するときは、接続を拒絶される。

※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元
 https://oss-db.jp/sample/silver_management_03/60_160125© LPI-Japan all rights reserved.

18

サンプル問題

1.73#OSS-DBnテンプレートデータベースに関する説明として誤っているものを１つ選びなさい。
A) CREATE DATABASEを実⾏すると、内部的にはテンプレートデータベースの1つがコピーされる。
B) 使⽤するテンプレートデータベースを指定しなければ、デフォルトではtemplate1が使⽤される。
C) デフォルトでは、データベースクラスタ内に2つのテンプレートデータベースが存在している。
D) データベースクラスタ内に作成できるテンプレートデータベースの数は最⼤で5個までである。
E) データベースがテンプレートデータベースかどうかを設定するフラグがあり、そのフラグが設定されているデー
タベースは削除(DROP DATABASE)できない。
※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元
 https://oss-db.jp/sample/silver_management_04/73_170522© LPI-Japan all rights reserved.

19

サンプル問題

1.21#OSS-DB

npostgresql.conf ファイルの設定⽅法について、最も適切なものを2つ選びなさい。
A) 論理値型のパラメータは true あるいは false と⼩⽂字で設定しなければならないB) メモリを設定するパラメータでは kB, MB, GB, TB の単位が指定できる
C) 時間を設定するパラメータは、すべて秒単位で指定するD) ⾏頭が # の⾏はコメント⾏である
E) パラメータの設定はparam1 = val1;のようにセミコロンで終了し、セミコロンがなければ次⾏に継続する※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元
 https://oss-db.jp/sample/silver_management_02/21_121023© LPI-Japan all rights reserved.

20

開発/SQL編（トランザクションの概念）#OSS-DB

n運⽤管理（52%）
 インストール⽅法 【重要度︓2】- initdb
 設定ファイル 【重要度︓5】- postgresql.conf- pg_hba.conf- pg_settingsビュー

n開発/SQL (32%)
 トランザクションの概念 【重要度︓1】- トランザクション分離レベル- デッドロック SQL コマンド 【重要度︓13】- マテリアライズドビュー- 宣⾔的パーティショニング© LPI-Japan all rights reserved.

21

トランザクションの概念#OSS-DBnトランザクションとは ユーザから⾒た⼀連の処理のまとまりを指す。
 DBMSは、トランザクション管理機能によってデータの論理的な整合性を確保している。

nここで⾔う⼀連の処理というのは、例として、銀⾏での振込処理が該当します。
 ⼝座001から⼝座002に1000円を振り込む
1. UPDATE 預金口座 SET 預金残高 = 預金残高 – 1000 WHERE 口座番号 = 001
2. UPDATE 預金口座 SET 預金残高 = 預金残高 + 1000 WHERE 口座番号 = 002
 上記の処理2の実⾏中にデータベースがクラッシュした場合でも、トランザクション管理機能によって、処理
1の実⾏結果が取り消される。
- つまり、「全ての処理が実⾏される」か、「どちらの処理も実⾏されない」のいずれかの結果となる。

© LPI-Japan all rights reserved.

22

ACID（アシッド）特性について#OSS-DBnトランザクション処理に求められる4つの要素
ACID特性意味

機能例

Atomicity（原⼦性）

トランザクションは、完全に実⾏されるか、完全に実⾏されないかのいずれかでなければならない

コミットメント制御機能

Consistency（整合性）

トランザクションの実⾏結果に関係なく、データは事前に与えられた制約を破った状態で残ってはならない

排他制御機能（同時実⾏制御）

Isolation（独⽴性・分離性）

トランザクションは、同時実⾏中の他のトランザクションの影響を受けず、並列実⾏時であっても単独実⾏時と同じ結果を返さなければならない

Durability（耐久性・持続性）

トランザクションの結果は、障害発⽣時でも失われてはならない

障害回復機能© LPI-Japan all rights reserved.

23

PostgreSQLのトランザクション#OSS-DBnトランザクション管理
分類

SQL⽂

トランザクション開始

BEGIN; もしくは START TRANSACTION;

トランザクション確定

COMMIT; もしくは END;

更新の廃棄

ABORT; もしくはROLLBACK [TO セーブポイント名];

セーブポイント作成

SAVEPOINT セーブポイント名;

セーブポイント破棄

RELEASE [SAVEPOINT] セーブポイント名;

 SAVEPOINTでは、現在のトランザクション内でセーブポイントを作成することができる。
- セーブポイントを設定することで、それ以降に実⾏されたコマンドを全てロールバックし、セーブポイント時点の状態に戻すことができる。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/sql-savepoint.html

nPostgreSQLでは、基本的にAUTOCOMMITが有効となっている。
 トランザクションを明⽰的に開始しない場合は、ひとつのSQLがひとつのトランザクションとして扱われ、即時にコミットされる。
© LPI-Japan all rights reserved.

24トランザクション分離レベル#OSS-DB

nPostgreSQLで実装されているトランザクション分離レベルは以下のとおり。
 read committed（※PostgreSQLでは、read uncommittedを設定しても同等に扱われる）- 1つの問い合わせで⾒えるのは、その問い合わせが開始される前にコミットされた⾏のみ⾒える。 デフォ
ルト設定。
 repeatable read
- 現在のトランザクションにおける全ての問い合わせは、トランザクションで最初の問い合わせまたは更新
処理が実⾏される前にコミットされた⾏だけ⾒える。
 serializable- 現在のトランザクションにおける全ての問い合わせは、トランザクションで最初の問い合わせまたは更新
処理が実⾏される前にコミットされた⾏だけ⾒える。
- 同時実⾏中のトランザクションのパターンによって、直列に処理が⾏えない場合はエラーとなり、ロール
バックされる。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/transaction-iso.html
https://www.postgresql.jp/document/11/html/sql-set-transaction.html© LPI-Japan all rights reserved.

25トランザクションの隔離性#OSS-DB

nread committedで発⽣する可能性のある現象
 反復不能読み取り（ファジーリード）- トランザクションが反復して同じデータを読み込むとき、別のトランザクションがそのデータを更新してCOMMITしたために、同じデータを読んでいるにもかかわらず、異なる結果を得てしまうこと。
 ファントムリード- トランザクションがある検索条件で問合せを再実⾏したとき、別のトランザクションが挿⼊をしてCOMMITしたために、同じ検索条件にもかかわらず、異なる結果を得てしまうこと。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/transaction-iso.html

反復不能読み取り

ファントムリード

トランザクションA
B=# BEGIN;=# BEGIN;=# BEGIN;=# UPDATE foo SET id = 2;=# BEGIN;=# INSERT INTO foo VALUES (3);=# SELECT * FROM foo;
1=# SELECT * FROM foo;1=# COMMIT;=# COMMIT;=# SELECT * FROM foo;2

トランザクションA
B=# SELECT * FROM foo;
1
3
© LPI-Japan all rights reserved.

26

デッドロック#OSS-DBn複数のトランザクションにおいて、お互いが相⼿の取得しているロック開放待ちとなり、どちらの処理も進まなくなっている状態を指す。
トランザクションA=# BEGIN;

ロックを取得テントを-1する

ロックを取得できないトランザクションB=# BEGIN;

商品ID

商品名

数量

i001

テント

5

焚き⽕台を＋10する

i002

ランタン

10

テントを＋10する

i003

焚き⽕台

3

ロックを
取得できない焚き⽕台を-1する

nPostgreSQLではデッドロックを検知する機構を有しており、「deadlock_timeout」というパラメー
タで制御される。
 デッドロックを検知した場合、問題となっているトランザクションのいずれかが⾃動でキャンセルされる。
ERROR:

deadlock detected

参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/explicit-locking.html
参考︓LPI-JAPAN OSS-DB
https://oss-db.jp/dojo/dojo_12© LPI-Japan all rights reserved.

27

サンプル問題（トランザクションの概念）#OSS-DBnOSS-DB Silver サンプル問題/例題解説 から本テーマに関するものをいくつか紹介します。
 本セミナーでは1題のみ、時間を取って実施していただき、その後解説を⾏います。
 時間︓2分間© LPI-Japan all rights reserved.

28

サンプル問題

3.72#OSS-DBn以下の⼀連のSQL⽂を実⾏した後、テーブルtestには何⾏のデータがあるか。⾏数を答えよ。
CREATE TABLE test (id INTEGER, val TEXT);
INSERT INTO test(id, val) VALUES (1,'aaa'),(2,'bbb');
BEGIN;INSERT INTO test(id, val) VALUES (3,'ccc'),(4,'ddd'),(5,'eee');
SAVEPOINT S1;UPDATE test SET val = 'CCC' WHERE id = 3;
SAVEPOINT S2;
DELETE FROM test WHERE id IN (2,4);
ROLLBACK TO S2;INSERT INTO test(id, val) VALUES(6,'fff'),(7,'ggg');
COMMIT;※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元

 https://oss-db.jp/sample/silver_development_04/72_170228© LPI-Japan all rights reserved.

29

サンプル問題

3.73#OSS-DB

nPostgreSQLのトランザクション分離レベルの説明として、適切なものを2つ選びなさい。
A) Read Uncommittedでは、他のトランザクションが更新した後、まだcommitしていないデータを読めてしまう。
B) Read Committedでは、トランザクション内で同じSELECT⽂を2回続けて実⾏しても、異なる結果が返されることがある。
C) Repeatable Readでは、トランザクション内で同じSELECT⽂を2回続けて実⾏したら、必ず同じ結果が返される。
D) Serializableは読み取りトランザクション専⽤の分離レベルである。
※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元
 https://oss-db.jp/sample/silver_development_04/73_170403© LPI-Japan all rights reserved.

30

サンプル問題

3.111#OSS-DBn以下の⼀連の操作を⾏った。最後のSELECT⽂がcurrvalとして返す値は何か。
CREATE TABLE test1(id integer, val varchar(10));CREATE TABLE test2(id integer, val varchar(10));CREATE SEQUENCE seq1;
INSERT INTO test1(id, val) VALUES (1, 'abc'), (2, 'def');
INSERT INTO test2(id, val) VALUES (nextval('seq1'), 'xyz');
BEGIN;INSERT INTO test2(id, val) SELECT nextval('seq1'), val FROM test1;
ROLLBACK;INSERT INTO test2(id, val) VALUES (nextval('seq1'), 'pqr');
SELECT currval('seq1');※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元
 https://oss-db.jp/sample/silver_development_06/111_200129© LPI-Japan all rights reserved.

31

開発/SQL（SQLコマンド編）#OSS-DB

n運⽤管理（52%）
 インストール⽅法 【重要度︓2】- initdb
 設定ファイル 【重要度︓5】- postgresql.conf- pg_hba.conf- pg_settingsビュー

n開発/SQL (32%)
 トランザクションの概念 【重要度︓1】- トランザクション分離レベル- デッドロック SQL コマンド 【重要度︓13】- マテリアライズドビュー- 宣⾔的パーティショニング© LPI-Japan all rights reserved.

32

マテリアライズドビュー#OSS-DBnマテリアライズドビューは、PostgreSQL 9.3に実装された。
 PostgreSQL 9.4以降、リフレッシュ中の参照処理のブロック緩和、処理のパラレル化等の機能強化が⾏われている。

nビューとは
 データベースには予め定義したSQL⽂をテーブルのように扱うビューという機能がある。
 ビューは実際のデータを持っているわけではなく、問い合わせでビューが参照される度に、指定された問い合
わせが⾏われ、あたかも通常のテーブルのように扱うことができる。
 ビューは以下のようなSQL⽂で定義することができる。
postgres=# CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy';- comediesビューに対してSELECTを実⾏した場合、AS以降のSELECT⽂が実⾏される。
 クエリを擬似的なテーブルとして扱うことができるので、複雑なSQL等を毎回書く必要がない。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/rules-views.html© LPI-Japan all rights reserved.

33

マテリアライズドビュー#OSS-DBnマテリアライズドビューとは
 実体（データ）を持ったビュー。
 ビューとは異なり、毎回SQLが実⾏されることがないので、⾼速に結果を返すことが可能。
n利⽤⽅法
 マテリアライズドビューの作成
postgres=# CREATE MATERIALIZED VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy'; リフレッシュ
postgres=# REFRESH MATERIALIZED VIEW comedies;n利⽤する上で注意が必要な点
 取得元とは別にデータを保有することになるので、データが陳腐化する場合がある。
 リフレッシュ処理には時間が掛かる。
- 古くなった部分のデータだけをリフレッシュする機能はなく、全データがリフレッシュされるため、データの規模によっては多くの時間が掛かる。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/rules-materializedviews.html© LPI-Japan all rights reserved.

34

宣⾔的パーティショニング#OSS-DB

nパーティショニングとは ⼤規模なテーブルを⼩さくて管理しやすいテーブルに分割して格納する⼿法。
親テーブル

パーティション化⼦テーブル

 パーティショニングすることで、性能改善やテーブルのメンテナンス等にメリットがある。
- SQL処理でアクセスするデータ量を抑えられる。（この効果により性能が改善するケースもある。）- TRUNCATEやDROP TABLEによりパーティションデータを⾼速に削除することが可能。

n宣⾔的パーティショニングとは PostgreSQL 10で実装された機能。以前は継承、トリガ機能を⽤いたパーティショニング⽅式のみだった。
 宣⾔的になったことで、トリガや関数の作成が不要になり、CREATE TABLE⽂だけでパーティション構成を作成することが可能になった。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/ddl-partitioning.html© LPI-Japan all rights reserved.

35補⾜︓従来型パーティション⽅式（継承、トリガ⽅式）#OSS-DB

nPostgreSQL 9.6まではパーティショニングはトリガ関数やトリガ定義で挿⼊⽅式を定義し、テーブルのCHECK制約定義を組み合わせて実現していた。（PostgreSQL 10以降でも利⽤可能）
 ⼦テーブルの作成は継承機能を利⽤する。
 親テーブルからの振り分けは⾃⾝で作り込む必要がある。（トリガ機能、CHECK制約を使⽤する）
トリガ
CHECK
制約

マスタテーブル（foo テーブル）

レコードA

⼦テーブル
トリガの定義例
CREATE TRIGGER insert_trigger
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE FUNCTION foo_insert_trigger();※トリガ関数foo_insert_trigger()は事前に定義しておく。

CHECK制約の定義例
CHECK (
create_date >= DATE '2020-01-01'
ANDcreate_date < DATE '2021-01-01')© LPI-Japan all rights reserved.

36

利⽤可能なパーティション⽅式#OSS-DB

nリスト

nハッシュ
nレンジ
 キー項⽬の値を⽤いて、指定した キー項⽬の値の範囲でパーテ
 キー項⽬の値を使い、事前に指数のパーティションに⾃動で分割
ィション分割する。
定した複数の値（リスト）から該する。
当するパーティションに分割する。
 概ね均等に分割される。
品川

東京

2020/10/17

2019年

横浜

レコードA

REMAINDER 0

神奈川

2020年

キー項⽬をハッシュ化
REMAINDER 1
し、該当するテーブルに⾃動で分割される

⼭梨

2021年

REMAINDER 2

甲府© LPI-Japan all rights reserved.

37

宣⾔的パーティショニングの作成⽅法１#OSS-DB

nリストパーティションのサンプル
 親テーブル作成
CREATE TABLE
japan(
pref text not null,
population int)
PARTITION BY LIST (pref); ⼦テーブル作成
CREATE TABLE japan_tokyo PARTITION OF japan FOR VALUES IN ('tokyo');
CREATE TABLE japan_kanagawa PARTITION OF japan FOR VALUES IN ('kanagawa');
CREATE TABLE japan_yamanashi PARTITION OF japan FOR VALUES IN ('yamanashi');n宣⾔的パーティショニングを使⽤すると、⼦テーブルのカラムは親テーブルの内容が引き継がれて、⾃動で作成される。
 親テーブルと全く同じ構成である必要があるため、⼦テーブルだけ個別にカラムを追加することはできない。
参考︓PostgreSQL⽂書
https://www.postgresql.jp/document/11/html/sql-createtable.html© LPI-Japan all rights reserved.

38

宣⾔的パーティショニングの作成⽅法２#OSS-DB

nレンジパーティションのサンプル
 親テーブル作成
CREATE TABLE
log(
id int ,create_date date not null,
num int)
PARTITION BY RANGE (create_date);

 ⼦テーブル作成
CREATE TABLE log_2019 PARTITION OF log FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
CREATE TABLE log_2020 PARTITION OF log FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE log_2021 PARTITION OF log FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

nリスト、レンジパーティションで範囲外のデータを挿⼊した場合はエラーとなる ただし、PostgreSQL11以降では、デフォルトパーティションを定義することができる。
CREATE TABLE log_other PARTITION OF log DEFAULT;© LPI-Japan all rights reserved.

39

宣⾔的パーティショニングの作成⽅法３#OSS-DB

nハッシュパーティションのサンプル
 親テーブル作成
CREATE TABLE
staff(
staff_id int not null,name text not null,
age int)
PARTITION BY HASH (staff_id);

 ⼦テーブル作成
CREATE TABLE staff_a PARTITION OF staff FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE staff_b PARTITION OF staff FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE staff_c PARTITION OF staff FOR VALUES WITH (MODULUS 3, REMAINDER 2);

nキー項⽬をUPDATEすることはできるか︖実⾏したら、どうなる︖
 PostgreSQL11以降では、実⾏可能。更新後、適切なパーティションに移動される。
 PostgreSQL10ではエラーとなる。
© LPI-Japan all rights reserved.

40

パーティショニング利⽤に関して#OSS-DB

nパーティション化しても、性能が改善するとは限らない
 条件によっては、データ量は多くても元の巨⼤なテーブルのままでインデックスを使⽤した処理のほうが⾼速な場合もある。
 使⽤する場合、想定する処理内容に加え、運⽤⽅法についても検討して決定する。

nバージョンによって制約内容が異なるので、なるべく最新のバージョンを使⽤する
 基本的に最新のPostgreSQLを利⽤することで制限を最⼩限に利⽤することができる。
 最新のPostgreSQLでは多くの改善が⾏われており、性能に関しても向上している。
- パーティションプルーニング– 必要な⼦テーブルのみスキャンを⾏い、他の⼦テーブルへのアクセスをスキップする機能- Partitionwise Join
– パーティション化された⼦テーブル同⼠を結合する機能© LPI-Japan all rights reserved.

41

サンプル問題（SQLコマンド）#OSS-DBnOSS-DB Silver サンプル問題/例題解説 から本テーマに関するものをいくつか紹介します。
 本セミナーでは1題のみ、時間を取って実施していただき、その後解説を⾏います。
 時間︓2分間© LPI-Japan all rights reserved.

42

サンプル問題

3.105#OSS-DBn PostgreSQLのバージョン10からサポートされた宣⾔的パーティショニングと、従来のバージョンからサポートされている継承を利⽤したパーティ
ショニングの説明として、適切なものを３つ選びなさい。なお、親テーブルの名前は parent、パーティションの名前は partition1,
partition2, partition3… だとします。
A)
B)

C)
D)

E)

どちらの⽅法でも、親テーブルの作成は通常の CREATE TABLE parent… で⾏い、パーティションを使うための特別なオプション指定は必要ない。
パーティションを作成するときのコマンドは、宣⾔的パーティショニングでは
CREATE TABLE partition1 PARTITION OF parent…
継承を利⽤する場合は
CREATE TABLE partition1 () INHERITS (parent)
で、どちらもCREATE TABLEを使⽤する。
どちらの⽅法でも、INSERT⽂の実⾏時にデータの格納先となるパーティションが⾃動的に作成することはなく、事前にパーティションを作成しておく必要がある。
どちらの⽅法でも、親テーブルとパーティションを適切なCREATEコマンドで作成しておけば、
INSERT INTO parent…
で挿⼊するデータは⾃動的に適切なパーティションに格納される。
宣⾔的パーティショニングではすべてのパーティションは同じ列を持っていて、異なる列を追加できないが、継承を利⽤したパーティショニングでは、パーティ
ションごとに異なる列を追加できる。

※この例題は実際のOSS-DB技術者認定試験とは異なります。

n 引⽤元

 https://oss-db.jp/sample/silver_development_06/105_190731© LPI-Japan all rights reserved.

43

サンプル問題

3.98#OSS-DBnマテリアライズド・ビューと通常のビューに関する説明として、正しいものを２つ選びなさい。
A) どちらも CREATE VIEW コマンドで作成する。
B) ビューの定義が同じなら、⼀般的に、マテリアライズド・ビューからの検索の⽅が⾼速である。
C) ビューの定義が同じなら、どちらからSELECTしても、必ず同じ結果が返される。
D) マテリアライズド・ビューは、それに含まれるデータの量に従ってディスク容量を必要とするが、通常のビュー
はディスク容量を必要としない。
E) ビューを定義する問い合わせを変更する場合、通常のビューは CREATE OR REPLACE VIEW コマ
ンドを使うのに対し、マテリアライズド・ビューでは REFRESH MATERIALIZED VIEW という特殊なコマンドを使⽤する。
※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元
 https://oss-db.jp/sample/silver_development_05/98_190409© LPI-Japan all rights reserved.

44

サンプル問題3.100#OSS-DB

nテーブルのパーティショニングを使うことで得られる可能性のある利点として、適切なものを選びなさい。
A) データが占めるディスク容量を削減する。
B) 問い合わせの性能を⼤幅に向上させる。
C) 頻繁にアクセスする⾏と、そうでない⾏を、別のディスク領域に配置する。
D) 頻繁にアクセスする列と、そうでない列を、別のディスク領域に配置する。
E) DELETEの代わりにDROP TABLEを実⾏して、⼤量データの⼀括削除を⾼速化する。
※この例題は実際のOSS-DB技術者認定試験とは異なります。

n引⽤元
 https://oss-db.jp/sample/silver_development_05/100_190515© LPI-Japan all rights reserved.

45

#OSS-DB■お問い合わせ■
NTTテクノクロス株式会社

ソフト道場

https://www.ntt-tx.co.jp/products/soft_dojyo/© LPI-Japan all rights reserved.

46