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

Transcript

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

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

3. 1. ͋͹͏ͱΈʔ

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

   தࠃࢧ෦௕

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

6. 2. PostgreSQLͷ഑ྻܕ

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

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

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

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

    varchar(16)[] )

11. ഑ྻܕͷྫ INSERT INTO blogs (title, content, tags) VALUES ('΋͘΋͘ձ', '໌೔͸΋͘΋͘ձͰ͢',

    '{ "ios", "android" , "PostgreSQL" }') title | contents | tags ------------+----------------------+-------------------------- ΋͘΋͘ձ | ໌೔͸΋͘΋͘ձͰ͢ | {ios,android,PostgreSQL} (1 row)

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

    )

13. ഑ྻܕͷݕࡧʹ͍ͭͯ 'ios' λά͕͋Δ΋ͷΛݕࡧ. SELECT * FROM blogs WHERE 'ios' =

    ANY(tags); title | contents | tags ------------+----------------------+-------------------------- ΋͘΋͘ձ | ໌೔͸΋͘΋͘ձͰ͢ | {ios,android,PostgreSQL} (1 row) ios λά ͕͍ͭͯΔ ͔ͭ android λά͕͍ͭͯΔ Έ͍ͨͳݕࡧ΋Ͱ͖Δɻ

14. ഑ྻܕͷऔಘʹ͍ͭͯ SELECT tags[1] AS tag FROM blogs; tag ---------- ios

    (1 row) ͦͷ΄͔ʹ΋ tags[2:3] : ഑ྻͷ 2ʙ3൪໨Λऔಘ tags[2:] : ഑ྻͷ 2൪໨Ҏ߱Λऔಘ tags[:2] : ഑ྻͷ 2൪໨ҎԼΛऔಘ ※ 0͔Β։࢝͠ͳ͍ࣄʹதࢭ

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)

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

17. 3. ·ͱΊ

18. 3. ·ͱΊ ࠓճ͸ɺPostgreSQLͷ഑ྻܕͷ঺հΛ͠·ͨ͠ɻ ͱͯ΋ڧྗͳ࢓૊ΈͰ͕͢΋ͪΖΜϝϦοτɾσϝϦοτ͕͋Γɺ ඞͣ͠΋ۜͷ஄ؙͱ͍͏Θ͚Ͱ͸ͳ͍Ͱ͢ɻ ͳͷͰɺunnest() ΍ array_agg() Λ࢖ͬͯ ߦ

    <=> ഑ྻؒΛ૬ޓม׵͢ΔͳͲɺ ϝϦοτɾσϝϦοτΛڗडͰ͖ΔΑ͏ͳܗͰ ࢖͍ͬͯ͘ͷ͕ྑ͍͔ͱࢥ͍·͢ʂ

19. 4. ࠂ஌ தࠃ஍ํDBษڧձ 2019-02-02 (౔) ୈ26ճ தࠃ஍ํDBษڧձ in Ԭࢁ https://dbstudychugoku.connpass.com/event/112468/

    SQLνϡʔχϯά(EXPLAIN)ͷํ๏΍ύϥϝʔλνϡʔχϯάΛ औΓ্͛·͢ɻ ઈࢍLTൃදऀΛืूதͰ͢ʂʂ

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