Distigme

A little learning is a dangerous thing.

MySQL and PostgreSQL

1 Comment

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?

Advertisements

One thought on “MySQL and PostgreSQL

  1. I hope my English is not too bad.
    I’m a DBA. I work everyday with SQL Server from 2000 to 2008r2, enterprise edition and Oracle 11.2g enterprise. Where I can choose I use Postgresql.
    I abandoned mysql years ago for a lot of reasons.
    – If you don’t set it to strict mode you are going to have bad times.
    – Postgres is robust: same server, both pg and mysql and powerdown: pg start with no problem, mysql lose data easly.
    – data types. You know, you mentioned them. Here Postgresql is simply superior to everyone. I started 2 years ago with Oracle: I was so diappointed…

    Some random thoughts:
    I used Postgresql with geographic data. It’s magic. You know for example openstreetmap? They also use it.
    Custom datatypes, and programmer are happy. Expecially when they read json.
    I really don’t like nosql world. But when I’m forced, I still use postgresql, with hstore. Fast, acid (mongo for example is not), I can also have hbrid rows, with some field that I can use in standard way. They are also improveing this in the future Postgresql 9.4
    For Amazon, something is moveing: http://aws.amazon.com/rds/postgresql/
    I love foreign-data wrapper, I can do things that programmer in php, c, ecc.. will require a lot of time, and foreign-data wrapper works better.

    Regards,

    Lucazeo.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s