What is the difference between pgsql and mysql

The differences between pgsql and mysql are: 1. The function implementation on pgsql is more rigorous than that on mysql, and the support for table connections is more complete; 2. The optimizer of pgsql has complete functions and supports many index types, while mysql’s The optimizer is relatively simple and suitable for simple query operations and so on.

The differences between pgsql and mysql are: the function implementation on pgsql is more rigorous than mysql, and the support for table connections is more complete; the optimizer function of pgsql is complete. It supports many index types, and the mysql optimizer is relatively simple and suitable for simple query operations

Advantages of PostgreSQL over MySQL

1. Standard implementation of SQL It is more complete than MySQL, and the function implementation is more rigorous;

2. The functional support of stored procedures is better than MySQL, and it has the ability to cache execution plans locally;

3. Support for table connections More complete, the optimizer has more complete functions, supports many index types, and has strong complex query capabilities;

4. PG main table is stored in a heap table, while MySQL uses index-organized tables, which can support larger databases than MySQL. amount of data.

5. PG's primary and secondary replication is physical replication. Compared with MySQL's binlog-based logical replication, data consistency is more reliable, replication performance is higher, and the impact on host performance is smaller.

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

Advantages of MySQL over PostgreSQL

1. Innodb’s MVCC mechanism based on rollback segments, compared to the XID-based MVCC mechanism that stores old and new data together in PG , is dominant. New and old data are stored together, and VACUUM needs to be triggered regularly, which will bring redundant IO and database object locking overhead, causing the overall concurrency capability of the database to decrease. Moreover, if VACUUM is not cleaned up in time, it may cause data expansion;

2. MySQL uses indexes to organize tables. This storage method is very suitable for queries and delete operations based on primary key matching, but there are constraints on the table structure design;

3. MySQL's optimizer is relatively simple, and the implementation of system tables, operators, and data types is very streamlined, which is very suitable for simple query operations;

4. The implementation requirements of MySQL partition tables The inheritance table-based partition implementation that is superior to PG is mainly reflected in the large difference in processing performance when the number of partitions reaches thousands or tens of thousands.

5. MySQL's storage engine plug-in mechanism makes its application scenarios more extensive. For example, in addition to innodb being suitable for transaction processing scenarios, myisam is suitable for static data query scenarios.

Summary

Generally speaking, open source databases are not very perfect, and the commercial database Oracle is still much more complete in terms of architecture and functions. 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 ).

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