Do You Select PostgreSQL or MySQL ?

by soudai sone

Slide 1

Slide 1 text

Do You Select PostgreSQL or MySQL? COSCUP 2019

Slide 2

Slide 2 text

https://speakerdeck.com/soudai/do-you-select-postgresql-or-mysql This slide is uploaded to speakerdeck.com

Slide 3

Slide 3 text

What is it? Do you Love RDBMS?

Slide 4

Slide 4 text

What is it? MySQL and PostgreSQL are great.

Slide 5

Slide 5 text

What is it? Today`s session is not about deciding which one is better.

Slide 6

Slide 6 text

What is it? I don't know which to choose.

Slide 7

Slide 7 text

What is it? If you know the two deeper, You can see how wonderful it is.

Slide 8

Slide 8 text

What is it? Know the difference between MySQL and PostgreSQL. Get people to like RDBMS more. That is the goal.

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

What is it?

Slide 11

Slide 11 text

Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together

Slide 12

Slide 12 text

Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together

Slide 13

Slide 13 text

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)

Slide 14

Slide 14 text

MySQL & PostgreSQL

Slide 15

Slide 15 text

Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together

Slide 16

Slide 16 text

MySQL or PostgreSQL? Which are you?

Slide 17

Slide 17 text

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.

Slide 18

Slide 18 text

MySQL or PostgreSQL? Even in Japan, there are few people who use both.

Slide 19

Slide 19 text

MySQL or PostgreSQL? Which are you? ↓ It is important to know the difference

Slide 20

Slide 20 text

MySQL or PostgreSQL? Difference between MySQL and PostgreSQL

Slide 21

Slide 21 text

MySQL or PostgreSQL? difference • architecture • license • Development style

Slide 22

Slide 22 text

MySQL or PostgreSQL? Server architecture

Slide 23

Slide 23 text

MySQL or PostgreSQL? MySQL → multi-threaded PostgreSQL → multi-process

Slide 24

Slide 24 text

MySQL or PostgreSQL? You may think that multithreading is faster.

Slide 25

Slide 25 text

MySQL or PostgreSQL? You may think that multithreading is faster. ↓ That's not true.

Slide 26

Slide 26 text

MySQL or PostgreSQL? In fact, slow queries and deadlocks affect speed more than fork time.

Slide 27

Slide 27 text

MySQL Architecture Client コネクションスレッドコネクションスレッド Connection thread Connector MyISAM NDB Cluster InnoDB Memcached Plugin SQL Parser Optimizer Executor Handler Storage engine API MySQL Cluster Memcached Client

Slide 28

Slide 28 text

PostgreSQL Architecture Client コネクションスレッドコネクションスレッド Backend process Parser INDEX Table file WAL Wal writer Rewriter Planner Executor Writer & checkpointer Master process Shared memory area

Slide 29

Slide 29 text

MySQL or PostgreSQL? However, the PostgreSQL12 Plugin replaces the storage engine

Slide 30

Slide 30 text

MySQL or PostgreSQL? Disk io Architecture

Slide 31

Slide 31 text

MySQL or PostgreSQL? MySQL → Update Type(InnoDB) PostgreSQL → Write-once type

Slide 32

Slide 32 text

MySQL Record 1 Record 2 Record 3 UPDATE Record 1 Record 2’ Record 3

Slide 33

Slide 33 text

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’

Slide 34

Slide 34 text

MySQL or PostgreSQL? Licence

Slide 35

Slide 35 text

MySQL or PostgreSQL? MySQL → GPL v2 or Commercial license PostgreSQL → PostgreSQL Licence

Slide 36

Slide 36 text

MySQL or PostgreSQL? the development style

Slide 37

Slide 37 text

MySQL or PostgreSQL? MySQL → Oracle PostgreSQL →Community

Slide 38

Slide 38 text

MySQL or PostgreSQL? MySQL is mainly developed by Oracle. Of course, it is OSS, if you provide your own patch, it may be merged.

Slide 39

Slide 39 text

MySQL or PostgreSQL? on the other hand,PostgreSQL is Community-based development. Many companies are involved, but there is no exclusive company.

Slide 40

Slide 40 text

MySQL or PostgreSQL? License is free, the development style is also free.

Slide 41

Slide 41 text

Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together

Slide 42

Slide 42 text

Advantage of MySQL Extensibility

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Advantage of MySQL It's surprising if we thinking about PostgreSQL.

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Advantage of MySQL InnoDB is a storage engine with transactions. Many of you may know that Oracle acquired it before MySQL.

Slide 47

Slide 47 text

Advantage of MySQL MySQL Strong Point fetching by Primary Key Also good at primary key update

Slide 48

Slide 48 text

Advantage of MySQL MySQL will break soon?

Slide 49

Slide 49 text

Advantage of MySQL MySQL will break soon? ↓ No such thing

Slide 50

Slide 50 text

Advantage of MySQL When MyISAM was the default, there was often break, and MySQL main table still used MyISAM.

Slide 51

Slide 51 text

Advantage of MySQL MyISAM dependency decreased from MySQL 8

Slide 52

Slide 52 text

Advantage of MySQL There are also Check constraints and Window functions

Slide 53

Slide 53 text

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.

Slide 54

Slide 54 text

Advantage of MySQL In other words, the latest version of Amazon RDS is 8.0.15, so the check constraint cannot be used.

Slide 55

Slide 55 text

Advantage of MySQL Flexible replication

Slide 56

Slide 56 text

Advantage of MySQL Multi Source Replication

Slide 57

Slide 57 text

Multi Source Replication Master Master Master Slave One slave has multiple masters

Slide 58

Slide 58 text

Advantage of MySQL Group Replication

Slide 59

Slide 59 text

Advantage of MySQL Group Replication ↓ Multi Master

Slide 60

Slide 60 text

Multi Master Replication Master Master Master Can be updated for any node Client Client Replication

Slide 61

Slide 61 text

Advantage of MySQL Cases like Amazon Aurora Multi-Master also came out

Slide 62

Slide 62 text

Advantage of MySQL Documents Store What is RDBMS?

Slide 63

Slide 63 text

Advantage of MySQL Using JSON type Represents a schemaless table

Slide 64

Slide 64 text

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

Advantage of MySQL Release schedule has changed

Slide 67

Slide 67 text

Advantage of MySQL Release schedule has changed ↓ Once every three months, It will be released a new function as a maintenance release.

Slide 68

Slide 68 text

Advantage of MySQL Maintenance release?

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together

Slide 71

Slide 71 text

Advantage of PostgreSQL PostgreSQL features MySQL has followed

Slide 72

Slide 72 text

Advantage of PostgreSQL Window function → MySQL 8.0 Check constraint → MySQL 8.0.14 Many other functions were added in the 8 series.

Slide 73

Slide 73 text

Advantage of PostgreSQL But what are the advantages of using PostgreSQL?

Slide 74

Slide 74 text

Advantage of PostgreSQL Advantages of PostgreSQL ↓ Large analytic queries

Slide 75

Slide 75 text

Advantage of PostgreSQL Parallel query

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

Advantage of PostgreSQL As you can see, there is no meaning in increasing the number of workers beyond the number of CPU cores.

Slide 78

Slide 78 text

Advantage of PostgreSQL Materialized view

Slide 79

Slide 79 text

Advantage of PostgreSQL Table A Table B join Query result Materialized view Materialization

Slide 80

Slide 80 text

Advantage of PostgreSQL Analytic query Table A Table B join Query result Materialized view Materialization Add index

Slide 81

Slide 81 text

Advantage of PostgreSQL Foreign Data Wrapper

Slide 82

Slide 82 text

Advantage of PostgreSQL Foreign Data Wrapper ↓ Like an external table Handle for your own table

Slide 83

Slide 83 text

Advantage of PostgreSQL Foreign Table Client FDW Extension Table PostgreSQL Other Database

Slide 84

Slide 84 text

Advantage of PostgreSQL • WHERE → OK • JOIN → OK • GROUP BY → OK • DELETE & UPDATE → OK Depends on the implementation of Extension

Slide 85

Slide 85 text

Advantage of PostgreSQL • From PostgreSQL to PostgreSQL • From MySQL to PostgreSQL • From OracleDB to PostgreSQL • From SQLite to PostgreSQL …etc

Slide 86

Slide 86 text

Advantage of PostgreSQL • From Redis to PostgreSQL • From Hadoop to PostgreSQL • From MongoDB to PostgreSQL • From Cassandra to PostgreSQL …etc

Slide 87

Slide 87 text

Advantage of PostgreSQL In other words, it doesn't have to be a database if it can be Record.

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

Advantage of PostgreSQL Any data store is acceptable What is RDBMS?

Slide 90

Slide 90 text

Advantage of PostgreSQL GIN INDEX & Function INDEX + JSON

Slide 91

Slide 91 text

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 行)

Slide 92

Slide 92 text

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 行)

Slide 93

Slide 93 text

Advantage of PostgreSQL Function INDEX example

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

Advantage of MySQL m(_ _)m

Slide 97

Slide 97 text

Advantage of PostgreSQL

Slide 98

Slide 98 text

Advantage of PostgreSQL

Slide 99

Slide 99 text

Advantage of PostgreSQL Function INDEX → MySQL 8.0.13 GIN INDEX → MySQL 8.0.17

Slide 100

Slide 100 text

Advantage of PostgreSQL

Slide 101

Slide 101 text

Advantage of MySQL Maintenance release?

Slide 102

Slide 102 text

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.

Slide 103

Slide 103 text

Advantage of PostgreSQL MySQL tracking is great! PostgreSQL still more There are many other benefits.

Slide 104

Slide 104 text

Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage of MySQL 4. Advantage of PostgreSQL 5. Together

Slide 105

Slide 105 text

Together MySQL and PostgreSQL Have a great influence.

Slide 106

Slide 106 text

Together PostgreSQL → MySQL • Check constraints • Window function • JSON type …etc

Slide 107

Slide 107 text

Together MySQL → PostgreSQL • Storage engine • Virtual column • Replication …etc

Slide 108

Slide 108 text

Together MySQL release Need to watch firmly For example, in 8.0.17 member became after reservation

Slide 109

Slide 109 text

Together MySQL releases more and more features Exciting phase

Slide 110

Slide 110 text

Together PostgreSQL has been fruitful for years of development, It is in a stable and usable state

Slide 111

Slide 111 text

Together In other words, it can be said that this DB can be used in production without risk.