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

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

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

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

Takahashi Ikki

January 20, 2019
Tweet

More Decks by Takahashi Ikki

Other Decks in Programming

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