MySQL VS PostgreSQL : Which one is better?
MySQL and PostgreSQL are the two most widely used RDMS system. As both of them come with a lot of features and helps its users to customize and extend it to manage a large number of data, they have become popular all over. However, this two RDBMS have some advantages and disadvantages as well.
Bute before going into their comparison let us see what these two RDBMS are.
MySQL is a relatively young RDBMS. It first appeared in 1994. Since then, it has become one of the most popular RDBMS. There is a software bundle named LAMP where L stands for Linux, A stands for Apache, M for MySQL and P for Oython/PHP/Perl. Even though t his RDBMS was initially designed as a fast back end web server, it has now become a support for various storage engines.
It has been deemed as world’s most advance open source RDBMS. As it does come with high installation cost or maintenance cost, this RDBMS clearly became a favorite in just some time.
PostgreSQL (pronounced Post-gress-cue-ell) bills itself as the world’s most advanced open source database. As Ingres Database’s descendent, it was born at the University of California in Berkeley in 1985.
Picture 1 : Database Poll made by css.fnal.gov
Here, let us compare MySQL and PostgreSQl in terms of usability and security to see which one of these two is the better one.
Pros of MySQL
MySQL is lightweight than the other RDBMS.
The users can enjoy replication to some degree and this feature sets it different from other RDBMS.
It offers enterprise grade clustering feature.
It works faster than others when you are selecting queries.
As this is one of the most popular and wide spread RDBMS, you will find lots of apps targeted towards it. Most of the hosting supports this RDBMS as well.
Also comes with commercial support as well.
Picture 2 : An Overview of MySQL Replication (Sources : askdbexperts.com)
Cons of MySQL
This RDBMS does not support transactions.
There is no group commit, no constraint and no referential integrity.
The users cannot specify TEMP table more than once in a query that sometimes hinders the work.
Not good for analytical workloads.
Subquery function in MySQL is not up to the mark.
Pros of PostgreSQL
This RDBMS comes with more ALTER TABLE functions
Offers flexible text search for users and comes with GIS add on functionality.
Easier for users to come up with their own extensions.
Multiple replications can be done in accordance with requirement and need.
Completely compliant with ACID
Access control framework is stronger
Picture 3 : An Overview of PostgreSQL Replication (Sources : askdbexperts.com)
Cons of PostgreSQL
Lack of replication feature. However, the recent versions come with synchronous and asynchronous replication feature. In fact the replication is immature.
Community and support is smaller compared to MySQL.
From the aforementioned features of MySQL and postgreSQL, we can come to the conclusion about when to use which RDBMS.
When to use MySQL over PostgreSQL
When there is no need for the user for clean up
When you need replication as MySQL comes with thoroughly tested replication
When you need faster speed as MySQL is faster than postgreSQL
When you need simpler database
When to use PostgreSQL over MySQL
When you need complex rule set or complex database design
When you need to use geographical data or stored procedure
When you need to use procedural languages
When you need to use R-trees on indexes
Compare Hosting with both MySQL and PostgreSQL Database Features