Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

F891bc57aad1b6bdbf344358e7fec3cc?s=128

Takahashi Ikki

January 20, 2019
Tweet

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. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ