Too clever
Tuesday, July 8th, 2008Another in my series of “lessons I’ve learnt as I progress in my journey from rank amateur to serious developer”.
Some time ago I posted a series of articles discussing the use of “advanced” database techniques with Rails, mostly utilising Postgres. See here:
Switching to PostgreSQL
Strict databases are great for discipline
hacking native UUID support into Schema:Dump
UUIDs in Rails redux
Anyone visiting those old articles these days will notice a new update: don’t do this. Why, you might ask? Since I thought it was such a good idea at the time.
Turns out that being “clever” with your DB setup is almost certainly more trouble than it’s worth.
Sure, using PGSQL schemas to share tables between your applications seems like a nice, elegant solution compared to throwing everything in one DB and using table namespacing to distinsguish between them. Sure, using the schemas saves you from having to define table names in every model where you access a “foreign” table. Unfortunately, it’s a giant pain in the ass to maintain and constricts your use of migrations.
Sure, hacking Rails to read and write PGSQL’s native UUID format is elegant and looks kewl in your migrations, etc. However, maintaining it is a giant pain in the ass and kills cross-DB compatibility.
These are examples of me being “too clever”. Not trying to brag or anything here, I’m using the term in a self-deprecating manner. What happened was that I, a relatively inexperienced developer, thought I’d found some “cool” solution to a problem I was having - or imagined I was having. I then “solved” the problem in a manner which was worse than anything the “problem” itself presented.
DO NOT use postgresql schemas unless you have a damn good reason for it. “Making your tables look nice” is not a good enough reason.
DO NOT use database-specific data types unless you have a damn good reason for it. Again, “looking nice” is not nearly enough.
UUIDs are strings, pure and simple. Using a UUID data type in PostgreSQL gains you one thing - checking that anything you try and save in there looks like a UUID. That should be in your own tests. What you lose is cross-platform compatibility, ability to use standard Rails, reliance on hacks to schema dumper, and more. And they’re strings, right? So store them in a fricking String field. Anything else is just not worth the hassle.
Schemas seem cool and elegant, and they are. Unfortunately, what you want isn’t “cool and elegant”, it’s “easy to work with and convenient to maintain and update and migrate and change around when you need to”.
It took me hours, days, to implement the changes to use the above features of PGSQL. It took a couple of hours to undo all of that and go back to standard data types, standard layouts, minimum complexity. Wasted work and time? Sure, but I learnt the lesson I’m trying to convey here - keep it simple, don’t be clever, maintain cross-compatibility and stick to the lowest common denominator unless you have a really, really good reason not to - and mine didn’t count in the end.
I’m still using PGSQL, of course - I like it, and even MySQL 6 inexplicably still doesn’t offer sub-second precision in DATETIME or TIMESTAMP, which I want. And I’m certainly not moving away from the UUID approach, which I firmly believe is best - all of that still stands. But I’ve gotten rid of all the special data structures and non-standard migrations and database-specific data types, because the miniscule aesthetic benefit just was not worth the loss of freedom and convenience everywhere else.
Live and learn, eh.