Why we prefer postgresql over mysql in rails

In 2016, we see that PostgreSQL is the preferred database by 84% of the Ruby on Rails community.There is 12% increase over the 2014 results. MySQL is still preferred by 14%.

These are the features in postgresql which makes it most used database nowadays.

JSON support and NoSQL

Postgres allows anyone who wants to use NoSQL and store JSON (JavaScript Object Notation) files in the database and this is a recent addition to PostgreSQL. Whereas on the other hand, mysql supports neither of these.

More advanced datatypes

There are more advanced datatypes in postgres compared to mysql. Some of them are arrays, hstore documents, UUID primary keys, full-text search, and backing Rails Active Record models with database views.

money: 
currency amount
uuid:
universally unique identifier
xml:
XML data
box:
rectangular box on a plane
inet:
IPv4 or IPv6 host address
interval [fields] [(p)]:
time span

Postgres has better data integrity

We 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. PostgreSQL has been strict about ensuring data is valid before inserting or updating it with proper Full text search, and other extensions.

Postgres has strong community due to it’s Licensing

MySQL has General Public License (GPL), so we must pay a commercial fee to Oracle or supply the source code of your application, while , on the other hand, PostgreSQL has a MIT-style license that allows you to do anything, including commercial use in open or closed source.

Subqueries Can Be Problematic

MySQL doesn’t support Full Outer Joins ,while PostgreSQL is better for joins.

Reliability issues

When reliability and data integrity are an absolute necessity without excuses, PostgreSQL is the better choice.
References, transactions, auditing etc. gets handled with MySQL renders it a little-less reliable compared to some other RDBMS.

Stagnated development

Since Oracle’s acquisition of mysql, more advanced updates have been reduced drastically, where as postgresql development has been happening from time to time.

Popularity

Since Posgresql has been used largely from developers, the deployment servers and frameworks such as heroku has been supporting more of postgresql compared to mysql.

With adopting PostgreSQL in an upcoming release, I stand confident that it’ll continue to dominate the open source community.