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.
