Wandering Thoughts archives

Databases are APIs

I recently realized something: databases are APIs.

Just like library functions and classes with defined methods, the columns and tables and inter-table relationships in your databases are effectively a collective API to the database. (As distinct from the APIs used to talk to the database program, ranging through ODBC to SQL.)

If you have what Martin Fowler calls an application database, used only inside one application, you have an internal API, just like a module's interface or a class's methods. But if you have an integration database, a database that integrates data across multiple applications, then your database is a public API.

I find this a useful insight because it gives me a different and much more accessible perspective on a number of database design issues. For example, no wonder DBAs twitch when people propose changing the schema in a public database; you're proposing a change to a public API, with all that that implies.

One of the problems of databases as APIs is that the API is amazingly low level and direct; it's the rough equivalent of directly reading and writing member variables in objects. This creates serious limitations in supporting backwards compatibility if we evolve the database 'API' by changing tables, columns, and interrelationships (as shown in yesterday's example of a problem in an evolving database).

(This makes the DBA love of stored procedures, the 'level 2' approach from my 'Three levels of database usage' entry, make much more sense. Backwards compatibility is much easier with them.)

The other problem for refactoring databases, even application databases, is that they are not just an API. Databases are also the stored data, so you can't just evolve the API itself; you need to also evolve the stored data so that it means the same thing as it used to.

One way to do this is to provide a tool that exports the data with its meanings exposed, and another tool that loads meaning-exposed data into the database. (This is one of the cases where I think XML makes a lot of sense; it's relatively easy to express meaning explicitly, and there are well developed XML generators and parsers. This also casts an interesting light on the desire of people for 'XML databases'.)

The Myth of Support (Part 3)

The big myth of support is that you can get it. Usually you can't.

Oh, there's no shortage of vendors who will take your money in order to tell you how to fix known problems. And why not? It's pretty easy money. (The only tricky bit is figuring out which known problem you're having.)

But this is the easy bit, so easy that we should call it by a different name like 'troubleshooting'. Real support is (also) handling the new problems, the bugs, and the undocumented limitations that you find in your important systems, all of which you want fixed.

Sometimes you can have the vendor find solutions for new problems, if they exist. Not infrequently vendor support contracts don't even cover this; to identify solutions to new problems, they want to charge you consulting fees or the like. (And why not? All of a sudden it may cost them serious money to figure out the answers, instead of being able to read them to you out of a knowledge base.)

Very few vendors are willing to actually fix bugs and undocumented limitations for you. Since this is the one thing you can't do yourself, what you most need vendor support for is the thing they are least likely to provide.

(There's also SupportMythPartI and SupportMythPartII.)

Really understanding availability numbers

People like to talk about reliability and availability, and throw around terms like '5-nines availability' and so on. You may have heard this from server salescritters (alongside their attempts to sell you redundant power supplies).

At the same time, what the terms really imply is not intuitive and is often surprising, especially at the high end.

The following chart comes from my co-worker John Calvin:

Things to note:

• a typical system takes longer than 30 seconds to boot.
• a five minute distributed denial of service attack can happen relatively routinely. (It certainly does to us.)
• the August 14th 2003 blackout in eastern US and Canada lasted over half a day for most people.
• very few service contracts will get you useful service in under two hours.
• just swapping power supplies, hard drives, or CPU fans can easily take more than ten minutes.

• there are many possible 4-Nines and 3-Nines availabilities, based on how much downtime you can accept in a single incident. 3-Nines where you can only be down for 10 minutes in any single incident is very different from 3-Nines where you can be down for a couple of hours twice a year.

Even with a good service contract, a single commodity server is exposed to multi-hour outages and is so at best 3-Nines available. In fact, anything that can ever require service is at most 3-Nines available.

(In practice you are cruising on the edge of even 3-Nines; you are betting on only a few service calls a year.)

Troubleshooting versus support

When we talk about 'support', especially things like 'vendor support', I think that there are actually two general things that are getting lumped under the same label: issues that you could have dealt with on your own if you knew enough, and things that are broken.

Although I don't have really good names for them, let's call the first sort of issue 'troubleshooting' and the second sort 'support'.

Unless the vendor dropped the ball on their documentation (which is certainly known to happen), calling the vendor up for troubleshooting is your fault, because you've failed to read or to understand (or you have decided that calling the vendor is faster than reading). In a sense you are wasting the vendor's time and asking them to do your work for you.

Support issues, genuinely broken things, are the vendor's fault, not yours. No amount of understanding the documentation or reading the FAQ can help (except perhaps to say 'this is a known bug'). Fixing the problem is not your work, it's the vendor's.

The difference matters in part because reasonable pricing is different between troubleshooting and support. It's hard to object to being charged each time you want a vendor to hold your hand, or to a vendor with flat rate troubleshooting saying 'you're asking for too much hand holding'. But being charged for each broken thing you discover, or hearing 'this is costing us too much to fix, go away' is infuriating for reasons I covered in more depth in SupportMythPartII.

Working in a heavily technical environment as I do, I don't care very much about a vendor's ability to do troubleshooting; we'll handle that ourselves. I do care quite a bit about the vendor being sane about support.

Three levels of database usage

One of the perennial programmer debates can be caricatured as a three way fight between adherents of MySQL, PostgreSQL, and Oracle (or the big iron database of your choice). As an example, you can see a recent round starting itself up here and here (both arguing what could be called the MySQL side).

Some time back it struck me that one way to think about how programs relate to SQL databases is that they can be doing it at three different conceptual levels. (And now that I have a blog, I can throw the thought out to the world.)

At level 0, programs use a SQL database as a simple data store. They could have used even simpler database packages, like Berkeley DB or gdbm, but using a SQL database means they don't have to write a lot of boring low-level code for marshalling stuff in and out of the database and scanning tables and so on. The programs mostly want this data storage and retrieval to go real fast, and maybe for multiple instances writing to the same database to not corrupt it.

At level 1, programs use the SQL database to store high-level data structures. Because SQL is pretty simple, these data structures need to be represented using multiple relational objects (multiple rows and multiple tables). Naturally, programs want the database to be able to guarantee that only 'whole' high-level objects are stored in it, because this vastly simplifies their lives. This means that the database needs at least things like foreign key constraints and row constraints.

At level 2, people use the database to hold abstract data types, which the programs interact with by using defined operations implemented in the database. They don't want programs using this ADT store to care about the underlying representation of the abstract data objects; that's for the DBAs to worry about. Thus they want powerful stored procedures, because those are the interfaces to the ADTs.

Regardless of the level of database interaction that your program uses, everyone is actually doing the same high-level thing in their programs. People using databases as data stores still have high-level data structures; they just prefer to store and manipulate them in their high-level code, instead of pushing this down into the database.

I don't think that there's a clear best conceptual level right now and I'm not sure there ever will be. (We can hope that database performance and features converge, so that everyone is fast as MySQL and as powerful as PostgreSQL or Oracle.)

There are various reasons for choosing different levels, some of them highly pragmatic. When I wrote my only SQL-using application (a student account management system), I used a 'level 1' approach because of laziness and paranoia. Laziness because I could easily write a few lines of SQL to do quite complex operations that would otherwise have taken me quite a bit of application level code. Paranoia because using SQL constraints in addition to program level checks meant that I had an extra line of defense against bad data (or programming mistakes) doing horrible things to student accounts.

Thinking about redundant power supplies

The next time a salesman tells you that a server needs redundant power supplies for reliability, ask him if the server also has redundant CPU fans.

By now, most everyone understands that extended warranties on consumer electronics are just overpriced insurance (hence why the salescritter tries to get you to buy them, as they're great profit for the store). Redundant power supplies are getting to be a standard option in server configurations, and while they're not necessarily pointless I have to feel that a fair number of people are probably being sold ones they don't really need.

Buying a spare power supply is often a sensible thing. With redundant power supply designs, you are paying extra to have the spare plugged in and ready to go, instead of having to take some downtime to put it in. How much reliability do you actually gain, and how much is it worth?

Power supplies are not the only thing that can fail in servers; there's a lot of other moving parts, some of them under high stresses. Thus, having a redundant power supply may not actually be lowering the overall chance of system failure. (Or at least not lowering it by enough to change any important decimal points. The whole high availability field is an expensive minefield.)

Power supplies can fail in a number of ways; simply ceasing to supply power is the most benign one. With more explosive failures, there can be damage or problems outside the power supply itself, and your redundant power supply is unlikely to help. (It may even do harm, by keeping the machine running when an abrupt shutdown would better contain the damage.)

If total downtime is an issue (not just unplanned downtime), you need the power supplies to be hotswappable. Otherwise you'll have to take the machine down at some point to replace the failed power supply; the only question is when.

None of this is commodity PC hardware, which means that you're paying more in general. This is true even if you don't buy the redundant power supply; you're paying a markup on the base system for the mere possibility.

(So yes, this can mean that once you have to buy the base hardware for some other reason, you might as well get the redundant power supply; the extra cost may be entirely rolled into the base system price and the additional power supply itself not marked up.)