Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what is the difference between postgresql and mysql". It is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "what is the difference between postgresql and mysql".

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

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

PostgreSQL is a fully featured object-relational database management system (ORDBMS) for free software.

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

Comparison of differences between postgresql and mysql

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

1. CPU restrictions

There is no limit on the number of CPU cores in PGSQL. You can use as many CPU cores as you have.

MySQL can use 128core CPU, but not more than 128core.

two。 Profile parameters

PGSQL has a total of 255parameters, of which about 80 are used. The parameters are relatively stable. You can also start the current large version of the database with the previous large version configuration file.

MySQL has a total of 707 parameters, about 180 of which are used. Parameters continue to increase, even small versions will add parameters, and there will be some parameter incompatibility between large versions.

3. Third-party tool dependencies

For PGSQL, only highly available clusters need to rely on third-party middleware, such as patroni+etcd, repmgr

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

4. The underlying principle of highly available master-slave replication

PGSQL physical stream replication belongs to physical replication. Like SQL Server image / AlwaysOn, it is strictly consistent and does not cause any inconsistency. In terms of performance and reliability, physical replication beats logical replication and is easy to maintain.

MySQL master-slave replication belongs to logical replication. (incorrect settings of sql_log_bin, binlog_format and other parameters will lead to master-slave inconsistency.) large transaction parallel replication is inefficient. For important business, it is necessary to rely on percona-toolkit 's pt-table-checksum and pt-table-sync tools to regularly compare and repair master-slave consistent master-slave replication. When there is a serious error, the logical replication of master-slave MySQL does not prevent two inconsistent databases from establishing a replication relationship.

5. Read-only status from the library

The PGSQL system automatically sets the default read-only from the library, does not require human intervention, and is easy to maintain.

MySQL slave library needs to set the parameter super_read_only=on manually to make it 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 community version, without any 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 some domestic and foreign database manufacturers that do secondary development based on PGSQL, such as Enterprise DB, Henkel database and so on. Of course, these are only secondary development and not independent branches.

Due to historical reasons, MySQL is divided into three branch versions, the MariaDB branch, the Percona branch and the official branch of Oracle. So far, each branch is basically incompatible with each other. The official branch of Oracle is also divided into the standard version, the enterprise version, the classic version and the community version.

7. SQL feature support

94 PGSQLSQL features are supported, and SQL syntax supports the most perfect, such as common table expressions (WITH queries).

36 MySQLSQL features are supported, while SQL syntax support is relatively weak. For example, common table expressions (WITH query) are not supported. For more information on SQL feature support, please see: http://www.sql-workbench.net/dbms_comparison.html

8. Master-slave replication security

PGSQL synchronous flow replication, strong synchronization (remote apply), high security, and no data loss. PGSQL synchronous flow replication: if all slave libraries are down, the master database will go on strike, and the master database cannot be automatically switched to asynchronous flow replication (asynchronous mode), which needs to be solved by increasing the number of slave libraries.

In a general production environment, at least two slaves are manually solved: modify the parameter synchronous_standby_names =''in the PG master library and execute the command: pgctl reload. Switching the master database to asynchronous mode is the first prerequisite for high availability switching.

Therefore, it is understandable that PGSQL chose the main library strike to enhance semi-synchronous replication by MySQL.

Mysql5.7 version enhances semi-synchronization to ensure that data will not be lost during master-slave replication. Parameters related to mysql5.7 semi-synchronous replication: parameter rpl_semi_sync_master_wait_for_slave_count waits for at least how many slaves to receive binlog before the master database commits the transaction

Generally set to 1, the highest performance parameter rpl_semi_sync_master_timeout waits for how many milliseconds, and automatically switches from the library without response to asynchronous mode.

Generally set to infinite, do not let the master library automatically switch to asynchronous mode, all slave libraries are down, the master library will go on strike

Cannot receive any answer package from the slave library for manual resolution: modify the parameter rpl_semi_sync_master_wait_for_slave_count=0 in the MySQL master library

9. Multi-field statistics

PGSQL supports multi-field statistics

MySQL does not support multi-field statistics

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 (virtual column needs to be created), hash index only in memory table

11. Physical table join algorithm

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

MySQL only supports nested-loop join

twelve。 Subquery and view performance

PGSQL subquery, view optimization, high performance

Under the condition of MySQL view predicate, there are many push restrictions and many pull-up restrictions for subqueries.

13. Immediate compilation of execution plan

PGSQL supports immediate compilation of JIT execution plans, using the LLVM compiler

MySQL does not support immediate compilation of execution plans

14. Parallel query

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

MySQL is limited and only supports primary key parallel queries.

15. Materialized view

PGSQL supports materialized views

MySQL does not support materialized views

16. Plug-in function

PGSQL supports plug-in functions, which can enrich the functions of PGSQL, GIS geography plug-ins, time series database plug-ins, vector execution plug-ins and so on.

MySQL does not support plug-in functionality

17. Check constraint

PGSQL supports check constraints

MySQL does not support check constraints, you can write check constraints, but the storage engine ignores its role, so check constraints do not work (mariadb support)

18. Gpu accelerates SQL

PGSQL can use gpu to accelerate the execution of SQL

MySQL does not support gpu to accelerate the execution of SQL

19. Data type

PGSQL is rich in data types, such as ltree,hstore, array type, ip type, text type. With text type, varchar,text type field maximum storage 1GB is no longer required.

MySQL data types are not rich enough.

20. Cross-database query

PGSQL does not support cross-database queries, which is the same as before Oracle 12C

MySQL can be queried across libraries

21. Backup and restore

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

MySQL backup and restore is relatively not simple. Full backup + binlog backup (incremental) full backup requires percona's XtraBackup tool to do physical backup. MySQL itself does not support physical backup. The operation steps of point-in-time restore are cumbersome and complicated.

twenty-two。 Performance View

PGSQL needs to install the pg_stat_statements plug-in, and the pg_stat_statements plug-in provides rich performance views: for example, waiting for events, system statistics and other disadvantages are that the database needs to be restarted to install the plug-in, and the database that needs to collect performance information needs to execute a command: create extension pg_stat_statements command or it will not collect any performance information, which is troublesome.

MySQL comes with PS libraries, and many functions are not turned on by default, and opening the performance view function of PS libraries has an impact on performance (for example, memory consumption leads to OOM bug)

23. Installation mode

PGSQL has packages for various platforms, such as rpm package, deb package and so on. Compared with MySQL, it lacks a binary package. It is generally compiled and installed with source code, which takes longer to install and executes more commands.

MySQL has packages for various platforms, such as rpm package, deb package, etc., source code compilation and installation, binary package installation, generally using binary package installation, convenient and fast

24. DDL operation

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

MySQL since most DDL operations lock tables, such as adding fields and increasing the length of variable length fields, you need to use the pt-online-schema-change tool in percona-toolkit to minimize the impact, especially for DDL operations on large tables. DDL operations cannot be rolled back.

25. Release speed of large version

PGSQLPGSQL releases a large version every year, and it can be released in production environment in the second year. The iteration speed of the version is very fast. PGSQL 9.6 official release time: 2016 PGSQL 10 official release time: 2017 PGSQL 11 official release time: 2018 PGSQL 12 official launch time: 20119

The release of a large version of MySQLMySQL is usually 2 to 3 years. Generally, the second year after the release of a large version, you can go to the production environment to avoid pits. The release speed of the version is relatively slow. MySQL5.5 official release time: 2010 MySQL5.6 official launch time: 2013 MySQL5.7 official launch time: 2015 MySQL8.0 official launch time: 2018

twenty-six。 Returning syntax

PGSQL supports returning syntax, and returning clause supports DML to return Resultset, reducing one Client DB Server interaction.

MySQL does not support returning syntax

twenty-seven。 Internal architecture

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

MySQL multithreaded architecture, although multithreaded architecture, but officially limited the number of connections, because the concurrency of the system is limited, there are too many threads, but the processing capacity of the system decreases, with the increase in the number of connections, the performance is generally only 200300 database connections at the same time.

twenty-eight。 Clustered index

PGSQL does not support clustered indexes, which is caused by the implementation mechanism of MVCC in PGSQL itself.

MySQL supports clustered indexes

twenty-nine。 Idle transaction termination function

PGSQL terminates idle transactions by setting the idle_in_transaction_session_timeout parameter. For example, if you forget to close open transactions in the application code, PGSQL will automatically kill this type of session transaction.

MySQL does not support terminating idle transactions

thirty。 Deal with large amount of data

PGSQL can not cope with the huge amount of data. Due to the MVCC design problems of PGSQL itself, garbage collection is needed, so we can only look forward to the optimization of the later large version.

MySQL can not cope with the problems of large amount of data and MySQL's own architecture.

thirty-one。 Distributed evolution

PGSQLHTAP database: cockroachDB, Tencent Tbase sharding cluster: Postgres-XC, Postgres-XLMySQLHTAP database: TiDB sharding cluster: various middleware, not enumerated one by one

thirty-two。 File name and naming rule of database

PGSQLPGSQL does not do well in this respect. DBA cannot clearly see the file name and naming rule of the database at the operating system level (in the state of database shutdown), the number of files and the size of files. Once the file is lost or the hard disk is damaged in the operating system, it is not conducive to recovery, because the name does not even know that the naming / storage rule of PGSQL table data physical files is: under a table space. If no tablespace is created, the default tablespace is under the default base folder, for example: / data/base/16454/3599base: the physical folder where the default tablespace pg_default is located: 16454: the oid3599 of the database where the table resides: the oid of the table object. Of course, multiple physical files will be generated when the size of a table exceeds 1GB, as well as table fsm files and vm files. So a large table will actually have multiple physical files because there are too many data file layouts in PGSQL, so you can consult the relevant information, of course, this is not all the fault of PGSQL, as a DBA, always do a good database backup and disaster recovery is the right way, media recovery is usually only done as a last resort.

The MySQL database name is the folder name, and under the database folder are the table data files. Each table has corresponding frm files and ibd files. Metadata and table / index data are stored. It is clear and convenient to do media recovery or tablespace transfer.

thirty-three。 Authority design

PGSQLPGSQL is rather tricky when it comes to permission design. Apart from instance permissions and tablespace permissions, the permission level of PGSQL is a bit like that of SQL Server,db= "schema=" object. To talk about Oracle, use Oracle to compare that before ORACLE 12C, instances and databases were one-to-one, that is to say, an instance can only have one database, unlike MySQL and SQL Server. And the reason why you can query across databases at will is also the reason why PGSQL cannot. PGSQL allows you to build multiple databases. The analogy with ORACLE means that there are multiple instances (the instance and database are one-to-one). Because PGSQL allows multiple instances, a single instance of PGSQL is not called an instance, but a cluster (cluster). The concept of clustering can refer to the relevant information of PGSQL. The schema under an instance / database in PGSQL is equivalent to a database, so the concept of schema corresponds to the database note of MySQL: precisely because a database is equivalent to an instance, PGSQL allows multiple instances / databases, so databases are logically isolated from each other, resulting in problems You cannot operate on all databases under a PGSQL cluster one by one. For example, if you need to install the pg_stat_statements plug-in mentioned above, if you need to do performance collection for all databases under the PGSQL cluster, you need to execute load commands one by one. For example, cross-database queries require dblink plug-ins or fdw plug-ins. Queries between two databases are equivalent to queries between two instances. It needs dblink plug-in or fdw plug-in, so it is very simple to operate permission operations on a database-by-database basis. Another reason is that although PGSQL is like SQL Server's permission hierarchy db= "schema=" object, it is actually a little more complicated than SQL Server. In addition, the newly built table needs additional authorization in PGSQL, the roles and users are the same, and sometimes novice users are confused. I don't know how to use roles, so PGSQL is really tricky when it comes to permission design.

MySQL uses the five permission tables under the mysql library to do permission mapping, which is simple and clear. The only problem is the lack of permission roles, user table, db table, host table, tables_ private table, columns _ privtable.

thirty-four。 History of development

PGSQL in 1995, developers Andrew Yu and Jolly Chen added a SQL (Structured Query Language, structured query language) translator to Postgres, a version called Postgres95, distributed in the open source community. In 1996, another major change was made to Postgres95, and it was named PostgresSQL version 6.0. the name of PostgresSQL was finalized, and it has a history of about 24 years since 1995.

MySQL was released in 1996 when MySQL 1. 0 was only available to a small group of people, the equivalent of an internal release. In October 1996, MySQL 3.11.1 was released (MySQL does not have 2.x version). At first, only the binary version under the Solaris operating system was available. A month later, the Linux version appeared in 1996, about 23 years old.

The above is all the content of the article "what's the difference between postgresql and mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.