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:

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 InnoDB and PostgreSQL are fully ACID compliant, so there’s really no difference between the platforms.
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.
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.
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.
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 it's finished. But PostgreSQL and InnoDB both use row-level locking which creates much less problem.
JSON Support - Recent PostgreSQL added JSON support, that makes 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.
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 a 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.
Better Licensing - PostgreSQL has an MIT-style license that allows you to do anything, including commercial use in open or closed source. On the other hand, with MySQL client library is GPL, so you must pay a commercial fee to Oracle or supply the source code of your application.
Databases deployment - PostgreSQL was written in C. It has support for the following programming languages: C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, Erlang. Typical installations are on various Linux servers, cloud-based or on-premise. Leading cloud vendors have PostgreSQL support on their platforms. There is also a REST API for any Postgres database. And MySQL was written in C and C++. It has support for the following programming languages: C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, Erlang. Again, cloud-based implementation and platform support are available. Customers can choose to use the Oracle Corporation or the open-source build.
Replication/clustering - PostgreSQL has synchronous replication (called 2-safe replication), that utilizes two database instances running simultaneously where your master database is synchronized with a slave database. On the other hand, MySQL replication is one-way asynchronous replication where one server acts as a master and others as slaves. You can replicate all databases, selected databases or even selected tables within a database.
Popularity - Companies that use PostgreSQL database are: Apple, BioPharm, Etsy, IMDB, Macworld, Debian, Fujitsu, Red Hat, Sun Microsystem, Cisco, Skype, etc. On the other hand, MySQL is used by GitHub, US Navy, NASA, Tesla, Netflix, WeChat, Facebook, Zendesk, Twitter, Zappos, YouTube, Spotify, etc.
Built-in support - PostgreSQL includes built-in support for regular B-tree and hash indexes. Indexes in PostgreSQL also support the features as Expression indexes that can be created with an index of the result of an expression or function, instead of simply the value of a column, and Partial indexes that index only a part of a table. Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULL TEXT) are stored in B-trees. Exceptions include the indexes on spatial data types that use R-trees. MySQL also supports hash indexes and the InnoDB engine uses inverted lists for FULLTEXT indexes.

Both databases are doing a great job in its field, but PostgreSQL is much better than MySQL. It depends upon what is the requirement of the programmer to design the web application or website. If you not yet try them, use them and let us know your experience in a comment below.

#rdms Stories - 1 stories

MySQL vs PostgreSQL