Choosing the right database: MySQL vs. Everything else

I've posted this before, but I have no reason to change this advice:

MySQL is easier to start using.

Nicer UI tools. Faster, if you don't use ACID. More tolerant of invalid data. Autoincrement columns are as easy as typing autoincrement. Permissions aren't as tied to the file systems and OS users. Setting a delimiter is easier than using PG's "dollar sign quoting" when writing a stored proc. In MySQL, you connect to all databases, not just one at a time.

Postgres (PG) is much more standards compliant, but it's uglier and more complicated, especially from a UI perspective. It used to require manual vacuuming, and actually enforces referential integrity (which is a great thing that can be a pain in the ass). Autoincrement is much more flexible, but requires sequences (which can me masked by using serial), and wait, what's an OID?

So if you don't really know or care much about databases, data validity, ACID compliance, etc, but you do care about ease and speed, you tend to go with MySQL.

Too many (not all, but many) "web programmers" know a lot about "web 2.0" or PHP or Java, but don't know much about database theory or practice ("an index? what's that?"). They tend to see a database as just a fancy hashtable or bag of data, and indeed one that's not anywhere as dynamically changeable or forgiving as a hashtable.

For these folks, MySQL -- because until 5.0 it wasn't really an RDBMS, and in many ways still is not -- is a godsend. It's "faster" than the competition, and doesn't "waste time" on "esoteric" database stuff a web programmer doesn't want, understand, or see the value of.

For somebody with a database background, on the other hand, MySQL is a minefield: stuff that should work (complicated views, group bys, order bys in group bys) may work or may if you're lucky crash the server, or if you're unlucky just give results with incorrect data.

I've spent days working around some of these things in admittedly complicated by not extraordinarily complex views and group bys.

And MySQL isn't really faster. If you're using InnoDb tables for ACID (or just because at more than 30 Million rows, MyISAM tables tend to get crappy), yes a straight one-table select is probably faster than in PG. But add in joins, and PG is suddenly significantly faster. (MySQL is especially bad at outer joins.)

In summary: if to you the database is a bag, if you never intend to do data mining or reporting, if you're mostly interested in serving up big hunks of text with few relations or updates -- that is, if you're using a database to power a blog, MySQL is a great choice.

But if you're actually managing data, if you understand that data lives longer and is more valuable to a business than front-end programs and middle-tier business rules , if you need the features of a real database, use PG.

A "web programmer" who has decided all his table structures can be auto-generated by Hibernate (or some other ORM) looks at that and says, "too complicated" and "I bet complicated means more cost and slower speed" and so he goes with MySQL.

As I said, PG is far superior, and I hate mucking with MySQL's bizarre bugs, and I think that overall PG performance is probably better than MySQL for any even slightly complicated query.

But MySQL makes things look (deceptively) simple, so you get a lot of people who don't really understand database design figuring that MySQL is a great choice.

Use PG. It's consistent, it's reliable, it's standards-compliant, it's faster on (even moderately) complicated queries, it doesn't completely throw off your schedule with weird bugs.

I think PostgreSQL is a very viable alternative to MySQL. It's much more Oracle-like.

Personally, I try to avoid MySQL whenever I can for the following reasons:

1. The default storage engine MyISAM lacks Foreign Key support. Innodb does, however it does not support Foreign Keys to MyISAM tables for obvious reasons.
2. If I attempt to insert invalid data, MySQL will happily change it for me.
   1. Illegal DateTime, Date, or Timestamp values are convert to "zero": http://dev.mysql.com/doc/refman/5.1/en/datetime.html
   2. Varchar and Char type columns: http://dev.mysql.com/doc/refman/5.1/en/char.html
   3. Numeric Datatypes depending on SQL Strict Mode: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

These things may not be of any importance to some people, but when it comes to quality of data, I would rather use something else.