In this article, we compare one of the most popular database management systems (DBMS) in the world, MySQL, with DB-Engines\u2019 DBMS of the year 2017<\/a>, PostgreSQL, to see which can offer more to users in 2018.<\/p>\n MySQL<\/a> started as a personal project created by David Axmark and Michael Widenius as a free, speedy, and more flexible replacement for the lightweight database management system called mSQL. Today, MySQL is owned by Oracle, which is why Widenius decided to fork it and launch MariaDB<\/a> to serve as a free and community-developed drop-in replacement.<\/p>\n The origin of PostgreSQL<\/a> can be traced to the year 1982 and the Ingres project at the University of California, Berkeley. PostgreSQL is known for its emphasis on extensibility and compliance with standards. It follows a regular release schedule<\/a>, aiming to make at least one minor release every quarter.<\/p>\n Both PostgreSQL and MySQL are mature relational database management systems with support for a wide range of features, foreign key references, including database replication, triggers, updatable views, full-text search, and many others.<\/p>\n Since version 9.1<\/a>, PostgreSQL supports triggers on views. \u201cMySQL triggers activate only for changes made to tables by SQL statements. This includes changes to base tables that underlie updatable views,\u201d as stated in the official documentation<\/a>.<\/p>\n The current stable version of MySQL, 5.7, does not support CTE (Common Table Expressions), which are a way how to create a view or temporary table for a single query. A major benefit of CTE is that this feature can improve the readability of SQL code. Support for CTE is available in MySQL 8.0.1<\/a>, whose first public milestone was announced<\/a> in September 2016. PostgreSQL supports CTE<\/a>.<\/p>\n Another important feature that was has been available to MySQL users only for a short while, since the release of MySQL 8.0.2, is the support for Window Functions<\/a>, which perform some calculation on a set of rows similar to grouped aggregate functions.<\/p>\n Also worth mentioning is MySQL\u2019s lack of support for Materialized Views, a feature readily supported by PostgreSQL<\/a>. As described<\/a> by Oracle, a Materialized View is \u201ca table segment whose contents are periodically refreshed based on a query, either against a local or remote table.\u201d<\/p>\n As demonstrated<\/a> by Alexander Korotkov and Sveta Smirnova, both PostgreSQL and MySQL are perfectly capable of handling millions of queries per second. This is thanks to a series of recent optimizations these two popular database management systems recently made for big servers.<\/p>\n However, it should be noted that MySQL natively does not support any kind of multi-threading. There are ways how to increase MySQL performance<\/a> with parallel query execution, but they require a bit of work or third-party plugins. On the other hand, PostgreSQL can devise query plans which can leverage multiple CPUs in order to answer queries faster.<\/p>\n One of the most often cited advantages of PostgreSQL over MySQL is its full ACID compliance. ACID stands for atomicity, consistency, isolation, and durability, and it is a set of properties of database transactions that ensure transaction validity even in the event of a sudden power outage or a critical error.<\/p>\n MySQL is ACID compliant only when using the InnoDB and NDB Cluster storage engines. The old default storage engine of MySQL, MyISAM, is not ACID compliant.<\/p>\n Since MySQL 5.7.8<\/a> and PostgreSQL 9.2.24<\/a>, MySQL and PostgreSQL support a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. In PostgreSQL 9.4.15, the JSONB data type was introduced as a binary version of JSON.<\/p>\n Because MySQL has always been focused primarily on speed, its compliance with the SQL standard lags significantly behind PostgreSQL. \u201cWe are not afraid to add extensions to SQL or support for non-SQL features if this greatly increases the usability of MySQL Server for a large segment of our user base,\u201d claim<\/a> MySQL developers.<\/p>\n According to the official documentation<\/a>, \u201cPostgreSQL supports most of the major features of SQL:2011. Out of 179 mandatory features required for full Core conformance, PostgreSQL conforms to at least 160. In addition, there is a long list of supported optional features.\u201d<\/p>\n For a detailed comparison of SQL compliance of MySQL, PostgreSQL, and other major database management systems, we recommend you visit this Wikipedia page<\/a>.<\/p>\n Because MySQL is a far more popular<\/a> database management system than PostgreSQL, it is typically easier to find help online. There are also more third-party tools and plug-ins available for MySQL than there are for PostgreSQL. That said, PostgreSQL also has a strong and very active community of users who are happy to help one another solve all sorts of PostgreSQL-related issues.<\/p>\n Commercial support for MySQL<\/a> is available 24\/7 to all commercial customers, who have the flexibility of choosing from three different editions\u2014MySQL Standard Edition, MySQL Enterprise Edition, and MySQL Cluster Carrier Grade Edition\u2014to meet specific business and technical requirements. Commercial support for PostgreSQL<\/a> users is available from many different companies in all regions of the world.<\/p>\nIntroduction<\/h1>\n
Features<\/h1>\n
Performance<\/h1>\n
ACID Compliance<\/h1>\n
JSON Support<\/h1>\n
SQL Compliance<\/h1>\n
Support<\/h1>\n
Licensing<\/h1>\n