MySQL vs PostgreSQL

Relational databases became popular thanks to management systems that implement the relational model, which has been in use for a long time and also proven to be a great way to work with data.

Developers are comparing MySQL and PostgreSQL for the past ten years. 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, 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 added JSON, that making it one of the few relational databases to also support NoSQL.

Basic introduction of MySQL and PostgreSQL

MySQL is a relational database management system currently developed by Oracle with open-source code. This code is available for free under the GNU General Public License, and commercial versions of MySQL are also available under various proprietary agreements.

Whereas, PostgreSQL is an object-RDBMS, which developed by the PostgreSQL Global Development Group. It also has an open source, and released under the permissive PostgresSQL License.
Let’s take a look at the differences between MySQL and PostgreSQL:

1. Fully ACID Compliant - PostgreSQL has one storage engine, where 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.

2. ANSI Standard Compatible - Compared to earlier versions, MySQL has made progress in the standards area. MySQL 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.

3. Better Data Integrity - PostgreSQL has always been strict about ensuring data is valid before inserting or updating it. In case of MySQL, you need to set the server to a strict SQL mode, otherwise adjusted values will be inserted or updated.

4. Problematic Subqueries - Subqueries were one of MySQL’s major weaknesses, it was notorious for losing its way with two or more levels of sub-queries. But PostgreSQL is still considered better for joins especially as MySQL doesn’t support Full Outer Joins.

5. Table Changes Without Locking - MyIsam uses table-locking to gain speed. Which is good, 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 which create much less problem.

6. JSON Support - Recent PostgreSQL added JSON support, that make the platform more appealing to anyone who wants to try out NoSQL and store JSON files in the database. It gives greater flexibility on how data is stored compared to traditional relational databases.

7. Server support - 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. 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.

8. Better Licensing - PostgreSQL has a MIT-style license that allows you to do anything, including commercial use in open or closed source. On the other handWith MySQL client library is GPL, so you must pay a commercial fee to Oracle or supply the source code of your application.

Both databases are doing great job in it's field, but PostgreSQL is much better than MySQL. If you not yet try them, use them and let us know your exprience in comment below.