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

PG or MySQL in the end which is better, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Overview

The editor introduces the comparison of some features of MySQL and PostgreSQL, so that you can understand the advantages and disadvantages of the two and make a better choice. In the current domestic situation, Internet companies use MySQL more, but the proportion of PostgreSQL is not high, but I believe that after seeing the new features of PG, you will fall in love with her. Of course, MySQL, as the most popular database, will still attract the attention of most people.

PostgreSQL touts itself as the most advanced open source database in the world, and even PG fans or some PGER claim that she can compete with Oracle (although PG is powerful, there is still a gap with Oracle, and of course, the advantages of PG are obvious), and not so expensive and arrogant customer service.

Of course, PG is fully functional and powerful, which began in version 9 and developed rapidly in version 10, adding a lot of features and features. PostgreSQL is a completely community-driven open source project, its core code is maintained by the community, and the commercial version is based on the secondary development of PG.

MySQL claims to be the most popular open source data. Looking at the current situation in China, it can be called worthy of the name. After being sold several times, MySQL finally fell into the hands of Oracle. It is for this reason that Monty, the father of MySQL, modified the source code of MySQL and created the MariaDB branch.

Speaking of which, of course, we have to mention another important branch, Percona Server of Percana. Percona is better at MySQL operations and has developed many very useful operation and maintenance tools, all of which are open source and given back to the community, such as XtraBackup and pt-Toolkits tools.

A simple comparison of MySQL and PostgreSQL found that behind MySQL is a mature business company (Oracle has its own MySQL Enterprise version, charged, there are many features that the community version does not have), while PostgreSQL is behind a large voluntary development team, compared to less commercial nature of PostgreSQL, he does not have the so-called PostgreSQL Enterprise Edition, but there are some enterprise-level PG databases developed based on PG.

Below, I will explain the similarities and differences and advantages and disadvantages of MySQL and PostgreSQL from the following aspects. Due to the limitations and inadequacies of the author, I would like to ask you to make more comments.

I. Open source

PostgreSQL: The world's most advanced open source database .

Open source agreement: PostgreSQL is based on a free BSD/MIT license that allows organizations to use, copy, modify, and redistribute code by providing a copyright notice.

PG's open source agreement is so flexible that any company or individual can sell PG as a product without having to modify most of the code to be a company product, as MySQL does.

MySQL:World's Most Popular Open Source Database .

Open source protocols: the core code is based on GPL or Commercial License.

MySQL's open source agreement is based on the GPL agreement, which can be used by any company for free. Modified and derived code is not allowed to be released and sold as closed-source commercial software. The copyright of MySQL is in the hands of Oracle, which can push its commercial closed-source version.

As shown in the figure above, open source software protocols

II. ACID support

PostgreSQL supports strong consistency of transactions, good transaction assurance, and fully supports ACID features.

MySQL only innodb engine supports transactions, transaction consistency guarantee can be adjusted according to the actual needs, in order to maximize the protection of data, MySQL can configure dual mode, the support for ACID is slightly weaker than PG.

III. Support of SQL standard

PostgreSQL supports almost all SQL standards and supports a wide range of types.

MySQL supports only part of the SQL standard, which is slightly weaker than the type of PG support.

IV. Replication

The replication of MySQL is a logical asynchronous replication based on binlog, so synchronous replication can not be realized.

Replication mode:

One master, one preparation.

One master, many preparations.

Cascade replication.

Circular replication.

Master copy.

Advantage of data flow: through the subscription and consumption of canal incremental data, you can synchronize data to kafka and transfer data through kafka.

All the high availability schemes of MySQL are based on binlog synchronization, and the distributed data based on MySQL is also based on binlog implementation of MySQL. Binlog is the most basic technology implementation of MySQL biosphere.

PostgreSQL can achieve synchronous, asynchronous, semi-synchronous replication, as well as log-based logical replication, can achieve table-level subscriptions and publications.

Replication mode:

One master, one preparation.

One master, many preparations.

Cascade replication.

Hot backup library / stream replication.

Logical replication.

Advantages of data flow: subscription and consumption of messages are realized through logical replication, data can be synchronized to kafka, and data flow is realized through kafka.

5. Concurrency control

PostgreSQL solves the concurrency problem effectively through its MVCC implementation, thus achieving very high concurrency.

PG new and old data are stored together based on XID MVCC mechanism, new and old data are stored together, and VACUUM needs to be triggered regularly, which will bring redundant IO and database object locking overhead, resulting in a decline in the overall concurrency of the database. And VACUUM cleaning is not timely, may also lead to data ballooning.

Of course, PostgreSQL also has an impact on comparison, in order to ensure strong consistency of transactions, outstanding transactions will affect all table VACUUM cleanup, resulting in table inflation.

MySQL supports MVCC only in InnoDB.

Innodb's MVCC mechanism is based on the rollback segment, but the gap lock of MySQL has a great influence and has a lot of locking data.

VI. Performance

1 、 PostgreSQL

1) PostgreSQL is widely used in large-scale systems with high reading and writing speed and high data consistency. In addition, it supports a variety of performance optimizations that are only available in commercial solutions, such as geospatial data support, no read locking concurrency, and so on.

2) PostgreSQL performance is most suitable for systems that need to execute complex queries.

3) PostgreSQL performs well in OLTP/ OLAP system, reading and writing speed and big data analysis, and GP database based on PG performs well in the field of data warehouse.

4) PostgreSQL is also suitable for business intelligence applications, but it is more suitable for data warehouses and data analysis applications that require fast read / write speeds.

2 、 MySQL

1) MySQL is a widely selected Web-based project that requires a database only for simple data transactions. However, MySQL usually does not perform well when it comes to heavy loads or attempts to complete complex queries.

2) the reading speed of MySQL performs well in OLTP system.

3) MySQL + InnoDB provides a very good read / write speed for OLTP scenarios. Overall, MySQL performs well in high concurrency scenarios.

4) MySQL is reliable and works well with business intelligence applications, which usually read a lot.

VII. Implementation of highly available technologies

1 、 PostgreSQL

1) Asynchronous and synchronous master-slave based on stream replication.

2) stream-based replication-keepalive.

3) stream-based replication-repmgr.

4) stream-based replication-patroni+etcd.

5) shared storage HA (corosync+pacemaker).

6) Postgres-XC.

7) Postgres-XL.

8) Middleware implementation: pgpool, pgcluster, slony, plploxy.

2 、 MySQL

1) Master-slave replication.

2) the main reply.

3) MHA.

4) LVS+KEEPALIVE.

5) MGR distributed database, multi-point writing is not recommended, based on paxos protocol.

6) PXC distributed database, multi-point writing is not recommended, based on token ring protocol.

7) INNODB CLUSTER [8.0 new technology, based on MGR implementation, upper layer encapsulating commands], based on paxos protocol.

8) Middleware implementation: mycat.

VIII. External data sources

PostgreSQL FDW-[an abbreviation for foreign-data wrapper, which can be called external encapsulation.

PostgreSQL does not support multiple data engines. However, it supports the expansion of Extension components, and reads and writes Oracle, Hadoop, MongoDB, SQLServer, Excel, CSV files as external tables through a technology called FDW, so it can provide a good interface between big data and relational database.

MySQL: none.

IX. Data storage and data types

The main table of PG is stored by heap table, which stores a large amount of data, and the way of data access is similar to that of Oracle.

MySQL uses indexes to organize tables. MySQL must have primary key indexes. All data access is achieved through primary keys. When secondary indexes are accessed, indexes (primary key and secondary index) need to be scanned twice.

Comparison of advantages and disadvantages between PostgreSQL and MySQL

1. 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 execution plan.

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) PG master table is stored in heap table, and MySQL uses index to organize tables, which can support a larger amount of data than MySQL.

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

6) the storage engine plug-in mechanism of MySQL has the problem that the lock mechanism is complex and affects concurrency, but PG does not exist.

7) PG pairs can query external data sources, and the types of data sources are rich.

8) PG native logical replication can achieve table-level subscription and publication, and data can be transferred through kafka without the need for other components.

9) PG supports three table joins, nested loops, hash joins, and sort merging, while MySQL only supports nested loops.

10) the PostgreSQL source code is written clearly and is much more readable than MySQL.

11) PostgreSQL supports geospatial data through PostGIS extensions. Geospatial data has dedicated types and functions that can be used directly at the database level, making it easier for developers to analyze and code.

12) Extensible system with rich extensible components, released as contribute.

13) PostgreSQL supports JSON and other NoSQL features, such as native XML support and use of HSTORE key-value pairs. It also supports indexing JSON data for faster access, especially version 10 JSONB is more powerful.

14) PostgreSQL is completely free, and it's a BSD agreement. If you change the PostgreSQL and then sell it for money, no one cares about you, which shows that the PostgreSQL database will not be controlled by other companies. On the contrary, MySQL is now mainly controlled by Oracle.

2. Advantages of MySQL over PG

1) the MVCC mechanism implemented by innodb based on rollback segment is superior to the 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, may also lead to data ballooning.

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 popularity of MySQL is higher than that of PG in China, and PG is somewhat lonely in China.

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.

Generally speaking, the open source database is not very 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), but it is not limited to this. Data formats such as PostgreSQL's json,jsonb,hstore are especially suitable for the analysis of some big data formats. MySQL is more suitable for Internet scenarios with relatively simple business logic and low data reliability requirements (such as google, facebook, alibaba). Of course, with the vigorous development of innodb engine, MySQL performs well.

MySQL and PostgreSQL complex open source relational database, this article is only the author based on his own experience to write the understanding of PG and MySQL, there will inevitably be inappropriate, inappropriate also please correct a lot.

The development of MySQL in China has been very mature, but if you turn to PostgreSQL, you will find a different world, academic style, rich features, will certainly bring you a different surprise.

After reading the above, have you mastered which method is better to use, PG or MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.