I’ve been working with databases for many years. I started out with Oracle and Informix then used a little Sybase. Then I discovered open source and used mSQL, MySQL, and PostgreSQL pretty extensively. Most recently I’ve been working with Oracle again, and just started playing with MSSQL. Why is any of this important? It’s important because I’ve seen the good and the bad of all these systems. And perhaps even more important are my conclusions after 10 years of working with RDBMS’.
When I first started using Mini SQL (mSQL) I didn’t think much of it. It was lacking all but the most basic of features, and got slow with anything more than a few thousand records in it. Then I ran into MySQL and eventually PostgreSQL.
I found the power, speed, and flexibility of both databases to be tremendous. MySQL tested faster than PostgreSQL, but only by a little. And to PostgreSQL’s defense, it had quite a few features that MySQL didn’t have. (MySQL is slowly starting to catch up in features.) Both have served me well in the past few years.
Now, after many years of working almost exclusively with PostgreSQL and MySQL, I find myself working on Oracle once again. (I work for a company that manufactures an open source CMS called WebGUI.) We’ve decided to support many commercial databases in addition to the open source databases. Naturally our first choice was to go after the biggest — Oracle 8i and 9i.
I remembered Oracle with fondness since it was one of the first databases I ever worked with. But now, to more experienced eyes, Oracle is the nightmare that haunts me as I sleep. And it is the thorn in my side while I am awake.
I’m a realist. I know that database manufacturers have to keep adding features to their products in order to sell more products. I know that they sometimes need to extend standards in order to accomplish the goals of their products. But I am sick of over-bloated database software. And I’m even more sick of how commercial vendors like Oracle and Microsoft have bastardized the standards.
Just over a month ago I downloaded both the Windows and Linux versions of Oracle to play with them on my two development workstations (one Windows XP and one Red Hat 7.3). For those of you who don’t know, the Oracle 9i database is now on THREE full CDs. It took me one full day just to download the CDs (Oracle was apparently having problems with their site). It then took another couple of days just to get set up with a couple of databases for testing. Not to gloat, but I’m a pretty decent tech-head and it hasn’t taken me three days to install anything in years. It didn’t take even one day to download, compile, install, and configure either PostgreSQL or MySQL.
I then decided to install the Oracle client on one of our test web servers (it was required for the Perl DBI driver to work). First let me state that Oracle required X Windows, which we didn’t have on our web servers, because they are servers and don’t need the wasted overhead. So I had to install X Windows. It also required its own user for the client. Why? Don’t ask me. Its just a client. Once I finally got the installer running, I checked that I only wanted to install the client, and not the database. The client turned out to be almost 1 gigabyte! Now, I’m sure that I could have selected some sort of advanced install that would have installed just the libraries I needed, but the Perl driver said to install the client, so I did. One gigabyte plus one more day wasted.
The next day I started testing our CMS with Oracle. Many features worked right out of the gate. (We worked hard to stick to the ANSI SQL standards to make porting to other databases as easy as possible.) I then started testing some of the features that used more complex SQL, like LEFT OUTER JOIN. To my dismay Oracle didn’t support such a basic feature. Don’t get me wrong; it does have a way to do outer joins, but not using the ANSI SQL standards. The thing that I found even more frustrating was that the LEFT OUTER JOIN syntax has been in the ANSI SQL standards since 1992 — more than enough time for Oracle to adopt it or at least provide an overlay interface for the standard.
Left Outer Join Syntax:
ANSI SQL = LEFT [OUTER] JOIN
MySQL = LEFT [OUTER] JOIN
PostgreSQL = LEFT [OUTER] JOIN
Oracle = table1.column(+) = table2.column
MSSQL = table1.column *= table2.column
The bastardization of the ANSI SQL standard doesn’t end there with either database, but I hate beating a dead horse. Suffice it to say that either Microsoft and Oracle engineers are stupid (which I don’t believe) or someone got it in their head that if they mutate the standard just enough they’d be able to make it difficult for their customers to move away from them.
Now some of you are going to say, “Why don’t you use a translating abstraction layer like ODBC or JDBC?” The point is that I shouldn’t have to. My team and I stuck to the standards for a reason: Portability with no sacrifices. For the reasons listed above, we’re now considering dropping support for Non-ANSI SQL compliant databases.
After 10 years of working with most of the major commercial and open source databases, I’m sad to report that I cannot say, “Look how for we’ve come.” Instead, I have to report, “Look how far away from the standards we’ve come.”
I’ve come to the realization that these two open source databases (PostgreSQL and MySQL), with less than 1/1000 the funding of the commercial databases, are actually better than any of the commercial databases. Sure the commercial vendors have more features, but for the most part they are features I don’t need. And I would guess that most developers and businesses don’t either.
I’m sticking with MySQL and PostgreSQL, and will look at commercial databases as inferior from this day forward.
That’s my two cents. What’s yours?
Category:
- Open Source
