In this article, we compare one of the most popular database management systems (DBMS) in the world, MySQL, with DB-Engines’ DBMS of the year 2017, PostgreSQL, to see which can offer more to users in 2018.
Introduction
MySQL 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 to serve as a free and community-developed drop-in replacement.
The origin of PostgreSQL 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, aiming to make at least one minor release every quarter.
Features
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.
Since version 9.1, PostgreSQL supports triggers on views. “MySQL triggers activate only for changes made to tables by SQL statements. This includes changes to base tables that underlie updatable views,” as stated in the official documentation.
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, whose first public milestone was announced in September 2016. PostgreSQL supports CTE.
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, which perform some calculation on a set of rows similar to grouped aggregate functions.
Also worth mentioning is MySQL’s lack of support for Materialized Views, a feature readily supported by PostgreSQL. As described by Oracle, a Materialized View is “a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.”
Performance
As demonstrated 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.
However, it should be noted that MySQL natively does not support any kind of multi-threading. There are ways how to increase MySQL performance 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.
ACID Compliance
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.
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.
JSON Support
Since MySQL 5.7.8 and PostgreSQL 9.2.24, 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.
SQL Compliance
Because MySQL has always been focused primarily on speed, its compliance with the SQL standard lags significantly behind PostgreSQL. “We 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,” claim MySQL developers.
According to the official documentation, “PostgreSQL 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.”
For a detailed comparison of SQL compliance of MySQL, PostgreSQL, and other major database management systems, we recommend you visit this Wikipedia page.
Support
Because MySQL is a far more popular 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.
Commercial support for MySQL is available 24/7 to all commercial customers, who have the flexibility of choosing from three different editions—MySQL Standard Edition, MySQL Enterprise Edition, and MySQL Cluster Carrier Grade Edition—to meet specific business and technical requirements. Commercial support for PostgreSQL users is available from many different companies in all regions of the world.
Licensing
According to Oracle, MySQL database server and MySQL Client Libraries are provided under a dual license model designed to meet the development and distribution needs of both commercial distributors (such as OEMs, ISVs, and VARs) and open source projects.
“Oracle’s Free and Open Source Software (‘FOSS’) License Exception (formerly known as the FLOSS License Exception) allows developers of FOSS applications to include Oracle’s MySQL Client Libraries (also referred to as ‘MySQL Drivers’ or ‘MySQL Connectors’) with their FOSS applications.” In other words, MySQL can be used for free and for any purpose as long as it is not redistributed within a closed source product.
“OEMs (Original Equipment Manufacturers), ISVs (Independent Software Vendors), VARs (Value Added Resellers) and other distributors that combine and distribute commercially licensed software with MySQL software and do not wish to distribute the source code for the commercially licensed software under version 2 of the GNU General Public License (the ‘GPL’) must enter into a commercial license agreement with Oracle.”
PostgreSQL is open source and released under the terms of the PostgreSQL License, which is similar to the BSD and MIT licenses. It grants PostgreSQL users the permission to use, copy, modify, and distribute PostgreSQL and its documentation for any purpose, without fee, and without a written agreement.
Conclusion
Because the developers of both MySQL and PostgreSQL have such different priorities, each of the two database management systems has its own distinct strengths and weaknesses. Keep in mind that unless you’re working on a very atypical project, the differences in performance and features likely will not matter to you nearly as much as the differences in licensing and community support.
