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

Do You Select PostgreSQL or MySQL ?

88f4e84b94fe07cddbd9e6479d689192?s=47 soudai sone
August 15, 2019

Do You Select PostgreSQL or MySQL ?

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

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

August 15, 2019
Tweet

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.
  9. None
  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 多謝