Introduction

In the past, choosing an RDBMS was largely decided by calling on several large vendors, promising millions of dollars, and waiting for one of them to take you to the best golf course available. Whichever vendor pampered you the most, you gave them too much money, and they delivered to you a largely useless piece of software that broke down often and required you to give them more money on a yearly basis.

If you don't subscribe to this philosophy of spending your company's money, here's a set of guidelines you can use to choose an RDBMS.

I have personally worked with Sybase, Oracle, DB2, MySQL, PostgreSQL, and MaxDB (aka many other names, click the link to find out what).

For the lazy: My overall impression at March 2004 is that MaxDB will offer the best cross-section of features, stability, support, and Freedom necessary to make a business happy.

ACID -- The Basic Features

ACID means Atomicity, Concurrency, Isolation, and Durability.

• Atomicity - A set of things that should be executed together will either all succeed or all fail.
• Concurrency - Multiple persons or processes can use the database at one time and will not (for the most part) interfere with each other.
• Isolation - Things that other processes are doing to the data will not (if I don't want them to) affect my view of the data.
• Durability - Database crashes or network failures will not cause data to disappear that are known to have been committed to the data set.

All RDBMSs worth mentioning have these features. I think it might be quicker to mention those that don't. These are probably things like Microsoft Access.

Advanced Features

Some of these features are becoming less and less advanced, but for now, I will call them advanced.

Replication

This is the ability for one instance of your database to remain synchronised to other instances of your database. This is useful for failover or for creating a "reporting" database.

MySQL: Yes
PostgreSQL: Yes
MaxDB: Yes
Commercial Offerings: Often for extra charge

Online Consistent Backups

This is important to get backups of your database without making it unavailable for use by the client computers, be they human or other applications.

MySQL: No (! use replication work-around)
PostgreSQL: Yes
MaxDB: Yes
Commercial Offerings: Yes

Point-in-time Recovery

If the database was known to be corrupted at 2pm, but your backup was at 9am, can you use logs to roll the database forward to 1:59pm?

MySQL: Yes
PostgreSQL: No
MaxDB: ?
Commercial Offerings: Some

Materialised Views

You create a view that computes various values from other tables. Most often used in OLAP situations.

MySQL: No
PostgreSQL: No
MaxDB: No
Commercial Offerings: Some (often for extra charge)

Partitioned Tables

If a table is too large to back up properly, or maintenance on it is a nightmare due to its unwieldy size, it is good to be able to break it into smaller chunks and allow queries to treat it as one large object, but administration to treat it as many smaller ones. This, again, is mainly useful in OLAP as OLTP databases should not be allowed to grow to this large of a size.

MySQL: No
PostgreSQL: No
MaxDB: No
Commercial Offerings: Some (often for extra charge)

Open Source vs. Proprietary

With the advent of open source software, and especially free software (GPL- or BSD-licensed software comes most readily to mind), more options were opened up. There are advantages and disadvantages to using Open Source RDBMSs:

Open Source Advantages

Usually the advantages to such an RDBMS equate to more Freedom in choice of vendor (or migrating from one vendor to another) and easier and better fixes to bugs.

Used by a wider audience (fewer bugs)

It is easy to underestimate how radically the usage by a wider audience of a piece of software contributes to its stability. In my 7 years of doing database administration, I have never used an RDBMS as stable and as easy to diagnose as MySQL in administration. (Note: I also have second-hand experience with a shop that uses PostgreSQL, at my recommendation, and understand it to be largely the same).

Here I list the RDBMSs used by widest to least wide audience (by my guess only, and only valid for the few months following March 2004).

MySQL: Widest.
MS-Access: This is Scary.
PostgreSQL: Surprisingly less-used than MySQL
Commercial Offerings: Oracle, Sybase, DB2, that sort of thing
MaxDB: Not too wide
The Rest: Things like Firewhatever, Informix.

Fix your own bugs if vendor is unresponsive

This is, again, an advantage of Free software that is often underestimated. There have been uncountable situations where I encountered a bug in an RDBMS that was clearly easy to fix, but the politics of working through customer support to engineering was so difficult that, had I access to the source, I feel I could have fixed the problem.

At the very least, other engineers in my organisation with more C (or whatever) programming skills could have diagnosed and fixed the problem.

Can you fix the bugs in YOUR RDBMS if you want? Can you hire someone to fix them if the vendor is unresponsive?

MySQL: Yes
PostgreSQL: Yes
MaxDB: Yes
Commercial Offerings: No

Choose another vendor to support your RDBMS without changing RDBMS

If you are unhappy with Sybase support, can you just call up Oracle and tell them you want to pay them $100,000 a year to support your Sybase installation? You sure can! Call them up and listen to the laughter.

You must invest millions of dollars transitioning away from an RDBMS if the company supporting it is lousy. (Not to pick on Sybase. I have direct experience with Sybase, Oracle, and IBM support for their RDBMSs and they're universally lousy).

If you're running PostgreSQL, however, and you're unhappy with your support vendor, you can call another vendor and ask them to support it. The price will be less than $100,000 per year, I guarantee you that.

At the very least, you can hire a very decent set of developers to do part-time work for you for $100,000 per year just fixing bugs in your RDBMS. Try doing that with your closed-source, expensive, proprietary RDBMS.

So, if you're unhappy with your current vendor's tech support, can you just go to another tech support vendor but keep the same RDBMS?

MySQL: Yes
PostgreSQL: Yes
MaxDB: Yes
Commercial Offerings: No

Open Source Disadvantages

The disadvantages of Open Source RDBMSs can be summed up by saying that usually these products are less mature than their commercial, proprietary cousins.

Usually less full-featured

For example, in Oracle and DB2 (at least) you have Materialised Views, which do not seem to be implemented in any of MySQL, PostgreSQL, or MaxDB (aka SAP/DB).

Is your RDBMS full-featured?

MySQL: No
PostgreSQL: No
MaxDB: No
Commercial Offerings: Yes (often not for the base price, though)

Perception of No-One to Sue

Can you sue your vendor if the RDBMS goes all dead on you during a critical moment, and your company looses billions and billions of dollars?

MySQL: No
PostgreSQL: No
MaxDB: No
Commercial Offerings: No

Look at the license agreement you signed with your commercial vendor if you doubt that last one.

Speed

Measuring the speed of an RDBMS is very, very difficult. For very simple queries on small datasets, most will find MySQL the most blazingly-fast RDBMS out there. And if you use MyISAM (thus losing ACID), you can make it even faster.

PostgreSQL is similarly quick, but places more emphasis on ACID compliance (you cannot actually choose an ACID-free mode like you can in MySQL which many argue is a Good Thing for PostgreSQL and lambast MySQL for even allowing such an option).

Many of the large RDBMSs are pitifully slow on small data sets but keep the same "slow" speed into insanely large data sets. MySQL at least is known to fall apart on even modestly large datasets and so can only be recommended in small installations.

Overall recommendations:

MySQL: Fast on small databases
PostgreSQL: Fast on small-to-large databases
MaxDB: Slow on small databases, proper on large ones
Commercial Offerings: Slow on small databases, proper on large ones

-- PhiloVivero - 18 Mar 2004