Those who support PostgreSQL argue that its standards support and ACID compliance outweighs MySQL’s speed. MySQL remains popular thanks to its inclusion in every Linux Web hosting package, meaning that many Web developers have used it; but ever since Oracle bought Sun, which owned the MySQL copyright and trademark, there have been widespread concerns that the platform isn’t quite as open-source as before. Meanwhile, PostgreSQL not only got faster, it also added JSON, making it one of the few relational databases to also support NoSQL.
(Just to go into a little more depth on the Oracle saga: Monty Widenius, the creator of MySQL, forked MySQL to create MariaDB, which is drop-in compatible with MySQL and not under Oracle’s control.)
The current version of MySQL/MariaDB is 5.7.6 (released to the community March 9, 2015), while PostgreSQL is 9.4.1. How do these latest versions match up? Let’s take a look.
ANSI Standard Compatible?
Compared to earlier versions, MySQL has made progress in the standards area; the philosophy behind MySQL is that they’ll support non-standard extensions if the customers like them. But with PostgreSQL, the standards were built into the platform from the start, whereas MySQL added them later.
Fully ACID Compliant?
PostgreSQL has one storage engine; MySQL has nine, but only two of those really matter to most users: MyIsam and InnoDB. MyIsam was the original engine, built for speed, but it lacked transactions; InnoDB has transactions and is speedier than MyIsam, which is why it’s the default storage engine. Both MySQL’s InnoDB and PostgreSQL are fully ACID compliant, so there’s really no difference between the platforms.
Table Changes Without Locking
MyIsam uses table-locking to gain speed. That’s fine if many sessions involve reading; but when writing to a table, the writing session gets exclusive access and other sessions must wait until its finished. But PostgreSQl and InnoDB both use row-level locking…so again, it’s much less of an issue.
Subqueries Can Be Problematic
Subqueries were one of MySQL’s major weaknesses for a long time; it was notorious for losing its way with two or more levels of sub-queries. Since 5.6.5, though, there have been major improvements—but PostgreSQL is still considered better for joins especially as MySQL doesn’t support Full Outer Joins.
JSON Support and NoSQL
So has MySQL caught up with PostgreSQL? In many ways, yes; but if you’re still looking for other reasons to jump ship to PostgreSQL, here are some more:
PostgreSQL has a MIT-style license that allows you to do anything, including commercial use in open or closed source. With MySQL, on the other hand, the client library is GPL, so you must pay a commercial fee to Oracle or supply the source code of your application. (That’s less of an issue when using MySQL in websites; MariaDB uses the GPL 2 license but also has a less restrictive LGPL license for MySQL Client libraries.)
Better Data Integrity
PostgreSQL has always been strict about ensuring data is valid before inserting or updating it. Whereas with MySQL, you need to set the server to a strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES), otherwise adjusted values will be inserted or updated.
Extending the Server
Both databases allow you to extend the server. MySQL has a plugin API for C/C++ or any C-compatible language. You can even do full text search in InnoDB from MySQL 5.7.3 onwards. PostgreSQL has a similar system but also supports a wide variety of languages including C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC and others; it’s even possible to run user-supplied code in separate processes, i.e. running as background workers.
PostgreSQL also supports information about data types, functions and access methods in the system catalogs along with the usual information about databases, tables, and columns that all relational databases hold. The user can modify these system-catalog tables to extend them.
I’ve worked with both databases for over ten years and think MySQL has done a great job of improving itself to keep relevant, but I have to confess to favoring PostgreSQL. There’s less hassle with licensing, custom data types, table inheritance, a rules systems, and database events.
- Interview Qs for MySQL DBAs and Developers
- MariaDB vs. MySQL: A Comparison
- Widenius on MySQL: Oracle’s Suffocating It