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

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

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

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

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

2. PostgreSQLͱ͸ 6

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

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

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

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

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

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

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

3. PostgreSQL12ͷ৽ػೳ 14

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

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

ੜ੒ྻͷαϙʔτ • ੜ੒ྻ͸ɺςʔϒϧʹରͯ͠ܭࢉ݁ՌΛݩʹͨ͠ྻΛఆٛ͢Δ. 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

ੜ੒ྻ ࣮ࡍʹಈ͔ͯ͠ΈΔͱ͜Μͳײ͡ 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

ੜ੒ྻ • 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

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

ͭ·Γɺͣͬͱ ϋλν 22

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

ੜ੒ྻͷαϙʔτ Կ͕خ͍͔͠ - σʔλͱͯ͠͸ බࣈɺ໊લ ͸෼͚͍͚ͯ࣋ͪͨͲ ࢀরͱͯ͠͸ බࣈ+໊લͰ࢖͍͍ͨɻ - 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

JSON PATH ͷαϙʔτ 25

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

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

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

• 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

• 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

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

• 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

• 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

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

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

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

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

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

pg_dumpͷڧԽ 40

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ωΠςΟϒɾύʔςΟγϣχϯά 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

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

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

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

ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ύʔςΟγϣϯɾΩʔͷ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

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

ύʔςΟγϣϯɾςʔϒϧͷڧԽ 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

ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ֎෦Ωʔͷࢀরઌͱͯ͠ύʔςΟγϣϯɾςʔϒϧΛࢦఆՄʹ 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

ύʔςΟγϣϯɾςʔϒϧͷڧԽ # 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

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

ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ¥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

ύʔςΟγϣϯɾςʔϒϧͷڧԽ • 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

Access Method 68

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

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

Access Method 71

Access Method 72

Access Method 73

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

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

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

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

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

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

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

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

Incremental Materialized View 82

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

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

5. ·ͱΊ 85

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

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

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

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

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

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

ࢀߟจݙ • 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