Recently saw the topic of PostgreSQL more, search for it and MySQL comparison
Cicada
Links: http://www.zhihu.com/question/20010554/answer/74037965
Source: Know
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).
What are the advantages of PostgreSQL compared to MySQL? Go
