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. 7.

    What is it? If you know the two deeper, You

    can see how wonderful it is.
  2. 8.

    What is it? Know the difference between MySQL and PostgreSQL.

    Get people to like RDBMS more. That is the goal.
  3. 9.
  4. 11.

    Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage

    of MySQL 4. Advantage of PostgreSQL 5. Together
  5. 12.

    Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage

    of MySQL 4. Advantage of PostgreSQL 5. Together
  6. 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)
  7. 15.

    Agenda 1. About myself 2. MySQL or PostgreSQL? 3. Advantage

    of MySQL 4. Advantage of PostgreSQL 5. Together
  8. 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.
  9. 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
  10. 28.

    PostgreSQL Architecture Client コネクション スレッド コネクション スレッド Backend process Parser

    INDEX Table file WAL Wal writer Rewriter Planner Executor Writer & checkpointer Master process Shared memory area
  11. 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’
  12. 35.
  13. 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.
  14. 39.

    MySQL or PostgreSQL? on the other hand,PostgreSQL is Community-based development.

    Many companies are involved, but there is no exclusive company.
  15. 41.

    Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage

    of MySQL 4. Advantage of PostgreSQL 5. Together
  16. 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
  17. 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
  18. 46.

    Advantage of MySQL InnoDB is a storage engine with transactions.

    Many of you may know that Oracle acquired it before MySQL.
  19. 50.

    Advantage of MySQL When MyISAM was the default, there was

    often break, and MySQL main table still used MyISAM.
  20. 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.
  21. 54.

    Advantage of MySQL In other words, the latest version of

    Amazon RDS is 8.0.15, so the check constraint cannot be used.
  22. 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
  23. 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
  24. 67.

    Advantage of MySQL Release schedule has changed ↓ Once every

    three months, It will be released a new function as a maintenance release.
  25. 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
  26. 70.

    Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage

    of MySQL 4. Advantage of PostgreSQL 5. Together
  27. 72.

    Advantage of PostgreSQL Window function → MySQL 8.0 Check constraint

    → MySQL 8.0.14 Many other functions were added in the 8 series.
  28. 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
  29. 77.

    Advantage of PostgreSQL As you can see, there is no

    meaning in increasing the number of workers beyond the number of CPU cores.
  30. 79.
  31. 80.

    Advantage of PostgreSQL Analytic query Table A Table B join

    Query result Materialized view Materialization Add index
  32. 84.

    Advantage of PostgreSQL • WHERE → OK • JOIN →

    OK • GROUP BY → OK • DELETE & UPDATE → OK Depends on the implementation of Extension
  33. 85.

    Advantage of PostgreSQL • From PostgreSQL to PostgreSQL • From

    MySQL to PostgreSQL • From OracleDB to PostgreSQL • From SQLite to PostgreSQL …etc
  34. 86.

    Advantage of PostgreSQL • From Redis to PostgreSQL • From

    Hadoop to PostgreSQL • From MongoDB to PostgreSQL • From Cassandra to PostgreSQL …etc
  35. 87.
  36. 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
  37. 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 行)
  38. 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 行)
  39. 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
  40. 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
  41. 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.
  42. 104.

    Agenda 1. about myself 2. MySQL or PostgreSQL? 3. Advantage

    of MySQL 4. Advantage of PostgreSQL 5. Together
  43. 108.

    Together MySQL release Need to watch firmly For example, in

    8.0.17 member became after reservation
  44. 111.

    Together In other words, it can be said that this

    DB can be used in production without risk.