アプリの実装に役立つBackendとしてのPostgreSQL

2019-01-20 の 岡山モバイルアプリ開発もくもく会 での発表資料です。

Transcript

1. ΞϓϦͷ࣮૷ʹ໾ཱͭ
   PostgreSQL
   2019-01-20 ୈ30ճϞόΠϧΞϓϦ։ൃ΋͘΋͘ձ
   

   View Slide

2. ͓͠ͳ͕͖
   1.͋͹͏ͱΈʔ
   2.PostgreSQLͷ഑ྻܕ
   3.·ͱΊ
   

   View Slide

3. 1. ͋͹͏ͱΈʔ
   

   View Slide

4. 1. ͋͹͏ͱΈʔ
   • ߴڮ Ұٍ
   • ΦϛΧϨ WebΤϯδχΞ
   • ೔ຊPostgreSQLϢʔβʔձ
   தࠃࢧ෦௕
   

   View Slide

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

   View Slide

6. 2. PostgreSQLͷ഑ྻܕ
   

   View Slide

7. ׂͱ͋Γ͕ͪͳΞϓϦ
   ͷཁ݅
   ಥવͰ͕͢ɺօ͞Μ
   ͜ΕɺͲ͏΍࣮ͬͯ૷͠·͢ʁ
   

   View Slide

8. λά࣮૷ׂͱਏ͗͢໰୊
   • ొ࿥͸؆୯ɻ
   • ໰୊͸ฤूͱݕࡧ͕஍ࠈɻ
   

   View Slide

9. 1ΧϥϜͷςΩετܕʹcsv۠੾ΓͰೖΕΔ
   ߋ৽ਏ͍ɻݕࡧਏ͍ɻ (δΣΠ΢ΥʔΫ)
   tag1, tag2, tag3,... ͬͯ΍Δɻ
   tagΛ3͔ͭ͠αϙʔτ͠ͳ͍ʁ
   

   View Slide

10. ͦ͜Ͱ഑ྻܕͰ͢Αɻ
    CREATE TABLE blogs (
    title varchar(64),
    content text,
    tags varchar(16)[]
    )
    

    View Slide

11. ഑ྻܕͷྫ
    INSERT INTO blogs
    (title, content, tags)
    VALUES
    ('΋͘΋͘ձ', '໌೔͸΋͘΋͘ձͰ͢', '{ "ios", "android" , "PostgreSQL" }')
    title | contents | tags
    ------------+----------------------+--------------------------
    ΋͘΋͘ձ | ໌೔͸΋͘΋͘ձͰ͢ | {ios,android,PostgreSQL}
    (1 row)
    

    View Slide

12. ഑ྻܕͷϝϦοτ
    • ΠϯσοΫε͕ޮ͘ɻ
    • ݕࡧΛ͢Δҝͷԋࢉࢠ΍ϝιουͷαϙʔτ͕๛෋ɻ
    • औಘޙ͸֤ݴޠͷ഑ྻͱͯ͠࢖༻Ͱ͖Δ ( Djangoͱ͔͸ͦ
    ͏. )
    

    View Slide

13. ഑ྻܕͷݕࡧʹ͍ͭͯ
    'ios' λά͕͋Δ΋ͷΛݕࡧ.
    SELECT * FROM blogs WHERE 'ios' = ANY(tags);
    title | contents | tags
    ------------+----------------------+--------------------------
    ΋͘΋͘ձ | ໌೔͸΋͘΋͘ձͰ͢ | {ios,android,PostgreSQL}
    (1 row)
    ios λά ͕͍ͭͯΔ ͔ͭ android λά͕͍ͭͯΔ
    Έ͍ͨͳݕࡧ΋Ͱ͖Δɻ
    

    View Slide

14. ഑ྻܕͷऔಘʹ͍ͭͯ
    SELECT tags[1] AS tag FROM blogs;
    tag
    ----------
    ios
    (1 row)
    ͦͷ΄͔ʹ΋
    tags[2:3] : ഑ྻͷ 2ʙ3൪໨Λऔಘ
    tags[2:] : ഑ྻͷ 2൪໨Ҏ߱Λऔಘ
    tags[:2] : ഑ྻͷ 2൪໨ҎԼΛऔಘ
    ※ 0͔Β։࢝͠ͳ͍ࣄʹதࢭ
    

    View Slide

15. ͦͷଞʹ΋ָʹཁ݅Λ
    ຬͨͤΔ
    SELECT unnest(tags)
    , count(*)
    FROM blogs
    GROUP BY unnest(tags)
    ORDER BY count(*) DESC;
    unnest | count
    ------------+-------
    database | 2
    ios | 1
    PostgreSQL | 1
    android | 1
    (4 rows)
    

    View Slide

16. ഑ྻܕͷσϝϦοτ
    • ֎෦Ωʔ੍໿͕షΕͳ͍ɻ
    => ஋ͷ੔߹ੑΛอূ͢Δͷ͕೉͍͠ɻ
    => λάݕࡧͳͲͷϑϦʔςΩετͷσʔλΛอ࣋͢Δͷʹ
    ద͍ͯ͠Δɻ
    

    View Slide

17. 3. ·ͱΊ
    

    View Slide

18. 3. ·ͱΊ
    ࠓճ͸ɺPostgreSQLͷ഑ྻܕͷ঺հΛ͠·ͨ͠ɻ
    ͱͯ΋ڧྗͳ࢓૊ΈͰ͕͢΋ͪΖΜϝϦοτɾσϝϦοτ͕͋Γɺ
    ඞͣ͠΋ۜͷ஄ؙͱ͍͏Θ͚Ͱ͸ͳ͍Ͱ͢ɻ
    ͳͷͰɺunnest() ΍ array_agg() Λ࢖ͬͯ
    ߦ <=> ഑ྻؒΛ૬ޓม׵͢ΔͳͲɺ
    ϝϦοτɾσϝϦοτΛڗडͰ͖ΔΑ͏ͳܗͰ
    ࢖͍ͬͯ͘ͷ͕ྑ͍͔ͱࢥ͍·͢ʂ
    

    View Slide

19. 4. ࠂ஌
    தࠃ஍ํDBษڧձ
    2019-02-02 (౔) ୈ26ճ தࠃ஍ํDBษڧձ in Ԭࢁ
    https://dbstudychugoku.connpass.com/event/112468/
    SQLνϡʔχϯά(EXPLAIN)ͷํ๏΍ύϥϝʔλνϡʔχϯάΛ
    औΓ্͛·͢ɻ
    ઈࢍLTൃදऀΛืूதͰ͢ʂʂ
    

    View Slide

20. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ
    

    View Slide