実践RDBMS〜PostgreSQL編〜

実践RDBMS〜PostgreSQL編〜
2018/5 IKEDA Kiyoshi
1

View Slide

About Me
➢ 2009〜2017 ... 渋谷の某IT企業でソシャゲ開発やイ
ンフラ運用に勤しむ
○ ほぼMySQL
○ 高負荷や大量データとの戦いの日々を経験
➢ 2017〜 ... M3
○ クラウド電子カルテ ... PostgreSQL
○ AIチーム ... MySQL, PostgreSQL
➢ PostgreSQL勉強中
➢ Oracle未着手
2

View Slide

はじめに
➢ 今日の目的
○ RDBを用いたアプリケーションの開発に必要な基礎知識を身に着け
る。
➢ 前提
○ SQLを書いたことがある
○ RDBを使ったアプリケーション開発の経験が浅い
○ 一般的な知識を復習したい
➢ 今日やらないこと:
○ 理論的なことには深入りしない
○ Oracle, MySQLについてはほぼ触れない
○ ハンズオンの時間はありません
■ 演習問題をいくつか用意しています←後ほど各自でどうぞ
■ サンプルリポジトリ有り(後掲)
3

View Slide

今日伝えたい大事なこと
➢ クソクエリを本番DBに流さない
➢ ORMを使っていても、発行されるSQLを意識しよう
➢ 実行計画を見よう
➢ 負荷試験をしよう
➢ 正しいDB設計でデータの整合性を守り、パフォーマ
ンスを担保する
➢ DBMSの公式ドキュメントを読もう
4

View Slide

お品書き
➢ RDB, RDBMSとは
➢ DB設計
○ ER図, 正規化
○ 制約
➢ SQL
○ SQLの基礎
○ リレーション演算, JOIN
➢ INDEXと実行計画
➢ ロックとトランザクション
5

View Slide

RDB, RDBMSとは
6

View Slide

RDBとは
➢ Relational Database = 関係(relation)DB
➢ 事前定義された、関連があるデータ項目の集合体
○ https://aws.amazon.com/jp/relational-database/
○ データ項目: 行と列を持つテーブルのセット
7
顧客ID 店舗ID 氏名 Email 入会日
1 101 Adam ***@example.com 2015/01/01
2 101 Bluce ***@example.com 2016/07/15
3 102 Chris ***@example.com 2011/09/23
4 103 Dave ***@example.com 2014/02/03
表：例 - 顧客テーブル

View Slide

RDBの理論
➢ リレーショナルモデル ... データを「リレーション」という概
念で表現する
➢ リレーション ... テーブルに相当
➢ 属性 ... テーブルの「列」に相当
➢ データを集合として扱う
○ 集合操作(和、差、積など)をサポート
○ よく使うのは「射影」「選択(制限)」「結合」(※後述)
8

View Slide

RDBMS
➢ RDB Management System
○ RDBを管理するためのソフトウェア
➢ PostgreSQL, Oracle, MySQL, etc.
➢ 機能
○ SQL
○ トランザクション
○ バックアップ
○ リストア
○ クラッシュリカバリ
○ レプリケーション
○ 権限管理
➢ ACID特性を持つ
9

View Slide

ACID特性について
➢ トランザクション処理を持つシステムの備えるべき4つ
の性質
➢ Atomicity(原子性)
○ 一連の処理は全て実行されるか、全く実行されないかのど
ちらか
➢ Consistency(一貫性)
○ 処理の前後でデータの整合性が保たれる
➢ Isolation(独立性)
○ 処理中の更新は確定するまで他の処理から不可視
➢ Durability(永続性)
○ 確定した結果が失われない
10

View Slide

DB設計
11

View Slide

サンプルDB
12
➢ https://github.com/progrhyme/postgres-tutorial
➢ ER図: http://www.postgresqltutorial.com/postgresql-sample-database/
➢ 以降の説明の一部でこのdvdrentalデータベースを使
う。

View Slide

RDBにおけるDB設計
➢ 例: 「DVDレンタルショップのシステムを作ろう」
➢ 関連するデータ属性の組をリレーション(=テーブル)
にまとめていく。
○ store ... 店舗
○ staff ... 店員
○ film ... 映画
○ actor ... 俳優
13

View Slide

ER図
14
store
store_id
manager_staff_id
address
staff
staff_id
store_id
name
email
active
film
film_id
title
description
release_date
length
actor
actor_id
name
birth_date
film_actor
film_id
actor_id
1:Nの関係
交差テーブルによりM:N
の関係を実現

View Slide

ER図について
➢ テーブル間のデータのカーディナリティ(多重度)を視覚化でき
る
➢ あると引き継ぎ時など便利
➢ テーブル数が20を超えるぐらいから可視化が難しくなる
○ 主要部分について、または、機能単位などで作ると良い
➢ ツールは色々あるが、ERMasterやMySQL Workbenchが使
いやすかった記憶
➢ DB上のテーブル定義からER図を生成できるものや、ER図か
らDDLを生成できるものもある
15

View Slide

リレーション(テーブル)の正規化
➢ 正規化 ... データの重複や矛盾を防ぐための作業
➢ 正規形(NF) ... 正規化の段階を示すもの
○ 第1〜第6正規形まである
○ BCNF(ボイスコッド正規形) ... 3NFと4NFの間
○ 第3正規形までは覚えよう
○ 第n正規形(n >= 2)はより下位の正規形を満たす
■ BCNFは1〜3NFを満たす
➢ 第1正規形
○ 重複がない(主キー or ユニークキーがある)
○ スカラ値のみを持つ
■ JSONとかNG
○ NULL値を取らない
16

View Slide

1NF → 2NF
17
作品レビュワー監督出身レート
A Bob Tom アリゾナ ★★★
A Alice Tom アリゾナ ★
B Bob Eva ミネソタ ★★
C Alice Tom アリゾナ ★★★
表：「レビュー」リレーション
(作品, レビュワー)を
候補キーという
作品レビュワーレート
A Bob ★★★
A Alice ★
B Bob ★★
C Alice ★★★
作品監督出身
A Tom アリゾナ
B Eva ミネソタ
C Tom アリゾナ
「候補キー → 非キー属性」へ
の関数従属性を除く

View Slide

2NF → 3NF
18
作品監督出身
A Tom アリゾナ
B Eva ミネソタ
C Tom アリゾナ
監督出身
Tom アリゾナ
Eva ミネソタ
作品監督
A Tom
B Eva
C Tom
「非キー属性 → 非キー属性」
への関数従属性を除く

View Slide

正規化しない / あえて非正規化する例
➢ 日付のNULL値をフラグ的に扱うやり方
○ 例) deleted_atがNULLならば削除されてないと見なす
➢ optionalな属性値をまとめて1つのカラムにJSON形
式で格納
○ RDB的にはアンチパターン
■ インデックス効かない(例外: PostgreSQLのjsonb型)
■ アプリケーションにロジックが寄る→データ不整合を生みやすい
○ メリット: ALTERを避けられる
○ NoSQLを使うという手もある
➢ JOINによってパフォーマンス劣化することがある vs
正しくクエリを書けば劣化しないはず
19

View Slide

制約 - 主キー、一意性制約、NOT NULL制約
➢ 主キー ... レコードを一意に特定するための列(の組)。基本、
作る。
○ 複合主キー ... 複数列から成る主キー
○ ナチュラルキー ... 意味のあるキー。ユーザ名, 氏名, メールアドレ
スなど
○ サロゲートキー ... システムが発行する意味のないキー。連番のID
など
➢ 一意性制約 = ユニークキー
○ 主キー以外にも設けられる
➢ NOT NULL制約 ... 適宜付ける
20
昔のMySQLやPostgreSQLはNULLだとINDEXが効かない問題があった
が、最近はINDEXが効く。PostgreSQLなら部分INDEX(後述)も使える。//
正規化の観点ではNULLは避けるべき

View Slide

制約 - 外部キー制約①
➢ 複数テーブル間でデータの整合性をとるために使う
➢ `REFERENCES` を指定する
21
CREATE TABLE city (
city_id integer NOT NULL,
city character varying(50) NOT NULL,
country_id smallint NOT NULL REFERENCES country,
last_update timestamp NOT NULL DEFAULT now(),
PRIMARY KEY (city_id)
);
参照テーブル
被参照テーブル

View Slide

制約 - 外部キー制約②
22
dvdrental=# INSERT INTO city (city,country_id) VALUES ('Tokyo',
9999);
ERROR: insert or update on table "city" violates foreign key
constraint "fk_city"
DETAIL: Key (country_id)=(9999) is not present in table
"country".
被参照テーブルに存在しない値でINSERTしようとしてエラー

View Slide

制約 - 外部キー制約③
23
CREATE TABLE inventory (
inventory_id integer NOT NULL,
film_id smallint NOT NULL REFERENCES film
ON UPDATE CASCADE
ON DELETE RESTRICT,
store_id smallint NOT NULL,
PRIMARY KEY (inventory_id)
);
被参照列の更新後の値が参照行にコピー
される。
被参照行の削除を防ぐ。
ON UPDATE RESTRICT ... 被参照行の更新を防ぐ
ON DELETE CASCADE ... 被参照行の削除時、参照行も削除する

View Slide

制約 - 外部キー制約の注意点
➢ 外部キーには暗黙的なインデックスが設定されない
➢ 先に挙げた2例だと city.country_id や
inventory.film_id にインデックスがないと、countryや
filmの行削除/更新時にフルスキャンが走る。
➢ 参考: PostgreSQLアンチパターン：外部キー制約の
更新コストを見くびる - Qiita
24

View Slide

DB設計 - 要点まとめ
➢ 第3正規形まではできるようになろう
➢ 主キーは付ける
○ ナチュラルキー、サロゲートキーは適宜使い分けるべし(異
論は有るかも)
➢ 外部キー制約はデータの整合性担保のために有用
だが、パフォーマンス上は要注意
25

View Slide

(演習) DB設計
➢ ERMasterなどを使って、案件で利用しているDBの
ER図を作ってみよう
➢ 正規化されていないテーブルがあれば、理由を考え
てみよう。また、正規化するとしたらどうするか考えて
みよう
➢ dvdrentalデータベースのfilmテーブルのデータを更
新・削除を試し、外部キー制約の挙動を確認しよう
26

View Slide

SQL
27

View Slide

SQLとは
28
➢ RDBの操作を行うための言語
➢ ISOによって国際標準のSQL仕様が定められている
➢ どの程度標準に準拠しているかは、各RDBMSによって異なる
○ 「方言」が多いといわれる←なるべく標準SQLを書こう
○ 参考: SQLの観点から「Oracle Database」「PostgreSQL」「MySQL」の特徴
を整理しよう！ | アシスト
➢ DDL ... データ定義言語。TABLEやINDEXのCREATE,
DROP, ALTERなど
➢ DML ... データ操作言語。レコードのCRUD (INSERT,
SELECT, UPDATE, DELETE)

View Slide

SQLの基礎 - テーブルの作成、削除
29
-- テーブルの作成
CREATE TABLE language (
language_id integer NOT NULL,
name character(20) NOT NULL,
PRIMARY KEY (language_id)
);
-- テーブルの削除
DROP TABLE language;
language_id name last_update
1 English 2006-02-15 10:02:19
2 Italian 2006-02-15 10:02:19
3 Japanese 2006-02-15 10:02:19
4 Mandarin 2006-02-15 10:02:19
5 French 2006-02-15 10:02:19
6 German 2006-02-15 10:02:19
※一部簡略化しています。

View Slide

SQLの基礎 - レコードの作成
30
-- レコードの挿入
INSERT INTO language VALUES (7, 'Chinese', NOW());
INSERT INTO language (name, language_id) VALUES ('Korean', 10);
-- 複数行の挿入(= BULK INSERT)
INSERT INTO language VALUES
(8, 'Russian', NOW()), (9, 'Greese', NOW());
-- サブクエリを利用した BULK INSERT
INSERT INTO language (name, language_id) SELECT name, language_id
+ 10 FROM language;

View Slide

SQLの基礎 - レコードの更新・削除
31
-- レコードの更新
UPDATE language SET name = 'Swidish' WHERE language_id = 6;
-- レコードの削除
DELETE FROM language WHERE language_id > 10;
WHERE句のないUPDATE, DELETEは
全件が対象となり、非常に危険！！

View Slide

SQLの基礎 - レコードの参照
32
-- 全件の全列を取得
SELECT * FROM language;
-- 列を指定
SELECT name,last_update FROM language;
-- 更に、行の条件を指定
SELECT name,last_update FROM language WHERE language_id IN (1,2);
-- ソート
SELECT * FROM language ORDER BY last_update DESC;
-- ソートして結果の件数を制限
SELECT * FROM language ORDER BY last_update LIMIT 3;

View Slide

SQLの基礎 - レコードの参照 + 集約クエリ
33
-- 結果のN件目からM件を取得
SELECT * FROM language LIMIT 3 OFFSET 5;
-- 結果から重複行を除く
SELECT DISTINCT last_update FROM language;
-- 集約
SELECT last_update, count(*) FROM language GROUP BY last_update;
SELECT last_update, count(*) FROM language GROUP BY last_update
HAVING count(*) > 3;
LIMIT OFFSET句は簡易なページングシステムなどでよく使われるが、OFFSET値
が大きくなるとDB負荷が大きくなるため、OFFSET上限が定まらない限りは安易に
使うべきではない

View Slide

SQLによるリレーション演算
➢ 射影 ... テーブルの中から一部の列だけを取り出す
○ 例) SELECT 顧客ID, 氏名 FROM 顧客;
➢ 選択(制限) ... 条件に合う行だけを取り出す
○ 例) SELECT * FROM 顧客 WHERE 店舗ID = 101;
➢ 結合 ... 2つのテーブルを結びつけて1つの仮想的なテーブルを作る
○ 例) SELECT * FROM 顧客 INNER JOIN 店舗 ON (顧客.店舗ID =
店舗.店舗ID);
34
顧客ID 店舗ID 氏名 Email 入会日
1 101 Adam ***@example.com 2015/01/01
2 101 Bluce ***@example.com 2016/07/15
3 102 Chris ***@example.com 2011/09/23
4 104 Dave ***@example.com 2014/02/03
店舗ID 管理者住所営業開始日
101 White アリゾナ 2013/05/01
102 River フロリダ 2011/04/20
103 Mountain ミシシッピ 2016/02/17
表:顧客テーブル
表:店舗テーブル

View Slide

結合の種類
➢ 条件による結合
○ A INNER JOIN B ... A及びBで値が一致する行について
結合
○ A LEFT OUTER JOIN B ... Aの各行は最低1回は現れ、
Bで値が存在しないものはNULLになる
○ A RIGHT OUTER JOIN B ... ↑の逆
○ A FULL OUTER JOIN B ... A, Bとも最低1回は現れ、他
方に値が存在しないものはNULLになる
➢ クロス結合
○ A CROSS JOIN B ... AとBの直積(デカルト積)。すべての
可能な行同士の組み合わせ。結果はn * m行になる。
35

View Slide

SQL補遺
➢ 公式チュートリアル:
○ https://www.postgresql.jp/document/10/html/tutorial-sql.html
➢ その他の集約関数 ... avg, sum, maxなど
○ 一覧:
https://www.postgresql.jp/document/10/html/functions-aggregate.html
➢ 集合操作:
○ UNION ... たまに使う。
○ EXCEPT, INTERSECT ... ほとんど使わない。
➢ ※(特にWeb)アプリケーションから複雑なSQLを実行するのは
推奨しません。
36
不慣れな人は一通りどうぞ

View Slide

(演習) SQL
➢ dvdrental DBについて
○ ①film, film_category, categoryの3テーブルを内部結合し
て、category_id = 2 (Animation) のtitleとnameを10件抽
出しましょう。
○ ②payment, customer, addressの3テーブルのデータか
ら、2007年2月の売上を顧客の住所ごとに合計して上位
10件を抽出して下さい。サブクエリを使うと良いでしょう。
37

View Slide

閑話:データ型とデータサイズ(PostgreSQL)
➢ https://www.postgresql.jp/document/10/html/datatype.html
➢ 数値データ型
○ integer(4B) … 約20億までなので
○ bigint(8B) … ↑に収まらないときはこちら
➢ 文字型
○ char(n) … n文字分。固定長
○ varchar(n) … 最大n文字分。可変長
○ 1文字のデータサイズは文字セット(エンコーディング)による
■ https://www.postgresql.jp/document/10/html/multibyte.html
■ UTF8 … 1-4B
➢ バイナリ型
➢ 日付/時刻データ型
38

View Slide

閑話:データ型とデータサイズ
➢ 1行のサイズ
○ 〜各列のデータサイズの合計
○ インデックスサイズ
■ 〜キー列のデータサイズの合計
➢ データ型は必要十分なサイズを選ぼう
➢ NG例:
○ 数値しか入らないのに文字型を使う
39

View Slide

INDEXと実行計画
40

View Slide

B-treeインデックス
41
➢ RDBのインデックスといえばまずこれ。
➢ PostgreSQLでは他にもいくつか選べるが、デフォルトは
B-tree.
➢ Balanced Treeである。(binary treeではない)
○ leafの高さを一定に保つアルゴリズム
➢ 計算量はO(log N)
➢ PKやユニークキーを作るとB-treeインデックスが作られる
➢ 制約のないインデックスを作ることもできる
➢ 参考:
○ B-treeインデックス入門 - Qiita
○ B木 - Wikipedia
○ 11.6. 一意インデックス - PostgreSQL 10.3文書

View Slide

どうインデックスを作るべきか？
➢ 検索やソートの要件に応じて、必要なイ
ンデックスを作る
➢ 例) paymentテーブル(右)
○ 要件:
■ 特定のpayment_dateの期間
■ staff_id + customer_idによる絞り込み
■ rental_idで絞り込んでpayment_dateでソート
○ INDEX:
■ (payment_date)
■ (staff_id, customer_id)
■ (rental_id, payment_date)
➢ ※UPDATEやDELETEの絞り込み条
件でもインデックスが用いられる
42
payment
payment_id
customer_id
staff_id
rental_id
amount
payment_date

View Slide

複合インデックスについて
➢ 複数列から成るインデックス
➢ 検索木は最初の列値で作られる
○ => 2番目以降の列値で絞り込みやソートをしてもインデックスは使わ
れない
○ 先の例だと、(rental_id, payment_date)は(payment_date)の代替に
ならない
➢ 複合PKでも同じ話
➢ カバリングインデックス
○ インデックスに含まれる列値のみをSELECT対象とする問合せで、イ
ンデックスオンリースキャンを可能にする
■ 実データ部にアクセスしないことでI/Oを抑えられる
○ この目的であえて検索条件として不要な列値をインデックスに加えるこ
ともある
43

View Slide

初心者がやりがちなインデックス効かない事例
➢ 中間一致
○ SELECT * FROM t WHERE a LIKE ‘%foo%’;
➢ 後方一致
○ SELECT * FROM t WHERE a LIKE ‘%foo’;
➢ 否定形
○ SELECT * FROM t WHERE a <> 1;
➢ 参考:
○ インデックスが使えない検索条件 - Qiita
※NULLはインデックス効きます
44

View Slide

インデックスのトレードオフ、他
➢ インデックスが多いほど、更新処理が重くなる
➢ インデックスのデータ量も増える
➢ 無駄なインデックスは省こう:
○ 例) (a, b, c) というインデックスがあるなら (a) や (a, b) は
無駄。
➢ 巨大なテーブルに後からインデックスを追加するのは
高コスト
○ 使いそうなインデックスは予め作っておく
45

View Slide

閑話：部分インデックス(PostgreSQL)
➢ https://www.postgresql.jp/document/10/html/index
es-partial.html
➢ CREATE INDEX … WHERE 〜で対象となるレ
コードを限定する。
➢ 頻出値(デフォルト値など)を除外するときに有効
➢ パフォーマンス向上する場合がある
○ インデックスサイズが小さく
○ 検索・更新が速く
46

View Slide

閑話：関数インデックス(PostgreSQL, Oracle)
➢ https://www.postgresql.jp/document/10/html/indexes-expr
essional.html
➢ 列値そのものでなく、1つ以上の列から計算した結果
の値でインデックスを作る
➢ 例)
○ メールアドレスに対してlower関数(小文字化)をかけてユ
ニーク制約を付ける
47

View Slide

PostgreSQLのクエリ処理と実行計画
参考:
➢ https://www.postgresql.jp/document/9.3/html/overview.html
➢ 『内部構造から学ぶPostgreSQL 設計・運用計画の鉄則』4.3 問い合わせ
の実行
48
図は割愛

View Slide

実行計画を見よう(PostgreSQL)
➢ 実行計画 … DBMSがどのようにクエリを実行するか、とい
う内部情報。チューニングの重要な情報源
➢ EXPLAIN 〜 ... 実行計画を表示
➢ EXPLAIN ANALYZE 〜 ... 実際にクエリを実行し、
かかった時間と合わせて実行計画を表示
○ ※更新系SQLを食わせると危険！
49

View Slide

PostgreSQL:実行計画の例と見方
dvdrental=# EXPLAIN SELECT f.title, l.name FROM film f, language l WHERE f.language_id
= l.language_id AND f.film_id IN (237,407);
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=0.28..13.85 rows=2 width=99)
Join Filter: (f.language_id = l.language_id)
-> Seq Scan on language l (cost=0.00..1.06 rows=6 width=88)
-> Materialize (cost=0.28..12.61 rows=2 width=17)
-> Index Scan using film_pkey on film f (cost=0.28..12.60 rows=2 width=17)
Index Cond: (film_id = ANY ('{237,407}'::integer[]))
(6 rows)
50
filmとlanguageをJOINして問合せ

View Slide

PostgreSQL:実行計画の例と見方
dvdrental=# EXPLAIN SELECT f.title, l.name FROM film f, language l WHERE f.language_id
= l.language_id AND f.film_id IN (237,407);
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=0.28..13.85 rows=2 width=99)
Join Filter: (f.language_id = l.language_id)
-> Seq Scan on language l (cost=0.00..1.06 rows=6 width=88)
-> Materialize (cost=0.28..12.61 rows=2 width=17)
-> Index Scan using film_pkey on film f (cost=0.28..12.60 rows=2 width=17)
Index Cond: (film_id = ANY ('{237,407}'::integer[]))
(6 rows)
51
filmとlanguageをJOINして問合せ
入れ子ループ
結合
インデックス
が使われる
シーケンシャルス
キャン。インデック
スが使われない
最初の1件を返すまでのコ
スト .. 全件を返すまでのコ
スト

View Slide

PostgreSQL:実行計画の見方の補足
➢ 実行計画は木構造
○ 下位ノードから実行される
○ 総実行コストは下位ノードの実行コストの総和
➢ コスト値は基準値(seq_page_cost)を1とする相対評価
➢ スキャン系ノード
○ Seq Scan, Index Scan, Index Only Scan他
➢ 結合ノード
○ Nested Loop, Hash Join, Merge Join
➢ その他ノード
○ Sort ... work_memが小さいと外部ソートが発生し、遅くなる
○ and more
52

View Slide

大事なこと
➢ 大量のフルスキャンを発生させない
○ 適切にインデックスを張る
○ インデックスが想定通り使われるか、実行計画を見て確認
➢ リリース当初は良くても、データが溜まって性能劣化
することがある
○ データが蓄積した状態の負荷試験をしましょう
53

View Slide

ロックとトランザクション
54

View Slide

ロック
55
➢ 一方が処理している間、他方は処理できないようにブ
ロックする。排他ロック
➢ 例)
○ AとBがほぼ同時に1つしかないDVD『ジュラシックパーク』を借りようと
する
○ ロック機構がないと、2人とも借りることに成功してしまう
➢ PostgreSQLのロックは細かくて難しい
○ テーブルレベルロック
■ SELECTでもゆるいロックを獲得する
■ 同一テーブルに競合するロックモードのトランザクションを同時に実行で
きない
○ 行レベルロック
■ 2つのトランザクションは同じ行に対して競合するロックを保持できない

View Slide

PostgreSQLのテーブルレベルロックモード①
56
ロックモード獲得タイミング
ACCESS SHARE SELECT
ROW SHARE SELECT FOR UPDATE, SELECT FOR SHARE
ROW EXCLUSIVE UPDATE, DELETE, INSERT
SHARE UPDATE EXCLUSIVE VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY,
CREATE STATISTICS, ALTER TABLE VALIDATE及び他の
ALTER TABLEの亜種
SHARE CREATE INDEX
SHARE ROW EXCLUSIVE CREATE COLLATION, CREATE TRIGGER, ALTER TABLE
EXCLUSIVE REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM
FULL, REFRESH MATERIALIZED VIEW, ALTER TABLE,
LOCK TABLE
弱
強

View Slide

PostgreSQLのテーブルレベルロックモード②
表: https://www.postgresql.jp/document/10/html/explicit-locking.html より
57

View Slide

PostgreSQLのテーブルレベルロックまとめ
➢ SELECTは基本ロック競合しない
➢ SELECT FOR UPDATEの他、UPDATEなど行を更
新するクエリは行ロックする(後述)
➢ 大抵のDDLは更新操作と競合する
○ DDLそのものとも競合する
➢ DROP TABLE, 一部のALTER TABLE, VACUUM
FULLなどはSELECTとも競合する
58

View Slide

PostgreSQLの行レベルロックモード
59
ロックモード獲得タイミング
FOR KEY SHARE SELECT FOR KEY SHARE
FOR SHARE SELECT FOR SHARE
FOR NO KEY UPDATE ユニークキーを含む列値に対する UPDATE, SELECT FOR NO
KEY UPDATE
FOR UPDATE UPDATE, DELETE, SELECT FOR UPDATE
弱
強
https://www.postgresql.jp/document/10/html/explicit-locking.html より

View Slide

デッドロック
➢ 異なる2者がそれぞれA→B, B→Aとロックを取り合
い、どちらも処理できなくなること
➢ PostgreSQLのデッドロック検出機構:
○ deadlock_timeout(デフォルト1秒)ごとにロック獲得待ちの
トランザクションをチェック
○ デッドロックしていたらどちらかをabort
○ 参考: PostgreSQLは雰囲気でデッドロックを殺す - そーだ
いなるらくがき帳
➢ 行ロックの場合、更新順序をソートするのがよくやる
解決策
60

View Slide

トランザクション
➢ トランザクション
○ 互いに関連する一連の処理を1まとめにしたもの
○ ACID特性を満たす
■ Atomicity ... 一連の処理は全て実行されるか、全く実行されないか
● ロールバックが可能
■ Consistency ... 処理の前後でデータの整合性が保たれる
■ Isolation ... 処理中の更新は確定するまで他の処理から不可視
■ Durability ... 確定した結果が失われない
➢ ロックとトランザクション
○ ロックは獲得された後、トランザクションの終わりまで保持
される
61

View Slide

トランザクションを使う
➢ psqlクライアント
○ 開始 ... `BEGIN` or `START TRANSACTION`
○ キャンセル ... `ROLLBACK`
○ 確定 ... `COMMIT`
○ ※トランザクションを使わないと、更新SQLを実行する度に
ただちに確定される
➢ ライブラリから接続するとき、多くの場合、トランザク
ションを有効にして使う
➢ PostgreSQLはDDLでもトランザクションを使える
62

View Slide

トランザクション分離レベルについて
https://www.postgresql.jp/document/10/html/transaction-iso.html
63
分離レベルダーティーリード反復不能読み取りファントムリード直列化異常
READ UNCOMMITTED OK※ NG NG NG
READ COMMITTED OK NG NG NG
REPEATABLE READ OK OK OK※ NG
SERIALIZABLE OK OK OK OK
OK※ ... 標準SQLでは許容されるが、PostgreSQLでは発生しない
表：分離レベルと発生し得る問題
PostgreSQLのデフォルトはREAD COMMITTED. M3でもこれを使って
いる。

View Slide

分離レベルが低いことで起こる問題
➢ ダーティリード
○ 他のトランザクション(Tx)による未コミットのデータを読み込んでしまう
➢ 反復不能読み取り
○ 他のTxによるコミット済みのデータを読み込むことで、Tx内の同一行
に対するSELECTの結果が異なる
➢ ファントムリード
○ ある範囲内への他のTxによるレコード挿入 + コミットによって、Tx内の
該当の範囲検索の結果が異なる
➢ 直列化異常
○ 複数のTxを正常にコミットした結果が、それぞれをあらゆる可能な順
序で実行した結果と一貫性がない
64

View Slide

分離レベルのトレードオフ
➢ 分離レベルが高いほどデータの信頼性は高くなる
➢ 引き換えに、性能が低くなりやすい
○ トランザクション並列度が下がる
○ ロック競合やデッドロックが発生しやすくなる
➢ REPEATABLE READ以上では、直列化失敗による
Txの再実行を考慮する必要がある
○ `ERROR: could not serialize access due to concurrent update`
○ ↑Tx中に他のTxによって同一行の更新が行われた
■ ロールバックされる
65

View Slide

ロックとトランザクションで大事なこと
➢ 適切にロックを取り、データ不整合を防ごう
○ 反復不能読み取りが許容できない箇所では `SELECT
FOR UPDATE` を使うといった対策が考えられる
➢ ソーシャル機能など、複数の更新主体があるとロック
問題を起こしやすい
○ 誰がデータを更新し得るか(=どういうロックを取るか)を網
羅しよう
➢ ロック・トランザクションの粒度はなるべく小さくする
○ ロック競合や性能の低下を防ぐ
66

View Slide

(演習) ロックとトランザクションを体験しよう
1. psqlでローカルのPostgreSQLにトランザクション処
理を実行
2. ターミナルを2つ開き、それぞれpsqlで接続
a. 更新Tx処理を行い、未コミットのデータが他方から見えないことを確認
b. 互いにロック競合するTx処理をしてみよう
i. テーブルロック、行ロックそれぞれ試してみる
ii. ロック競合しないパターンについても動作確認してみる
3. デッドロックの実験
a. ↑と同じ要領で。
(参考) 複数プロセスが同時に操作しても問題ないことを確認する
には、スクリプトを書くなどしてテストする
67

View Slide

まとめ：大事なこと(再掲)
➢ クソクエリを本番DBに流さない
➢ ORMを使っていても、発行されるSQLを意識しよう
➢ 実行計画を見よう
➢ 負荷試験をしよう
➢ 正しいDB設計でデータの整合性を守り、パフォーマ
ンスを担保する
➢ DBMSの公式ドキュメントを読もう
68

View Slide

その他のトピック
➢ RDBMS共通
○ ストアドプロシージャ(PostgreSQLだとFUNCTION)
○ トリガー
○ VIEW
○ シーケンス
○ レプリケーション
○ PARTITIONING
➢ PostgreSQL特有
○ プロセスの種類と役割
○ VACUUM ← 追記型アーキテクチャ
➢ 「何それ？」というものがあれば調べてみて下さい
69

View Slide

参考文献等
➢ 『理論から学ぶデータベース実践入門』奥野幹也 /
2015年
➢ 『内部構造から学ぶPostgreSQL 設計・運用計画の
鉄則』勝俣智成，佐伯昌樹，原田登志 / 2014年
➢ PostgreSQL公式ドキュメント(日本語)
○ 第14章性能に関するヒント … チューニングTips
70

View Slide

お疲れ様でした。
71

View Slide

おまけ：1テーブル1インデックス
➢ 一度に1テーブルで1インデックスしか使われないとい
う原則の話。
➢ 参考: インデックスの結合 : ひとつのテーブルへのア
クセスに複数のインデックスを使用します。
➢ PostgreSQLではBitmapスキャンがあるので、カー
ディナリティ(データのばらつき)の低い複数の列にそ
れぞれ単列のインデックスを張っておくことが有効な
こともある
○ 参考: 複合インデックス非推奨: つれづれネット散歩
72

View Slide

実践RDBMS〜PostgreSQL編〜

実践RDBMS〜PostgreSQL編〜

progrhyme

More Decks by progrhyme

Other Decks in Technology

Featured

Transcript