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

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

Transcript

1. IPA データベーススペシャリスト試験が
   秋に実施されると聞いたので
   PostgreSQL と絡めて紹介してみる
   第 17 回 PostgreSQL アンカンファレンス@オンライン　
   2020/09/24
   まつひさ（hmatsu47）
   

   View Slide

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
   

   View Slide

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
   

   View Slide

4. 今日は…
   ● PostgreSQL アンカンファレンスのゆるふわ枠拡充のために、
   登壇枠で参加（2 回目）
   ● 2020/03 に「インフラ勉強会」で 2 回に分けて話したネタが
   ベース
   ○ DBスペシャリスト午後2問1で覚えるRDBMSの機能 (1)
   ○ DBスペシャリスト午後2問1で覚えるRDBMSの機能 (2)
   ● 新型コロナの影響で 春試験が延期され、DB スペシャリストが
   秋試験で実施されるので取り上げてみた
   4
   

   View Slide

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/ より
   

   View Slide

6. わたしの情報処理技術者試験受験歴
   ● 平成 4 年秋 第二種情報処理技術者試験
   ● 平成 6 年春 第一種情報処理技術者試験
   （間が空いて…）
   ● 平成 26 年春 情報セキュリティスペシャリスト試験（SC）
   ○ その後、情報処理安全確保支援士に登録（001158）
   ● 平成 26 年秋 ネットワークスペシャリスト試験（NW）
   ● 平成 27 年春 データベーススペシャリスト試験（DB）
   ● 平成 28 年春 情報セキュリティマネジメント試験（SG）
   6
   【注】このあたりは
   　「IPA 試験」
   　ではありません
   

   View Slide

7. わたしの受験スタンス
   7
   

   View Slide

8. PostgreSQL と何の関係があるの？
   ● IPA の DB スペシャリスト試験は、特定の DBMS の使用を前提
   にした試験ではない
   ● ただし、IPA の DB スペシャリスト試験は「標準 SQL」を前提
   に作問されている
   ● 近年 MySQL も標準 SQL を意識した仕様変更・機能追加が進
   んでいるが、標準 SQL 対応ではまだまだ PostgreSQL に分が
   ある
   ○ 実際に試すなら PostgreSQL のほうが合っている
   8
   

   View Slide

9. データベーススペシャリスト試験とは？（1/3）
   ● 高度試験
   ○ 記述式（スペシャリスト試験）区分の 1 つ
   ● 高度試験の中では合格率が高めだった（16 ～ 18%）
   ○ ただし、ここ 3 年は他の高度試験並みの約 14%
   ● 午前はマークシート問題（Ⅰ・Ⅱ）、午後は記述問題（Ⅰ・Ⅱ）
   ○ 午前 Ⅰ は高度試験共通（全 30 問／一部免除あり）
   ○ 午前 Ⅱ は全 25 問
   ○ 午後 Ⅰ は大問 3 問中 2 問を選択
   ○ 午後 Ⅱ は大問 2 問中 1 問を選択
   9
   

   View Slide

10. データベーススペシャリスト試験とは？（2/3）
    ● それぞれ 100 点中 60 点を取らないと不合格
    ● 午後問題では概念データモデルおよび関係スキーマの完成問題
    の出題が多い
    ○ E-R 図（エンティティ、リレーションシップ、スーパータイプとサブタ
    イプ、…）
    ○ 関数従属性、主キーと候補キー、正規化
    ○ 午後 Ⅰ の 3 問中 1 ～ 2 問、午後 Ⅱ 問 2 がこの種の問題
    10
    

    View Slide

11. データベーススペシャリスト試験とは？（3/3）
    ● 以前よりDBA・インフラエンジニア寄りになった？
    ○ SQLや物理（テーブル）実装、RDBMSの機能に関する問題が増えた
    　　　　　　　　　（IPA 情報処理技術者試験・情報処理安全確保支援士試験のページより引用）
    11
    

    View Slide

12. 今回取り上げるのは、
    ● 平成 31 年春期試験・午後 Ⅱ 問 1（の一部）
    ● 通常、午後 Ⅱ 問題のうち、問 1 が物理（テーブル）実装や
    RDBMS の機能に関する問題
    ○ 過去に出題されたのは、インデックス／ビュー、ロール、CHECK 制約、
    トリガ、CTE、Window 関数、FDW、パーティショニング、レプリケー
    ション、クラスタリング、クラウド・マネージドサービスなど
    ○ テーマとしては新規システム開発、機能追加に伴う設計変更、システム
    リプレースに伴うデータ移行、性能向上、クラウド移行など
    ○ SQL 問題が入ることも多い
    12
    

    View Slide

13. 平成 31 年春期試験・午後 Ⅱ 問 1 の概要
    ● データベースの実装、設計（＝物理設計）の問題
    ● 銀行の窓口端末のログを分析して事務手続やアプリケーション
    ソフトウェアの改善に役立てるために、新たな分析システムを
    構築することになった
    ● 木構造をもつログデータを効率よく扱いたい
    ● 性能面を考えて、テーブルのパーティショニングやクラスタ化
    を検討したい
    13
    

    View Slide

14. 平成 31 年春期試験・午後 Ⅱ 問 1 の概要
    ● データベースの実装、設計（＝物理設計）の問題
    ● 銀行の窓口端末のログを分析して事務手続やアプリケーション
    ソフトウェアの改善に役立てるために、新たな分析システムを
    構築することになった
    ● 木構造をもつログデータを効率よく扱いたい
    ● 性能面を考えて、テーブルのパーティショニングやクラスタ化
    を検討したい
    14
    

    View Slide

15. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・1/4）
    15
    TS : タイムスタンプ
    

    View Slide

16. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・2/4）
    16
    

    View Slide

17. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・2/4）
    17
    再帰 CTE をサポート
    

    View Slide

18. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・3/4）
    18
    

    View Slide

19. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・3/4）
    19
    ハッシュ／レンジ
    パーティショニングを
    サポート
    

    View Slide

20. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・3/4）
    20
    シェアードナッシング
    方式のクラスタリング
    をサポート
    

    View Slide

21. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・4/4）
    21
    

    View Slide

22. 関連仕様を抜粋（H31 春期 DB スペシャリスト午後 Ⅱ 問 1 より・4/4）
    22
    シャーディングと
    レプリケーション
    をサポート
    

    View Slide

23. 木構造をもつログデータを効率よく扱いたい（1/7）
    試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋
    23
    

    View Slide

24. 木構造をもつログデータを効率よく扱いたい（2/7）
    試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋
    24
    

    View Slide

25. 木構造をもつログデータを効率よく扱いたい（2/7）
    試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋
    25
    バグの匂いを感じる…（理由は後ほど）
    

    View Slide

26. 木構造をもつログデータを効率よく扱いたい（3/7）
    （平成 31 年春期試験・午後 Ⅱ 問 1）P.14〔”ログ関連”テーブルの検討〕より抜粋
    ※案 3 以外でも木構造の親子関係は同じ
    26
    

    View Slide

27. 木構造をもつログデータを効率よく扱いたい（4/7）
    ● 案 1：SQL アンチパターン「ナイーブツリー」
    ○ 親へのリンク（親 ID）しか持たない
    ○ 再帰 CTE を使うのならこの構造のままでも問題なし
    ● 案 2 : 経路列挙モデルへの変更
    ○ アンチパターン「ジェイウォーク」でもある（非正規形）
    ○ バグに繋がる仕様も（末尾は「/」にしましょう）
    ● 案 3 : 入れ子集合モデルへの変更
    ○ 更新負荷が高い
    ○ 構造がちょっと複雑になる（バグの原因に）
    27
    

    View Slide

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列は数値に変更
    

    View Slide

29. 木構造をもつログデータを効率よく扱いたい（6/7）
    案 1 問合せ [A] を PostgreSQL 12で実行してみる（再帰 CTE）
    29
    

    View Slide

30. 木構造をもつログデータを効率よく扱いたい（7/7）
    試験問題は案 3 を採用
    ● 案 3 の弱点「更新」（木構造への挿入・削除）を行わないため
    ● 要件が違えば最適な選択肢は異なる
    ○ おそらく案 3 （入れ子集合モデル）が採用されるケースは少ない
    ● 案 1（再帰 CTE）が採用されなかった理由は？
    ○ 問題文では「SQL 文としての再帰 CTE の難易度の高さ」を暗に指摘
    30
    

    View Slide

31. 木構造をもつログデータを効率よく扱いたい（7/7）
    試験問題は案 3 を採用
    ● 案 3 の弱点「更新」（木構造への挿入・削除）を行わないため
    ● 要件が違えば最適な選択肢は異なる
    ○ おそらく案 3 （入れ子集合モデル）が採用されるケースは少ない
    ● 案 1（再帰 CTE）が採用されなかった理由は？
    ○ 問題文では「SQL 文としての再帰 CTE の難易度の高さ」を暗に指摘
    ○ 本当の理由は、勘の良い方ならすでに分かっているはず !?
    31
    

    View Slide

32. テーブルパーティショニングで性能向上（1/9）
    試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.10〔RDBMS の主な仕様〕より再び抜粋
    ※今回は試験問題に例示されているデータだけでは少ないので、
    全く別のテーブルで試す（レンジパーティショニング）
    32
    

    View Slide

33. テーブルパーティショニングで性能向上（2/9）
    33
    ● 年別のパーティション +
    範囲外用のデフォルト
    パーティション（2015 年
    ～ 2020 年・各 24,000 行）
    ● 年単位のデータ取得が
    高速に
    ● 過去分を年単位で削除
    する場合も高速に
    

    View Slide

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
    

    View Slide

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
    日時（タイムスタンプ）で
    パーティショニングを試す
    

    View Slide

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
    日時（タイムスタンプ）で
    パーティショニングを試す
    ↓範囲外はデフォルトパーティション
    

    View Slide

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）
    

    View Slide

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）
    ↑主キーにパーティションキーを含める
    

    View Slide

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）
    

    View Slide

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する
    

    View Slide

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
    

    View Slide

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年のパーティションから検索されている
    

    View Slide

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年のパーティションから検索されている
    

    View Slide

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
    

    View Slide

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
    インデックスで絞り込まれている
    

    View Slide

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
    インデックスで絞り込まれている
    パーティショニングのほうが若干速い
    

    View Slide

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
    

    View Slide

48. テーブルパーティショニングで性能向上（9/9）
    ● 高速化できるケースは限られる
    ○ 高速化できても劇的に速くなるわけではない
    ○ 試験問題でも「パーティショニングでは将来のデータ増加に耐えられな
    い」と判断して、クラスタリングの検討に入った
    ● パーティション単位の一括削除が必要なら検討する価値
    あり？
    48
    

    View Slide

49. クラスタリングで将来のデータ増加に備える（1/4）
    試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.11〔RDBMS の主な仕様〕より再び抜粋
    49
    

    View Slide

50. クラスタリングで将来のデータ増加に備える（2/4）
    試験問題（平成 31 年春期試験・午後 Ⅱ 問 1）P.11〔RDBMS の主な仕様〕より再び抜粋
    ○
    50
    

    View Slide

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
    

    View Slide

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」
    とは
    

    View Slide

53. クラスタリングで将来のデータ増加に備える（4/4）
    http://docs.citusdata.com/en/v9.4/faq/faq.html#are-there-any-postgresql-features-n
    ot-supported-by-citus
    53
    

    View Slide

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 を採用しなかった理由がここに !?
    

    View Slide

55. 今後ありそうな出題
    ● 地理分散・冗長化をテーマにしたもの
    ○ 事業のグローバル化とか災害対策とか
    ○ さすがに「NewSQL」というワードは出てこないとは思うが、C の付く
    昆虫系 DB を（どことなく）意識したやつとか…
    ● RDBMS が得意としないデータ構造をあえて扱うもの
    ○ 時系列データ（IoT 向け）、グラフ構造、改ざん検知を意識した構造
    etc.
    55
    

    View Slide

56. 今後ありそうな出題
    ● 地理分散・冗長化をテーマにしたもの
    ○ 事業のグローバル化とか災害対策とか
    ○ さすがに「NewSQL」というワードは出てこないとは思うが、C の付く
    昆虫系 DB を（どことなく）意識したやつとか…
    ● RDBMS が得意としないデータ構造をあえて扱うもの
    ○ 時系列データ（IoT 向け）、グラフ構造、改ざん検知を意識した構造
    etc.
    ● GPU を使うもの
    56
    

    View Slide

57. まとめ
    ● 試験問題を実際の RDBMS に当てはめて考える・実際に
    SQL 文を実行して試してみるとわかりやすい
    ○ 試すなら PostgreSQL で
    ○ 試験問題と実環境で違うところもあるが、それ自体が勉強になる
    ● 受験しなくても、たまに試験問題にチャレンジしてみる
    と頭の体操になる
    ○ 試験問題に書かれていないところまで想像すると、なお良し（？）
    57
    

    View Slide