Comparison version: PostgreSQL 11    VS      MySQL5.7 (innodb engine) Oracle official community version
Copyright: PostgreSQL 11 (free open source), MySQL5.7 Oracle official community version (free open source)

1.CPU limit
PGSQL
No CPU core limit, how many CPU cores are used?

MySQL
Can use 128 core CPU, more than 128 cores can not be used

2. Configuration file parameters

PGSQL
There are a total of 255 parameters, about 80 are used, the parameters are relatively stable, and the current large version of the database can also be started with the previous version configuration file.

MySQL
There are a total of 707 parameters, about 180 are used, the parameters are increasing, even if the small version will increase the parameters, there will be some parameter incompatibility between the large versions.
3. Third-party tool dependencies
PGSQL
Only high-availability clusters rely on third-party middleware, such as: patroni+etcd, repmgr

MySQL
Most operations rely on percona’s third-party tools (percona-toolkit, XtraBackup), too many tool commands, high learning costs, high-availability clusters also require third-party middleware, the official MGR cluster is not yet mature

4. The underlying master-slave replication principle

PGSQL
Physical replication, like SQL Server image/AlwaysOn, is strictly consistent, there is no possibility of inconsistency, performance and reliability, physical replication is perfect, logical replication, maintenance is simple

MySQL
Logical replication, (sql_log_bin, binlog_format and other parameters are incorrectly set, resulting in inconsistent master-slave). Large transaction parallel replication is inefficient. For important services, you need to rely on percona-toolkit’s pt-table-checksum and pt-table-sync tools to periodically compare and Fix master-slave master-slave replication error when serious need to re-master logical replication from MySQL does not prevent two inconsistent databases from establishing replication relationship

5. Read-only status from the library

PGSQL
The system automatically sets the default read-only from the library, no manual intervention, and simple maintenance.

MySQL
The slave library needs to manually set the parameter super_read_only=on, so that the slave library is set to read-only, and the super_read_only parameter has a bug.

6. Version branch

PGSQL
Only the community version, there is no other branch version, PGSQL official unified development, unified maintenance, community version has all the functions, unlike SQL Server and MySQL have standard version, enterprise version, classic version, community version, development version, web version There are also some database manufacturers based on PGSQL for secondary development at home and abroad, such as: Enterprise DB, database, etc. Of course, these are only secondary developments and are not independent branches.

MySQL
Due to historical reasons, split into three branch versions, MariaDB branch, Percona branch, Oracle official branch, development so far each branch is basically incompatible with Oracle official branch and version, divided into standard version, enterprise version, classic version Community version

7. SQL feature support

PGSQL
The SQL feature support case supports 94 kinds, and the SQL syntax support is the most complete, for example: support common table expression (WITH query)

MySQL
SQL feature support supports 36 kinds, SQL syntax support is weak, for example: does not support common table expressions (WITH query)
For a comparison of SQL feature support, you can refer to:
http://www.sql-workbench.net/dbms_comparison.html

8. Master-slave replication security

PGSQL synchronous stream replication, strong apply, high security, no data loss PGSQL synchronous stream replication: all slave libraries, the main library will strike, the main library can not automatically switch to asynchronous stream replication (asynchronous mode), need By increasing the number of libraries, the general production environment is manually resolved by at least two slave libraries : modify the parameter synchronous_standby_names = ” in the PG main library, and execute the command:  pgctl reload  to switch the main library to asynchronous mode.
The master-slave data is completely consistent with the first premise of high-availability switching, so PGSQL chooses the main library strike is also understandable

MySQL enhances semi-synchronous replication, mysql5.7 version enhances semi-synchronization to ensure that master-slave replication does not lose data. mysql5.7 semi-synchronous replication related parameters: parameter rpl_semi_sync_master_wait_for_slave_count Waiting for at least how many slave libraries are received from the library, the main library submits the transaction, generally Set to 1, the highest performance parameter rpl_semi_sync_master_timeout How many milliseconds to wait, automatically switch from the library no response to asynchronous mode, generally set to infinite, do not let the main library automatically switch to asynchronous mode, all the slaves down, the main library will strike, because can not Received any response packets from the library
Manual solution: modify the parameter rpl_semi_sync_master_wait_for_slave_count=0 in the MySQL main library

9. Multi-field statistics

PGSQL
Support for multi-field statistics

MySQL
Multi-field statistics are not supported

10. Index type

PGSQL
Multiple index types (btree, hash, gin, gist, sp-gist, brin, bloom, rum, zombodb, bitmap, partial index, expression index)

MySQL

Btree index, full-text index (inefficient), expression index (need to build virtual columns), hash index only in memory table

11. Physical Table Join Algorithm

PGSQL
Support nested-loop join, hash join, merge join

MySQL

Only supports nested-loop join

12. Subquery and view performance

PGSQL
Subquery, view optimization, high performance

MySQL

View predicate condition pushdown limit, subquery pull-up limit

13. Execution plan instant compilation

PGSQL
Support   JIT    execution plan for instant compilation, using LLVM compiler

MySQL

Does not support execution plan instant compilation

14. Parallel query

PGSQL
Parallel query (multiple parallel query optimization methods), parallel query is generally more common in commercial databases, is a heavyweight function

MySQL

Limited, only supports primary key parallel query

15. Materialized view

PGSQL
Support materialized view

MySQL

Does not support materialized views

16. Plugin function

PGSQL
Support plug-in function, can enrich PGSQL functions, GIS geo-plugin, timing database plug-in, vectorization execution plug-in, etc.

MySQL

Plugin functionality is not supported

17. check constraints

PGSQL
Support check constraints

MySQL

The check constraint is not supported and the check constraint can be written, but the storage engine ignores its effect, so the check constraint does not work (mariadb support)

18. gpu accelerates SQL

PGSQL
Can use gpu to speed up SQL execution speed

MySQL

Does not support gpu to speed up the execution of SQL

19. Data Type

PGSQL
Rich data types, such as ltree, hstore, array type, ip type, text type, varchar is no longer needed with text type, text type field stores up to 1GB

MySQL

Data type is not rich enough

20. Cross-database query

PGSQL
Cross-database queries are not supported, this is the same as before Oracle 12C

MySQL

Can cross-database query

21. Backup and restore

PGSQL
Backup and restore is very simple, time-point restore operation is simpler than SQL Server, full backup + wal archive backup (incremental) If there is a three-node PGSQL master-slave cluster, you can do a full backup and wal archive backup on one of the nodes.

MySQL

Backup and restore is relatively simple, full backup + binlog backup (incremental) full backup requires percona XtraBackup tool to do physical backup, MySQL itself does not support physical backup point restore operation steps are cumbersome and complicated

22. Performance view

PGSQL
Need to install the pg_stat_statements plugin, pg_stat_statements plugin provides a rich performance view: such as: waiting for events, system statistics, etc. The bad place is that the installation plugin needs to restart the database, and the database needs to collect performance information need to execute a command: create extension pg_stat_statements The command will not collect any performance information, it is more troublesome

MySQL

With the PS library, many functions are not enabled by default, and the performance view function of the PS library is affected by performance (for example, memory usage causes OOM bugs)

23. Installation method

PGSQL
There are package rpm packages, deb packages, etc. for each platform. Compared to MySQL, there is a lack of binary packages. Generally, the source code is compiled and installed. The installation time will be longer and the execution commands will be more.

MySQL

There are package rpm packages for various platforms, deb packages, etc., source code compilation and installation, binary package installation, generally installed with binary package, convenient and fast

24. DDL operations

PGSQL
Adding fields and variable-length field types will not lock the table. All DDL operations do not need to use third-party tools, and like commercial databases, DDL operations can be rolled back to ensure transaction consistency.

MySQL

Since most DDL operations will lock the table, such as adding fields, variable length field type length is changed, so you need to use the pt-online-schema-change tool in percona-toolkit to complete the operation to minimize the impact, especially Large table for DDL operation DDL operation cannot be rolled back

25. Large version release speed

PGSQL
PGSQL is released in a large version every year. The second year of the release of the big version can be used in the production environment. The version is iteratively fast.
PGSQL 9.6 official release time: 2016
PGSQL 10 official release time: 2017 PGSQL 11 official release time: 2018 PGSQL 12 official release time: 2019
The large version of MySQL MySQL is generally released for 2 years to 3 years. The second year after the release of the general large version can be used in the production environment to avoid pits. The release speed is slower. MySQL5.5 official release time: 2010 MySQL5. 6 official version launch time: 2013 MySQL5.7 official version launch time: 2015 MySQL8.0 official version launch time: 2018

26. returning grammar

PGSQL
Support returning syntax, returning clause supports DML to return Resultset, reduce Client <-> DB Server interaction once

MySQL

Returning syntax is not supported

27. Internal architecture

PGSQL
Multi-process architecture, the number of concurrent connections can not be too much, like Oracle, since it is the same as Oracle, then many optimization methods are similar, for example: open large page memory

MySQL

Multi-threaded architecture, although multi-threaded architecture, but the official limit the number of connections, because the system’s concurrency is limited, the number of threads is too much, but the system’s processing power is reduced, as the number of connections rises, but the performance declines generally only Can handle 200 ~ 300 database connections

28. Clustered index

PGSQL
Does not support clustered index, PGSQL itself’s implementation mechanism of MVCC

MySQL

Support for clustered indexes

29. Idle transaction termination function

PGSQL
The idle transaction is terminated by setting the idle_in_transaction_session_timeout parameter. For example, if the application code forgets to close the opened transaction, PGSQL will automatically kill this type of session transaction.

MySQL

Ending idle transaction function is not supported

30. Coping with large data volumes

PGSQL
Can not cope with the huge amount of data, due to VGSQL’s own MVCC design issues, need garbage collection, can only look forward to the next big version to optimize

MySQL

Can’t cope with the huge amount of data, the problem of MySQL’s own architecture

31. Distributed evolution

PGSQL
HTAP database: cockroachDB, Tencent Tbase
Sharding cluster: Postgres-XC, Postgres-XL
MySQL HTAP database: TiDB fragmentation cluster: a variety of middleware, not enumerated

32. File name and naming convention of the database

PGSQL
PGSQL does not do well in this regard. DBA cannot see the file name and naming convention of the database at the operating system level (in the state of the shutdown), the number of files, and the size of the file.
Once the operating system has a file loss or hard disk damage, it is very unfavorable for recovery, because even the name does not know
PGSQL table data physical file naming / storage rules are: Under a table space, if there is no table space default in the default table space is also under the base folder, for example: /data/base/16454/3599
Base: the physical folder where the default tablespace pg_default is located. 16454: The oid 3599 of the database where the table is located is the oid of the table object. Of course, after the size of a table exceeds 1 GB, multiple physical files are generated, and the fsm file of the table and Vm file, so a large table actually has multiple physical files
Since PGSQL has too many data file layouts, you can check the related information.
Of course, this can’t all blame PGSQL. As a DBA, it is the right way to do database backup and disaster recovery at all times. It is generally a last resort to do media recovery.

MySQL

The database name is the folder name, the database folder is the table data file, each table has a corresponding frm file and ibd file, storage metadata and table / index data, clear and clear, do media recovery or table space transfer are very Convenience

33. Authority design

PGSQL
PGSQL in the permission design is a bit more boring, leaving the instance permissions and table space permissions, PGSQL permissions hierarchy is a bit like SQL Server, db = “schema = “object

To talk about permissions, here to talk about Oracle, use Oracle to analogy
Before ORACLE 12C, the instance and the database are one-to-one, which means that an instance can only have one database. Unlike an instance of MySQL and SQL Server, there can be multiple databases, and you can query across the database at will.
The reason why PGSQL can’t cross-database query is the same. PGSQL allows multiple databases to be built. Compared with ORACLE, there are multiple instances (the previous example is one-to-one with the database).
A database is equivalent to an instance, because PGSQL allows multiple instances, so PGSQL single instance is not called an instance, called cluster, cluster concept can refer to PGSQL related information
PGSQL inside an instance / database below the schema is equivalent to the database, so the concept of this schema corresponds to the MySQL database

Note: Just because a database is equivalent to an instance, PGSQL allows multiple instances/databases, so the databases are logically isolated from each other. The problem is that you cannot operate on all databases under one PGSQL cluster at a time.
You must go through the database one by one, for example, install the pg_stat_statements plugin mentioned above. If you need to perform performance collection on all databases under the PGSQL cluster, you need to execute the load command one by one.
For example, cross-database query requires dblink plugin or fdw plugin. Query between two databases is equivalent to query between two instances. It has already spanned the instance, so dblink plugin or fdw plugin is needed, so the reason is very simple.

Permission operations are also performed on a database-by-database basis. Another is that PGSQL, like SQL Server’s permission hierarchy db=”schema=”object, is actually more complicated than SQL Server, and there is a new table that is authorized.
In PGSQL, the role and the user are the same. For the novice user, sometimes it is silly and unclear, and I don’t know how to use the character. Therefore, PGSQL is really awkward in the permission design.

MySQL

Use the five permission tables below the mysql library to do permission mapping, simple and clear, the only problem is the lack of permission roles

User table db table host table tables_priv table columns_priv table

34. History of development

PGSQL In 1995, developers Andrew Yu and Jolly Chen added a SQL (Structured Query Language) translator to Postgres, called Postgres95, which was distributed in the open source community. In 1996, Postgres95 was changed again and named PostgresSQL version 6.0. The name of PostgresSQL was finalized. From 1995, it has a history of 24 years.

MySQL was released in 1996, MySQL 1.0, it is only for a small set of people, equivalent to internal release. In October 1996, MySQL 3.11.1 was released (MySQL does not have version 2.x). Initially, only the binary version under the Solaris operating system was provided. After one month, the Linux version appeared from 1996, and it has a history of 23 years.

 

 

summary

The comparison table above is not perfect, only some features that I think are more critical are compared.

 

In general, both databases have advantages and disadvantages. Everyone needs to choose carefully when selecting the model. MySQL needs to be more tossed. PGSQL makes you less toss, because PGSQL itself has been done more well, and it is not necessary to rely on some third parties. tool

Of course, if you choose the Percona branch on MySQL, the MariaDB branch, or Oracle’s official MySQL Enterprise Edition is another matter.

MySQL needs to support the replacement of the storage engine, so some functions are subject to the storage engine layer, for example: physical replication

PGSQL does not support the replacement of the storage engine (the plug-in table access interface is also supported at the beginning of PGSQL V12), and has been developed and maintained by the official, so it is relatively stable, and the function is relatively perfect, which is worthy of its title: “The most powerful open source database in the world”

After PGSQL V12 supports pluggable table access interface, it is possible for third-party storage engine to improve the MVCC implementation mechanism of PGSQL itself, without waiting for the official to solve it. Clustered index and undo tablespace are no longer problems.