生誕20周年を迎えたPostgreSQLを使ってみよう

Slide 1

Slide 1 text

ੜ஀20प೥Λܴ͑ͨ PostgreSQLΛ࢖ͬͯΈΑ͏ ΦʔϓϯιʔεΧϯϑΝϨϯεˏ౦ژ

Slide 2

Slide 2 text

What is it? PostgreSQL͸ࠓ೥Ͱ஀ੜ͔Βॕ20೥ʂ

Slide 3

Slide 3 text

ܥ࣌୅ ෭໰߹ͤ 1-5DM 1-QH42- 7JFX ϚϧνόΠτจࣈྻαϙʔτ .7$$ Ұ࣌ද $"4& */5&34&$5 &9$&15 ܥ࣌୅ ֎෦Ωʔ੍໿ 8"- 50"45 065&3+0*/ ίϯΧϨϯτ7"$66. 1-1ZUIPO εΩʔϚ υϝΠϯ 13&1"3& *1W JOGPSNBUJPO@TDIFNB ܥ࣌୅ .JDSPTPGU8JOEPXTରԠ 4"7&10*/5 1*53 දྖҬ ૬ίϛοτ 30-& ߦڞ༗ϩοΫ ςʔϒϧɾύʔςΟγϣχϯά ΢ΥʔϜελϯόΠ (*/ ߋ৽ॲཧੑೳͷ޲্ 9.-σʔλܕ શจݕࡧ +*49 &/6.ܕ 66*%ܕ ࠶ؼΫΤϦ ΢Οϯυ΢ؔ਺ ྻ୯ҐͷΞΫηε੍ޚ 42-ͱؔ਺ͷੑೳղੳػೳ

Slide 4

Slide 4 text

ܥ࣌୅ ˞ͷ࣍͸ͰܾఆࡁΈ ܥ࣌୅ ݱࡏ ϨϓϦέʔγϣϯ Ұׅݖݶมߋ ಗ໊ϓϩγʔδϟ CJU8JOEPXTαϙʔτ Ҡಈฏۉ ྻ৚݅τϦΨ Ұҙੑ੍໿ͷ஗Ԇ ഉଞ੍໿ ಉظϨϓϦέʔγϣϯ ֎෦ςʔϒϧ ύοέʔδ؅ཧ 6/-0((&%ςʔϒϧ ߋ৽Մೳͳ8*5)۟ ۙ๣ݕࡧ 4&-JOVYݖݶ੍ޚ ΠϯσοΫεΦϯϦʔεΩϟϯ ΧεέʔυϨϓϦέʔγϣϯ +40/ܕ ൣғܕ ϚςϦΞϥΠζυϏϡʔ ֎෦ςʔϒϧ΁ͷॻ͖ग़͠ ΠϕϯττϦΨ σʔλϖʔδɾνΣοΫαϜ -"5&3"-۟ +40/#ܕ 42-͔Βͷαʔόʔઃఆͷมߋ ϨϓϦέʔγϣϯεϩοτ 614&35ػೳ ϒϩοΫϨϯδΠϯσοΫε ߦ୯ҐηΩϡϦςΟ੍ޚίϚϯυ ύϥϨϧΫΤϦ ෳ਺ಉ࣌ελϯόΠ શจݕࡧͷϑϨʔζରԠ $01:จͷSFUVSOJOH۟ରԠ

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

What is it? • 6ܥ ඪ४SQLʹ४ڌ͠ɺMVCCͳͲͷRDBMSͱͯ͠ͷ  جຊతͳػೳΛ࣮૷ • 7ܥ ੍໿΍WALΛखʹೖΕͯACIDΛຬͨ͢ • 8ܥ VACUUM΍HOTͳͲͷӡ༻ʹ  ௚݁͢ΔύϑΥʔϚϯεվળ • 9ܥ ϨϓϦέʔγϣϯ΍OLTPɾDWH྆ํͷ໘Ͱੑೳ޲্

Slide 9

Slide 9 text

What is it? ਐԽ͠ଓ͚ΔPostgreSQL

Slide 10

Slide 10 text

What is it? ਐԽ͠ଓ͚ΔPostgreSQL ↓ ͦΜͳPostgreSQLΛબͿཧ༝

Slide 11

Slide 11 text

What is it? ࢖͍ͨ͘ͳΔPostgreSQLͷັྗ Λօ༷ʹ఻͍͑ͨͱࢥ͍·͢

Slide 12

Slide 12 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ࠷৽ࣄ৘ ̏ɹPostgreSQLͱଞDB ̐ɹPostgreSQL΁ͷҠߦ ̑ɹ·ͱΊ

Slide 13

Slide 13 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ࠷৽ࣄ৘ ̏ɹPostgreSQLͱଞDB ̐ɹPostgreSQL΁ͷҠߦ ̑ɹ·ͱΊ

Slide 14

Slide 14 text

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Slide 15

Slide 15 text

Slide 16

Slide 16 text

தࠃ஍ํDBษڧձ https://dbstudychugoku.github.io/

Slide 17

Slide 17 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ࠷৽ࣄ৘ ̏ɹPostgreSQLͱଞDB ̐ɹPostgreSQL΁ͷҠߦ ̑ɹ·ͱΊ

Slide 18

Slide 18 text

PostgreSQLͷ࠷৽ࣄ৘ ·ͣ1PTUHSF42-͓͞Β͍ ʢ஌Βͳ͍ਓͷͨΊʹʣ

Slide 19

Slide 19 text

PostgreSQLͷ࠷৽ࣄ৘

Slide 20

Slide 20 text

PostgreSQLͷ࠷৽ࣄ৘ 1PTUHSF42-ͷ˓˓͕͍͢͝

Slide 21

Slide 21 text

PostgreSQLͷ࠷৽ࣄ৘ 1PTUHSF42-ͷ˓˓͕͍͢͝ w 42-ͷߏจ͕๛෋ w αʔυύʔςΟͷॆ࣮͕ੌ͍ w ֦ுػೳ͕ੌ͍

Slide 22

Slide 22 text

PostgreSQLͷ࠷৽ࣄ৘ ࠓͷҰ൪ͷྲྀߦΓ͸ίϨ

Slide 23

Slide 23 text

PostgreSQLͷ࠷৽ࣄ৘ 1PTUHSF42-ͷσʔλ෼ੳ͕͍͢͝

Slide 24

Slide 24 text

PostgreSQLͱσʔλ෼ੳ ࢛ଇԋࢉͱ͔ ػցֶशͱ͔ ܗଶૉղੳͱ͔ Պֶܭࢉͱ͔

Slide 25

Slide 25 text

PostgreSQLͱσʔλ෼ੳ 1ZUIPO͸σʔλ෼ੳʹڧ͍

Slide 26

Slide 26 text

PostgreSQLͱσʔλ෼ੳ 1PTUHSF42-͸1-1ZUIPO͕͋Δ

Slide 27

Slide 27 text

Slide 28

Slide 28 text

PostgreSQLͱσʔλ෼ੳ ͔ͳΓલ͔Βඪ४Ͱ αϙʔτ͞Ε͍ͯΔ

Slide 29

Slide 29 text

PostgreSQLͱσʔλ෼ੳ ͦͯ͠1-1ZUIPO಺Ͱ ௚઀-JCSBSZΛݺ΂Δ

Slide 30

Slide 30 text

PostgreSQLͱσʔλ෼ੳ ͔͠΋͔ΒύϥϨϧΫΤϦରԠʂ

Slide 31

Slide 31 text

PostgreSQLͱσʔλ෼ੳ ͭ·Γ1ZUIPOͰετΞυॻ͍ͯ 42-Ͱؔ਺ݺͿ͚ͩͰσʔλ෼ੳʂ

Slide 32

Slide 32 text

PostgreSQLͱσʔλ෼ੳ ໪࿦બ΂ΔλΠϓ 1ZUIPOPS1ZUIPO

Slide 33

Slide 33 text

PostgreSQLͱσʔλ෼ੳ σʔλ෼ੳͰ1ZUIPOΛ ࢖Θͳ͍ཧ༝͸ͳ͍

Slide 34

Slide 34 text

PostgreSQLͱσʔλ෼ੳ ྫ͑͹ʜ w සग़͢ΔϫʔυΛௐ΂ɺྲྀߦΛ࡯஌͢Δ w ϒϩάʹཁ໿ػೳΛ௥Ճ͢Δ w ػցֶशΛར༻ͯ͠ະདྷΛ༧૝͢Δ

Slide 35

Slide 35 text

PostgreSQLͱσʔλ෼ੳ ετΞυͰݺ΂Δ1ZUIPO͕ݺ΂ΔϝϦοτ wΞϓϦέʔγϣϯଆͷίʔυ͕γϯϓϧʹͳΔ wσʔλΛ௚઀ݺ΂ΔͨΊɺωοτϫʔΫͷϘτϧωοΫΛ ղফग़དྷΔ wύϥϨϧΫΤϦͱซ༻͢ΔࣄͰϝχʔίΞΛ׆͔ͨ͠  ूܭ͕Ͱ͖Δ wΞυϗοΫͳΫΤϦͱ૊Έ߹Θ͢ࣄͰॊೈͰਝ଎ʹ  ෼ੳग़དྷΔ

Slide 36

Slide 36 text

PostgreSQLͱσʔλ෼ੳ σʔλ෼ੳ͸τϥΠΞϯυΤϥʔ ˣ ؔ਺Λ༻ҙ͢Δ͜ͱͰ ϩδοΫͷ࠶ར༻͕͠΍͍͢

Slide 37

Slide 37 text

PostgreSQLͱσʔλ෼ੳ σϞ

Slide 38

Slide 38 text

PostgreSQLͱσʔλ෼ੳ σʔλ෼ੳ͸τϥΠΞϯυΤϥʔ

Slide 39

Slide 39 text

PostgreSQLͱσʔλ෼ੳ σʔλ෼ੳ͸τϥΠΞϯυΤϥʔ ˣ ؔ਺Λ༻ҙ͢Δ͜ͱͰ ϩδοΫͷ࠶ར༻͕͠΍͍͢

Slide 40

Slide 40 text

PostgreSQLͱσʔλ෼ੳ σʔλ෼ੳ͸τϥΠΞϯυΤϥʔ ˣ ؔ਺Λ༻ҙ͢Δ͜ͱͰ ϩδοΫͷ࠶ར༻͕͠΍͍͢ ֬ఆͨ͠σʔλ෼ੳ಺༰͸,1*ͱ ͯ͠ॳΊͯΞϓϦʹ͢Ε͹͍͍

Slide 41

Slide 41 text

PostgreSQLͷ࠷৽ࣄ৘ ଞʹ΋࣌୅ΛઌऔΓ͢Δػೳ͕ຬࡌ w *1WରԠ w '%8 ֎෦%#࿈ܞ w &YUFOTJPO ֦ு

Slide 42

Slide 42 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ࠷৽ࣄ৘ ̏ɹPostgreSQLͱଞDB ̐ɹPostgreSQL΁ͷҠߦ ̑ɹ·ͱΊ

Slide 43

Slide 43 text

PostgresͱଞDB ঎༻%#ͱ1PTUHSF42-

Slide 44

Slide 44 text

PostgresͱଞDB ঎༻%# w 0SBDMF%# w 42-4FSWFS w %#

Slide 45

Slide 45 text

PostgresͱଞDB 3%#ͷجຊతͳ෦෼Ͱ͸ ௥͍͍͖ͭͯͨ

Slide 46

Slide 46 text

PostgresͱଞDB w ύϥϨϧΫΤϦ w 42-ߏจ w όοΫΞοϓ΍৑௕Խ w શจݕࡧͳͲͷ֦ு

Slide 47

Slide 47 text

PostgresͱଞDB ·ͩ·ͩ঎༻%#ʹ ෛ͚͍ͯΔ෦෼΋͋Δ

Slide 48

Slide 48 text

PostgresͱଞDB w (6*্Ͱͷૢ࡞ w ӡ༻ܥͷػೳ w ؂ࠪܥͷػೳ w ྻࢦ޲*/%&9΍ࣗಈߋ৽ϚςϏϡʔ  ͷΑ͏ͳ%8)ͷػೳ

Slide 49

Slide 49 text

PostgresͱଞDB ٯʹ঎༻%#ʹউͬͯΔ෦෼΋͋Δ

Slide 50

Slide 50 text

PostgresͱଞDB w ϥΠηϯεͷࣗ༝౓ w +40/#ܕͳͲͷ๛෋ͳܕ w ֦ுੑͷߴ͞ w 8"'ͱͷ૬ੑͷྑ͞

Slide 51

Slide 51 text

PostgresͱଞDB σϞ

Slide 52

Slide 52 text

PostgresͱଞDB 1PTUHSF42-ͱ044%#

Slide 53

Slide 53 text

PostgresͱଞDB 044%# w .Z42- w 42-JUF w 'JSFCJSE

Slide 54

Slide 54 text

PostgresͱଞDB ॅΈ෼͚͕ਐΜͰ͖ͨ ˣ ͦΕͧΕͷ։ൃํ਑ͷҧ͍

Slide 55

Slide 55 text

PostgresͱଞDB 044%# w .Z42- w 42-JUF w 'JSFCJSE

Slide 56

Slide 56 text

PostgresͱଞDB 044%# w .Z42- w 42-JUF w 'JSFCJSE

Slide 57

Slide 57 text

PostgresͱଞDB 044%# w .Z42- w 42-JUF w 'JSFCJSE

Slide 58

Slide 58 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ࠷৽ࣄ৘ ̏ɹPostgreSQLͱଞDB ̐ɹPostgreSQL΁ͷҠߦ ̑ɹ·ͱΊ

Slide 59

Slide 59 text

PostgreSQL΁ͷҠߦ 0SBDMF%#͔Β1PTUHSF42-

Slide 60

Slide 60 text

PostgreSQL΁ͷҠߦ 4PGUXBSF%FTJHO݄߸ΛಡΜͰ

Slide 61

Slide 61 text

PostgreSQL΁ͷҠߦ ݁࿦

Slide 62

Slide 62 text

PostgreSQL΁ͷҠߦ 0SBDMF4&͔Β͸େମ͍͚Δ

Slide 63

Slide 63 text

PostgreSQL΁ͷҠߦ .Z42-΋େମ͍͚Δ

Slide 64

Slide 64 text

PostgreSQL΁ͷҠߦ ঎༻%#ͷߴౕ͍͸೉͍͠

Slide 65

Slide 65 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ࠷৽ࣄ৘ ̏ɹPostgreSQLͱଞDB ̐ɹPostgreSQL΁ͷҠߦ ̑ɹ·ͱΊ