Google Search

Google

Sunday, June 10, 2007

Oracle 10g Vs PostgreSQL 8 vs MySQL 5

Full Disclosure: I am strongly biased towards Oracle and fully expected no real competition.

License: I will not get into a debate over open source and closed source. Oracle is a commercial database and requires licensing to use. MySQL and PostgreSQL are both open source projects. Oracle provides a free developers license for you to "test out" and create prototypes.

Configuration: I am installing on an older machine with 256 Megs of Ram and a 40 Gig hard drive. The CPU is a 633 Mhz Celeron. The OS is Windows 2000 Professional. I want to install at the low end of the spectrum to gauge the ability for home user-type installations.

OS: I chose to perform this comparison under Windows, as I believe that that is still the most common platform that new people to the world of databases will use. By that, I mean people installing at home. For new corporate users, the odds are good that they will not have a choice of either OS or database so a comparison is pointless.

Versions:

PostgreSQL 8.0 - PostgreSQL 8.0 is a very new product. I chose 8.0 as it is the latest version and it natively supports Windows. This is the first version that actually does support Windows without an emulator or third-party recompilation.

MySQL - MySQL 5.0 is also a very new product. As a matter of a fact, the version I used, v5.0.4, is actually a beta version. I argued with myself about whether I should use the stable v4 or the beta v5. I chose v5 because v4 does not compete with PostgreSQL or Oracle. V4 does not have stored procedures, triggers, views, etc. Without those features, I would not consider it as a contender. The upside to that is that v5 does support those features.

Oracle 10g - This is the latest version of Oracle and was released last year. In my opinion, Oracle 10g is the gold standard of databases. Nevertheless, I also recognize that it is very expensive and requires quite a bit of knowledge to support in a production environment.



Ranking Factors:
  • Documentation and Getting Started Support
  • Ease of installation
  • Ease of Verifying Successful Installation
  • Creation of Non-Admin User
  • Time to Run First Query
  • Resource Requirements

Ranking Values: I rank each factor a score of 1 through 10, with 10 being the best or highest.

Documentation and Getting Started Support

PostgreSQL - I found a lot of support for previous versions (under Linux, Cygwin, etc.), but almost nothing for 8.0 under Windows. The PostgreSQL documentation website, http://www.postgresql.org/docs/ , provides very good, very complete documentation. In my opinion, one of PostgreSQL's strengths in the past has been the documentation.

That web site includes a very good installation document. However, post installation and getting started documentation is very limited. I hope that that will change in time.

I googled "PostgreSQL getting started" and got thousands, mostly version 7, of hits. PostgreSQL has some Usenet newsgroup support, but I noticed they were not very active.

PostgreSQL Documentation Ranking: 5

MySQL - Because v5 is so new, there is not very much documentation yet. There is a world of documentation for MySQL v3 and v4. Check Amazon or BN.com. MySQL seems to be the choice for a lot of smaller Internet and java-oriented companies.

Because v5 is beta, I will not rank this factor according to the documentation available for that release. V4 has amazing documentation and I fully expect v5 to have the same by the time the software is released to production. I will rank according to v4 documentation.

I googled "MySQL getting started" and got millions, mostly version 4, of hits. I could find no MYSQL-specific Usenet newsgroup support, but I noticed some postings for MySQL support on the comp.databases group.

MySQL Documentation Ranking: 8

Oracle - Oracle, as a commercial product, has a large staff of full-time technical writers. Every feature is spelled out and many features receive their own books. The installation documentation provided by Oracle is superior and, where that is lacking, it is made up by third party sites dedicated to Oracle. The documentation, as well as the documentation portal, is very mature.

In addition to installation guides for just about every OS, Oracle provides a "2 day DBA" guide. This document is indispensable for new users.

I googled "Oracle getting started", and received millions of hits. Oracle also has robust Usenet newsgroup support.

Oracle Documentation Ranking: 10

Ease of Installation

PostgreSQL

I haven't previously used PostgreSQL outside of Cygwin (a Unix environment for Windows). I was surprised at the feel of the installation. It seemed very native and ran flawlessly. It was also a very quick install.


PostgreSQL installed the quickest. According to the documentation, you can install PostgreSQL without administrative rights. As this is the first version of PostgreSQL to support Windows (without Cygwin), I wanted to test the Services support. PostgreSQL installed as a service and came up without problems after rebooting.

PostgreSQL Installation Ranking: 10

MySQL

I ran a web site in the past, mainly for my own amusement, and used MySQL as the database. The technology stack I used was Java/Apache/MySQL. I was fully expecting MySQL to win the installation competition based on experience. However, MySQL was the only install to fail during installation. I performed a little cleanup and then re-ran the install. It failed again. I cleaned up again, rebooted and re-ran the installation. This time it installed. I'm not sure what caused the error. It was very cryptic but it was the same both times it failed. I was able to bulldoze past that, though. Remember, this is a beta release.

MySQL also installed as a service and on reboot came up with no errors. The install, when it ran correctly, ran quickly.

MySQL Installation Ranking: 5

Oracle

Oracle again proved that it is a very mature product. Of the three, Oracle is the only one that can NOT be installed without admin rights. It would be nice for this to be an option.

It did, however, install easily. I received no errors. Oracle was the only install to point me towards the next step at the end of the install. This is a sure fire method to support new users and requires almost no effort. All databases should provide this kind of support.

Where Oracle really took a hit was in the amount of time it took to install. This is a real issue for home users. There were a couple of spots where I thought the machine might be hung as I received no feedback for an extended amount of time.

Oracle Installation Ranking: 7

Ease of Verifying Successful Installation

To verify the success of the install was a little different, but all three provide data access tools. I followed the instructions in the provided documentation and was able to access the server in all three instances.

Verification Ranking for all 3: 10

Creation of Non-Admin User:

PostgreSQL: PostgreSQL uses the standard CREATE USER syntax and I was able to perform this task easily. The documentation walks the user through this task (which is required for security reasons).

PostgreSQL Create User Ranking: 10

MySQL: In v5, MySQL also uses the CREATE USER syntax. Prior releases did not. Due to the fact that the documentation is not yet complete; it is not completely obvious that CREATE USER is available. For a new user, this can be confusing.

MySQL Create User Ranking: 7

Oracle: Oracle uses the standard CREATE USER syntax and I was able to perform this task easily. The documentation walks the user through this task. Oracle Create User Ranking: 10

Time to Run First Query

All three database documentation sets provide a tutorial for getting connected and running your first query.

All three First Query Ranking: 10

Resource Requirements:

PostgreSQL - I was amazed at PostgreSQL's minimal need for resources. I performed some tests like opening web browsers and MS-Word while connecting remotely and could see no impact at all on my test machine.

I couldn't find the minimum hardware specs, but 256Meg on an old machine seems to be more than enough.

PostgreSQL Resource Ranking: 10

MySQL - MySQL's minimal need for resources is well known. That is one of its biggest selling points and always has been. I performed the same test that I preformed for PostgreSQL and MySQL performed as well as PostgreSQL.

Oddly enough, I couldn't find the minimum hardware specs for MySQL either, but 256Meg on an old machine seems to be more than enough.

MySQL Resource Ranking: 10

Oracle - Oracle uses the most memory, and Oracle 10g uses even more than previous versions. It really would run better on a 512M machine, and I would recommend 1gig.

On the test box, it takes forever (well, many long minutes) for Oracle to even start up. When I opened Word, I started getting swapping and stalling. With multiple remote connections, the database came to a standstill. Oracle will install and run in 256 Megs, but I don't recommend it.

Oracle Resource Ranking: 3

So how does each stack up? Here are the final rankings:

PostgreSQL: 55

MySQL: 50

Oracle: 50

So, that is the install and first use ranking. That is not at all what my expectations were. I think it's great that all three rated so high. I was not expecting that or that Oracle and MySQL would tie. I was definitely not expecting PostgreSQL to come in first.

However, installation is only a small piece of using a database. There is language support, administration, additional usability features, security and third-party support. In the near future, I will write up a comparison of these components.