Do You Select PostgreSQL or MySQL ?

coscup 2019での登壇資料です。
https://coscup.org/2019/

Transcript

1. Do You Select PostgreSQL or MySQL? COSCUP 2019
2. https://speakerdeck.com/soudai/do-you-select-postgresql-or-mysql This slide is uploaded to speakerdeck.com
3. What is it? Do you Love RDBMS?
4. What is it? MySQL and PostgreSQL are great.
5. What is it? Today`s session is not about deciding which one is better.
6. What is it? I don't know which to choose.
7. What is it? If you know the two deeper, You can see how wonderful it is.
8. What is it? Know the difference between MySQL and PostgreSQL. Get people to like RDBMS more. That is the goal.
10. What is it?
11. Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together
12. Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together
13. About myself 曽根 壮大(Sone Taketomo) @soudai1025 Omicale Inc. Vice President and CTO. • Web Application Engineer • Japan PostgreSQL User Group Director • Microsoft Most Valuable Professional(Data Platform)
14. MySQL & PostgreSQL
15. Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together
16. MySQL or PostgreSQL? Which are you?
17. MySQL or PostgreSQL? I know that there are very likely to use each of the databases. There may be few people using both of them.
18. MySQL or PostgreSQL? Even in Japan, there are few people who use both.
19. MySQL or PostgreSQL? Which are you? ↓ It is important to know the difference
20. MySQL or PostgreSQL? Difference between MySQL and PostgreSQL
21. MySQL or PostgreSQL? difference • architecture • license • Development style
22. MySQL or PostgreSQL? Server architecture
23. MySQL or PostgreSQL? MySQL → multi-threaded PostgreSQL → multi-process
24. MySQL or PostgreSQL? You may think that multithreading is faster.
25. MySQL or PostgreSQL? You may think that multithreading is faster. ↓ That's not true.
26. MySQL or PostgreSQL? In fact, slow queries and deadlocks affect speed more than fork time.
27. MySQL Architecture Client コネクション スレッド コネクション スレッド Connection thread Connector MyISAM NDB Cluster InnoDB Memcached Plugin SQL Parser Optimizer Executor Handler Storage engine API MySQL Cluster Memcached Client
28. PostgreSQL Architecture Client コネクション スレッド コネクション スレッド Backend process Parser INDEX Table file WAL Wal writer Rewriter Planner Executor Writer & checkpointer Master process Shared memory area
29. MySQL or PostgreSQL? However, the PostgreSQL12 Plugin replaces the storage engine
30. MySQL or PostgreSQL? Disk io Architecture
31. MySQL or PostgreSQL? MySQL → Update Type(InnoDB) PostgreSQL → Write-once type
32. MySQL Record 1 Record 2 Record 3 UPDATE Record 1 Record 2’ Record 3
33. PostgreSQL Record 1 Record 2 Record 3 Record 2’ Record 1 Record 2 Record 3 UPDATE Record 1 Reusable Record 3 Record 2’ VACUUM Record 1 Record 2 Record 3 Record 2’
34. MySQL or PostgreSQL? Licence
35. MySQL or PostgreSQL? MySQL → GPL v2 or Commercial license PostgreSQL → PostgreSQL Licence
36. MySQL or PostgreSQL? the development style
37. MySQL or PostgreSQL? MySQL → Oracle PostgreSQL →Community
38. MySQL or PostgreSQL? MySQL is mainly developed by Oracle. Of course, it is OSS, if you provide your own patch, it may be merged.
39. MySQL or PostgreSQL? on the other hand,PostgreSQL is Community-based development. Many companies are involved, but there is no exclusive company.
40. MySQL or PostgreSQL? License is free, the development style is also free.
41. Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together
42. Advantage of MySQL Extensibility
43. MySQL Architecture Client コネクション スレッド コネクション スレッド Connection thread Connector MyISAM NDB Cluster InnoDB Memcached Plugin SQL Parser Optimizer Executor Handler Storage engine API MySQL Cluster Memcached Client
44. Advantage of MySQL It's surprising if we thinking about PostgreSQL.
45. MySQL Architecture Client コネクション スレッド コネクション スレッド Connection thread Connector MyISAM NDB Cluster InnoDB Memcached Plugin SQL Parser Optimizer Executor Handler Storage engine API MySQL Cluster Memcached Client
46. Advantage of MySQL InnoDB is a storage engine with transactions. Many of you may know that Oracle acquired it before MySQL.
47. Advantage of MySQL MySQL Strong Point fetching by Primary Key Also good at primary key update
48. Advantage of MySQL MySQL will break soon?
49. Advantage of MySQL MySQL will break soon? ↓ No such thing
50. Advantage of MySQL When MyISAM was the default, there was often break, and MySQL main table still used MyISAM.
51. Advantage of MySQL MyISAM dependency decreased from MySQL 8
52. Advantage of MySQL There are also Check constraints and Window functions
53. Advantage of MySQL Please be careful that these can only be used with MySQL 8 series. In particular, the check constraint is MySQL 8.0.16 or later.
54. Advantage of MySQL In other words, the latest version of Amazon RDS is 8.0.15, so the check constraint cannot be used.
55. Advantage of MySQL Flexible replication
56. Advantage of MySQL Multi Source Replication
57. Multi Source Replication Master Master Master Slave One slave has multiple masters
58. Advantage of MySQL Group Replication
59. Advantage of MySQL Group Replication ↓ Multi Master
60. Multi Master Replication Master Master Master Can be updated for any node Client Client Replication
61. Advantage of MySQL Cases like Amazon Aurora Multi-Master also came out
62. Advantage of MySQL Documents Store What is RDBMS?
63. Advantage of MySQL Using JSON type Represents a schemaless table
64. MySQL Architecture Client コネクション スレッド コネクション スレッド Connection thread Connector MyISAM NDB Cluster InnoDB Memcached Plugin SQL Parser Optimizer Executor Handler Storage engine API MySQL Cluster Memcached Client X Protocol Client X Plugin
65. Advantage of MySQL A dedicated X Protocol is also prepared for Protocol, and it can access like ORM or REST API instead of SQL. Of course, the stored data can also be accessed by SQL
66. Advantage of MySQL Release schedule has changed
67. Advantage of MySQL Release schedule has changed ↓ Once every three months, It will be released a new function as a maintenance release.
68. Advantage of MySQL Maintenance release?
69. Advantage of MySQL The first feature of MySQL is the speed of adding functions 8.0.14 → 8.0.16 → 8.0.17 Everything is different
70. Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together
71. Advantage of PostgreSQL PostgreSQL features MySQL has followed
72. Advantage of PostgreSQL Window function → MySQL 8.0 Check constraint → MySQL 8.0.14 Many other functions were added in the 8 series.
73. Advantage of PostgreSQL But what are the advantages of using PostgreSQL?
74. Advantage of PostgreSQL Advantages of PostgreSQL ↓ Large analytic queries
75. Advantage of PostgreSQL Parallel query
76. Advantage of PostgreSQL Backend Process SELECT * FROM tb_name Execute CPU CPU CPU Parallels worker Parallels worker Parallels worker Record 1 Record 2 Record 3 Record 4 Record 5 Record 6 Record 7 Record 8 Record 9 Scan Scan Scan
77. Advantage of PostgreSQL As you can see, there is no meaning in increasing the number of workers beyond the number of CPU cores.
78. Advantage of PostgreSQL Materialized view
79. Advantage of PostgreSQL Table A Table B join Query result Materialized view Materialization
80. Advantage of PostgreSQL Analytic query Table A Table B join Query result Materialized view Materialization Add index
81. Advantage of PostgreSQL Foreign Data Wrapper
82. Advantage of PostgreSQL Foreign Data Wrapper ↓ Like an external table Handle for your own table
83. Advantage of PostgreSQL Foreign Table Client FDW Extension Table PostgreSQL Other Database
84. Advantage of PostgreSQL • WHERE → OK • JOIN → OK • GROUP BY → OK • DELETE & UPDATE → OK Depends on the implementation of Extension
85. Advantage of PostgreSQL • From PostgreSQL to PostgreSQL • From MySQL to PostgreSQL • From OracleDB to PostgreSQL • From SQLite to PostgreSQL …etc
86. Advantage of PostgreSQL • From Redis to PostgreSQL • From Hadoop to PostgreSQL • From MongoDB to PostgreSQL • From Cassandra to PostgreSQL …etc
87. Advantage of PostgreSQL In other words, it doesn't have to be a database if it can be Record.
88. Advantage of PostgreSQL • From CSV to PostgreSQL • From Git to PostgreSQL • From S3 to PostgreSQL • From Google Spreadsheets to PostgreSQL • From Twitter to PostgreSQL …etc
89. Advantage of PostgreSQL Any data store is acceptable What is RDBMS?
90. Advantage of PostgreSQL GIN INDEX & Function INDEX + JSON
91. Advantage of PostgreSQL demo=# CREATE TABLE public.users ( id serial NOT NULL, name character varying(128) NOT NULL, properties jsonb NOT NULL, CONSTRAINT users_pkey PRIMARY KEY (id) ); demo=# select * FROM users; id | name | properties ----+------+------------------------------------------------- 1 | test | {} 2 | test | {} 4 | hoge | {"age": 18, "nickname": "hoge"} 3 | test | {"age": 18, "nickname": "test"} 6 | fuga | {"age": 20, "nickname": "fuga"} 7 | bar | {"age": 40, "gender": "man", "nickname": "foo"} (6 行)
92. Advantage of PostgreSQL -- Search with JSON key and value demo=# SELECT * FROM users WHERE properties->>'nickname' = 'hoge'; id | name | properties ----+------+--------------------------------- 4 | hoge | {"age": 18, "nickname": "hoge"} (1 行) -- Search by specifying JSON key / value pairs demo=# SELECT * FROM users WHERE properties @> '{"age":18}'::jsonb; id | name | properties ----+------+--------------------------------- 4 | hoge | {"age": 18, "nickname": "hoge"} 3 | test | {"age": 18, "nickname": "test"} (2 行) -- Search for specified key in JSON demo=# SELECT * FROM users WHERE properties ? 'gender'; id | name | properties ----+------+------------------------------------------------- 7 | bar | {"age": 40, "gender": "man", "nickname": "foo"} (1 行)
93. Advantage of PostgreSQL Function INDEX example
94. Advantage of PostgreSQL -- More than 10 million test data prepared demo=# SELECT count(*) FROM users; count ---------- 11743748 (1 行) -- Output execution time along with psql execution result demo=# ¥timing タイミングは on です。 demo=# SELECT * FROM users WHERE properties->>'nickname' = 'hogefuga'; id | name | properties ----------+------+------------------------------------- 11743750 | hoge | {"age": 18, "nickname": "hogefuga"} (1 行) 時間: 4880.380 ms
95. Advantage of PostgreSQL -- Create expression INDEX on search target demo=# CREATE INDEX demo_json_index ON public.users USING btree ((properties ->> 'nickname'::text)); CREATE INDEX 時間: 45063.501 ms demo=# SELECT * FROM users WHERE properties->>'nickname' = 'hogefuga'; id | name | properties ----------+------+------------------------------------- 11743750 | hoge | {"age": 18, "nickname": "hogefuga"} (1 行) 時間: 26.461 ms
96. Advantage of MySQL m(_ _)m
97. Advantage of PostgreSQL
98. Advantage of PostgreSQL
99. Advantage of PostgreSQL Function INDEX → MySQL 8.0.13 GIN INDEX → MySQL 8.0.17
100. Advantage of PostgreSQL
101. Advantage of MySQL Maintenance release?
102. Advantage of PostgreSQL This is the case when using new MySQL, and in cases where it is not possible to choose, there are still benefits to PostgreSQL.
103. Advantage of PostgreSQL MySQL tracking is great! PostgreSQL still more There are many other benefits.
104. Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together
105. Together MySQL and PostgreSQL Have a great influence.
106. Together PostgreSQL → MySQL • Check constraints • Window function • JSON type …etc
107. Together MySQL → PostgreSQL • Storage engine • Virtual column • Replication …etc
108. Together MySQL release Need to watch firmly For example, in 8.0.17 member became after reservation
109. Together MySQL releases more and more features Exciting phase
110. Together PostgreSQL has been fruitful for years of development, It is in a stable and usable state
111. Together In other words, it can be said that this DB can be used in production without risk.
112. Together In the future, JIT, Plugin, etc. A phase of investing in the future
113. Together What do you select database?
114. Together I ❤ RDBMS
115. Together 多謝