Translation The advantages of PostgreSQL over MYSQL/MARIADB

Over the past decade, many developers and professionals have compared MySQL with PostgreSQL, most of whom consider the latter to be more superior in performance. The supporters of PostgreSQL argue that its standard support and acid[1] are more than MySQL. MySQL is also popular because there are many Linux Web application installation packages with it, but since Sun has been acquired by Oracle with MySQL copyright and trademark, people are worried that MySQL will not be as open as before. PostgreSQL is not only fast, but also supports JSON, making him a handful of NoSQL-enabled relational databases. (Gossip: MySQL's author, Monty Widenius, builds a mariadbthat is fully compatible with MySQL and is not owned by Oracle in the MySQL branch. ）

The current version of MYSQL/MARIADB is 5.7.6, while the PostgreSQL version is 9.4.1.

ANSI Standard:

MySQL is more supportive of the standard than its earlier version (MySQL says: If the user likes it, they can support non-standard), but PostgreSQL supports it from the start and MySQL joins it.

Draw

ACID Properties

Postpresql has a storage engine, MySQL has 9. However only two are widely used: Myisam,innodb. MyISAM is the first engine, fast but does not support transactions, InnoDB supports transactions, and is faster, is the default engine. MySQL's InnoDB engine and PostgreSQL all have acid properties, no difference.

Draw

Table lock

MyISAM uses table locks to get faster speeds. Performance is good when a large number of read operations are performed, but the write operation will lock the entire table and wait for the write to complete. The InnoDB engine and PostgreSQL use row-level locks, which are also flat.

Draw

Sub-query uncertainty

subqueries have always been MySQL "Notorious" problem: two or two layers or more sub-query efficiency is very low, although 5.6.5 has been a great improvement, but postpresql in the joint query performance more prominent, especially MySQL does not support full Outer Joins.

PostgreSQL wins

Support JSON and NoSQL

PostgreSQL new features support the use of NoSQL and store json. Data storage is more flexible than traditional relational databases.

PostgreSQL wins

Other benefits of PostgreSQL:

Better licensing.

Postgrepost has a Mit-style certificate that allows you to do anything, including commercial use of open/closed sources. The MySQL client library, which is a GPL certificate, means you have to pay a business fee like Oracle, or provide the program source code. (web site using MySQL will be a little trouble; Mariadb uses GPL2 certificates and provides less restrictive LGPL certificates for MySQL clients)

Better data integrity

PostgreSQL defaults to a strict check of inserted and updated data, and MySQL is only turned on (strict_all_tables/strict_trans_taqbles) for rigorous inspection.

Database extension Development

two of databases allow extended development. MySQL provides API interfaces with C-series languages. You can even do a full-text search in the InnoDB table after MySQL5.7.3. PostgreSQL also has the same functionality, but supports more languages, including C/C++,JAVA,.NET,PERL,PYTHON,RUBY,TCL,ODBC, and can even run user-supplied code in a separate process (background).

PostgreSQL provides information about database, table, row/column data types, methods, access methods, and so on in the system directory. Users can modify the system catalog tables to extend the database.

Conclusion

Although I have used two databases for more than 10 years, and MySQL has improved a lot, but has to admit to the preference for PostgreSQL. PostgreSQL better uses licenses, custom data types, table inheritance, regular system and database events.

[1] ACID: refers to the four basic elements of database transactions performed correctly: Atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability)

sql:structured Query Language( Structured query statement )

Translation The advantages of PostgreSQL over MYSQL/MARIADB