So after my last post dissing MySQL, I've had a few more issues with MySQL that's made me decide it's far more trouble than it's worth to keep it, so I'm ditching it for PostgreSQL.

The final straw was partially my own fault. I added a new table to the game's database, but forgot to add it to the Blitz database. So my blitz database started filling up with error messages (error messages get added to the database so I can analyze them later). It only took a couple of days for ibdata file to fill the entire disk. Now, you'd think this wasn't all that big of a deal, right? Just truncate the table, delete all the rows and it'll shrink back down again, right? Wrong, there is no way to shrink the the ibdata file! What you have to do, if you want to shrink your ibdata file, is export your data, blow the whole thing away, and re-import it again!

Worse, I can't just fix the table and leave the big file, because table definitions are stored in the same directory, and the disk was already full!

(There are other reasons for not using MySQL as well, but the general feeling of distrust towards Oracle also helps).

But in the end, since blowing the whole database away and starting again was my only option anyway, I decided that I'd finally migrate off MySQL.

Migrating the actual data

This was actually the simplest part, using pgloader, you just set up the mapping and it runs in no time. The biggest issue I faced was that the tool is written in lisp and the version of list that Debian runs is quite old, so I had to compile the latest version by hand. But with that out of the way, the migration is actually very quick (about 5 minutes for 4GB of data).

Migrating the code

When developing the server code, I was careful to limit the number of MySQL-isms, but it wasn't entirely possible to eliminate all of them. The first step in porting the code was to switch to PostgreSQL's JDBC driver, which is luckily not that difficult. It took me only a couple of hours one evening to port the rest of code away from MySQL-isms to PostgreSQL-isms, and another couple of evenings testing it all (this is where unit tests would have come in handy...)

Conclusion

I've only been running PostgreSQL for a few hours, but so far the performance has been pretty good. We'll see how it goes in the future.