April 1, 2006

PostgreSQL | Goodbye Elephant, Hello Dolphin

You may have noticed that this site hasn't been updated very much in recent months. That's because I've been busy reengineering the site to make it fully Web 2.0-compliant. Unfortunately my graphic designer is off sick, so the curvy borders weren't ready in time for the relaunch, but rest assured they will make their appearance once the AJAX interface is complete.

One of the most important changes has been to the backend database. While PostgreSQL has served me well over the past few years, I feel the time has come to give MySQL a try, because it seems everyone is using it for their websites these days. Indeed, it is causing such a stir that even renowned companies such as Oracle have acquired a stake in strategic MySQL technologies such as InnoDB. I've listed some of the reasons for my move below.

Obviously data integrity is vital to the reliable operation of any database, however small. This may sound rather paradoxical, but this is my reason for not using the InnoDB table type: I am concerned that in the future Oracle will make it more compatible with their own database product, meaning I will have to change all the VARCHAR columns to VARCHAR2 definitions, and more seriously they might introduce their "zero-length string is the same as NULL"-feature, which would break some of my code. Instead I've opted to use the native MyISAM data engine, in the assumption that it is more closely bound to the MySQL core philosophies.

I was particularly impressed to see that MyISAM accepted all my foreign key definitions without any errors, and transactions also run very smoothly; in fact rolling back a transaction on a large MyISAM table is several orders of magnitude faster than the same operation on an InnoDB table.

I also believe MySQL provided a high degree of "future proofing". For example, its date type is ready to accept up to 31 days in any given month, which is a major advantage should the International Astronomical Union succeed in its efforts reform the current, rather confusing system. (Note that more recent MySQL versions will automatically convert dates which are invalid under the current system into the more useful 0000-00-00 to save any embarrasment).

Another MySQL advantage is that it can properly tell the difference between upper and lowercase tablenames. This is particularly useful when you realise you already have a table name like data, just call the new table DATA.

Finally, no other database offers a blackhole storage engine. I haven't tested yet it on the production site but will keep you updated as things progress.

Posted at 1:05 AM