Shulou(Shulou.com)06/01 Report--

This article is to share with you about the differences between pgsql and mysql. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The differences between pgsql and mysql are as follows: the functional implementation of pgsql is more rigorous than mysql, and the table join support is more complete; the optimizer of pgsql is complete and supports many index types, while the optimizer of mysql is relatively simple and suitable for simple query operations.

Advantages of PostgreSQL over MySQL

1. The standard implementation of SQL is more perfect than that of MySQL, and the functional implementation is more rigorous.

2. The functional support of stored procedures is better than that of MySQL, and it has the ability of local cache to execute plans.

3. The table join is fully supported, the function of the optimizer is complete, many index types are supported, and the complex query ability is strong.

4. The main table of PG is stored by heap table, and MySQL uses index to organize the table, which can support a larger amount of data than MySQL.

5. The active and standby replication of PG belongs to physical replication. Compared with MySQL binlog-based logical replication, the consistency of data is more reliable, the replication performance is higher, and the impact on host performance is less.

6. The plug-in mechanism of MySQL storage engine has the problem of complex locking mechanism affecting concurrency, but PG does not exist.

Advantages of MySQL over PostgreSQL

1. Innodb's MVCC mechanism based on rollback segment is superior to XID-based MVCC mechanism in which PG new and old data are stored together. When the new and old data are stored together, the VACUUM needs to be triggered regularly, which will bring redundant IO and database object locking overhead, resulting in a decline in the overall concurrency ability of the database. And VACUUM cleaning is not timely, it may also lead to data inflation.

2. MySQL uses indexes to organize tables, which is very suitable for query and deletion operations based on primary key matching, but there are constraints on table structure design.

3. The optimizer of MySQL is simple, and the implementation of system tables, operators and data types is very concise, which is very suitable for simple query operations.

4. The implementation of MySQL partition table is better than that of PG based on inheritance table, which is mainly reflected in the great difference in processing performance when the number of partitions reaches tens of thousands.

5. The plug-in mechanism of MySQL storage engine makes its application scenarios more extensive. For example, except that innodb is suitable for transaction processing scenarios, myisam is suitable for static data query scenarios.

Summary

Generally speaking, the open source database is not perfect, and the commercial database oracle is much better in terms of architecture and function. In terms of application scenarios, PG is more suitable for strict enterprise application scenarios (such as finance, telecommunications, ERP, CRM), while MySQL is more suitable for Internet scenarios with relatively simple business logic and low data reliability requirements (such as google, facebook, alibaba).

Thank you for reading! What are the differences between pgsql and mysql to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.