$30 off During Our Annual Pro Sale. View Details »

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

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

# 概要

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

# 対象者

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

Takahashi Ikki

May 18, 2019
Tweet

More Decks by Takahashi Ikki

Other Decks in Programming

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. 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 ('[email protected]', '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