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. Verdict: Tie.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. Verdict: TieTable 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. Verdict: TieSubqueries 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. Verdict: PostgreSQLJSON Support and NoSQL
This is a recent addition to PostgreSQL, and it does make the platform more appealing to anyone who wants to try out NoSQL and store JSON (JavaScript Object Notation) files in the database. It gives greater flexibility on how data is stored compared to traditional relational databases; with PostgreSQL, you can have the best of all worlds. Verdict: PostgreSQL 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:Better Licensing
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.Conclusion
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.Related Articles
- Interview Qs for MySQL DBAs and Developers
- MariaDB vs. MySQL: A Comparison
- Widenius on MySQL: Oracle’s Suffocating It
Upload Your ResumeEmployers want candidates like you. Upload your resume. Show them you're awesome.
