What & How to Choose: PostgreSQL vs MySQL

This post will be a quick guide for the TL;DR minded individuals looking to make an informed decision in the least possible amount of time.

To get started, let me say that no, one is not better than the other. Postgres is good at some things and MySQL at others.

The good news is that the strong-points for each are actually polar opposite enough to make it easier to pick the right one for a variety of uses as long as you know what your app needs.

So, without further ado, let's take a look at our first comparison...

Data Types

MySQL (21) PostgreSQL (38)
TINYINT-
SMALLINTsmallint
MEDIUMINT-
INT or INTEGERinteger
BIGINTbigint
FLOATreal
DOUBLE, DOUBLE PRECISION, REALdouble precision
DECIMAL, NUMERIC-
DATEdate
DATETIME-
TIMESTAMP-
TIME-
YEAR-
CHARcharacter [(n)]
VARCHARcharacter varying [(n)]
TINYBLOB, TINYTEXT-
BLOB, TEXTtext
MEDIUMBLOB, MEDIUMTEXT-
LONGBLOB, LONGTEXT-
ENUM-
SET-
-bigserial
-bit [(n)]
-bit varying [(n)]
-boolean
-box
-bytea
-cidr
-circle
-inet
-interval [fields] [(p)]
-line
-lseg
-macaddr
-money
-numeric [(p, s)]
-path
-point
-polygon
-serial
-time [(p)] [without time zone]
-time [(p)] with time zone
-time [(p)] with time zone
-timestamp [(p)] [without time zone]
-timestamp [(p)] with time zone
-tsquery
-tsvector
-txid_snapshot
-uuid
-xml

As you can see in the data types section Postgres takes the medal for having more and better specialized types.

If the decision came down to just data types I'd generally go with Postgres since it includes boolean and uuid which I find to be extremely useful.

Speed

MySQL

If what you need is speed, period, then if you strike the right balance between scale, as in number of read/write replicants per user cluster, and combine it with a reliable memory cache such as Redis, then MySQL is your best bet.

Building a real-time app? Messaging apps, chats, social networks, live stock graphing, etc. Just MySQL the shit out of it. There's a reason facebook has stuck with MySQL for this long. It's just FAST, specially if you use an optimized flavour such as MariaDB.

PostgreSQL

If what you need is consistent speed, specially under high traffic situations, Postgres is your champ.

I recently underwent a trial by fire with PostgreSQL on Heroku. I was in charge of development for the crowdfunding site Aligned. One day the owner decides to do massive marketing and the site went from having sporadic traffic to having 80 new unique concurrent visitors per second. The site went down for 2 minutes, but all I had to do was add more CPU workers to handle the influx of new visitors, meanwhile Postgres didn't even flinch at having to serve data to 5x more the amount of workers.

In that case all users experienced exactly the same latency, which is what Postgres is optimized for. No matter if 1 active connection or 100, all of them will have the same latency.

Availability and Cost

Want cheap hosting from both big corporate server farms or your local Joe with a server rack down the block? Go with MySQL.

Postgres is just not there yet. Hosting and management is pricier than MySQL, and limited to few choices. Well, at least for now. The tendency is slowly shifting thanks to big Postgres players such as Heroku, but it's still not as cost-effective as MySQL.

Ease of Setup

Hands-down, MySQL is easier to get up and running both as stand-alone as well as in clusters. And it's not just because it has been around longer and thus better documented. It simply has better support for a wider variety of operating systems and setups.

PostgreSQL simply lacks in the field of user experience for setting up, as well as needing way more finagling to get it to run properly on even some mainstream platforms.

Ability to Handle Complex Tasks

Postgres blows it out of the park on this category. Not just because of the extra data types, but the stored procedure syntax allows for triggers to be able to perform repetitive tasks as good, if not better, than if coded using the best of back-end programming languages.

I once built a coupon application out of mainly Postgres DB triggers. The back-end merely would relay data to Postgres and the data would be automagically turned into usage analytics, reports, and responses.

MySQL is just clunky in this sense. Not unusable, but definitely expect to be needing a really solid API to handle complex data transformations.

Ease of Maintenance

If used correctly and if picked for having the right attributes for your app I would almost say both PostgreSQL and MySQL are tied in how easy they are to maintain.

Almost...

Unless you take into consideration the following point...

Reliability

MySQL has a higher risk of having data corrupted and/or lost. This can easily be mitigated by keeping at least one replicant and/or keeping regular backups at hand.

This might not be as bad as it sounds, we're talking about a few rows a year, not whole databases or tables at any given time. But it's still an annoyance you won't run into with Postgres which does a virtually flawless job of keeping data integrity.

Recap

  • Data types: Postgres has more and better.
  • Speed: MySQL is faster, Postgres is more consistent.
  • Availability and Cost: MySQL is cheaper and widely available.
  • Ease of Setup: MySQL is easier to setup and better supported.
  • Ability to Handle Complex Tasks: You could practically write a whole web app out of Postgres.
  • Ease of Maintenance: Almost tied unless you take into account...
  • Reliability: PostgreSQL is virtually flawless at keeping data integrity. MySQL might make you lose a row here or there once or twice a year unless you prepare for it.
Show Comments