Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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
  2. ෳ਺छྨͷIndexछผͷαϙʔτ • B-Tree Index • Hash Index • GiST Index,

    SP-GiST Index , GIN Index (શจݕࡧ) • BRIN Index 10
  3. όʔδϣϯΞοϓཤྺ • ໿1೥αΠΫϧͰϝδϟʔόʔδϣϯΞοϓ͕ߦΘΕ͍ͯΔɻ • 9.6 ·Ͱ͸ x.y.z ͱ͍͏όʔδϣϯܗࣜͩͬͨ • x.y

    : ϝδϟʔόʔδϣϯ • z : ϚΠφʔόʔδϣϯ • 10ΑΓ x.z ͱ͍͏όʔδϣϯܗࣜʹͳͬͨɻ 12
  4. ੜ੒ྻͷαϙʔτ • ੜ੒ྻ͸ɺςʔϒϧʹରͯ͠ܭࢉ݁ՌΛݩʹͨ͠ྻΛఆٛ͢Δ. 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
  5. ੜ੒ྻ ࣮ࡍʹಈ͔ͯ͠ΈΔͱ͜Μͳײ͡ 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
  6. ੜ੒ྻ • 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
  7. ੜ੒ྻ ੜ੒ྻͷ੍໿ • ෆมͳ஋Λฦؔ͢਺͔͠࢖͑ͳ͍ • ͦͷଞͷੜ੒ྻͷ஋Λࢀর͢Δࣄ͸ग़དྷͳ͍ • ੜ੒ྻ͸ύʔςΟγϣϯΩʔʹࢦఆ͢Δࣄ͸ग़དྷͳ͍ • ͳͲ

    ... age(timestamp) ͸immutableͳؔ਺͡Όͳ͍. ʢͦ΋ͦ΋ɺINSERT࣌ʹ஋Λੜ੒࣮ͯ͠ମͱͯ͠σʔλΛอ࣋͢Δ࢓૊Έ.ʣ 21
  8. ੜ੒ྻ • ॻ͖׵ΘΔλΠϛϯά͸ INSERT/UPDATE ͷ࣌ɻ • ͪͳΈʹ౾஌ࣝͱͯ͠... SELECTͷ౓ʹܭࢉͯ͘͠ΕΔ࢓૊Έ΋ଞͷRDBMSʹ͋Δ • Ծ૝ྻ

    ͱݺ͹ΕΔ • GENERATED ALWAYS AS ( ) STORED Λ GENERATED ALWAYS AS ( ) VIRTUAL ͱॻ͚͹Ծ૝ྻʢଞRDBMSͷ৔߹ʣ • ߏจ΋ࣅͯΔͷͰ͍͔ͭ͸࣮૷͞Εͦ͏ʢئ๬ 23
  9. ੜ੒ྻͷαϙʔτ Կ͕خ͍͔͠ - σʔλͱͯ͠͸ බࣈɺ໊લ ͸෼͚͍͚ͯ࣋ͪͨͲ ࢀরͱͯ͠͸ බࣈ+໊લͰ࢖͍͍ͨɻ - 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
  10. JSONܕͱJSONBܕͷҧ͍ JSONܕ JSONBܕ ֨ೲํࣜ ςΩετ όΠφϦʢෆཁͳۭന΍ ॏෳkeyΛ࡟আͳͲʣ ΠϯσοΫε Btree Btree,

    GINΠϯσοΫε ݕࡧੑೳ Ұൠతʹ௿଎ɺධՁͷ౓ ʹ಺෦Ͱύʔαʔ͕ಈ͘ GINʹΑΓޮ཰తʹݕࡧ Մೳ 28
  11. • 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
  12. • 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
  13. • JSONܕͱJSONBܕͷҧ͍ (WHERE) EXPLAIN ANALYZE SELECT * FROM member WHERE

    profile->>'name' = 'ߴڮ'; JSONܕ JSONBܕ • ͲͪΒ΋ಉ͡Ϩίʔυ͕ฦΔ. 31
  14. • 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
  15. • 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
  16. JSON PATHͷྫ JSON PATH આ໌ $.store.book[*].title bookͷதͷtitleΛऔಘ $..price શΤϨϝϯτͷத͔Βprice Λ࠶ؼతʹऔಘ

    $..book[2] bookΛ࠶ؼతʹऔಘͨ͠ޙ bookͷ഑ྻͷ3൪໨Λऔಘ $..book.length() bookΛ࠶ؼతʹऔಘͨ͠ޙ bookͷ഑ྻͷཁૉ਺Λऔಘ 36
  17. 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
  18. 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
  19. 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
  20. 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
  21. pg_dumpͷڧԽ • όοΫΞοϓͷ࣌ʹ BulkInsertʹରԠग़དྷΔΑ͏ʹͳͬͨ. • ౰વɺ௨ৗͷInsertΑΓBulkInsertͷํ͕଎͍ɻ • ࢖͍Ͳ͖͕͋·Γු͔ΜͰͳ͍ɻɻ • ຖ೔ৗʹόοΫΞοϓΛςεταʔόʔʹ෮ݩ͢ΔΈ͍ͨͳϢʔεέʔεͰ

    InsertจΛՃ޻ͯ͠σʔλϚεΫ͢ΔɺΈ͍ͨͳͦ͏͍͏ײ͡ʁ • ଎౓͕ϘτϧωοΫͱ͔ࢥͬͯͨΒ͜ΕΛ࢖༻͢Δͱଟগ৭ʑͰ͖Δɻ • ޙɺON CONFLICT DO NOTHING Λ͚ͭΔ͜ͱͰ༧ΊσʔλΛߜΒͳͯ͘΋ྑ͍Έͨͳཚ๫ͳࣄ΋ Ͱ͖ͳ͍͜ͱ΋ͳ͍ɻ ※ ࠓճͷόʔδϣϯΞοϓͰ COPYจʹWHERE͚۟ͭΔࣄ͕Ͱ͖ΔΑ͏ʹɾɾ 46
  22. 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
  23. 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
  24. ωΠςΟϒɾύʔςΟγϣχϯά 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
  25. ωΠςΟϒɾύʔςΟγϣχϯά͕଎͍ཧ༝ • ύʔςΟγϣϯɾϓϧʔχϯά SELECT * FROM japan_cities WHERE pref =

    'Ԭࢁ'; ࣮ߦܭը͸ࢠςʔϒϧͷ ύʔςΟγϣϯΩʔͷ৚݅Λ֬ೝͯ͠ okayama_cities ςʔϒϧΛର৅ʹݕࡧ͢Δ => ແବͷແ͍ݕࡧ͕Մೳʹͳͬͨ ORM͸͋͘·Ͱ japan_cities ʹରͯ͠ΫΤϦΛ౤͛ DBଆͷ੹຿Ͱ෼ࢄΈ͍ͨͳࣄ΋Մೳ 57
  26. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ύʔςΟγϣϯɾΩʔͷ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
  27. ύʔςΟγϣϯɾςʔϒϧͷڧԽ CREATE TABLE sale_1 PARTITION OF sale FOR VALUES FROM

    (CURRENT_DATE::timestamp) TO (CURRENT_DATE::timestamp + '1 year'); CURRENT_DATE::timestamp ͩͱ౎౓ύʔςΟγϣϯΩʔ͕มΘΔ ͷͰ͸ ! ʁ 60
  28. ύʔςΟγϣϯɾςʔϒϧͷڧԽ 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
  29. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ֎෦Ωʔͷࢀরઌͱͯ͠ύʔςΟγϣϯɾςʔϒϧΛࢦఆՄʹ 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
  30. ύʔςΟγϣϯɾςʔϒϧͷڧԽ # 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
  31. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • ¥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
  32. ύʔςΟγϣϯɾςʔϒϧͷڧԽ • 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
  33. Access Method ͜Ε͸ࠓޙͷਐԽͷҝͷ෍ੴ..! • Pluggable Table Storage Interface • ಠࣗͷετϨʔδΤϯδϯʢAccess

    Method) ΛఆٛͰ͖ΔΑ͏ʹͳͬͨ • ݱࡏ͸σϑΥϧτʢैདྷʣͷHeapͷΈ • MySQL Ͱ͍͏MyISAMͱInnoDBΈ͍ͨͳ΋ͷ • Vacuum͕͍Βͳ͍ετϨʔδΤϯδϯͱ͔. • PG13Ҏ߱Ͱ࣮૷༧ఆͱͷࣄΒ͍͠ 69
  34. 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
  35. 4. PostgreSQL13ʹظ଴͞ΕΔػೳ • DROP DATABASE db_name WITH (FORCE) ͷαϙʔτ •

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

    db_name ͱ͢ΔࣄͰ PostgreSQLʹ͓͚Δ database Λυϩοϓ͢Δࣄ͕Ͱ͖Δ. • ͨͩ͠ɺ͜ͷߏจ͸ active ͳηογϣϯ͕͋ΔͱDropͰ͖ͳ ͔ͬͨ • DROP DATABASE db_name WITH (FORCE) ͱ͢ΔࣄͰ໰౴ແ༻Ͱফ 77
  37. \set PROMPT2 '%w' ͷαϙʔτ • ීஈ͸͜Μͳײ͡ chugokudb=# SELECT * chugokudb-#

    FROM member; • ͜Ε·Ͱ͸͜Μͳͷ͸αϙʔτ͋ͬͨɻ chugokudb=# \set PROMPT2 '' chugokudb=# SELECT * FROM member; • ͜͏͍͏ײ͡ʹͳΔ chugokudb=# \set PROMPT2 '%w' chugokudb=# SELECT * FROM member; 81
  38. ࠂ஌ • Ԭࢁ Ruby, Ruby on Railsษڧձ • 2020೥02݄29೔(౔) •

    https://okaruby.connpass.com/event/ 161062/ 91
  39. ࢀߟจݙ • 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