Comparing Database Platforms

databasedatabase-design

My employer has a database committee and we've been discussing different platforms. Thus far we've had people present on SqlLite, MySQL, and PostreSql. We currently use the Microsoft stack so we're all quite familiar with Microsoft Sql Server.

As a part of this comparison I thought it would be interesting to create a small reference application for each database platform to explore the details involved in working with it.

First: does that idea make sense or is the comparison require going beyond the scope of a trivial sample application?

Second: I would imagine each reference application having a discrete but small set of requirements that fulfill many of the scenarios we run into on a regular basis. Here is what I have so far, what else can be added to the list but still keep the application small enough to be built in a very limited timespan?

  • Connectivity from the application layer

  • Tools for database administration

  • Process of creating a schema (small "s" schema, tables/views/functions other objects)

  • Simple CRUD (Create, Retrieve, Update, Delete)

  • Transaction support

Third: has anyone gone through this process, what are your findings?

Best Solution

Does that idea make sense or is the comparison require going beyond the scope of a trivial sample application?

I don't think it's a good idea. Most of the things that will really affect you are long term database management issues, and how the database management system you choose can handle those things.

You could be tempted in the short term with things like "I found out in 3 seconds how to do this with XYZ database management system". Now, I'm not saying support is not important; quite the contrary. But finding an answer in google in 3 seconds means that you got an answer to a simple question. How quickly, if ever, can you find an answer to a challenging problem?

A short list (not exhaustive) of important things to consider are:

  • backup and recovery -- at both logical level and physical level
  • good support for functions (or stored procedures), triggers, various SQL query constructs
  • APIs that allow real extensibility -- these things can get you out of tough situations and allow you to solve problems in creative ways. You'd be surprised what can be accomplished with user-defined types and functions. How do the user-defined types interact with the indexing system?
  • SQL standard support -- doesn't trump everything else, but if support is lacking in a few areas, really consider why it is lacking, what the workarounds are, and what are the costs of those workarounds.
  • A powerful executor that offers a range of fundamental algorithms (e.g. hash join, merge join, etc.) and indexing structures (btree, hash, maybe a full text option, etc.). If it's missing some algorithms or index structures, consider the types of questions that the database will be inefficient at answering. Note: I don't just mean "slow" here; the wrong algorithm can easily be worse by orders of magnitude.
  • Can the type system reasonably represent your business? If the set of types available is incredibly weak, you will have a mess. Representing everything as strings is kind of like assembly programming (untyped), and you will have a mess.

A trivial application won't show you any of those things. Simple things are simple to solve. If you have a "database committee" then your company cares about its data, and you should take the responsibility seriously. You need to make sure that you can develop applications on it easily with the results you and your developers expect; and when you run into problems you need to have access to a powerful system and quality support that can get you through it.