Adaptive Database Optimizations?
I love Rails Migrations. Not only do they help making database development a part of an agile development process, they also make my life easier as a developer with shallow knowledge in the field of database programming. But, even with frameworks like these, I think we’re still dealing with databases on a very low level.
Conceptually, databases are very simple objects. You can store data and you can retrieve data. The complexity should go no further than to declare and organize the data into relational units. Making queries should be a simple matter of getting the right data.
Reality is different. We need to consider performance, create and drop indexes, write stored procedures, configure the database for optimal performance on our specific data and specific usage; We need to write queries that not only gets us the right data, but also gets us the right data efficiently.
To get the most out of our databases we therefore need deep knowledge of the database engine, and how it’s being used by our system. For the last part, all we can do is make a good guess based on tests and supervision. In all the projects I’ve been in so far, tuning databases has always been a hefty task, hard to get right.
If we allow ourselves to think outside the box, does the tuning task really have to be this difficult? We go through great effort to collect and predict usage data although there is an object that has access to the most accurate data at all times: The database engine should be able to figure out the best tuning actions, and do them at run-time.
Java’s Virtual Machine HotSpot is a great example of a self-tuning technique, called Adaptive optimization. HotSpot starts out as a simple interpreter, but performs dynamic recompilation on portions of the bytecode that is heavily used. An adaptive optimizer can theoretically perform better than a pre-compiled program since it can make optimizations based on actual usage and local conditions.
Now, wouldn’t it be possible to create a self-tuning database engine as well?
As I said, I’m not a database expert, and I appreciate the complexity involved, but on a conceptual level there are no real obstacles – that I can see. I see no reason why it couldn’t be done. Can you?