Why Choose PostgreSQL over MySQL

Introduction

IT pros have been comparing PostgreSQL and MySQL for over a decade, and with good reason. While MySQL has been a force to be reckoned with for some time, PostgreSQL is seen as the better administrative tool for a number of reasons. While MySQL remains popular mainly due to it being included with all Linux web-hosting packages, PostgreSQL supporters say the database’s ACID compliance and support makes Postgres a better choice than MySQL. Even though MySQL is regarded as faster than Postgres, since Oracle bought Sun, who owned the copyright and trademark for MySQL, MySQL is no longer 100 percent open-source. Additionally, while PostgreSQL has gotten significantly faster, it also became one of the few relational databases to support NoSQL with the addition of JSON. The creator of MySQL, Monty Widenius create MariaDB by forking MySQL. MariaDB is not controlled by Oracle and is drop-in compatible with MySQL. PostgreSQL current version is 9.4.1. and the current version of MariaDB is 5.7.6. To help answer “Why choose PostgreSQL over MySQL?”, here is a head-to-head comparison:

ANSI Compatibility

MySQL has made progress with ANSI standards, but MySQL position is non-standard extensions will be supported if the customers find it useful. However, Postgre built the standards directly into the platform from the beginning, not adding them on as an afterthought.

ACID Compliance

PostgreSQL has just one storage engine with MySQL having nine. However, of those nine only MyIsam and InnoDB are of any use to most users. While MyIsam, the original storage engine, was designed for speed it fell short on transactions. On the other hand, InnoDB is the default storage engine and offers both speed and transactions. With both Postgre and MySQL being fully ACID compliant, there may not be much, if any, difference between the two platforms.

Table Changes

While MyIsam employs table-locking to increase speed, which works well provided sessions mainly involve reading, it runs into problems when writing to a table. Because writing sessions are given top priority, other sessions are put on hold until writing is completed. However, because both InnoDB and PostgreSQL employ row-level locking, it isn’t that much of a problem.

Handling Subqueries

Subqueries were a primary failing for MySQL, not being able to keep up with more than one level of sub-queries. While MySQL has made a lot of improvements with subqueries since version 5.6.5, MySQL still does not support full outer joins.

NoSQL and JSON

PostgreSQL offer much greater flexibility for storing data compared to other traditional relational databases. PostgreSQL recently added the ability to store JSON files and experiment with NoSQL, making it very attractive to anyone wanting an alternative to traditional relational databases.

Licensing Advantages

PostgreSQL provides a MIT-style license, a software license originally developed at the Massachusetts Institute of Technology, that allows for all types of open- and closed-source use, including commercial. However, MySQL uses a GPI client library that requires a commercial fee be paid to Oracle, or supply the application source code.

Data Integrity

Postgre has always taken great care in making sure that data is valid before adding it to their program. However, MySQL requires users set the server to a strict SQL mode, such as Strict_All or Trans_Tables modes, or adjusted values will be automatically added.

Server Extensions

Both MySQL and PostgresSQL allow users to extend the server. However, there are some important distinctions between the two. MySQL offers a plugin API for any C-compatible language and versions 5.7.3 and newer support full-text searches in InnoDB. However, PostgreSQL provides a similar system and has the benefits of supporting a wider variety of languages, such as C/C++, Java, Perl, Python, Ruby and ODBC, among others. Postgres even allows running user-supplied code in separate processes. Additionally, along with the usual information about databases, PostgreSQL supports information about access methods, columns, data types, functions and tables. While all relational databases hold similar information in their catalogs, PostgreSQL allows users to modify the catalog.

Conclusion

Those in the industry who have worked with both databases for the last decade say that MySQL has worked hard to improve its features to keep itself in the game. However, while MySQL will continue to have it fans, PostgreSQL appeals to a wider rage of users with better compatibly and compliance as well as custom data types, maintaining database integrity, table inheritance and no-hassle licensing. The verdict seems clear, for better database integrity, a wider range of uses and less-hassle licensing, Postgres seems the be the better choice when considering why choose PostgreSQL over MySQL.