MySQL

I wanted to title this article PostgreSQL sucks, but that wouldn't have been fair to PostgreSQL has it really doesn't suck, exactly. It is a very well featured database, and was the first open source database to have many advanced features. That said, MySQL from the start has been easier to administer, and generally easier to use as well.

Before I dive in further, I should warn that while I've used both MySQL and PostgreSQL for years and have extensive experience with both, I have dealt with MySQL considerably more. This is due mostly to the fact that most projects I work on tend to use MySQL as their database over PostgreSQL.

After years of using them both, I have had much fewer problems out of MySQL. Backups and restores, even the complex binary ones done with LVM have gone mostly without issues. I can't say the same with PostgreSQL. I've had MySQL problems for sure, but I seem to run into PostgreSQL problems with far more frequency over things that really shouldn't have been problems in the first place.

One could make the argument that since I spend more time with MySQL, I know it better and thus know how to avoid more problems than I do with PostgreSQL. I'll buy into that as well, at least in part. But I see far to many developers pulling their hair out on public forums and newsgroups over how to fix a problem in PostgreSQL that simply doesn't seem to be a problem on MySQL. That isn't to say that people don't have problems with MySQL as well, they do. But considering that more people use MySQL, you would expect more problems, but you don't really see that. In fact, it's the reverse.

Could a simpler path to administration and programming be why more people use MySQL? Probably so. Back when it lacked features that PostgreSQL had, it still was more popular. Its ease of use had to have been at least a significant factor in many decisions to use it.

Charles Jolley, CEO of Sproutit and I have been talking quite a bit about data storage problems, particularly in the ineffectiveness and the eventual scaling block you run into with most SQL-based solutions.

Facebook's success story with MySQL aside, most top sites either don't use SQL or use such simple queries in their data that the searches are simplistic, and the heavy lifting is done by careful indexing. Complex joins are almost unheard of in these situations, and actually having to perform one of these searches while the user is waiting can be a painful experience.

Ruby on Rails actually makes constructing these complex and slow joins painfully easy. You get instant results just by adding water. Perform complex joins, multiple levels of deep associations, and find_by_anything till you're blue in the face and you just won't notice it, until your application is an overnight success.

It's not Rails fault, or even SQL's fault. Having to give serious thought to how you're going to scale your data storage has always been a necessity for big applications, it's just something that you can defer now with modern frameworks. I suppose that's a good thing, as long as you can make the scaling happen on the fly.

We're actually working on some techniques to combat this problem, using things like Memcached, S3, and custom data formats. If you're interested, stay tuned. I'll be posting more on the subject soon.