HowDoYouLikePostgreSQl12/Chugokudb28-1

wip

Transcript

1. How do you like PostgreSQL12
   2020-01-25
   ୈ28ճ தࠃ஍ํDBษڧձ in Ԭࢁ
   ೔ຊPostgreSQLϢʔβʔձ தࠃ஍ํࢧ෦௕
   ߴڮɹҰٍ
   1
   

   View Slide

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

   View Slide

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

   View Slide

4. 1. ࣗݾ঺հ
   • ߴڮɹҰٍ
   • Ԭࢁࡏॅ
   • גࣜձࣾΦϛΧϨ
   WebΞϓϦέʔγϣϯΤϯδχΞ
   • ೔ຊPostgreSQLϢʔβʔձ
   தࠃࢧ෦௕
   4
   

   View Slide

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

   View Slide

6. 2. PostgreSQLͱ͸
   6
   

   View Slide

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

   View Slide

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

   View Slide

9. SQLඪ४ʹ४ڌͨ͠ߏจͷαϙʔτ
   • SQLඪ४
   ϝʔΧʔ΍γεςϜʹΑͬͯ࢓༷͕େ͖͘ҟͳͬͯͨɻ
   ඪ४ԽΛٻΊΔ੠͕ଟ͘ू·ͬͨɻ
   • ISO/IEC 9075 ͱͯ͠ࠃࡍඪ४Խ͞Εͨɻ
   • Check੍໿ͱ͔Windowؔ਺ͱ͔ɻ
   ʢ8.0·ͰMySQLʹ͸ͳ͔ͬͨΜ΍ɾɾʣ
   9
   

   View Slide

10. ෳ਺छྨͷIndexछผͷαϙʔτ
    • B-Tree Index
    • Hash Index
    • GiST Index, SP-GiST Index , GIN Index (શจݕࡧ)
    • BRIN Index
    10
    

    View Slide

11. ๛෋ͳσʔλܕͷαϙʔτ
    • ྻྻڍʢEnumʣ
    • زԿσʔλܕ
    • ࠲ඪ఺ɺ௚ઢɺԁ
    • IPΞυϨεܕ (IPv4, IPv6)
    • 192.168.10.5, 192.168.11.1 Ͳ͕ͬͪେ͖͍͔ൺֱग़དྷΔ.
    11
    

    View Slide

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

    View Slide

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

    View Slide

14. 3. PostgreSQL12ͷ৽ػೳ
    14
    

    View Slide

15. 3. PostgreSQL12ͷ৽ػೳ
    • ੜ੒ྻͷαϙʔτ
    • JSON PATH ͷαϙʔτ
    • pg_dumpͷڧԽ
    • ύʔςΟγϣϯɾςʔϒϧͷڧԽ
    • Access Method
    15
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

21. ੜ੒ྻ
    ੜ੒ྻͷ੍໿
    • ෆมͳ஋Λฦؔ͢਺͔͠࢖͑ͳ͍
    • ͦͷଞͷੜ੒ྻͷ஋Λࢀর͢Δࣄ͸ग़དྷͳ͍
    • ੜ੒ྻ͸ύʔςΟγϣϯΩʔʹࢦఆ͢Δࣄ͸ग़དྷͳ͍
    • ͳͲ
    ... age(timestamp) ͸immutableͳؔ਺͡Όͳ͍.
    ʢͦ΋ͦ΋ɺINSERT࣌ʹ஋Λੜ੒࣮ͯ͠ମͱͯ͠σʔλΛอ࣋͢Δ࢓૊Έ.ʣ
    21
    

    View Slide

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

    View Slide

23. ੜ੒ྻ
    • ॻ͖׵ΘΔλΠϛϯά͸ INSERT/UPDATE ͷ࣌ɻ
    • ͪͳΈʹ౾஌ࣝͱͯ͠...
    SELECTͷ౓ʹܭࢉͯ͘͠ΕΔ࢓૊Έ΋ଞͷRDBMSʹ͋Δ
    • Ծ૝ྻ ͱݺ͹ΕΔ
    • GENERATED ALWAYS AS ( ) STORED Λ
    GENERATED ALWAYS AS ( ) VIRTUAL ͱॻ͚͹Ծ૝ྻʢଞRDBMSͷ৔߹ʣ
    • ߏจ΋ࣅͯΔͷͰ͍͔ͭ͸࣮૷͞Εͦ͏ʢئ๬
    23
    

    View Slide

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
    

    View Slide

25. JSON PATH ͷαϙʔτ
    25
    

    View Slide

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

    View Slide

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

    View Slide

28. JSONܕͱJSONBܕͷҧ͍
    JSONܕ JSONBܕ
    ֨ೲํࣜ ςΩετ όΠφϦʢෆཁͳۭന΍
    ॏෳkeyΛ࡟আͳͲʣ
    ΠϯσοΫε Btree Btree, GINΠϯσοΫε
    ݕࡧੑೳ Ұൠతʹ௿଎ɺධՁͷ౓
    ʹ಺෦Ͱύʔαʔ͕ಈ͘
    GINʹΑΓޮ཰తʹݕࡧ
    Մೳ
    28
    

    View Slide

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
    

    View Slide

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
    

    View Slide

31. • JSONܕͱJSONBܕͷҧ͍ (WHERE)
    EXPLAIN ANALYZE SELECT * FROM member WHERE profile->>'name' = 'ߴڮ';
    JSONܕ
    JSONBܕ
    • ͲͪΒ΋ಉ͡Ϩίʔυ͕ฦΔ.
    31
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

36. JSON PATHͷྫ
    JSON PATH આ໌
    $.store.book[*].title bookͷதͷtitleΛऔಘ
    $..price શΤϨϝϯτͷத͔Βprice
    Λ࠶ؼతʹऔಘ
    $..book[2] bookΛ࠶ؼతʹऔಘͨ͠ޙ
    bookͷ഑ྻͷ3൪໨Λऔಘ
    $..book.length() bookΛ࠶ؼతʹऔಘͨ͠ޙ
    bookͷ഑ྻͷཁૉ਺Λऔಘ
    36
    

    View Slide

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
    

    View Slide

38. σϞ
    38
    

    View Slide

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

    View Slide

40. pg_dumpͷڧԽ
    40
    

    View Slide

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

    View Slide

42. pg_dumpͷڧԽ
    CREATE TABLE option_user
    (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
    );
    42
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

46. pg_dumpͷڧԽ
    • όοΫΞοϓͷ࣌ʹ BulkInsertʹରԠग़དྷΔΑ͏ʹͳͬͨ.
    • ౰વɺ௨ৗͷInsertΑΓBulkInsertͷํ͕଎͍ɻ
    • ࢖͍Ͳ͖͕͋·Γු͔ΜͰͳ͍ɻɻ
    • ຖ೔ৗʹόοΫΞοϓΛςεταʔόʔʹ෮ݩ͢ΔΈ͍ͨͳϢʔεέʔεͰ
    InsertจΛՃ޻ͯ͠σʔλϚεΫ͢ΔɺΈ͍ͨͳͦ͏͍͏ײ͡ʁ
    • ଎౓͕ϘτϧωοΫͱ͔ࢥͬͯͨΒ͜ΕΛ࢖༻͢Δͱଟগ৭ʑͰ͖Δɻ
    • ޙɺON CONFLICT DO NOTHING Λ͚ͭΔ͜ͱͰ༧ΊσʔλΛߜΒͳͯ͘΋ྑ͍Έͨͳཚ๫ͳࣄ΋
    Ͱ͖ͳ͍͜ͱ΋ͳ͍ɻ
    ※ ࠓճͷόʔδϣϯΞοϓͰ COPYจʹWHERE͚۟ͭΔࣄ͕Ͱ͖ΔΑ͏ʹɾɾ
    46
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

53. PostgreSQL9.6·ͰͷύʔςΟγϣχϯά
    • ਌ςʔϒϧʹ൓ө
    CREATE TRIGGER japan_pref_insert_trigger
    BEFORE INSERT ON japan_cities
    FOR EACH ROW EXECUTE PROCEDURE pref_partion();
    53
    

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

60. ύʔςΟγϣϯɾςʔϒϧͷڧԽ
    CREATE TABLE sale_1 PARTITION OF sale
    FOR VALUES FROM (CURRENT_DATE::timestamp) TO (CURRENT_DATE::timestamp + '1 year');
    CURRENT_DATE::timestamp ͩͱ౎౓ύʔςΟγϣϯΩʔ͕มΘΔ
    ͷͰ͸
    !
    ʁ
    60
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

68. Access Method
    68
    

    View Slide

69. Access Method
    ͜Ε͸ࠓޙͷਐԽͷҝͷ෍ੴ..!
    • Pluggable Table Storage Interface
    • ಠࣗͷετϨʔδΤϯδϯʢAccess Method) ΛఆٛͰ͖ΔΑ͏ʹͳͬͨ
    • ݱࡏ͸σϑΥϧτʢैདྷʣͷHeapͷΈ
    • MySQL Ͱ͍͏MyISAMͱInnoDBΈ͍ͨͳ΋ͷ
    • Vacuum͕͍Βͳ͍ετϨʔδΤϯδϯͱ͔.
    • PG13Ҏ߱Ͱ࣮૷༧ఆͱͷࣄΒ͍͠
    69
    

    View Slide

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
    

    View Slide

71. Access Method
    71
    

    View Slide

72. Access Method
    72
    

    View Slide

73. Access Method
    73
    

    View Slide

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

    View Slide

75. 4. PostgreSQL13ʹظ଴͞ΕΔػೳ
    • DROP DATABASE db_name WITH (FORCE) ͷαϙʔτ
    • εϩʔΫΤϦϩάͷ཈੍ػೳ
    • \set PROMPT2 '%w' ͷαϙʔτ
    • Incremental Materialized View (CFத)
    75
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

82. Incremental Materialized View
    82
    

    View Slide

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

    View Slide

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

    View Slide

85. 5. ·ͱΊ
    85
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

91. ࠂ஌
    • Ԭࢁ Ruby, Ruby on Railsษڧձ
    • 2020೥02݄29೔(౔)
    • https://okaruby.connpass.com/event/
    161062/
    91
    

    View Slide

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
    

    View Slide

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

    View Slide