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
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
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
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.