MySQL and PostgreSQL comparison

1, MySQL relatively young, for the first appearance in 1994 years. It claims to be the most popular open source database. MySQL is the M in the lamp (for web development packages, including Linux, Apache, and Perl/php/python). Most applications built on the lamp stack use MySQL, including those that are well-known applications such as WordPress, Drupal, Zend, and phpBB.
At first, MySQL was designed to become a fast Web server backend, using a fast indexed sequence access method (ISAM), which does not support acid. After an early and rapid development, MySQL began to support more storage engines and achieved acid through the InnoDB engine. MySQL also supports other storage engines, provides temporary table functionality (using the memory storage engine), implements a high-speed read database through the MyISAM engine, and other core storage engines and third-party engines.
2. PostgreSQL advertises itself as the most advanced open source database in the world. Some of PostgreSQL's fans say it is comparable to Oracle and has less expensive price and arrogant customer service. It has a long history, originally developed in 1985 at the University of California, Berkeley, as a successor to the Ingres database.
PostgreSQL is a fully community-driven open source project maintained by more than 1000 contributors worldwide. It provides a single, full-featured version, unlike MySQL, which offers a number of different community, commercial, and Enterprise editions. PostgreSQL is based on a free Bsd/mit license, and the organization can use, copy, modify, and redistribute the code, only to provide a copyright notice.
Although there are different histories, engines and tools, there is no clear reference to which of the two databases can be used in all cases. Many organizations like to use PostgreSQL because of its reliability, good at protecting data, and a community project that is not trapped in a vendor's cage. MySQL is more flexible and offers more options for tailoring to different tasks. Most of the time, it is more important for an organization to be proficient in a particular software than it is for an attribute. Cicada
Links: https://www.zhihu.com/question/20010554/answer/74037965
Source: Know
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

The advantages of PG relative to MySQL:
1, the standard implementation of SQL is better than MySQL, and the function of the implementation of more rigorous;
2, the function of the stored procedure is better than MySQL, with the ability to cache the execution plan locally;
3, the table connection support is more complete, the function of the optimizer is more complete, the supporting index type is many, the complex query ability is strong;
4, pg Main Table with heap table storage, MySQL using index organization table, can support larger than MySQL data volume.
5, PG's primary and standby replication is a physical replication, compared to MySQL based on binlog logical replication, data consistency is more reliable, replication performance is higher, the impact on host performance is also smaller.
6, MySQL storage engine plug-in mechanism, there is a complex lock mechanism affects concurrency problems, and PG does not exist.

Ii. MySQL's advantages over PG:
1, InnoDB based on the implementation of the rollback segment MVCC mechanism, relative to PG new and old data stored together based on XID MVCC mechanism, is superior. The new and old data are stored together, it is necessary to trigger the vacuum periodically, which will result in extra lock overhead of IO and database objects, and decrease the concurrency of the whole database. and vacuum cleanup is not timely, it may also trigger data expansion;
2, MySQL uses index organization table, this kind of storage method is very suitable based on the primary key matching query, the deletion operation, but has the constraint to the table structure design;
3, the MySQL optimizer is simpler, the system table, operator, data type implementation are very concise, very suitable for simple query operation;
4, the implementation of MySQL partition table is better than PG based on the inheritance table partition implementation, mainly reflected in the number of partitions reached tens of thousands of processing performance difference.
5, MySQL storage engine plug-in mechanism, making it more extensive application scenarios, for example, in addition to INNODB suitable for transaction processing scenarios, MyISAM suitable for static data query scenarios.

Third, in general, the open source database is not very perfect, the business database Oracle in terms of architecture and functionality is still a lot better. From the application scenario, PG is more suitable for rigorous enterprise applications (such as finance, telecommunications, ERP, CRM), while MySQL is more suitable for Internet scenarios where business logic is relatively simple and data reliability is less demanding (such as Google, Facebook, Alibaba). MySQL supports stored procedures
MySQL supports semi-synchronous replication, Google patches
InnoDB's MVCC basically can do the transaction without lock

MySQL and PostgreSQL comparison