HowDoYouLikePostgreSQl12/Chugokudb28-1

Transcript

1. How do you like PostgreSQL12 2020-01-25 ୈ28ճ தࠃ஍ํDBษڧձ in Ԭࢁ

   ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํࢧ෦௕ ߴڮɹҰٍ 1

2. ஫ҙࣄ߲ • εϥΠυ͸ެ։͍ͯ͠·͢ɻ • ࣭໰ͱ͔͋Ε͹ɺηογϣϯதʹԕྀͳ͘ฉ͍͍ͯͩ͘͞ʂ • ฉ͘ͷ͸ͪΐͬͱɾɾͬͯਓ͕͍Ε͹ #ChugokuDB ΁͓ئ͍க ͠·͢ʂ

   2

3. ͓͠ͳ͕͖ 1. ࣗݾ঺հ 2. PostgreSQLͱ͸ 3. PostgreSQL12ͷ৽ػೳ 4. PostgreSQL13ʹظ଴͞ΕΔػೳ 5.

   ·ͱΊ 3

4. 1. ࣗݾ঺հ • ߴڮɹҰٍ • Ԭࢁࡏॅ • גࣜձࣾΦϛΧϨ WebΞϓϦέʔγϣϯΤϯδχΞ •

   ೔ຊPostgreSQLϢʔβʔձ தࠃࢧ෦௕ 4

5. גࣜձࣾΦϛΧϨ • શࠃͷࠗ׆ύʔςΟʔ ໿30,000݅Λܝࡌͯ͠Δ ϙʔλϧαΠτ. • ग़ձ͍͕0ΛZeroʹ͢Δ ΛVisionʹ೔ʑ׆ಈͯ͠·͢ • ΤϯδχΞʢΠϯϑϥͷਓʣΛ

   ืूͯ͠·͢ʂ 5

6. 2. PostgreSQLͱ͸ 6

7. 2. PostgreSQLͱ͸ • PostgreSQLʢΆ͢ͱ͙Ε͖͢Ύʔ͑Δʣ ୅දతͳΦʔϓϯιʔεͷRDBMSͷ1ͭ ݩʑɺେֶͷݚڀ༻ʹ։ൃ͞Εͨݚڀ༻ͷRDBMSͷ Ingres͕ݩͱͳ͍ͬͯΔɻ ݚڀ༻ʹར༻͞Ε͍ͯͨࣄ΋͋Γɺ ཧ࿦ʹ஧࣮ʹ։ൃ͞Ε͍ͯΔɻ 7

8. 2. PostgreSQLͱ͸ • SQLඪ४ʹ४ڌͨ͠ߏจͷαϙʔτ • ෳ਺छྨͷIndexछผͷαϙʔτ • ๛෋ͳσʔλܕͷαϙʔτ 8

9. SQLඪ४ʹ४ڌͨ͠ߏจͷαϙʔτ • SQLඪ४ ϝʔΧʔ΍γεςϜʹΑͬͯ࢓༷͕େ͖͘ҟͳͬͯͨɻ ඪ४ԽΛٻΊΔ੠͕ଟ͘ू·ͬͨɻ • ISO/IEC 9075 ͱͯ͠ࠃࡍඪ४Խ͞Εͨɻ •

   Check੍໿ͱ͔Windowؔ਺ͱ͔ɻ ʢ8.0·ͰMySQLʹ͸ͳ͔ͬͨΜ΍ɾɾʣ 9

10. ෳ਺छྨͷIndexछผͷαϙʔτ • B-Tree Index • Hash Index • GiST Index,

    SP-GiST Index , GIN Index (શจݕࡧ) • BRIN Index 10

11. ๛෋ͳσʔλܕͷαϙʔτ • ྻྻڍʢEnumʣ • زԿσʔλܕ • ࠲ඪ఺ɺ௚ઢɺԁ • IPΞυϨεܕ (IPv4,

    IPv6) • 192.168.10.5, 192.168.11.1 Ͳ͕ͬͪେ͖͍͔ൺֱग़དྷΔ. 11

12. όʔδϣϯΞοϓཤྺ • ໿1೥αΠΫϧͰϝδϟʔόʔδϣϯΞοϓ͕ߦΘΕ͍ͯΔɻ • 9.6 ·Ͱ͸ x.y.z ͱ͍͏όʔδϣϯܗࣜͩͬͨ • x.y

    : ϝδϟʔόʔδϣϯ • z : ϚΠφʔόʔδϣϯ • 10ΑΓ x.z ͱ͍͏όʔδϣϯܗࣜʹͳͬͨɻ 12

13. ͱ͍͏ॴͰɾɾɾ PostgreSQL12 ࠓ೔ͷ͓࿩Ͱ͢ʂʂʂ 13

14. 3. PostgreSQL12ͷ৽ػೳ 14

15. 3. PostgreSQL12ͷ৽ػೳ • ੜ੒ྻͷαϙʔτ • JSON PATH ͷαϙʔτ • pg_dumpͷڧԽ

    • ύʔςΟγϣϯɾςʔϒϧͷڧԽ • Access Method 15

16. ੜ੒ྻ(GENERATEDྻ)ͷαϙʔτ 16

17. ੜ੒ྻͷαϙʔτ • ੜ੒ྻ͸ɺςʔϒϧʹରͯ͠ܭࢉ݁ՌΛݩʹͨ͠ྻΛఆٛ͢Δ. CREATE TABLE member ( id SERIAL NOT

    NULL CONSTRAINT member_pkey PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, full_name VARCHAR(50) NOT NULL GENERATED ALWAYS AS ( first_name || ' ' || last_name) STORED ); • full_name͸ first_name ͱ ۭന ͱ last_name Λ จࣈྻ݁߹ͨ͠΋ͷʹͳΔɻ 17

18. ੜ੒ྻ ࣮ࡍʹಈ͔ͯ͠ΈΔͱ͜Μͳײ͡ chugokudb=# INSERT INTO member (first_name, last_name) VALUES ('ߴڮ',

    'Ұٍ'); INSERT 0 1 chugokudb=# chugokudb=# SELECT * FROM member; id | first_name | last_name | full_name ----+------------+-----------+----------- 1 | ߴڮ | Ұٍ | ߴڮ Ұٍ (1 row) 18

19. ੜ੒ྻ • Insert͸ෆՄ. chugokudb=# INSERT INTO member (first_name, last_name, full_name)

    chugokudb-# VALUES ('ߴڮ', 'Ұٍ', 'ߴڮ Ұٍ'); ERROR: cannot insert into column "full_name" DETAIL: Column "full_name" is a generated column. • DB2 => ஋Λ౉ͤ͹ͦͷ஋ɺ ౉͞ͳ͔ͬͨΒੜ੒ྻ Έ͍ͨͳౕ΋͋Δ. 19

20. ʮ͓ɺ΍ͬͨʂ ͜ΕͰ஀ੜ೔͔Β೥ྸΛৗʹܭࢉग़དྷΔ΍Μʂʯ 20

21. ੜ੒ྻ ੜ੒ྻͷ੍໿ • ෆมͳ஋Λฦؔ͢਺͔͠࢖͑ͳ͍ • ͦͷଞͷੜ੒ྻͷ஋Λࢀর͢Δࣄ͸ग़དྷͳ͍ • ੜ੒ྻ͸ύʔςΟγϣϯΩʔʹࢦఆ͢Δࣄ͸ग़དྷͳ͍ • ͳͲ

    ... age(timestamp) ͸immutableͳؔ਺͡Όͳ͍. ʢͦ΋ͦ΋ɺINSERT࣌ʹ஋Λੜ੒࣮ͯ͠ମͱͯ͠σʔλΛอ࣋͢Δ࢓૊Έ.ʣ 21

22. ͭ·Γɺͣͬͱ ϋλν 22

23. ੜ੒ྻ • ॻ͖׵ΘΔλΠϛϯά͸ INSERT/UPDATE ͷ࣌ɻ • ͪͳΈʹ౾஌ࣝͱͯ͠... SELECTͷ౓ʹܭࢉͯ͘͠ΕΔ࢓૊Έ΋ଞͷRDBMSʹ͋Δ • Ծ૝ྻ

    ͱݺ͹ΕΔ • GENERATED ALWAYS AS ( ) STORED Λ GENERATED ALWAYS AS ( ) VIRTUAL ͱॻ͚͹Ծ૝ྻʢଞRDBMSͷ৔߹ʣ • ߏจ΋ࣅͯΔͷͰ͍͔ͭ͸࣮૷͞Εͦ͏ʢئ๬ 23

24. ੜ੒ྻͷαϙʔτ Կ͕خ͍͔͠ - σʔλͱͯ͠͸ බࣈɺ໊લ ͸෼͚͍͚ͯ࣋ͪͨͲ ࢀরͱͯ͠͸ බࣈ+໊લͰ࢖͍͍ͨɻ - MySQLͰ

    ٙࣅCheck੍໿ͱͯ͠࢖͏Tips͕͋ͬͨɻ CREATE TABLE users ( id SERIAL NOT NULL CONSTRAINT users_pkey PRIMARY KEY, gender integer NOT NULL, gender_text NOT NULL VARCHAR(2) GENERATED ALWAYS AS (CASE WHEN (gender = 0) then 'உੑ' WHEN (gender = 1) then 'ঁੑ' ELSE NULL END) STORED ); 24

25. JSON PATH ͷαϙʔτ 25

26. JSON PATH ͷαϙʔτ PostgreSQLͰ͸2012೥ͷv9.2͔ΒJSONܕΛαϙʔτ ֤όʔδϣϯຖʹJSONܕ΁ͷαϙʔτΛڧԽ͍ͯͬͨ͠ • ࠓճ͸SQL2016ඪ४Ͱఏএ͞Εͨ SQL/JSONʹؔ͢ΔҰ෦ͷؔ਺Λఏڙ • ݕࡧΛΑΓָʹ.

    26

27. PostgreSQLͱJSONͷาΈ • PostgreSQLͰ͸v9.2ʢ2012೥ʣ͔ΒJSONܕΛαϙʔτ • v9.4ʢ2014೥ʣͰ͸JSONBܕΛαϙʔτ GINΠϯσοΫε΋αϙʔτ։࢝ • v9.3ɺv9.5...v11Ͱ͸ؔ਺΍ԋࢉࢠͷڧԽ ྫ͑͹ JSONܕΛύʔεͯ͠Ϩίʔυʹม׵͢Δؔ਺ͱ͔

    27

28. JSONܕͱJSONBܕͷҧ͍ JSONܕ JSONBܕ ֨ೲํࣜ ςΩετ όΠφϦʢෆཁͳۭന΍ ॏෳkeyΛ࡟আͳͲʣ ΠϯσοΫε Btree Btree,

    GINΠϯσοΫε ݕࡧੑೳ Ұൠతʹ௿଎ɺධՁͷ౓ ʹ಺෦Ͱύʔαʔ͕ಈ͘ GINʹΑΓޮ཰తʹݕࡧ Մೳ 28

29. • JSONܕͱJSONBܕͷҧ͍ JSONܕ CREATE TABLE member ( id SERIAL NOT

    NULL CONSTRAINT member_pkey PRIMARY KEY, profile JSON NOT NULL ); JSONBܕ CREATE TABLE member ( id SERIAL NOT NULL CONSTRAINT member_pkey PRIMARY KEY, profile JSONB NOT NULL ); 29

30. • JSONܕͱJSONBܕͷҧ͍ (INSERT/SELECT *) INSERT INTO member (profile) VALUES ('{"name":

    "ߴڮ", "gender": "உੑ", "age":"29"}'); JSONܕ id | profile ----+------------------------------------------------ 1 | {"name": "ߴڮ", "gender": "உੑ", "age":"29"} (1 row) JSONBܕ id | profile ----+------------------------------------------------- 1 | {"age": "29", "name": "ߴڮ", "gender": "உੑ"} (1 row) 30

31. • JSONܕͱJSONBܕͷҧ͍ (WHERE) EXPLAIN ANALYZE SELECT * FROM member WHERE

    profile->>'name' = 'ߴڮ'; JSONܕ JSONBܕ • ͲͪΒ΋ಉ͡Ϩίʔυ͕ฦΔ. 31

32. • JSONܕͱJSONBܕͷҧ͍ (WHERE) JSONܕ SELECT * FROM member WHERE profile

    = '{"name": "ߴڮ", "gender": "உੑ", "age":"29"}'::json; ERROR: operator does not exist: json = json JSONBܕ SELECT * FROM member WHERE profile = '{"name": "ߴڮ", "gender": "உੑ", "age":"29"}'::jsonb; id | profile ----+------------------------------------------------- 1 | {"age": "29", "name": "ߴڮ", "gender": "உੑ"} ౳Ձൺֱ͚ͩ͡Όͳͯ͘ ؚΉ ͱ͔΋ݕࡧग़དྷΔ 32

33. • JSONBܕԋࢉࢠ # ݁߹ ɾ SELECT profile || '{"birthday":"1990-12-11"}'::jsonb FROM

    member; # {"age": "29", "name": "ߴڮ", "gender": "உੑ", "birthday": "1990-12-11"} # ࡟আ ɾ SELECT profile - 'name'::jsonb FROM member; # {"age": "29", "gender": "உੑ"} # keyνΣοΫ OR ɾ SELECT id, profile ?| array['name','birthday'] FROM member_b; # true # keyνΣοΫ AND ɾ SELECT profile ?| array['name','birthday'] FROM member; # false 33

34. JSONܕͱJSONBܕͷҧ͍ JSONܕͱJSONBܕɺJSONBܕͷํ͕ྑ͍ɻ - ؔ਺ͷαϙʔτ͕๛෋ ʢߋ৽΍ల։ָ͕ʣ - ΠϯσοΫεʹΑΔݕࡧʹ΋ରԠ͓ͯ͠Γߴ଎ 34

35. JSON PATHͷαϙʔτ • JSONBܕ΁ͷػೳͷ௥Ճ • ஋ͷऔΓग़͠Λָʹ͢Δػߏ 35

36. JSON PATHͷྫ JSON PATH આ໌ $.store.book[*].title bookͷதͷtitleΛऔಘ $..price શΤϨϝϯτͷத͔Βprice Λ࠶ؼతʹऔಘ

    $..book[2] bookΛ࠶ؼతʹऔಘͨ͠ޙ bookͷ഑ྻͷ3൪໨Λऔಘ $..book.length() bookΛ࠶ؼతʹऔಘͨ͠ޙ bookͷ഑ྻͷཁૉ਺Λऔಘ 36

37. JSON PATH ͷྫ • jsonb_path_query: ࢦఆ͞ΕͨJSONB஋ͷJSONPATHʹΑͬͯฦ͞ Εͨ͢΂ͯͷJSON߲໨Λऔಘ • jsonb_path_exists: JSONPATH͕ࢦఆ͞ΕͨJSONB஋ͷ߲໨Λฦ͢

    ͔Ͳ͏͔Λ֬ೝ͢Δ CREATE TABLE books ( id SERIAL NOT NULL CONSTRAINT books_pkey PRIMARY KEY, data JSONB NOT NULL ); 37

38. σϞ 38

39. JSON PATHͷαϙʔτ • JSON PATH ʹΑͬͯ࠶ؼతʹݕࡧ͕ߦΘΕΔͷͰָ. • ીࠜઌੜͷ໊ஶʰࣦഊ͔ΒֶͿRDBͷਖ਼͍͠า͖ํʱʹ΋͋Δ Α͏ʹʲJSONͷ؁͍᠘ʳʹ஫ҙ. ʢ͜͏΍ͬͯͪ΍΄΍͢Δͷ΋ޙҰिؒͰ͢Ͷˑʣ

    • ORM... 39

40. pg_dumpͷڧԽ 40

41. pg_dumpͷڧԽ ʢ͜͜ΒͰগָ͠ͳ΋ͷʣ pg_dump ʹҎԼͷΦϓγϣϯ͕௥Ճ • --rows-per-insert • --on-conflict-do-nothing ্ه͸ͲͪΒ΋ --inserts

    Φϓγϣϯͱಉ༷ʹ࢖༻͢Δ 41

42. pg_dumpͷڧԽ CREATE TABLE option_user ( id SERIAL NOT NULL PRIMARY

    KEY, name VARCHAR(50) NOT NULL ); 42

43. pg_dumpͷڧԽ • pg_dump -U docker -t option_user chugokudb --inserts INSERT

    INTO public.option_user VALUES (1, 'Berte Kliement'); INSERT INTO public.option_user VALUES (2, 'Lula Da Costa'); INSERT INTO public.option_user VALUES (3, 'Rodi Rapport'); INSERT INTO public.option_user VALUES (4, 'Iris Runacres'); INSERT INTO public.option_user VALUES (5, 'Peirce MacPike'); INSERT INTO public.option_user VALUES (6, 'Pat Haresnaip'); INSERT INTO public.option_user VALUES (7, 'Fiann Gascard'); INSERT INTO public.option_user VALUES (8, 'Pauly Cantera'); INSERT INTO public.option_user VALUES (9, 'Leo Drewes'); INSERT INTO public.option_user VALUES (10, 'Joane Clague'); INSERT INTO public.option_user VALUES (11, 'Annabelle Stollberger'); INSERT INTO public.option_user VALUES (12, 'Nichole Andreaccio'); INSERT INTO public.option_user VALUES (13, 'Cahra Gabites'); INSERT INTO public.option_user VALUES (14, 'Zola Laudham'); INSERT INTO public.option_user VALUES (15, 'Tanitansy Lacrouts'); 43

44. pg_dumpͷڧԽ • pg_dump -U docker -t option_user chugokudb --inserts --on-conflict-do-

    nothing INSERT INTO public.option_user VALUES (1, 'Berte Kliement') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (2, 'Lula Da Costa') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (3, 'Rodi Rapport') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (4, 'Iris Runacres') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (5, 'Peirce MacPike') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (6, 'Pat Haresnaip') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (7, 'Fiann Gascard') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (8, 'Pauly Cantera') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (9, 'Leo Drewes') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (10, 'Joane Clague') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (11, 'Annabelle Stollberger') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (12, 'Nichole Andreaccio') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (13, 'Cahra Gabites') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (14, 'Zola Laudham') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (15, 'Tanitansy Lacrouts') ON CONFLICT DO NOTHING; 44

45. pg_dumpͷڧԽ • pg_dump -U docker -t option_user chugokudb --inserts --rows-per-insert=5

    INSERT INTO public.option_user VALUES (1, 'Berte Kliement'), (2, 'Lula Da Costa'), (3, 'Rodi Rapport'), (4, 'Iris Runacres'), (5, 'Peirce MacPike'); INSERT INTO public.option_user VALUES (6, 'Pat Haresnaip'), (7, 'Fiann Gascard'), (8, 'Pauly Cantera'), (9, 'Leo Drewes'), (10, 'Joane Clague'); INSERT INTO public.option_user VALUES (11, 'Annabelle Stollberger'), (12, 'Nichole Andreaccio'), (13, 'Cahra Gabites'), (14, 'Zola Laudham'), (15, 'Tanitansy Lacrouts'); 45

46. pg_dumpͷڧԽ • όοΫΞοϓͷ࣌ʹ BulkInsertʹରԠग़དྷΔΑ͏ʹͳͬͨ. • ౰વɺ௨ৗͷInsertΑΓBulkInsertͷํ͕଎͍ɻ • ࢖͍Ͳ͖͕͋·Γු͔ΜͰͳ͍ɻɻ • ຖ೔ৗʹόοΫΞοϓΛςεταʔόʔʹ෮ݩ͢ΔΈ͍ͨͳϢʔεέʔεͰ

    InsertจΛՃ޻ͯ͠σʔλϚεΫ͢ΔɺΈ͍ͨͳͦ͏͍͏ײ͡ʁ • ଎౓͕ϘτϧωοΫͱ͔ࢥͬͯͨΒ͜ΕΛ࢖༻͢Δͱଟগ৭ʑͰ͖Δɻ • ޙɺON CONFLICT DO NOTHING Λ͚ͭΔ͜ͱͰ༧ΊσʔλΛߜΒͳͯ͘΋ྑ͍Έͨͳཚ๫ͳࣄ΋ Ͱ͖ͳ͍͜ͱ΋ͳ͍ɻ ※ ࠓճͷόʔδϣϯΞοϓͰ COPYจʹWHERE͚۟ͭΔࣄ͕Ͱ͖ΔΑ͏ʹɾɾ 46

47. ύʔςΟγϣϯɾςʔϒϧͷڧԽ 47

48. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ύʔςΟγϣϯɾΩʔͷFOR VALUES۟Ͱݻఆ஋͚ͩͰͳ͘ ܭࢉͨ͠஋΋ࢦఆՄೳʹ • ֎෦Ωʔͷࢀরઌͱͯ͠ύʔςΟγϣϯɾςʔϒϧΛࢦఆՄೳ ʹ • ؔ਺ͰύʔςΟγϣϯɾςʔϒϧͷπϦʔߏ଄ΛදࣔՄೳʹ

    48

49. ύʔςΟγϣϯɾςʔϒϧͷาΈ PostgreSQL10 • ωΠςΟϒɾύʔςΟγϣχϯά͕αϙʔτ։࢝ PostgreSQL11 • ਌ςʔϒϧʹΠϯσοΫεΛઃఆ͢Δͱࢠςʔϒϧʹ΋ੜ੒͞ΕΔ • Ͳͷ৚݅ʹ΋߹க͠ͳ͍σʔλΛ อଘ͢ΔσϑΥϧτύʔςΟγϣϯΛαϙʔτ

    • ϋογϡύʔςΟγϣϯΛαϙʔτ 49

50. ύʔςΟγϣϯɾςʔϒϧͷาΈ PostgreSQL9.6·Ͱɻɻɻ ௒ؤுͬͯ ύʔςΟγϣχϯάΛ࣮ݱ͍ͯͨ͠ɻ 50

51. PostgreSQL9.6·ͰͷύʔςΟγϣχϯά • ਌ςʔϒϧΛఆٛ CREATE TABLE japan_cities ( pref VARCHAR(10), city

    VARCHAR(25), UNIQUE(pref, city) ); • ࢠςʔϒϧΛఆٛ CREATE TABLE okayama_cities (CHECK (pref IN ('Ԭࢁ'))) INHERITS (japan_cities); CREATE TABLE hiroshima_cities (CHECK (pref IN ('޿ౡ'))) INHERITS (japan_cities); 51

52. PostgreSQL9.6·ͰͷύʔςΟγϣχϯά • τϦΨʔΛఆٛ CREATE OR REPLACE FUNCTION pref_partion() RETURNS TRIGGER

    AS $$ BEGIN IF ( NEW.pref = 'Ԭࢁ') THEN INSERT INTO okayama_cities VALUES (NEW.*); ELSIF ( NEW.pref = '޿ౡ') THEN INSERT INTO hiroshima_cities VALUES (NEW.*); ELSE RAISE EXCEPTION 'ERROR'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; 52

53. PostgreSQL9.6·ͰͷύʔςΟγϣχϯά • ਌ςʔϒϧʹ൓ө CREATE TRIGGER japan_pref_insert_trigger BEFORE INSERT ON japan_cities

    FOR EACH ROW EXECUTE PROCEDURE pref_partion(); 53

54. PostgreSQL9.6·ͰͷύʔςΟγϣχϯά ैདྷͷํ๏ͷσϝϦοτ • τϦΨʔఆٛ໘౗ष͍ • ࢠςʔϒϧ͕૿͑ΔͱτϦΨʔͷΞοϓσʔτ͕ඞཁ • ஗͍ • ORMͱ૬ੑ͕ѱ͍

    54

55. ωΠςΟϒɾύʔςΟγϣχϯά CREATE TABLE japan_cities ( pref VARCHAR(10), city VARCHAR(25), UNIQUE(pref,

    city) ) PARTITION BY LIST (pref); CREATE TABLE okayama_cities PARTITION OF japan_cities FOR VALUES IN ('Ԭࢁ'); CREATE TABLE hiroshima_cities PARTITION OF japan_cities FOR VALUES IN ('޿ౡ'); 55

56. ωΠςΟϒɾύʔςΟγϣχϯά ϝϦοτ • τϦΨʔͷϝϯςφϯε͕ෆཁ • ࢠςʔϒϧͷ௥Ճ΋DDLจͰ׬݁͢Δ • ৼΓ෼͚ͷϧʔϧʹॏෳ͕͋ͬͨΒΤϥʔʹͳΔ • ଎͍ʢैདྷͷ10ഒͱ͔)

    56

57. ωΠςΟϒɾύʔςΟγϣχϯά͕଎͍ཧ༝ • ύʔςΟγϣϯɾϓϧʔχϯά SELECT * FROM japan_cities WHERE pref =

    'Ԭࢁ'; ࣮ߦܭը͸ࢠςʔϒϧͷ ύʔςΟγϣϯΩʔͷ৚݅Λ֬ೝͯ͠ okayama_cities ςʔϒϧΛର৅ʹݕࡧ͢Δ => ແବͷແ͍ݕࡧ͕Մೳʹͳͬͨ ORM͸͋͘·Ͱ japan_cities ʹରͯ͠ΫΤϦΛ౤͛ DBଆͷ੹຿Ͱ෼ࢄΈ͍ͨͳࣄ΋Մೳ 57

58. PostgreSQL12ͰͷύʔςΟγϣϯɾςʔϒϧͷ৽ػೳ • ύʔςΟγϣϯɾΩʔͷFOR VALUES۟Ͱݻఆ஋͚ͩͰͳ͘ ܭࢉͨ͠஋΋ࢦఆՄೳʹ • ֎෦Ωʔͷࢀরઌͱͯ͠ύʔςΟγϣϯɾςʔϒϧΛࢦఆՄೳ ʹ • ؔ਺ͰύʔςΟγϣϯɾςʔϒϧͷπϦʔߏ଄ΛදࣔՄೳʹ

    58

59. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ύʔςΟγϣϯɾΩʔͷFOR VALUES۟Ͱݻఆ஋͚ͩͰͳ͘ ܭࢉͨ͠஋΋ࢦఆՄೳʹ CREATE TABLE sale ( id

    integer not null, sale_data date not null ) PARTITION BY RANGE (sale_data); CREATE TABLE sale_1 PARTITION OF sale FOR VALUES FROM (CURRENT_DATE::timestamp) TO (CURRENT_DATE::timestamp + '1 year'); CREATE TABLE sale_2 PARTITION OF sale FOR VALUES FROM (CURRENT_DATE::timestamp + '1 year') TO (CURRENT_DATE::timestamp + '2 year'); ※ ͜Ε·Ͱ͸ syntax error ͩͬͨɻ 59

60. ύʔςΟγϣϯɾςʔϒϧͷڧԽ CREATE TABLE sale_1 PARTITION OF sale FOR VALUES FROM

    (CURRENT_DATE::timestamp) TO (CURRENT_DATE::timestamp + '1 year'); CURRENT_DATE::timestamp ͩͱ౎౓ύʔςΟγϣϯΩʔ͕มΘΔ ͷͰ͸ ! ʁ 60

61. ύʔςΟγϣϯɾςʔϒϧͷڧԽ FOR VALUES ʹࢦఆͨؔ͠਺ͳͲͷ஋͸ CREATE TABLE࣌ʹҰ౓ͩ ͚ධՁ͞Εɺςʔϒϧఆٛʹ࣮ߦ݁Ռ͕อଘ͞ΕΔɻ \d+ test_sale Partitioned

    table "public.test_sale" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | not null | | plain | | sale_data | date | | not null | | plain | | Partition key: RANGE (sale_data) Partitions: test_sale_1 FOR VALUES FROM ('2020-01-19') TO ('2021-01-19'), test_sale_2 FOR VALUES FROM ('2021-01-19') TO ('2022-01-19') 61

62. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ֎෦Ωʔͷࢀরઌͱͯ͠ύʔςΟγϣϯɾςʔϒϧΛࢦఆՄʹ CREATE TABLE users ( id integer not

    null primary key ) PARTITION BY RANGE (id); CREATE TABLE users_1 PARTITION OF users FOR VALUES FROM (1) TO (100000); CREATE TABLE users_2 PARTITION OF users FOR VALUES FROM (100001) TO (200000); CREATE TABLE user1_profile ( id BIGSERIAL PRIMARY KEY, user_id INTEGER references users_1 (id) deferrable initially deferred, name VARCHAR(25) NOT NULL ); 62

63. ύʔςΟγϣϯɾςʔϒϧͷڧԽ # SELECT * FROM users; id -------- 50 190100

    # INSERT INTO user1_profile (user_id, name) VALUES (50, 'Ϣʔβʔ50'); INSERT 0 1 # INSERT INTO user1_profile (user_id, name) VALUES (190100, 'Ϣʔβʔ190100'); ERROR: insert or update on table "user1_profile" violates foreign key constraint "user1_profile_user_id_fkey" 63

64. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ͜Ε΋࢖͍ॴ͸Α͘ߟ͑ͯ΍Δඞཁ͕͋Δ • ๭ࢯͷஶॻ ୈ̕ষ ڧ͗͢Δ੍໿ ʢ·্͔ͩ࢘ͩΒͶʣʹ΋͋ ΔΑ͏ʹ ଟ༻͢Δͱ

    ύʔςΟγϣϯɾςʔϒϧͷྑ͍ॴΛ˓͠ ͯ͠·͏ɻ 64

65. ύʔςΟγϣϯɾςʔϒϧͷڧԽ 65

66. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ¥dP Ͱ ύʔςΟγϣϯɾςʔϒϧ͕ϦετԽ # \dP List of partitioned

    relations Schema | Name | Owner | Type | Table --------+----------------------------+--------+-------------------+-------------- public | japan_cities | docker | partitioned table | public | test_sale | docker | partitioned table | public | users | docker | partitioned table | public | japan_cities_pref_city_key | docker | partitioned index | japan_cities public | users_pkey | docker | partitioned index | users 66

67. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • pg_partition_tree Ͱ ύʔςΟγϣϯɾςʔϒϧͷ֊૚ΛՄࢹ Խ # SELECT * FROM

    pg_partition_tree('japan_cities'); relid | parentrelid | isleaf | level ------------------+--------------+--------+------- japan_cities | | f | 0 okayama_cities | japan_cities | t | 1 hiroshima_cities | japan_cities | t | 1 67

68. Access Method 68

69. Access Method ͜Ε͸ࠓޙͷਐԽͷҝͷ෍ੴ..! • Pluggable Table Storage Interface • ಠࣗͷετϨʔδΤϯδϯʢAccess

    Method) ΛఆٛͰ͖ΔΑ͏ʹͳͬͨ • ݱࡏ͸σϑΥϧτʢैདྷʣͷHeapͷΈ • MySQL Ͱ͍͏MyISAMͱInnoDBΈ͍ͨͳ΋ͷ • Vacuum͕͍Βͳ͍ετϨʔδΤϯδϯͱ͔. • PG13Ҏ߱Ͱ࣮૷༧ఆͱͷࣄΒ͍͠ 69

70. Access Method ςʔϒϧʹରͯ͠ετϨʔδΤϯδϯΛࢦఆͰ͖ΔΑ͏ʹͳͬ ͨɻ CREATE TABLE test_table ( id integer,

    name varchar(25) ) USING heap; ಉ༷ʹ Materialized View Ͱ΋ࢦఆͰ͖Δ CREATE MATERIALIZED VIEW test_view USING heap AS SELECT * FROM test_table; 70

71. Access Method 71

72. Access Method 72

73. Access Method 73

74. 4. PostgreSQL13ʹظ଴͞ΕΔػೳ 74

75. 4. PostgreSQL13ʹظ଴͞ΕΔػೳ • DROP DATABASE db_name WITH (FORCE) ͷαϙʔτ •

    εϩʔΫΤϦϩάͷ཈੍ػೳ • \set PROMPT2 '%w' ͷαϙʔτ • Incremental Materialized View (CFத) 75

76. DROP DATABASE db_name WITH (FORCE) ͷαϙʔτ 76

77. DROP DATABASE db_name WITH (FORCE) ͷ αϙʔτ • DROP DATABASE

    db_name ͱ͢ΔࣄͰ PostgreSQLʹ͓͚Δ database Λυϩοϓ͢Δࣄ͕Ͱ͖Δ. • ͨͩ͠ɺ͜ͷߏจ͸ active ͳηογϣϯ͕͋ΔͱDropͰ͖ͳ ͔ͬͨ • DROP DATABASE db_name WITH (FORCE) ͱ͢ΔࣄͰ໰౴ແ༻Ͱফ 77

78. εϩʔΫΤϦϩάͷ཈੍ػೳ 78

79. εϩʔΫΤϦϩάͷ཈੍ػೳ • εϩʔΫΤϦͰ஗͍ΫΤϦ͕͋ΔࣄΛௐ΂Δͷ͸େࣄɻ • ൓໘ɺϩά͕ංେԽͨ͠ΓՄಡੑ͕௿Լͨ͠ΓͳΓ͕ͪɻ • ʮεϩʔΫΤϦͷ಺ɺXϛϦඵ௒͑ͨΫΤϦΛҰ෦ϩάʹه࿥ ͢ΔʯΈ͍ͨͳࣄ͕Մೳ 79

80. \set PROMPT2 '%w' ͷαϙʔτ 80

81. \set PROMPT2 '%w' ͷαϙʔτ • ීஈ͸͜Μͳײ͡ chugokudb=# SELECT * chugokudb-#

    FROM member; • ͜Ε·Ͱ͸͜Μͳͷ͸αϙʔτ͋ͬͨɻ chugokudb=# \set PROMPT2 '' chugokudb=# SELECT * FROM member; • ͜͏͍͏ײ͡ʹͳΔ chugokudb=# \set PROMPT2 '%w' chugokudb=# SELECT * FROM member; 81

82. Incremental Materialized View 82

83. Incremental Materialized View • ·ͩೖΔ͔Ͳ͏͔ܾ·ͬͯͳ͍ • Materialized View ͷࠩ෼ߋ৽͕Ͱ͖ΔΑ͏ʹ͢Δػೳ 83

84. 4. PostgreSQL13ʹظ଴͞ΕΔػೳ • ࣍ͷϦϦʔε͸ 2020೥ 10ʙ12݄ࠒɻ 84

85. 5. ·ͱΊ 85

86. "͍͔͕Ͱͨ͠Ͱ͠ΐ͏͔" • PostgreSQL12ͷϦϦʔεϊʔτΛ͝঺հ͠·ͨ͠ • ৽ػೳ ͍ͬͯ͏໨ۄͳ΋ͷͬͯ͋Μ·Γແ͍ҹ৅ • ओʹݩʑ͋ͬͨػೳͷύϫʔΞοϓ • PostgreSQL13΍ͦΕҎ߱ͷόʔδϣϯΞοϓʹظ଴ʂʂ

    • Incremental Materialized View • Pluggable Table Storage Interface ͷରԠ 86

87. 5. ·ͱΊ • PostgreSQLͷ࠷৽Λ௥͏ͷ͕͖͍ͭ • ݩʑ͋ͬͨػೳΛ஌Δͷ͕͖͍ͭ • ͥͻͱ΋ίϛϡχςΟΛ׆༻͍ͯͩ͘͠͞ʂ 87

88. ࠂ஌ • தࠃ஍ํDBษڧձ͸ελοϑΛืू͓ͯ͠Γ·͢ʂ 88

89. ࠂ஌ • Φʔϓϯηϛφʔ2020@޿ౡ • 2020೥02݄08೔(౔) • https://osh.connpass.com/event/ 146828/ 89

90. ࠂ஌ • Okayama-js 2020/02 Ionic΋͘΋͘ձ • 2020೥02݄15೔(౔) • https://okayama-js.connpass.com/ event/161703/

    90

91. ࠂ஌ • Ԭࢁ Ruby, Ruby on Railsษڧձ • 2020೥02݄29೔(౔) •

    https://okaruby.connpass.com/event/ 161062/ 91

92. ࢀߟจݙ • https://github.com/json-path/JsonPath • https://www.postgresql.jp/document/11/html/app-pgdump.html • https://qiita.com/nuko_yokohama/items/82b9960dba3dee830b09 • https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/ pdf/PostgreSQL12GANewFeaturesja20191011-1.pdf

    • https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2019/10/ pg12report1004.pdf • https://anarazel.de/talks/2019-05-30-pgcon-pluggable-table-storage/pluggable.pdf • https://qiita.com/mimitaro/items/40bbdd89fef05374fa89 92

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