$30 off During Our Annual Pro Sale. View Details »

HowDoYouLikePostgreSQl12/Chugokudb28-1

 HowDoYouLikePostgreSQl12/Chugokudb28-1

wip

Takahashi Ikki

January 25, 2020
Tweet

More Decks by Takahashi Ikki

Other Decks in Programming

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