Download to read offline
![: 1 =# CREATE PUBLICATION pub1 FOR TABLE table1, table2, table3 WITH (publish = ‘INSERT’); =# CREATE SUBSCRIPTION sub1 CONNECTION ‘host=[IP ] dbname=[DB ]’ PUBLICATION pub1; table 2 table 4 table 1 table 3 table 5 pub1 table 2 table 1 table 3 sub1(:pub1) DB1 DB2 INSERT](https://image.slidesharecdn.com/oscosakapg4-180131120710/75/PostgreSQL10-13-2048.jpg)
![: 2 =# CREATE PUBLICATION pub1 FOR TABLE table1, table2, table3; =# CREATE PUBLICATION pub2 FOR TABLE table3, table4; =# CREATE SUBSCRIPTION sub1 CONNECTION ‘host=[IP ] dbname=[DB ]’ PUBLICATION pub1, pub2; table 2 table 4 table 1 table 3 table 5 pub2 table 1 DB2 table 3 pub1 sub1(:pub1, pub2) DB1 table 2 table 4](https://image.slidesharecdn.com/oscosakapg4-180131120710/75/PostgreSQL10-15-2048.jpg)
![10 INSERT 10 0 35 70 105 140 elaped time [s] trigger based native 300](https://image.slidesharecdn.com/oscosakapg4-180131120710/75/PostgreSQL10-46-2048.jpg)
PostgreSQL10の新機能 ～ロジカルレプリケーションを中心に～
- 1.
- 2. ( ) PostgreSQL / HadoopOpenStack Swift
- 3.
- 4. PostgreSQL RDBMS Ingres(1970~) PostgreSQL6.0(1996 ) 20 BSD(PostgreSQL ) PostgreSQL Global Development Group 4
- 5.
- 6. PostgreSQL 10 / 10 10.1 9.69.6.6 : DB :
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13. : 1 =# CREATEPUBLICATION pub1 FOR TABLE table1, table2, table3 WITH (publish = ‘INSERT’); =# CREATE SUBSCRIPTION sub1 CONNECTION ‘host=[IP ] dbname=[DB ]’ PUBLICATION pub1; table 2 table 4 table 1 table 3 table 5 pub1 table 2 table 1 table 3 sub1(:pub1) DB1 DB2 INSERT
- 14.
- 15. : 2 =# CREATEPUBLICATION pub1 FOR TABLE table1, table2, table3; =# CREATE PUBLICATION pub2 FOR TABLE table3, table4; =# CREATE SUBSCRIPTION sub1 CONNECTION ‘host=[IP ] dbname=[DB ]’ PUBLICATION pub1, pub2; table 2 table 4 table 1 table 3 table 5 pub2 table 1 DB2 table 3 pub1 sub1(:pub1, pub2) DB1 table 2 table 4
- 16. pub walsender (WAL) COMMITpub sub ABORT sub sub apply worker walsender
- 17. INSERT WAL wal sender INSERT X INSERTY INSERT Y (WAL) UPDATE A DELETE B INSERT C INSERT Z Pub DB apply worker table table table Sub DB
- 18. COMMIT WAL wal sender INSERT X INSERTY COMMIT (WAL) UPDATE A DELETE B INSERT C INSERT Z Pub DB apply worker table table table Sub DB COMMIT Sub Sub
- 19. ABORT WAL wal sender INSERT X INSERTY ABORT (WAL) UPDATE A DELETE B INSERT C INSERT Z Pub DB apply worker table table table Sub DB ABORT
- 20. COPY COPY sub tablesync worker max_sync_workers_per_subscription ( :2)
- 21.
- 22. Sub ERROR: duplicate keyvalue violates unique constraint "t1_pkey" DETAIL: Key (id)=(2) already exists. ... LOG: worker process: logical replication worker for subscription 16392 (PID 1494) exited with exit code 1 5
- 23.
- 24. DDL CREATE TABLE TRUNCATE (serial,identity) pub serial, identity sub INSERT pub
- 25.
- 26. ((Tomas Vondra, DavidRowley, Álvaro Herrera)
- 27.
- 28. 1 "WEHRE X =0” * selectivity(X = 0) 9.6
- 29. 1 "WHERE (X =0) AND (Y = 1)" * selectivity(X = 0) * selectivity(Y = 1) 9.6
- 30. 1 "WHERE (X =0) AND (Y = 1)" * selectivity(X = 0) * selectivity(Y = 1) 9.6 X Y X Y
- 31. 3 9.6 x | y| z ---+---+--- 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 2 | 2 | 2 2 | 2 | 2 ….cont =# EXPLAIN ANALYZE SELECT * FROM test WHERE (X = 1) AND (Y = 1) AND (Z = 1)’; ———————————————- Seq Scan on test (cost=0.00..2.75 rows=1 width=12) (actual time=0.035..0.063 rows=10 loops=1) Filter: ((x = 1) AND (y = 1) AND (z = 1)) Rows Removed by Filter: 90 Planning time: 0.690 ms Execution time: 0.262 ms (5 rows)
- 32. 9.6 =# EXPLAIN ANALYZESELECT * FROM test WHERE (X = 1) AND (Y = 1) AND (Z = 1)’; ———————————————- Seq Scan on test (cost=0.00..2.75 rows=1 width=12) (actual time=0.035..0.063 rows=10 loops=1) Filter: ((x = 1) AND (y = 1) AND (z = 1)) Rows Removed by Filter: 90 Planning time: 0.690 ms Execution time: 0.262 ms (5 rows) 10 1 3 x | y | z ---+---+--- 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 2 | 2 | 2 2 | 2 | 2 ….cont
- 34. =# CREATE STATISTICSstat_test ON x, y, z FROM test; =# ANALYZE; =# EXPLAIN ANALYZE SELECT * FROM test WHERE (X = 1) AND (Y = 1) AND (Z = 1); ———————————————- Seq Scan on test (cost=0.00..2.75 rows=10 width=12) (actual time=0.028..0.053 rows=10 loops=1) Filter: ((x = 1) AND (y = 1) AND (z = 1)) Rows Removed by Filter: 90 Planning time: 0.687 ms Execution time: 0.123 ms (5 rows)
- 35. CREATE STATISTICS =# SELECT* FROM pg_statistic_ext; stxrelid | 16607 stxname | stat_test stxnamespace | 2200 stxowner | 10 stxkeys | 1 2 3 stxkind | {d,f} stxndistinct | {"1, 2": 11, "1, 3": 11, "2, 3": 11, "1, 2, 3": 11} stxdependencies | {"1 => 2": 1.000000, "1 => 3": 1.000000, "2 => 1": 1.000000, "2 => 3": 1.000000, "3 => 1": 1.000000, "3 => 2": 1.000000, "1, 2 => 3": 1.000000, "1, 3 => 2": 1.000000, "2, 3 => 1": 1.000000} stxndistinct: stxdependencies:
- 36. CREATE STATISTICS =# SELECT* FROM pg_statistic_ext; stxrelid | 16607 stxname | stat_test stxnamespace | 2200 stxowner | 10 stxkeys | 1 2 3 stxkind | {d,f} stxndistinct | {"1, 2": 11, "1, 3": 11, "2, 3": 11, "1, 2, 3": 11} stxdependencies | {"1 => 2": 1.000000, "1 => 3": 1.000000, "2 => 1": 1.000000, "2 => 3": 1.000000, "3 => 1": 1.000000, "3 => 2": 1.000000, "1, 2 => 3": 1.000000, "1, 3 => 2": 1.000000, "2, 3 => 1": 1.000000}
- 37.
- 38.
- 39. : 1 AP 1 / table tabletable table
- 40.
- 41. 9.6 table CREATE TABLE japan( pref text, city text, data text ); table CREATE TABLE osaka ( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE TABLE kyoto ( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE TABLE shiga( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE TABLE nara ( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE OR REPLACE FUNCTION pref_insert_trigger_func() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.pref = ' ') THEN INSERT INTO osaka VALUES (NEW.*); ELSIF ( NEW.pref = ' ') THEN INSERT INTO kyoto VALUES (NEW.*); ELSIF ( NEW.pref = ' ') THEN INSERT INTO shiga VALUES (NEW.*); ELSIF ( NEW.pref = ' ') THEN INSERT INTO nara VALUES (NEW.*); ELSE RAISE EXCEPTION 'Data out of range. Fix the pref_insert_trigger()'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; table CREATE TRIGGER pref_insert_trigger BEFORE INSERT ON japan FOR EACH ROW EXECUTE PROCEDURE pref_insert_trigger_func();
- 42. 9.6 table CREATE TABLE japan( pref text, city text, data text ); table CREATE TABLE osaka ( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE TABLE kyoto ( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE TABLE shiga( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE TABLE nara ( CHECK (pref IN (' ')) ) INHERITS (japan); CREATE OR REPLACE FUNCTION pref_insert_trigger_func() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.pref = ' ') THEN INSERT INTO osaka VALUES (NEW.*); ELSIF ( NEW.pref = ' ') THEN INSERT INTO kyoto VALUES (NEW.*); ELSIF ( NEW.pref = ' ') THEN INSERT INTO shiga VALUES (NEW.*); ELSIF ( NEW.pref = ' ') THEN INSERT INTO nara VALUES (NEW.*); ELSE RAISE EXCEPTION 'Data out of range. Fix the pref_insert_trigger()'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; table CREATE TRIGGER pref_insert_trigger BEFORE INSERT ON japan FOR EACH ROW EXECUTE PROCEDURE pref_insert_trigger_func(); trigger
- 43.
- 44.
- 45. 10 table -- table CREATE TABLEjapan ( pref text, city text, data text ) PARTITION BY LIST (pref); table CREATE TABLE osaka PARTITION OF japan FOR VALUES IN (' '); CREATE TABLE kyoto PARTITION OF japan FOR VALUES IN (' '); CREATE TABLE shiga PARTITION OF japan FOR VALUES IN (' '); CREATE TABLE nara PARTITION OF japan FOR VALUES IN (' '); trigger
- 46. 10 INSERT 10 0 35 70 105 140 elaped time [s] triggerbased native 300
- 47.
- 48. (Robert Haas, AmitKapila, Rahila Syed, Rafia Sabih, Dilip Kumar)
- 49.
- 50.
- 51. 9.6 ... Scan Sequential Scan Join NestedLoop Join, Hash Join
- 52. 10 ... Scan Sequential Scan,Bitmap Heap Scan, Index Scan Join Nested Loop Join, Hash Join, Merge Join
- 53.
- 54.
- 55. PostgreSQL 10 https://www.slideshare.net/toshiharada/20171106-ntttxpostgre-sql10-81645645 PostgreSQL10 https://www.slideshare.net/masahikosawada98/postgresql10 Logical ReplicationInternals https://www.slideshare.net/noriyoshishinoda/pgconfasia-2017-logical-replication- internals-japanese Next-Generation Parallel Query https://www.pgcon.org/2017/schedule/attachments/445_Next-Generation Parallel Query - PGCon.pdf