IPA DBスペシャリスト試験が秋に実施されると聞いたのでPostgreSQLと絡めて紹介してみる

第17回 PostgreSQL アンカンファレンス@オンライン 2020/09/24

Transcript

1. IPA データベーススペシャリスト試験が 秋に実施されると聞いたので PostgreSQL と絡めて紹介してみる 第 17 回 PostgreSQL アンカンファレンス@オンライン　 2020/09/24 まつひさ（hmatsu47）
2. 自己紹介（前回と同じ） 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋で Web インフラのお守り係をしています MySQL 8.0 の薄い本を作って配っています ◦ Qiitaの記事： https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ GitHub リポジトリの他、印刷版を勉強会などで無料配布していました ◦ 新型コロナウイルスの関係でオフライン勉強会ができなくなったので、 現在は BOOTH でも配布しています（100円＋送料）不良在庫が… https://booth.pm/ja/items/2062599 2
3. 自己紹介（前回と同じ） 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋で Web インフラのお守り係をしています MySQL 8.0 の薄い本を作って配っています ◦ Qiitaの記事： https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ GitHub リポジトリの他、印刷版を勉強会などで無料配布していました ◦ 新型コロナウイルスの関係でオフライン勉強会ができなくなったので、 現在は BOOTH でも配布しています（100円＋送料）不良在庫が… https://booth.pm/ja/items/2062599 3 （ちょっとだけ）レビューに参加しました　 https://booth.pm/ja/items/2398704
4. 今日は… • PostgreSQL アンカンファレンスのゆるふわ枠拡充のために、 登壇枠で参加（2 回目） • 2020/03 に「インフラ勉強会」で 2 回に分けて話したネタが ベース ◦ DBスペシャリスト午後2問1で覚えるRDBMSの機能 (1) ◦ DBスペシャリスト午後2問1で覚えるRDBMSの機能 (2) • 新型コロナの影響で 春試験が延期され、DB スペシャリストが 秋試験で実施されるので取り上げてみた 4
5. 今日は… • PostgreSQL アンカンファレンスのゆるふわ枠拡充のために、 登壇枠で参加（2 回目） • 2020/03 に「インフラ勉強会」で 2 回に分けて話したネタが ベース ◦ DBスペシャリスト午後2問1で覚えるRDBMSの機能 (1) ◦ DBスペシャリスト午後2問1で覚えるRDBMSの機能 (2) • 新型コロナの影響で 春試験が延期され、DB スペシャリストが 秋試験で実施されるので取り上げてみた 5 一部の試験区分が中止に（CBTへ） 今後、秋試験と春試験が逆転することに https://www.jitec.ipa.go.jp/ より
6. わたしの情報処理技術者試験受験歴 • 平成 4 年秋 第二種情報処理技術者試験 • 平成 6 年春 第一種情報処理技術者試験 （間が空いて…） • 平成 26 年春 情報セキュリティスペシャリスト試験（SC） ◦ その後、情報処理安全確保支援士に登録（001158） • 平成 26 年秋 ネットワークスペシャリスト試験（NW） • 平成 27 年春 データベーススペシャリスト試験（DB） • 平成 28 年春 情報セキュリティマネジメント試験（SG） 6 【注】このあたりは 　「IPA 試験」 　ではありません
7. わたしの受験スタンス 7
8. PostgreSQL と何の関係があるの？ • IPA の DB スペシャリスト試験は、特定の DBMS の使用を前提 にした試験ではない • ただし、IPA の DB スペシャリスト試験は「標準 SQL」を前提 に作問されている • 近年 MySQL も標準 SQL を意識した仕様変更・機能追加が進 んでいるが、標準 SQL 対応ではまだまだ PostgreSQL に分が ある ◦ 実際に試すなら PostgreSQL のほうが合っている 8
9. データベーススペシャリスト試験とは？（1/3） • 高度試験 ◦ 記述式（スペシャリスト試験）区分の 1 つ • 高度試験の中では合格率が高めだった（16 ～ 18%） ◦ ただし、ここ 3 年は他の高度試験並みの約 14% • 午前はマークシート問題（Ⅰ・Ⅱ）、午後は記述問題（Ⅰ・Ⅱ） ◦ 午前 Ⅰ は高度試験共通（全 30 問／一部免除あり） ◦ 午前 Ⅱ は全 25 問 ◦ 午後 Ⅰ は大問 3 問中 2 問を選択 ◦ 午後 Ⅱ は大問 2 問中 1 問を選択 9
10. データベーススペシャリスト試験とは？（2/3） • それぞれ 100 点中 60 点を取らないと不合格 • 午後問題では概念データモデルおよび関係スキーマの完成問題 の出題が多い ◦ E-R 図（エンティティ、リレーションシップ、スーパータイプとサブタ イプ、…） ◦ 関数従属性、主キーと候補キー、正規化 ◦ 午後 Ⅰ の 3 問中 1 ～ 2 問、午後 Ⅱ 問 2 がこの種の問題 10
11. データベーススペシャリスト試験とは？（3/3） • 以前よりDBA・インフラエンジニア寄りになった？ ◦ SQLや物理（テーブル）実装、RDBMSの機能に関する問題が増えた 　　　　　　　　　（IPA 情報処理技術者試験・情報処理安全確保支援士試験のページより引用） 11
12. 今回取り上げるのは、 • 平成 31 年春期試験・午後 Ⅱ 問 1（の一部） • 通常、午後 Ⅱ 問題のうち、問 1 が物理（テーブル）実装や RDBMS の機能に関する問題 ◦ 過去に出題されたのは、インデックス／ビュー、ロール、CHECK 制約、 トリガ、CTE、Window 関数、FDW、パーティショニング、レプリケー ション、クラスタリング、クラウド・マネージドサービスなど ◦ テーマとしては新規システム開発、機能追加に伴う設計変更、システム リプレースに伴うデータ移行、性能向上、クラウド移行など ◦ SQL 問題が入ることも多い 12
13. 平成 31 年春期試験・午後 Ⅱ 問 1 の概要 • データベースの実装、設計（＝物理設計）の問題 • 銀行の窓口端末のログを分析して事務手続やアプリケーション ソフトウェアの改善に役立てるために、新たな分析システムを 構築することになった • 木構造をもつログデータを効率よく扱いたい • 性能面を考えて、テーブルのパーティショニングやクラスタ化 を検討したい 13
14. 平成 31 年春期試験・午後 Ⅱ 問 1 の概要 • データベースの実装、設計（＝物理設計）の問題 • 銀行の窓口端末のログを分析して事務手続やアプリケーション ソフトウェアの改善に役立てるために、新たな分析システムを 構築することになった • 木構造をもつログデータを効率よく扱いたい • 性能面を考えて、テーブルのパーティショニングやクラスタ化 を検討したい 14
15. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・1/4） 15 TS : タイムスタンプ
16. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・2/4） 16
17. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・2/4） 17 再帰 CTE をサポート
18. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・3/4） 18
19. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・3/4） 19 ハッシュ／レンジ パーティショニングを サポート
20. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・3/4） 20 シェアードナッシング 方式のクラスタリング をサポート
21. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・4/4） 21
22. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・4/4） 22 シャーディングと レプリケーション をサポート
23. 木構造をもつログデータを効率よく扱いたい（1/7） 試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋 23
24. 木構造をもつログデータを効率よく扱いたい（2/7） 試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋 24
25. 木構造をもつログデータを効率よく扱いたい（2/7） 試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋 25 バグの匂いを感じる…（理由は後ほど）
26. 木構造をもつログデータを効率よく扱いたい（3/7） （平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋 ※案 3 以外でも木構造の親子関係は同じ 26
27. 木構造をもつログデータを効率よく扱いたい（4/7） • 案 1：SQL アンチパターン「ナイーブツリー」 ◦ 親へのリンク（親 ID）しか持たない ◦ 再帰 CTE を使うのならこの構造のままでも問題なし • 案 2 : 経路列挙モデルへの変更 ◦ アンチパターン「ジェイウォーク」でもある（非正規形） ◦ バグに繋がる仕様も（末尾は「/」にしましょう） • 案 3 : 入れ子集合モデルへの変更 ◦ 更新負荷が高い ◦ 構造がちょっと複雑になる（バグの原因に） 27
28. 木構造をもつログデータを効率よく扱いたい（5/7） 案 1 問合せ [A] を PostgreSQL 12で実行してみる（再帰 CTE） 28 tree_test=# WITH RECURSIVE temp(log_id) AS tree_test-# (SELECT log_id FROM log_relation1 WHERE log_id = 101 tree_test(# UNION ALL tree_test(# SELECT a.log_id FROM log_relation1 a, temp b tree_test(# WHERE a.parent_log_id = b.log_id) tree_test-# SELECT log_id FROM temp; log_id -------- 101 102 103 104 105 106 (6 rows) 【注】カラム名は英数字と記号、ID列は数値に変更
29. 木構造をもつログデータを効率よく扱いたい（6/7） 案 1 問合せ [A] を PostgreSQL 12で実行してみる（再帰 CTE） 29
30. 木構造をもつログデータを効率よく扱いたい（7/7） 試験問題は案 3 を採用 • 案 3 の弱点「更新」（木構造への挿入・削除）を行わないため • 要件が違えば最適な選択肢は異なる ◦ おそらく案 3 （入れ子集合モデル）が採用されるケースは少ない • 案 1（再帰 CTE）が採用されなかった理由は？ ◦ 問題文では「SQL 文としての再帰 CTE の難易度の高さ」を暗に指摘 30
31. 木構造をもつログデータを効率よく扱いたい（7/7） 試験問題は案 3 を採用 • 案 3 の弱点「更新」（木構造への挿入・削除）を行わないため • 要件が違えば最適な選択肢は異なる ◦ おそらく案 3 （入れ子集合モデル）が採用されるケースは少ない • 案 1（再帰 CTE）が採用されなかった理由は？ ◦ 問題文では「SQL 文としての再帰 CTE の難易度の高さ」を暗に指摘 ◦ 本当の理由は、勘の良い方ならすでに分かっているはず !? 31
32. テーブルパーティショニングで性能向上（1/9） 試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.10〔RDBMS の主な仕様〕より再び抜粋 ※今回は試験問題に例示されているデータだけでは少ないので、 全く別のテーブルで試す（レンジパーティショニング） 32
33. テーブルパーティショニングで性能向上（2/9） 33 • 年別のパーティション + 範囲外用のデフォルト パーティション（2015 年 ～ 2020 年・各 24,000 行） • 年単位のデータ取得が 高速に • 過去分を年単位で削除 する場合も高速に
34. postgres=# CREATE DATABASE partition_test; CREATE DATABASE postgres=# \c partition_test You are now connected to database "partition_test" as user "postgres". partition_test=# CREATE TABLE log_record ( partition_test(# log_id SERIAL NOT NULL, partition_test(# log_datetime TIMESTAMP NOT NULL, partition_test(# log_text VARCHAR(500) NOT NULL partition_test(# ) partition_test-# PARTITION BY RANGE (log_datetime); CREATE TABLE partition_test=# CREATE TABLE log_record_default PARTITION OF log_record DEFAULT; CREATE TABLE partition_test=# CREATE TABLE log_record_2015 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'); CREATE TABLE テーブルパーティショニングで性能向上（3/9） 34
35. postgres=# CREATE DATABASE partition_test; CREATE DATABASE postgres=# \c partition_test You are now connected to database "partition_test" as user "postgres". partition_test=# CREATE TABLE log_record ( partition_test(# log_id SERIAL NOT NULL, partition_test(# log_datetime TIMESTAMP NOT NULL, partition_test(# log_text VARCHAR(500) NOT NULL partition_test(# ) partition_test-# PARTITION BY RANGE (log_datetime); CREATE TABLE partition_test=# CREATE TABLE log_record_default PARTITION OF log_record DEFAULT; CREATE TABLE partition_test=# CREATE TABLE log_record_2015 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'); CREATE TABLE テーブルパーティショニングで性能向上（3/9） 35 日時（タイムスタンプ）で パーティショニングを試す
36. postgres=# CREATE DATABASE partition_test; CREATE DATABASE postgres=# \c partition_test You are now connected to database "partition_test" as user "postgres". partition_test=# CREATE TABLE log_record ( partition_test(# log_id SERIAL NOT NULL, partition_test(# log_datetime TIMESTAMP NOT NULL, partition_test(# log_text VARCHAR(500) NOT NULL partition_test(# ) partition_test-# PARTITION BY RANGE (log_datetime); CREATE TABLE partition_test=# CREATE TABLE log_record_default PARTITION OF log_record DEFAULT; CREATE TABLE partition_test=# CREATE TABLE log_record_2015 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'); CREATE TABLE テーブルパーティショニングで性能向上（3/9） 36 日時（タイムスタンプ）で パーティショニングを試す ↓範囲外はデフォルトパーティション
37. テーブルパーティショニングで性能向上（4/9） 37 partition_test=# CREATE TABLE log_record_2016 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00'); CREATE TABLE partition_test=# CREATE TABLE log_record_2017 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'); CREATE TABLE partition_test=# CREATE TABLE log_record_2018 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'); CREATE TABLE partition_test=# CREATE TABLE log_record_2019 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'); CREATE TABLE partition_test=# ALTER TABLE log_record ADD PRIMARY KEY (log_id, log_datetime); ALTER TABLE （データINSERT）
38. テーブルパーティショニングで性能向上（4/9） 38 partition_test=# CREATE TABLE log_record_2016 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00'); CREATE TABLE partition_test=# CREATE TABLE log_record_2017 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'); CREATE TABLE partition_test=# CREATE TABLE log_record_2018 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'); CREATE TABLE partition_test=# CREATE TABLE log_record_2019 PARTITION OF log_record partition_test-# FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'); CREATE TABLE partition_test=# ALTER TABLE log_record ADD PRIMARY KEY (log_id, log_datetime); ALTER TABLE （データINSERT） ↑主キーにパーティションキーを含める
39. テーブルパーティショニングで性能向上（5/9） 39 postgres=# CREATE DATABASE no_partition_test; CREATE DATABASE partition_test=# \c no_partition_test You are now connected to database "no_partition_test" as user "postgres". no_partition_test=# CREATE TABLE log_record ( no_partition_test(# log_id SERIAL NOT NULL, no_partition_test(# log_datetime TIMESTAMP NOT NULL, no_partition_test(# log_text VARCHAR(500) NOT NULL no_partition_test(# ); CREATE TABLE no_partition_test=# ALTER TABLE log_record ADD PRIMARY KEY (log_id); ALTER TABLE no_partition_test=# CREATE INDEX ON log_record (log_datetime); CREATE INDEX （データINSERT）
40. テーブルパーティショニングで性能向上（5/9） 40 postgres=# CREATE DATABASE no_partition_test; CREATE DATABASE partition_test=# \c no_partition_test You are now connected to database "no_partition_test" as user "postgres". no_partition_test=# CREATE TABLE log_record ( no_partition_test(# log_id SERIAL NOT NULL, no_partition_test(# log_datetime TIMESTAMP NOT NULL, no_partition_test(# log_text VARCHAR(500) NOT NULL no_partition_test(# ); CREATE TABLE no_partition_test=# ALTER TABLE log_record ADD PRIMARY KEY (log_id); ALTER TABLE no_partition_test=# CREATE INDEX ON log_record (log_datetime); CREATE INDEX （データINSERT） 比較用に非パーティショニング 　のテーブルも用意する 全く同じデータをINSERTする
41. partition_test=# EXPLAIN VERBOSE SELECT COUNT(*) FROM log_record partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Aggregate (cost=2135.00..2135.01 rows=1 width=8) Output: count(*) -> Seq Scan on public.log_record_2019 (cost=0.00..2075.00 rows=24000 width=0) Filter: ((log_record_2019.log_datetime >= '2019-01-01 00:00:00'::timestamp without time zone) AND (log _record_2019.log_datetime <= '2019-12-31 23:59:59'::timestamp without time zone)) (4 rows) partition_test=# \timing Timing is on. partition_test=# SELECT COUNT(*) FROM log_record partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; count ------- 24000 (1 row) Time: 5.854 ms テーブルパーティショニングで性能向上（6/9） 41
42. partition_test=# EXPLAIN VERBOSE SELECT COUNT(*) FROM log_record partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Aggregate (cost=2135.00..2135.01 rows=1 width=8) Output: count(*) -> Seq Scan on public.log_record_2019 (cost=0.00..2075.00 rows=24000 width=0) Filter: ((log_record_2019.log_datetime >= '2019-01-01 00:00:00'::timestamp without time zone) AND (log _record_2019.log_datetime <= '2019-12-31 23:59:59'::timestamp without time zone)) (4 rows) partition_test=# \timing Timing is on. partition_test=# SELECT COUNT(*) FROM log_record partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; count ------- 24000 (1 row) Time: 5.854 ms テーブルパーティショニングで性能向上（6/9） 42 2019年のパーティションから検索されている
43. partition_test=# EXPLAIN VERBOSE SELECT COUNT(*) FROM log_record partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Aggregate (cost=2135.00..2135.01 rows=1 width=8) Output: count(*) -> Seq Scan on public.log_record_2019 (cost=0.00..2075.00 rows=24000 width=0) Filter: ((log_record_2019.log_datetime >= '2019-01-01 00:00:00'::timestamp without time zone) AND (log _record_2019.log_datetime <= '2019-12-31 23:59:59'::timestamp without time zone)) (4 rows) partition_test=# \timing Timing is on. partition_test=# SELECT COUNT(*) FROM log_record partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; count ------- 24000 (1 row) Time: 5.854 ms テーブルパーティショニングで性能向上（6/9） 43 2019年のパーティションから検索されている
44. テーブルパーティショニングで性能向上（7/9） PostgreSQL 12の例（SELECTの場合） 44 no_partition_test=# EXPLAIN VERBOSE SELECT COUNT(*) FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------- Aggregate (cost=2638.89..2638.90 rows=1 width=8) Output: count(*) -> Index Only Scan using log_record_log_datetime_idx on public.log_record (cost=0.42..2576.84 rows=24821 w idth=0) Output: log_datetime Index Cond: ((log_record.log_datetime >= '2019-01-01 00:00:00'::timestamp without time zone) AND (log_ record.log_datetime <= '2019-12-31 23:59:59'::timestamp without time zone)) (5 rows) （中略） no_partition_test=# SELECT COUNT(*) FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; count ------- 24000 (1 row) Time: 7.361 ms
45. テーブルパーティショニングで性能向上（7/9） PostgreSQL 12の例（SELECTの場合） 45 no_partition_test=# EXPLAIN VERBOSE SELECT COUNT(*) FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------- Aggregate (cost=2638.89..2638.90 rows=1 width=8) Output: count(*) -> Index Only Scan using log_record_log_datetime_idx on public.log_record (cost=0.42..2576.84 rows=24821 w idth=0) Output: log_datetime Index Cond: ((log_record.log_datetime >= '2019-01-01 00:00:00'::timestamp without time zone) AND (log_ record.log_datetime <= '2019-12-31 23:59:59'::timestamp without time zone)) (5 rows) （中略） no_partition_test=# SELECT COUNT(*) FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; count ------- 24000 (1 row) Time: 7.361 ms インデックスで絞り込まれている
46. テーブルパーティショニングで性能向上（7/9） PostgreSQL 12の例（SELECTの場合） 46 no_partition_test=# EXPLAIN VERBOSE SELECT COUNT(*) FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------- Aggregate (cost=2638.89..2638.90 rows=1 width=8) Output: count(*) -> Index Only Scan using log_record_log_datetime_idx on public.log_record (cost=0.42..2576.84 rows=24821 w idth=0) Output: log_datetime Index Cond: ((log_record.log_datetime >= '2019-01-01 00:00:00'::timestamp without time zone) AND (log_ record.log_datetime <= '2019-12-31 23:59:59'::timestamp without time zone)) (5 rows) （中略） no_partition_test=# SELECT COUNT(*) FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'; count ------- 24000 (1 row) Time: 7.361 ms インデックスで絞り込まれている パーティショニングのほうが若干速い
47. テーブルパーティショニングで性能向上（8/9） 47 partition_test=# DROP TABLE log_record_2015; DROP TABLE Time: 7.023 ms partition_test=# SELECT COUNT(*) FROM log_record partition_test-# WHERE log_datetime BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 23:59:59'; count ------- 0 (1 row) no_partition_test=# DELETE FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 23:59:59'; DELETE 24000 Time: 123.978 ms no_partition_test=# SELECT COUNT(*) FROM log_record no_partition_test-# WHERE log_datetime BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 23:59:59'; count ------- 0 (1 row) パーティショニングの場合は子テーブルを DROP パーティショニングを利用して DROP したほうが速い 非パーティショニングの場合は行を DELETE
48. テーブルパーティショニングで性能向上（9/9） • 高速化できるケースは限られる ◦ 高速化できても劇的に速くなるわけではない ◦ 試験問題でも「パーティショニングでは将来のデータ増加に耐えられな い」と判断して、クラスタリングの検討に入った • パーティション単位の一括削除が必要なら検討する価値 あり？ 48
49. クラスタリングで将来のデータ増加に備える（1/4） 試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.11〔RDBMS の主な仕様〕より再び抜粋 49
50. クラスタリングで将来のデータ増加に備える（2/4） 試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.11〔RDBMS の主な仕様〕より再び抜粋 ◦ 50
51. クラスタリングで将来のデータ増加に備える（3/4） Citus（PostgreSQL ベースのシャーディング型クラスタ） • Microsoft が開発元の Citus Data 社を買収 • Azure Database for PostgreSQL - Hyperscale (Citus) としてサービス提供 https://azure.microsoft.com/ja-jp/services/postgresql/ • Microsoft 社内で Windows Update のログデータ分析に 利用されている https://www.atmarkit.co.jp/ait/articles/1911/06/news003.html 51
52. クラスタリングで将来のデータ増加に備える（3/4） Citus（PostgreSQL ベースのシャーディング型クラスタ） • Microsoft が開発元の Citus Data 社を買収 • Azure Database for PostgreSQL - Hyperscale (Citus) としてサービス提供 https://azure.microsoft.com/ja-jp/services/postgresql/ • Microsoft 社内で Windows Update のログデータ分析に 利用されている https://www.atmarkit.co.jp/ait/articles/1911/06/news003.html 52 「伝統的な 新しいSQL」 とは
53. クラスタリングで将来のデータ増加に備える（4/4） http://docs.citusdata.com/en/v9.4/faq/faq.html#are-there-any-postgresql-features-n ot-supported-by-citus 53
54. クラスタリングで将来のデータ増加に備える（4/4） http://docs.citusdata.com/en/v9.4/faq/faq.html#are-there-any-postgresql-features-n ot-supported-by-citus 54 木構造の検討で案 1 を採用しなかった理由がここに !?
55. 今後ありそうな出題 • 地理分散・冗長化をテーマにしたもの ◦ 事業のグローバル化とか災害対策とか ◦ さすがに「NewSQL」というワードは出てこないとは思うが、C の付く 昆虫系 DB を（どことなく）意識したやつとか… • RDBMS が得意としないデータ構造をあえて扱うもの ◦ 時系列データ（IoT 向け）、グラフ構造、改ざん検知を意識した構造 etc. 55
56. 今後ありそうな出題 • 地理分散・冗長化をテーマにしたもの ◦ 事業のグローバル化とか災害対策とか ◦ さすがに「NewSQL」というワードは出てこないとは思うが、C の付く 昆虫系 DB を（どことなく）意識したやつとか… • RDBMS が得意としないデータ構造をあえて扱うもの ◦ 時系列データ（IoT 向け）、グラフ構造、改ざん検知を意識した構造 etc. • GPU を使うもの 56
57. まとめ • 試験問題を実際の RDBMS に当てはめて考える・実際に SQL 文を実行して試してみるとわかりやすい ◦ 試すなら PostgreSQL で ◦ 試験問題と実環境で違うところもあるが、それ自体が勉強になる • 受験しなくても、たまに試験問題にチャレンジしてみる と頭の体操になる ◦ 試験問題に書かれていないところまで想像すると、なお良し（？） 57