Distigme

A little learning is a dangerous thing.


1 Comment

MySQL and PostgreSQL

I started a project not long ago using MySQL. It seems like the obvious choice for a free database, right? Then, still early in the project, I ran into one barrier after another, where MySQL just won’t do the right thing, because it sucks. It’s not like you absolutely can’t get something done, but rather you are continually forced into cumbersome and inefficient workarounds.

Some others have recommended PostgreSQL as an alternative. While I haven’t taken the plunge, it sure looks impressive, and I’m strongly considering it.

Here’s a quick sampling of the ways MySQL (using InnoDB) has already disappointed me:

  • Can’t rename a database.
  • Can’t alter a table in-place (it actually copies the entire table to replace it).
  • Table names are case-sensitive on Linux, contrary to the SQL standard, but not on Windows.
  • Transactions don’t support DDL. If a schema migration fails partway through, you’re screwed.
  • DDL is missing basic constructs like ADD COLUMN IF NOT EXISTS, requiring complex workarounds. The trouble is exacerbated by…
  • Direct queries are limited to a single statement. Even using the IF keyword requires wrapping everything in a stored procedure.
  • No support for arrays. For example, how do you pass a list of items to retrieve into a stored procedure? A comma-delimited string?
  • No reasonable support for time. What exists is stupid with handling time zones, and TIMESTAMP, which is only precise to the second, drags in its own baggage.
  • No reasonable way to deal with UUIDs. Would you rather use a cumbersome BINARY(16) or inefficient CHAR(36)? And what do you think happens with indexes and primary keys?
  • Inferior support for complex data structures (PostgreSQL, for example, can natively store and query JSON).
  • No support for CTEs, which are essential for recursion (e.g., querying for all the ancestors of a sub-organization).
  • In general, MySQL adherence to ANSI SQL standards is quite poor. Sometimes MySQL requires its own alternative syntax, and sometimes it doesn’t even offer an alternative.
  • The query optimizer tends to be quite poor. The longstanding cause of this is MySQL’s assumption that each table can have a different storage engine (even though everyone just uses InnoDB or some variant nowadays). Said to be the big improvement targeted for 5.6, but I don’t have high expectations.
  • Simple COUNT(*) queries require an index scan, which is so bad that common practice is to maintain denormalized counts updated by a trigger.
  • Tools (i.e., MySQL Workbench) are notoriously buggy. I fully expect to crash and lose data on a daily basis.
  • Materialized views are simply not supported (same for PostgreSQL, but at least it’s under active development).

And I haven’t even started doing anything really complicated yet. Pretty disappointing for 2013. Yes, the fail is strong with this one.

These were pretty much all non-issues when I used SQL Server ten years ago, and reportedly are well-supported in PostgreSQL (except materialized views, as noted).

Really the main thing MySQL has going for it is that it’s really really popular. Big sites like Facebook use it (though regretfully). But it seems that the tide is turning, and mass abandonment is now underway.

A lot of the movement away from MySQL has been into NoSQL engines like MongoDB. At first I had a hard time understanding the whole impetus behind the NoSQL movement. But if your view of the world of relational databases is through the lens of MySQL, it’s easy to see why you would demand something radically different.

So, I look at PostgreSQL, and it seems to do just about everything I could want, but two things hold me back. One is the worry that “the grass is always greener” and I end up trading known problems for new unknown problems. The other, more practically, is that Amazon RDS, which we plan to use, supports MySQL but not PostgreSQL. We’d probably have to go to Heroku (which, interestingly, is actually built on top of Amazon’s cloud).

Any advice from those with more experience?