DMSを使った異種DB移行で学んだPostgreSQLの面白い所/chugokudb27-postgresql-is-interesting

# 概要

所属の会社にて 去年の8月頃から AWSのDMSを使用して MySQL から PostgreSQLへ異種間のDB移行をやっています。 これまでMySQLしか使ってこなかった自分がPostgreSQLを始めて使って行った中で面白いな、と思ったPostgreSQLの話を紹介させて頂きたいと思います。

# 対象者

・ DMSって何って方（概要は説明しようと思います）
・ MySQLを業務で触っている方
・ PostgreSQLを業務で触っている方
・ アプリケーションエンジニア寄りの方

Transcript

1. 2019-05-18 ୈ27ճ தࠃ஍ํDBษڧձ
   DMSΛ࢖ͬͨҟछDBҠߦͰֶΜͩ
   PostgreSQLͷ͓΋͠Ζ͍ॴ
   ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํࢧ෦௕ ߴڮ Ұٍ
   1
   

   View Slide

2. ͓͠ͳ͕͖
   1. ࣗݾ঺հ
   2. DMSΛ؆୯ʹ঺հ
   3. DMSΛ࢖ͬͨDBϦϑΝΫλϦϯά
   4. PostgreSQLͷ͓΋͠Ζ͍ॴ
   5. ·ͱΊ
   2
   

   View Slide

3. ஫ҙࣄ߲
   • εϥΠυ͸ެ։͠·͢ɻ
   • ࣭໰ͱ͔͝ࢦఠͱ͔
   ॾʑͷϑΟʔυόοΫ͕͍͟͝·ͨ͠Β
   #ChugokuDB ΁ ͓ئ͍க͠·͢ɻ
   3
   

   View Slide

4. 1. ࣗݾ঺հ
   • ߴڮ Ұٍ
   • Ԭࢁࡏॅ
   • גࣜձࣾΦϛΧϨ
   όοΫΤϯυΤϯδχΞ
   • ೔ຊPostgreSQLϢʔβʔձ
   தࠃࢧ෦௕
   4
   

   View Slide

5. 2019/02/24 ύύʹͳΓ·ͨ͠ʂ
   5
   

   View Slide

6. גࣜձࣾΦϛΧϨ
   શࠃ30,000݅ͷࠗ׆ύʔςΟΛ
   ܝࡌͨ͠ϙʔλϧαΠτ
   35ສਓڧͷձһͷํʑʹ͝ར༻௖͍
   ͍ͯ·͢ɻ
   \\ࠗ׆ͱݴ͑͹ΦϛΧϨʂ//
   ग़ձ͍ΛٻΊ͍ͯΔํͷ
   ҰॿͱͳΕΕ͹ʂʂ
   6
   

   View Slide

7. ؇࿨ٳ୊
   7
   

   View Slide

8. ࠓ೔ͷ͓࿩
   • ΦϛΧϨʹͯDBϦϑΝΫλϦϯάΛ΍ͬͯΔɻ
   • ͦΕʹ͸DMSͱ͍͏AWSͷαʔϏεΛ׆༻͍ͯ͠Δɻ
   • DMSͬͯԿʁ
   • ͲΜͳ෩ʹ΍ͬͯΔͷʁ
   • MySQL͔͠΍ͬͯແ͔ͬͨ๻͕PostgreSQLΛ৮ͬͯΈͯ
   ͜Μͳࣄग़དྷΔͷ͔ɺͱࢥͬͨࣄΛ঺հ
   ʮ໘ന͍ͱ͜Ζʢখฒײʣʯ
   8
   

   View Slide

9. ͪΐͬͱখ࿩
   9
   

   View Slide

10. ͳͥɺDBϦϑΝΫλϦϯάΛ΍Δͷ͔
    10
    

    View Slide

11. ΦϛΧϨʹ͍ͭͯ
    • αʔϏε։͔࢝Β໿8೥͕ܦա͍ͯ͠Δɻ
    • ͜͜1೥Ͱ ։ൃਓһ͕ࣾһ1໊ + ֎஫ ͷମ੍͔Β
    ։ൃࣾһ9໊ ͷମ੍ʹมֵɻ
    • ৭Μͳେਓͷࣄ৘͔Β͍ΘΏΔٕज़తෛ࠴ͱ
    ݺ͹ΕΔ΋ͷ͕ͦΕͳΓʹ͋Δɻ
    11
    

    View Slide

12. ྫ͑͹ɾɾ
    ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ
    12
    

    View Slide

13. ྫ͑͹ɾɾ
    ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ
    ʮ͜ͷςʔϒϧԿʹ࢖ͬͯΔ͔୭͔஌ͬͯΔʁʯ
    13
    

    View Slide

14. ྫ͑͹ɾɾ
    ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ
    ʮ͜ͷςʔϒϧԿʹ࢖ͬͯΔ͔୭͔஌ͬͯΔʁʯ
    ʮʮʮ͍΍͊ɻɻɻ
    !
    ʁʯʯʯ
    14
    

    View Slide

15. ྫ͑͹ɾɾ
    ʮσʔλϕʔεߏ଄ΛோΊΔձ΍Ζ͏ʂʯ
    ʮ͜ͷςʔϒϧԿʹ࢖ͬͯΔ͔୭͔஌ͬͯΔʁʯ
    ʮʮʮ͍΍͊ɻɻɻ
    !
    ʁʯʯʯ
    ୭΋஌Βͳ͍ςʔϒϧͷڪා...
    15
    

    View Slide

16. gender ΧϥϜ
    ૝ఆ ࣮ࡍ
    ܕ int
    ೖΔσʔλ 0, 1, 2
    σʔλͷҙຯ 0: ະఆٛ,
    1: உੑ,
    2: ঁੑ
    16
    

    View Slide

17. gender ΧϥϜ
    ૝ఆ ࣮ࡍ
    ܕ int
    ೖΔσʔλ 0, 1, 2
    σʔλͷҙຯ 0: ະఆٛ,
    1: உੑ,
    2: ঁੑ
    PostgreSQLʮ!!cannot convert int!!ʯ
    17
    

    View Slide

18. gender ΧϥϜ
    ૝ఆ ࣮ࡍ
    ܕ int varchar
    ೖΔσʔλ 0, 1, 2 0, 1, 2, உੑ, ঁੑ
    σʔλͷҙຯ 0: ະఆٛ,
    1: உੑ,
    2: ঁੑ
    0: ະఆٛ,
    1: உੑ,
    2: ঁੑ
    18
    

    View Slide

19. ݱঢ়
    • ҋRDBΞϯνύλʔϯ͕ͲΜͲΜ૿͍͑ͯ͘ɻ
    • ͱ͸͍͑ݶΒΕͨϦιʔεΛશͯϦϑΝΫλϦϯάʹ
    ׂ͘ࣄ͸ग़དྷͳ͍ɻ
    • ͳΔ΂͘ແఀࢭͰ͔ͭԁ׈ʹ։ൃΛਐΊΔͨΊʹ
    ฐࣾCTOͦʔ͍ͩ(@soudai1025)͞Μ͕
    ؤுͬͯߟ͑ͨɻ
    19
    

    View Slide

20. 2. DMS͕ͲΜͳ΋ͷ͔؆୯ʹ͝঺հ
    20
    

    View Slide

21. AWS Database Migration
    ωοτϫʔΫӽ͠ʹ
    σʔλҠߦ͕ग़དྷΔαʔϏε
    ಄จࣈΛऔͬͯɺDMSͱݺ͹ΕΔɻ
    Oracle→Oracle͸΋ͪΖΜͷࣄ
    SQL Server→Amazon Auroraͳ
    ͲͷҟछDBؒͷҠߦ΋αϙʔτ͞
    ΕΔɻ
    21
    

    View Slide

22. AWS Database Migration Serviceͷ͝঺հ
    • AWS DMS ͷߏ੒ཁૉ
    • ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ
    • λεΫͷৼΔ෣͍ʹ͍ͭͯ
    • λεΫͷઃఆํ๏ʹ͍ͭͯ
    22
    

    View Slide

23. AWS DMS ͷߏ੒ཁૉ
    • ιʔεΤϯυϙΠϯτ
    • σʔλͷίϐʔݩɻEC2onDB, RDS, ΦϯϓϨDB, S3 ͳͲɻ
    • λʔήοτΤϯυϙΠϯτ
    • σʔλͷίϐʔઌɻEC2onDB, RDS, ΦϯϓϨDB, S3, DynamoDB ͳͲɻ
    • ϨϓϦέʔγϣϯΠϯελϯε
    • ίϐʔݩ͔Βίϐʔઌ΁σʔλͷϨϓϦέʔγϣϯΛߦ͏Πϯελϯεɻ
    • λεΫ
    • ͲͷςʔϒϧΛͲͷΑ͏ʹҠߦ͢Δ͔ΛఆΊͨϧʔϧɻ
    େମ͜Ε͚͓͚֮ͩ͑ͯ͹׬શʹཧղग़དྷΔ
    23
    

    View Slide

24. ϨϓϦέʔγϣϯΠϯελϯεʹ͍ͭͯ
    • ΠϯελϯεΫϥε͸ T2, C4, R4ͷ̏छྨΛαϙʔτ.
    • T2: ։ൃɾݕূ༻
    • C4: େྔͷԋࢉΛߦ͏৔߹ʹ໾ཱͭɻ
    (AWSతʹ͸ ҟछDBҠߦ͸͜ΕΛ͢͢Ί͍ͯΔ)
    • R4: ϝϞϦ૿ՃΠϯελϯεɻ
    (ҰճͷτϥϯβΫγϣϯ͕େ͖͍࣌͸͜Ε.)
    • ϚϧνAZʹରԠ͍ͯͯ͠ɺϑΣΠϧΦʔόʔΛαϙʔτ.
    • ࣮ࡍɺDMSͱ͓͕͔͔ͯۚ͠Δͷ͸͜ͷΠϯελϯεར༻ྉͱετϨʔδ͚ͩɻ
    ʢ※ॾઆʣ
    24
    

    View Slide

25. λεΫͷৼΔ෣͍ͷछྨʹ͍ͭͯ
    λεΫͷৼΔ෣͍͸ओʹ3λΠϓ͋Δɻ
    • શϩʔυ+ܧଓతͳϨϓϦέʔγϣϯ(CDC).
    • ΦϛΧϨͰར༻͍ͯ͠ΔλΠϓɻ
    • طଘͷσʔλΛϑϧϩʔυͨ͠ޙ(PostgreSQLͩͱCOPYจͰߦ͏)
    ͦͷޙ͸τϥϯβΫγϣϯϩά͔ΒܧଓతʹϨϓϦέʔγϣϯ
    • ܧଓతͳϨϓϦέʔγϣϯ(CDC)ͷΈ
    • શϩʔυͷΈ
    25
    

    View Slide

26. λεΫͷઃఆํ๏ʹ͍ͭͯ
    • ϨϓϦέʔγϣϯΛߦ͏ςʔϒϧͷࢦఆํ๏͸̎छྨ
    • userɿ จࣈྻҰகͰ user ͷΈΛର৅ͱ͢Δɻ
    • user_%ɿ ਖ਼نදݱͰ user_xxx ʹ߹க͢Δ
    શͯͷςʔϒϧΛର৅ͱ͢Δɻ
    26
    

    View Slide

27. λεΫͷઃఆํ๏ʹ͍ͭͯ
    • ·ͨɺ֤ςʔϒϧʹରͯ͠ҠߦϧʔϧΛઃఆग़དྷΔɻ
    • ྫ͑͹Ҡߦޙͷςʔϒϧ໊/ΧϥϜ໊Λมߋͨ͠Γ
    ର৅ͷσʔλΛWHERE۟ͷΑ͏ʹϧʔϧͰ
    ߜΓࠐΜͩΓग़དྷΔɻ
    • ଞʹ΋ςʔϒϧͷҠߦઌͷεΩʔϚ໊ͷࢦఆͳͲɻ
    27
    

    View Slide

28. ͦͷଞDMSͷओͳ׆༻ࣄྫ
    • ΦϯϓϨͷDB͔ΒAWSͷRDS΁σʔλΛҠߦ͢Δɻ
    • ͔ͷߴڮগ೥͸ɾɾɾ
    Ҏલਂ໷ϑϧόοΫΞοϓΛऔͬͯAWSʹ
    సૹͯ͠෮ݩ͢Δ࡞ۀΛ͍ͯͨ͠
    • Lambda+RDS͸ಉ࣌઀ଓͷ໰୊͔Β
    ਪ঑͞Ε͍ͯͳ͍ͷͰ
    RDS → DynamoDB ʹܧଓతʹϨϓϦέʔγϣϯΛͯ͠
    Lambdaઐ༻ͷDynamoDBΛ࡞Δɻ
    28
    

    View Slide

29. ͦͷଞDMSͷओͳ׆༻ࣄྫ
    • લʑճͷதࠃ஍ํDBษڧձͰ
    PostgreSQLͷ
    όʔδϣϯΞοϓΛDMSͰ
    ؤுΔΈ͍ͨͳൃදΛͨ͠
    • https://speakerdeck.com/
    takahashiikki/chugokudb-25-
    session1
    29
    

    View Slide

30. 2. DMS͕ͲΜͳ΋ͷ͔؆୯ʹ͝঺հ ·ͱΊ
    • DMS͸ωοτϫʔΫӽ͠ʹϨϓϦέʔγϣϯΛ࣮ݱ͢ΔαʔϏε.
    • σʔλͷίϐʔݩͱίϐʔઌΛࢦఆͯ͠
    ҠߦϧʔϧΛ࡞੒͢ΔࣄͰσʔλͷҠߦ͸
    શͯDMSͷ੹຿Ͱ࣮ݱͯ͘͠ΕΔɻ
    • DMS୯ମͱͯ͠͸
    ϨϓϦέʔγϣϯΠϯελϯεͷӡ༻අ͕͔͔Δఔ౓Ͱ
    ҆ՁͰਐΊΔࣄ͕ग़དྷΔɻ
    30
    

    View Slide

31. 3. DMSΛ࢖ͬͨDBϦϑΝΫλϦϯά
    31
    

    View Slide

32. ϦϑΝΫλϦϯάख๏
    32
    

    View Slide

33. ݱঢ়ͷΞʔΩςΫνϟ
    ෳ਺ͷαʔϏε͕ӡ༻͞Ε͓ͯΓ୯ҰͷMySQLͷhogeεΩʔϚ͔ΒσʔλΛࢀর͍ͯ͠Δɻ
    33
    

    View Slide

34. WebͷϦϑΝΫλϦϯά
    APIαʔϏεΛ৽͘͠࡞Γɺ
    ֤ػೳΛݸผͷAPIͱͯ͠࡞੒
    ྫ͑͹
    • ΦϛΧϨձһొ࿥/ࢀরAPI
    • ࠗ׆ύʔςΟΛݕࡧ͢Δػೳ
    • ࠗ׆ύʔςΟͷ༧໿͢ΔAPI
    34
    

    View Slide

35. DBͷϦϑΝΫλϦϯά
    • ϦϑΝΫλϦϯάͨ͠ޙͷDBΛ༻ҙ͠
    DMSΛͰϨϓϦέʔγϣϯ͢Δɻ
    • ϨϓϦέʔγϣϯ͞ΕͨλΠϛϯάͰ
    DBͷτϦΨʔΛൃಈͤ͞Δ
    • AuroraͷҠߦઌΛPostgreSQLʹͨ͠ͷ
    ͸
    PostgreSQLͷํ͕τϦΨʔ͕ॊೈͳҝ
    • τϦΨʔ಺ͰچσʔλΛՃ޻͠
    ϦϑΝΫλϦϯάͨ͠ςʔϒϧͷ
    ܗࣜʹ߹͏Α͏ʹొ࿥͢Δɻ
    35
    

    View Slide

36. ϦϑΝΫλϦϯά
    • Web͸গͮͭ͠APIܦ༝Ͱ
    ࢀরɾॻࠐ͢ΔΑ͏ʹมߋΛ
    ਐΊΔɻ
    (WebͷϦϑΝΫλϦϯά)
    • API͸ॻࠐ͸MySQLଆ΁ߦ͍ɺ
    ಡࠐ͸PostgreSQLଆ͔Βߦ͏ɻ
    (DBͷϦϑΝΫλϦϯά)
    • WebଆΛશͯAPIܦ༝ʹग़དྷͨΒ
    APIͷॻࠐ΋PostgreSQLଆ΁มߋ͢
    Δɻ
    36
    

    View Slide

37. View Slide

38. ͜ͷϦϑΝΫλϦϯάํ๏ͷϝϦοτ
    • ߋ৽API͸MySQL΁ॻࠐΛ͢ΔͷͰগͮͭ͠APIܦ༝ʹͯ͠΋
    طଘαʔϏεͷςʔϒϧͷࢀরՕॴʹӨڹ͕ແ͍ɻ
    • ҰՕॴAPIܦ༝ʹͯ͠ΈΔ → ໰୊͕͋Ε͹ௐ੔͢Δɻˠ ...
    ͳͲͷΑ͏ʹ ΧφϦΞϦϦʔε ܗࣜͰ
    গͮͭ͠վળΛਐΊ͍͚ͯΔɻ
    • σʔλͷಡࠐ͸PostgreSQL͔Βߦ͏ͷͰ
    ॻࠐAPIΛݺͿଆ͸ͲͪΒʹॻ͖ࠐΜͰ͍Δ͔Λ
    ؾʹ͠ͳͯ͘΋ྑ͍ɻ
    38
    

    View Slide

39. ͜ͷϦϑΝΫλϦϯάํ๏ͷσϝϦοτ
    • ϩʔΧϧͷ։ൃ؀ڥΛ࡞੒͢Δࣄ͕ग़དྷͳ͍ɻ
    ※ ຊ౰ਏ͍ɻ͍͍ײ͡ͷແ͍Ͱ͔͢Ͷɾɾʁ
    • DMS͕ࢭ·ͬͨ࣌ʹࢀরAPI͕ίέΔɻ
    ※ τϦΨʔΤϥʔ౳ͰDMS͕ࢭ·ΔͱαʔϏε͕ࢭ·Δɻ
    • DMS͕஗Ԇͨ࣌͠ͷߟྀ͕೉͍͠ɻ
    ※ ࠓͷॴɺக໋ই͸ͳ͍ɻ(ই͕ແ͍ͱ͸ݴͬͯͳ͍)
    • ίϯϑϦΫτ͢Δࣄ͕ݒ೦͞ΕΔͷͰϦʔυϨϓϦΧʹ·ͰτϦΨʔΛషΕͳ͍͕
    ϦʔυϨϓϦΧ͕Ϛελʔʹঢ֨͢ΔͱαʔϏε͕ࢭ·Δɻ
    • ͦͷଞ৭Μͳҋ͸࠙਌ձͰʂ
    Suddenly Kigenzen ͱ͔ God Unknown Datetime ͱ͔ɻ
    39
    

    View Slide

40. 3. DMSΛ࢖ͬͨDBϦϑΝΫλϦϯά ·ͱΊ
    • AWS DMSͰ MySQL͔ΒPostgreSQLʹ
    ϨϓϦέʔγϣϯ͍ͯ͠Δɻ
    • DMS͕Ҡߦͨ͠λΠϛϯάͰઃఆͨ͠τϦΨʔ͕ൃಈͯ͠
    ϦϑΝΫλϦϯά͞ΕͨςʔϒϧʹॻࠐΛ͢Δɻ
    • ߋ৽ɾࢀরΛAPIܦ༝ʹ͢ΔࣄͰطଘͷ։ൃΛਐΊͭͭ
    ϦϑΝΫλϦϯάͨ͠DBʹࢀরΛ੾Γସ͍͚͍͑ͯͯΔɻ
    • ϩʔΧϧͷ؀ڥߏங͕೉͍͠ͷͱɺDMS͕ࢭ·ͬͨ࣌ʹ
    ࢀরAPI͕ίέΔͷͰߟྀ͕೉͍͠ɻ
    40
    

    View Slide

41. 4. PostgreSQLͷ͓΋͠Ζ͍ॴ
    41
    

    View Slide

42. JPUGதࠃࢧ෦௕ͷδϯΫε
    • தࠃࢧ෦௕͸ۀ຿ͰPostgtrSQLΛ࢖͑ͳ͍
    • લ৬͸ SQLServerɾMySQL ͩͬͨɻ
    • ۤઅ5೥ɾɾPostgreSQLΛॳΊͯۀ຿Ͱ৮ͬͨࣗ෼͕ײͨ͡
    PostgreSQLͷ໘ന͍ॴɺ໾ཱͬͨॴΛ͝঺հ
    ※ APIΛDjangoͰॻ͍ͯΔͷͰ
    DjangoͷDatasetͷίϯςΩετ͕ଟগؚ·Ε·͢ɻ
    ※ ΞϓϦέʔγϣϯΤϯδχΞ͔Βͷࢹ఺͕
    ଟ෼ʹؚ·Ε·͢ɻ
    42
    

    View Slide

43. PostgreSQLΛ࢖༻͢Δ͚ͩͰ
    ಘΒΕΔ࢓૊Έ
    43
    

    View Slide

44. PostgreSQLΛ࢖༻͢Δ͚ͩͰಘΒΕΔ࢓૊Έ
    JOINͷΞϧΰϦζϜ
    MySQLͱPostgreSQLͰ࠾༻͞Ε͍ͯΔ
    ݁߹ॲཧͷΞϧΰϦζϜʹҧ͍͕͋ΔҟͳΔɻ
    MySQLͰ͸ Nested Loop JOINͷΈ͚ͩͲɺ
    PostgreSQLͰ͸
    Nested Loop JOINɺHash JOINɺMerge JOINͷ
    ࡾछྨ͕αϙʔτ͞Ε͍ͯΔɻ
    44
    

    View Slide

45. ݁߹ϊʔυͷҧ͍
    Nested Loop Join
    • ۦಈද(JOINݩ)Λ1ߦຖʹ಺෦ද
    (JOINઌ)ͷ
    શϨίʔυͱ෇͖߹Θͤͯ֘౰
    ͷ΋ͷΛ݁߹͍ͯ͘͠ɻ
    45
    

    View Slide

46. ݁߹ϊʔυͷҧ͍
    Hash Join
    • ಺෦දͷ݁߹Ωʔͷ
    ϋογϡϦετΛ࡞੒ͯ͠
    ͦͷޙɺۦಈදͱ݁߹Λߦ͏ɻ
    46
    

    View Slide

47. ݁߹ϊʔυͷҧ͍
    Merge Join
    • ۦಈදͱ಺෦දΛ݁߹ΩʔͰ
    ιʔτͨ͠ޙͰ
    ॱ൪ʹ෇͖߹Θͤͯ݁߹͍ͯ͠
    ͘ɻ
    47
    

    View Slide

48. JOINʹΑΔϝϦοτ
    • JOIN͕σʔλ͕খ͍͞΋ͷಉ࢜ʹݶΒͳ͍ɻ
    Ͳͷ৔߹Ͱ΋࠷దͳJOINΞϧΰϦζϜ͕
    PostgreSQLଆͷ੹຿Ͱબ୒͞ΕΔɻ
    ͦΕͳΓͷ଎౓࠷దԽ͕ݟࠐΊΔɻ
    48
    

    View Slide

49. PostgreSQLΛ࢖༻͢Δ͚ͩͰಘΒΕΔ࢓૊Έ
    ύϥϨϧΫΤϦ
    ΫΤϦ໰͍߹ΘͤΛ
    ෳ਺CPUʹͯฒྻͰूܭͯ݁͠ՌΛฦ͢࢓૊Έɻ
    49
    

    View Slide

50. ύϥϨϧΫΤϦͷαϙʔτ
    PostgreSQLͷόʔδϣϯΛ্͛Δ͚ͩͰαϙʔτ͕૿͑Δɻ
    ʢόʔδϣϯΞοϓ͚ͩͰ଎͘ͳΔʣ
    • Seq Scan(9.6͔Β)
    • Index Scan(10.0͔Β)
    • ֤JOIN
    ʢNLJͱHash JOIN͸9.6͔ΒɺMerge JOIN ͸10.0͔Βʣ
    • UNIONͱ͔
    50
    

    View Slide

51. ύϥϨϧΫΤϦͷϝϦοτ
    ฒྻͰ࣮ߦ͞ΕΔͷͰCPUϘτϧωοΫͷ
    ΫΤϦ໰͍߹Θͤͷ଎౓վળ͕ظ଴͞ΕΔ.
    ྫ͑͹ ΠϯσοΫε͕షΓ੾Εͳ͍ɺ
    ෳࡶͳݕࡧʹ͓͍ͯ΋ͦΕͳΓͷ
    ύϑΥʔϚϯε͕ग़Δ
    ʢҰൠʹ ̎ʙ̐ഒͱ͔ݴΘΕΔʣ
    51
    

    View Slide

52. PostgreSQLͷαϙʔτߏจ
    (MySQLൺ)
    52
    

    View Slide

53. PostgreSQLͷαϙʔτߏจ (MySQLൺ)
    Returning۟
    PostgreSQLͷಠ֦ࣗுͰɺINSERTɾUPDATEɾ
    DELETEͷ݁ՌΛฦ͢ࣄ͕ग़དྷΔػೳ.
    ྫ͑͹ DELETE ~ WHERE ~; ͳͲͷෳ਺݅DELETE΍
    INSERT INTO ~ SELECT~ ͷෳ਺݅INSERT΋
    αϙʔτ͍ͯ͠Δɻ
    53
    

    View Slide

54. Returning۟
    ྫ͑͹ɺ (id͸serialܕ (auto increment) )
    INSERT INTO users (email, password)
    VALUES ('test@exapmple.jp', 'password')
    RETURNING id, email, password;
    ͱॻ͘ͱ ૠೖ݁Ռ͕ฦͬͯ͘Δɻ
    MySQLͷLAST_INSER_ID()ΑΓ΋ڧྗ.
    54
    

    View Slide

55. Կ͕خ͍͔͠
    • ΋͘͠͸ϦΧόϦͷ࣌ʹҎԼͷΑ͏ʹ༧Ίର৅ΛνΣο
    Ϋग़དྷͨΓͨ͠.
    BEGIN;
    -- ↓ ෳࡶͳ৚݅Ͱ࡟আΛ͢Δ.
    DELETE FROM party WHERE date < '2019-02-25'
    RETURNING id, date;
    COMMIT;
    55
    

    View Slide

56. Returning۟ͷϝϦοτ
    DjangoͰInsertର৅ͷ֤ΦϒδΣΫτΛ࡞ͬͨޙʹ
    PostgreSQLʹෳ਺݅ҰׅͰBulk Insert͢Δͱ
    ໭Γ஋ͷΦϒδΣΫτͷ഑ྻ͸શͯૠೖલͷΦϒδΣΫτʹ
    idϓϩύςΟͷ஋͕ηοτ͞Εͨঢ়ଶͰฦͬͯ͘ΔͷͰ
    SQLΛ౤͛Δճ਺͕100ճͱ͔1000ճͱ͔ͷ୯ҐͰݮͬͨɻ
    ݁Ռɺόον଎౓͕௒޲্ ͨ͠ɻ
    56
    

    View Slide

57. parties = [
    {'title': 'ύʔςΟʔ1', date: '2019೥06݄10೔'},
    {'title': 'ύʔςΟʔ2', date: '2019೥06݄11೔'},
    {'title': 'ύʔςΟʔ3', date: '2019೥06݄12೔'},
    ]
    inserted_parties = Party.objects.bulk_create(parties)
    # ↓ ͜͏ͳΔ
    [
    {'id': 1, 'title': 'ύʔςΟʔ1', date: '2019೥06݄10೔'},
    {'id': 2, 'title': 'ύʔςΟʔ2', date: '2019೥06݄11೔'},
    {'id': 3, 'title': 'ύʔςΟʔ3', date: '2019೥06݄12೔'},
    ]
    57
    

    View Slide

58. PostgreSQLͷαϙʔτߏจ (MySQLൺ)
    Materialized View
    ͦʔ͍ͩ͞Μ͕େ޷͖ͳౕɻ
    ςʔϒϧͷΑ͏ʹʮ݁ՌΛอ࣋ʯͨ͠Viewɻ
    58
    

    View Slide

59. ྫ͑͹ҎԼͷΑ͏ʹఆٛ͢Δͱ
    ఆٛͨ͠λΠϛϯάͰͷσʔλ͕ active_users ʹ
    ֨ೲ͞ΕΔ.
    CREATE MATERIALIZED VIEW active_users AS
    SELECT id
    , name
    , gender
    FROM users
    WHERE delete_flag = 0;
    59
    

    View Slide

60. ఆٛͨ͠ Materialized View͸௨ৗͷςʔϒϧͷΑ͏ʹ
    ΞΫηε͕ՄೳͱͳΔɻ
    -- SELECT͸΋ͪΖΜͷ͜ͱ
    SELECT *
    FROM active_users;
    -- JOIN΋Մೳ
    SELECT *
    FROM reserves
    INNER JOIN active_users
    ON reserves.user_id = active_users.id;
    60
    

    View Slide

61. ͦͷଞʹ΋IndexΛషΕͨΓ͢Δ.
    CREATE MATERIALIZED VIEW active_users AS
    SELECT id
    , name
    , gender
    , created_at
    FROM users
    WHERE delete_flag = 0;
    CREATE INDEX active_users_idx1
    ON active_users (created_at);
    61
    

    View Slide

62. Materialized ViewͷϝϦοτ
    ݟͯ΋Βͬͯͷ௨Γ Ωϟογϡతͳ໾ׂΛߦ͏ͷͰ
    ύϑΥʔϚϯεͷ޲্͕ظ଴ग़དྷΔɻ
    ಛʹෳࡶͳΫΤϦूܭΛߦ͏৔߹ͱ͔͸༧Ί
    ϚςϏϡʔʹ͓ͯ͘͜͠ͱͰ
    ௨ৗͷϏϡʔͷaliasͱͯ͠ͷଆ໘ͷ
    Ըܙ΋ड͚Δ͜ͱ͕ग़དྷΔɻ
    62
    

    View Slide

63. Materialized ViewͷσϝϦοτ
    ͱ͸͍͑ɺ͋͘·Ͱ ʮΩϟογϡʯ ͳͷͰ
    ϦΞϧλΠϜੑͷ͋Δ΋ͷ౳ʹ͸޲͔ͳ͍ɻ
    ߋ৽͢Δʹ͸
    MATERIALIZED VIEW active_users;
    Λ͠ͳ͍ͱ͍͚ͳ͍͕ શσʔλΛߋ৽͢ΔͷͰ
    ॏ͍Query͸REFRESHͷ౓ʹྲྀΕͯ͠·͏ɻ
    ʢPostgreSQLʹ͸ϚςϏϡʔͷ෦෼ߋ৽ͷػೳ͸ͳ͍ʣ
    63
    

    View Slide

64. ͦͷଞ
    • CUBEߏจʢখܭ͕औΕΔʣ
    • WITH۟
    ʢαϒΫΤϦʹผ໊Λ͚ͭΔΈ͍ͨͳౕʢޠኮྗʣʣ
    64
    

    View Slide

65. 4. PostgreSQLͷ͓΋͠Ζ͍ॴ ·ͱΊ
    • JOINαϙʔτͩͬͨΓύϥϨϧΫΤϦͳͲ
    όʔδϣϯΞοϓͨ͠ΓPostgreSQLΛ
    ࢖༻͢Δ͚ͩͰಘΒΕΔԸܙ͕͋ͬͨɻ
    • Materialized View͸ࠔͬͨ࣌ʹ
    ॿ͚ͯ͘ΕΔɻ·͞ʹຑༀɻɻɻ
    65
    

    View Slide

66. ͓ΘΓʹ
    σʔλϕʔεͷϦϑΝΫλϦϯάΛ
    ௕ظͰ΍͍ͬͯΔɻ
    ࠓճͷख๏͸σʔλϕʔεϦϑΝΫλϦϯάͱ͍͏
    ໊ஶʹऔΓ্͛ΒΕ͍ͯΔख๏ͷ͋Δछɺݱ୅൛ʢͱ
    ࢥ͍ͬͯΔʣɻ
    66
    

    View Slide

67. ͓ΘΓʹ
    1೥ऑ૸͖͚ͬͯͨͲ...ҋ͸ͨ͘͞Μ͋Δɻ
    ࠷ऴϩάΠϯ೔͕࣌ لݩલʹͳ͍ͬͯΔਓ΋͍ͨ͠
    ύʔςΟʔ͕Կނ͔ਂ໷2͔࣌Β։࠵͞ΕΔΈ͍ͨͳ
    ͜ͱ΋͋Δɻɻ
    ͱ͸͍͑ɺϨδΣϯυίʔυ ͱ͍͏͔
    ࠓ·Ͱͷച্Λࢧ͑ͯ͘Εͨେ੾ͳίʔυͰ͋Δ͜ͱ͸
    ͪΌΜͱཧղ͍͔ͯ͠ͳ͍ͱ͍͚ͳ͍ͱࢥ͏ɻ
    ·͞ʹίʔυΛ଀ΜͰਓΛ଀·ͣɻ
    67
    

    View Slide

68. ͓ΘΓʹ
    ࠓճɺPostgreSQLʹͳͬͨࣄͰ
    ඇৗʹۀ຿Ͱ࢖͑ΔPostgreSQLͷ஌͕ࣝ૿͑ͨɻ
    ΠϯϑϥΤϯδχΞͱͯ͠ۀ຿ͯ͠ͳ͍ͷͰ
    ͋·ΓRDBͷػೳʹ໨Λ޲͚Δࣄ͕ແ͔͚ͬͨͲ
    RDBͷػೳΛ஌͍ͬͯΔࣄʹΑΓ
    ઃܭྗ͕େ෼ண͍ͨɻ
    ࠓճͷൃද͕Կ͔఻ΘΔ΋ͷ͕͋Ε͹͍͍ͳɻ
    68
    

    View Slide

69. ΑΓྑ͍RDBϥΠϑΛʂ
    69
    

    View Slide

70. ࠂ஌
    70
    

    View Slide

71. Πϕϯτࠂ஌
    • ୈ28ճ தࠃ஍ํDBษڧձ in Ԭࢁ
    • ͪΐͬͱ࣌ظ͕ະఆɾɾ
    71
    

    View Slide

72. Πϕϯτࠂ஌
    • Φʔϓϯηϛφʔ2019@Ԭࢁ
    • 2019೥06݄29೔ (౔)
    • ࠓճͷςʔϚ͸ʮTechnology X Societyʯ
    ࠓ೥ͷΦʔϓϯηϛφʔԬࢁͰ͸ɺςΫϊϩδʔͰࣾձͱؔΘΓʹ͓
    ͍ͯϢχʔΫͳൃ૝΍࢖໋Λ͓࣋ͪͷํͨͪͷ͓࿩Λ͓ಧ͚͠·͢ɻ
    ਃ͠ࠐΈ: https://okayama.open-seminar.org/
    ʢΦϛΧϨ͸ΰʔϧυεϙϯαʔʂʣ
    72
    

    View Slide

73. Πϕϯτࠂ஌
    • JAWS-UG Ԭࢁ 2019
    • 2019೥08݄17೔ (౔)
    https://jaws-ug-okayama.doorkeeper.jp/events/
    91487
    73
    

    View Slide

74. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·
    ͨ͠ʂʂ
    74
    

    View Slide