What is the difference between postgresql and mysql

Differences: 1. PGSQL does not have a limit on the number of CPU cores, but mysql has a limit; 2. PGSQL has a total of 255 configuration file parameters, and MySQL has a total of 707; 3. PGSQL supports multi-field statistical information, and MySQL does not support it; 4. PGSQL supports real-time compilation of execution plans, but MySQL does not support it.

The operating environment of this tutorial: windows7 system, PostgreSQL 11&&MySQL5.7 version, Dell G3 computer.

PostgreSQL is a free software object-relational database management system (ORDBMS) with very complete features.

PostgreSQL supports most SQL standards and provides many other modern features, such as complex queries, foreign keys, triggers, views, transaction integrity, and multi-version concurrency control wait. Likewise, PostgreSQL can be extended in many ways, such as by adding new data types, functions, operators, aggregate functions, index methods, procedural languages, etc. In addition, because of the flexible license, anyone can use, modify and distribute PostgreSQL for any purpose for free.

Comparison of the differences between postgresql and mysql

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

1. CPU limit

PGSQL has no limit on the number of CPU cores. Use as many CPU cores as there are

MySQL can use 128-core CPU, more than 128 cores cannot be used

2. Configuration file parameters

PGSQL There are a total of 255 parameters, and about 80 of them are used. The parameters are relatively stable. You can also start the current large version of the database using the previous large version configuration file.

MySQL has a total of 707 parameters, and about 80 of them are used. 180, parameters are constantly increasing, even small versions will increase parameters, and some parameters will be incompatible between large versions

3. Third-party tool dependencies

PGSQL only high-availability clusters need to rely on third-party middleware, such as: patroni etcd, repmgr

Most operations of MySQL rely on third-party tools from percona (percona-toolkit, XtraBackup), and there are too many tool commands. , the learning cost is high, and high-availability clusters also require third-party middleware. The official MGR cluster is not yet mature

4. The underlying principle of high-availability master-slave replication

PGSQL physics Streaming replication is a physical replication. It is the same as SQL Server mirroring/AlwaysOn. It is strictly consistent and has no possibility of causing inconsistency. In terms of performance and reliability, physical replication beats logical replication and is easy to maintain.

MySQL master-slave replication is Logical replication, (incorrect parameter settings such as sql_log_bin, binlog_format, etc. will lead to master-slave inconsistency) The efficiency of parallel replication of large transactions is low. For important businesses, you need to rely on the pt-table-checksum and pt-table-sync tools of percona-toolkit to compare the sums regularly. Repair the master-slave consistent master-slave replication error. It is necessary to re-establish the master-slave MySQL logical replication and does not prevent two inconsistent databases from establishing a replication relationship

5. Read-only status of the slave database

PGSQL system automatically sets the slave library to read-only by default, no manual intervention is required, and maintenance is simple

MySQL slave library needs to manually set the parameter super_read_only=on to set the slave library to read-only. The super_read_only parameter has bug, link: https://baijiahao.baidu.com/s?id=1636644783594388753&wfr=spider&for=pc

6. Version branch

PGSQL only has the community version, There is no other branch version. PGSQL is officially developed and maintained in a unified way. The community version has all the functions. Unlike SQL Server and MySQL, which have standard version, enterprise version, classic version, community version, development version, and web version, they are available at home and abroad. Some database manufacturers do secondary development based on PGSQL, such as Enterprise DB, Hangao Database, etc. Of course, these are only secondary developments and are not independent branches.

MySQL is split into three branch versions due to historical reasons , MariaDB branch, Percona branch, Oracle official branch. So far, the branches are basically incompatible with each other. Oracle official branch also has versions, which are divided into Standard Edition, Enterprise Edition, Classic Edition, and Community Edition

7. SQL feature support

PGSQLSQL feature support supports 94 types, and the SQL syntax support is the most complete, for example: supporting common table expressions (WITH query)

MySQLSQL feature support 36 situations are supported, and SQL syntax support is relatively weak. For example, common table expressions (WITH queries) are not supported. For a comparison of SQL feature support, please refer to: http://www.sql-workbench.net/dbms_comparison.html

8. Master-slave replication security

PGSQL synchronous stream replication, strong synchronization (remote apply), high security, no data loss PGSQL synchronous stream replication: all slaves If the database is down, the master database will go on strike. The master database cannot automatically switch to asynchronous streaming replication (asynchronous mode). This needs to be solved by increasing the number of slave databases.

Generally, the production environment has at least two slave libraries. Manual solution: modify the parameter synchronous_standby_names ='' in the PG main library, and execute the command: pgctl reload. Switch the main library to asynchronous mode. The master-slave data is completely consistent. High The first prerequisite for available switching.

So it is understandable that PGSQL chooses the master database to strike. MySQL enhanced semi-synchronous replication

The enhanced semi-synchronization of mysql5.7 version can ensure that no data is lost during master-slave replication. Mysql5.7 semi-synchronous replication related parameters: parameter rpl_semi_sync_master_wait_for_slave_count Wait for at least how many slave libraries to receive binlog before the master library commits the transaction.

Generally set to 1. The highest performance parameter rpl_semi_sync_master_timeout waits for how many milliseconds. The slave library will automatically switch to asynchronous mode if there is no response.

General Set to infinite to prevent the main library from automatically switching to asynchronous mode. All slave libraries will be down. The main library will go on strike because it cannot receive any response packet from the slave library. Manual solution: modify the parameter rpl_semi_sync_master_wait_for_slave_count in the MySQL main library. =0

9. Multi-field statistical information

PGSQL supports multi-field statistical information

MySQL does not support multi-field statistical information

10. Index type

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

MySQLbtree index, full-text index (inefficient), expression index (need to create a virtual column), hash index only in memory table

11. Physical table connection algorithm

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

MySQL only supports nested-loop join

12. Subquery and view performance

PGSQL subquery, view optimization, relatively high performance

MySQL view predicate conditions have many push-down restrictions, and subqueries have many pull-up restrictions

13. Just-in-time compilation of execution plans

PGSQL supports JIT execution plan just-in-time compilation, using the LLVM compiler

MySQL does not support just-in-time compilation of execution plans

14. Parallelism Query

PGSQL parallel query (multiple parallel query optimization methods), parallel query is generally seen in commercial databases, and is a heavyweight function

MySQL is limited and only supports primary key parallel query

15. Materialized views

PGSQL supports materialized views

MySQL does not support materialized views

16. Plug-in functions

PGSQL supports plug-in functions, which can enrich the functions of PGSQL, GIS geographical plug-ins, time series database plug-ins, vectorized execution plug-ins, etc.

MySQL does not support plug-in functions

17. check constraints

PGSQL supports check constraints

MySQL does not support check constraints. You can write check constraints, but the storage engine will ignore its effect, so the check constraints do not It works (supported by mariadb)

18. GPU accelerated SQL

PGSQL can use GPU to accelerate SQL execution speed

MySQL does not support GPU accelerated SQL Execution speed

19. Data type

PGSQL has rich data types, such as ltree, hstore, array type, ip type, text type, and the text type is no longer The maximum storage of varchar and text type fields is 1GB

MySQL data types are not rich enough

20. Cross-database query

PGSQL does not support cross-database query. This is the same as before Oracle 12C

MySQL can query across databases

21. Backup and restore

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

MySQL backup and restore are relatively easy Too simple. Full backup binlog backup (incremental) full backup requires percona's XtraBackup tool for physical backup. MySQL itself does not support physical backup. The point restore operation steps are cumbersome and complicated

22. Performance View

PGSQL needs to install the pg_stat_statements plug-in. The pg_stat_statements plug-in provides a rich performance view: such as: waiting for events, system statistics, etc. The disadvantage is that installing the plug-in requires restarting the database and collecting performance information from the database. You need to execute a command: create extension pg_stat_statements command, otherwise no performance information will be collected, which is more troublesome

MySQL comes with the PS library, many functions are not turned on by default, and turning on the performance view function of the PS library has an impact on performance ( For example: OOM bug caused by memory usage)

23. Installation method

PGSQL has rpm packages, deb packages for various platforms, etc. Compared with MySQL, it lacks binary Packages are generally compiled and installed with source code. The installation time will be longer and there will be more commands to execute.

MySQL has rpm packages, deb packages, etc. for various platforms. Source code is compiled and installed, and binary packages are installed. Binary packages are generally used. Installation is fast and convenient

24. DDL operations

PGSQL adds fields and changes the length of variable-length field types without locking the table. All DDL operations are not required. With the help of 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 and changing the length of variable-length field types, Therefore, you need to use the pt-online-schema-change tool in percona-toolkit to complete the operation and minimize the impact, especially for DDL operations on large tables. DDL operations cannot be rolled back

25. Large Version release speed

PGSQLPGSQL releases a major version every year. It can be put into the production environment in the second year after the major version is released. The version iteration speed is very fast. The official version of PGSQL 9.6 was launched in 2016. The official version of PGSQL 10 was launched in 2017. The official version of PGSQL 11 was launched in 2017. Launch time: 2018 PGSQL 12 official version launch time: 2019

MySQL It usually takes 2 to 3 years for a major version of MySQL to be released. Generally, a major version can only be put into the production environment in the second year after it is released to avoid any problems. Pit, the version release speed is relatively slow. The official version of MySQL5.5 was launched in 2010. The official version of MySQL5.6 was launched in 2013. The official version of MySQL5.7 was launched in 2015. The official version of MySQL8.0 was launched in 2018.

26. Returning syntax

PGSQL supports returning syntax, returning clause supports DML return Resultset, reducing one Client <-> DB Server interaction

MySQL does not Supports returning syntax

27. Internal architecture

PGSQL multi-process architecture, the number of concurrent connections cannot be too many, just like Oracle. Since it is the same as Oracle, there are many optimization methods It is also the same, for example: enable large page memory

MySQL multi-threaded architecture. Although the multi-threaded architecture, there is an official limit on the number of connections. The reason is that the concurrency of the system is limited. If there are too many threads, the system will be damaged. The processing capacity decreases. As the number of connections increases, the performance decreases. Generally, only 200 to 300 database connections can be processed at the same time

28. Clustered index

PGSQL does not support Clustered index, caused by the MVCC implementation mechanism of PGSQL itself

MySQL supports clustered index

29. Idle transaction termination function

PGSQL sets idle_in_transaction_session_timeout parameters to terminate idle transactions. For example, if you forget to close an open transaction in the application code, PGSQL will automatically detect and kill this type of session transaction

MySQL does not support the function of terminating idle transactions

30. Coping with extremely large data volumes

PGSQL cannot cope with extremely large data volumes. Due to the MVCC design problem of PGSQL itself, garbage collection is required. We can only expect optimization in subsequent major versions

MySQL cannot cope with extremely large amounts of data, and there are problems with MySQL’s own architecture

31. Distributed evolution

PGSQLHTAP database: cockroachDB, Tencent Tbase sharding cluster: Postgres-XC, Postgres-XLMySQLHTAP database: TiDB sharding cluster: various middlewares, too many to list one by one

32. Database file names and naming rules

PGSQLPGSQL This aspect is not done well. The DBA cannot clearly see the file names and naming rules of the database at the operating system level (when the database is stopped), the number of files, and the size of the files. Once the operating system files are lost or the hard disk is damaged, it is very difficult to recover. , because I don’t even know the name. The naming/storage rule of physical files of PGSQL table data is: under a table space, if no table space is created, it defaults to the default table space, which is the base folder, for example: /data/base/16454 /3599base: The physical folder where the default table space pg_default is located 16454: The oid of the database where the table is located3599: It is the oid of the table object. Of course, when the size of a table exceeds 1GB, multiple physical files will be generated, as well as the fsm file of the table and vm file, so a large table actually has multiple physical files. Since PGSQL has too much data file layout content, you can check the relevant information. Of course, this cannot be entirely blamed on PGSQL. As a DBA, you must always do database backup and disaster recovery. That's the right way. Media recovery is usually done only as a last resort.

The MySQL database name is the folder name. Under the database folder are the table data files. Each table has its corresponding frm file and ibd file. , stores metadata and table/index data clearly, and is very convenient for media recovery or table space transfer

33. Permission design

PGSQLPGSQL is quite tricky in terms of permission design. Putting aside instance permissions and table space permissions, the permission level of PGSQL is a bit like SQL Server. db=》schema=》object talks about permissions. Here I want to talk about Oracle. Use Oracle. To make an analogy, before ORACLE 12C, instances and databases were one-to-one, which means that an instance can only have one database. Unlike MySQL and SQL Server, an instance can have multiple databases and can query across databases at will. PGSQL cannot cross databases. The reason for database query is also the same. PGSQL allows multiple databases to be built. In analogy with ORACLE, there are multiple instances (the instances and databases mentioned before are one-to-one). One database is equivalent to one instance, because PGSQL allows multiple instances, so A single instance of PGSQL is not called an instance, it is called a cluster. For the concept of cluster, you can check the relevant information of PGSQL. The schema under an instance/database in PGSQL is equivalent to a database, so the concept of this schema corresponds to the database of MySQL. Notes: 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 operations cannot be performed on all databases under a PGSQL cluster at one time and must be performed one by one. Operate database by database, such as installing the pg_stat_statements plug-in mentioned above. If you need to collect performance from all databases under the PGSQL cluster, you need to execute the load command one by one. For example, cross-database query requires the dblink plug-in or fdw plug-in. Querying between two databases is equivalent to querying between two instances. It has spanned instances, so dblink plug-in or fdw plug-in is required, so the principle is very simple. Permission operations are also performed on a database-by-database basis. The other one is PGSQL. It is like the permission hierarchy of SQL Server db=》schema=》object, but it is actually more complicated than SQL Server. In addition, the newly created table needs to be authorized separately in PGSQL. The roles and users are the same, so for novice users He said that sometimes he can’t tell the difference, and he doesn’t know how to use roles, so PGSQL is really tricky in terms of permission design.

MySQL uses the 5 permission tables under the mysql library to do permission mapping, which is simple Clear, the only problem is the lack of permissions role user table db table host table tables_priv table columns_priv table

34. Development history

PGSQL In 1995, developers Andrew Yu and Jolly Chen added a SQL (Structured Query Language, Structured Query Language) translation program to Postgres. This version is called Postgres95 and is released in the open source community. In 1996, major changes were made to Postgres95 again, and it was released as PostgresSQL version 6.0. The name of PostgresSQL was finalized. Counting from 1995, it has a history of about 24 years.

MySQL was launched in 1996 In 2009, MySQL 1.0 was released. It was only available to a small group of people and was equivalent to an internal release. In October 1996, MySQL 3.11.1 was released (MySQL does not have a 2.x version). At first, it only provided a binary version under the Solaris operating system. A month later, the Linux version appeared. Counting from 1996, it has a history of about 23 years.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the difference between postgresql and mysql. For more information, please follow other related articles on the PHP Chinese website!