There are many databases out there but most web applications developed using an open-source web framework use either PostgreSQL or MySQL. I've been asked by many clients over the years why they should use PostgreSQL over MySQL for their Ruby on Rails application.
Here at reinteractive we prefer PostgreSQL. While they both have merits, we use PostgreSQL for the following reasons:
PostgreSQL enforces data types. For example, if you make a string column in MySQL, and it has a limit of 200 characters and try and put 300 characters in it, PostgreSQL will raise an exception and your app will crash, MySQL will insert the first 200 and silently delete the last 100 characters.
Migrations - in PostgreSQL if there is a failure while modifying your database, the entire modification gets rolled back to where you started. In MySQL it just crashes at whatever point it got up to (say half way through updating your table) and you need to figure out how to fix it, while your production app is offline, and everyone is screaming (I've been there... never again!).
PostgreSQL has full text search. Straight out of the box you can do full text search really well. So you can load up PostgreSQL with a whole lot of, say, product descriptions and search across all those descriptions in an easy way without installing anything else.
Full support of UUIDs as primary keys in Rails. So instead of /product/1, /product/2 etc, you have /product/3c892309-ee0d-49c9-addb-9564c3c60eea. This is good because it is more difficult to hack something by trying to guess such an ID.
Extra data types, like JSON in the database. You can store a JSON object in PostgreSQL and then index on that JSON object making queries super fast and basically making PostgreSQL like a NoSQL database. It also supports BigInt, Decimal, and many other native PostgreSQL data types.
Great support in Rails.
Rails Hosting Survey: > 85% of rails respondents use PostgreSQL.
PostgreSQL is ACID compliant (Atomicity, Consistency, Isolation, Durability) all the time. This means, if you write to the database, it will either work, or it won't with an exception, no in between "Yes it worked, but it didn't write to disk". MySQL is compliant only under certain conditions and with certain table types.
PostgreSQL is owned by the PostgreSQL community. There is no corporate owner of the code. MySQL is owned by Oracle, who have OracleDB as their major money spinner, a competitor to MySQL. I can't see them developing it (a free product) to be better than OracleDB. On the other hand, PostgreSQL routinely competes with OracleDB installations through EnterpriseDB, a privately held company that sells corporate-level support.
PostgreSQL is more SQL standards compliant than MySQL - making it easier to debug and work on.
PostgreSQL supports materialised views, these are AWESOME! Say you have a products table, and you are always doing a query of all products joined to orders... a complex query that takes a few seconds each time to run. What you can do is make a view of this query and then "save it" into a materialised view, which acts as a live updating dynamic table which you can then query off are REALLY fast speeds, good for big databases.
You can actually program inside of the PostgreSQL database with Ruby and a dozen other languages, which you can't do with MySQL.
Proxying Outbound HTTP Requests Through Static IP Addresses on...
reinteractive is presenting at Dreamforce 2019!
How do you Successfully Manage a Software Development Project?
reinteractive is Australia’s largest dedicated Ruby on Rails development company. We don’t cut corners and we know what we are doing.
We are an organisation made up of amazing individuals and we take pride in our team. We are 100% remote work enabling us to choose the best talent no matter which part of the country they live in. reinteractive is dedicated to making it a great place for any developer to work.
Webinars are our online portal for tips, tricks and lessons learned in everything we do. Make the most of this free resource to help you become a better developer.
The Ruby on Rails Installfest includes a full setup of your development environment and step-by-step instructions on how to build your first app hosted on Heroku. Over 1,800 attendees to date and counting.
The Ruby on Rails Development Hub is a monthly event where you will get the chance to spend time with our team and others in the community to improve and hone your Ruby on Rails skills.