Which database...

[markw at mohawksoft.com (markw at mohawksoft.com)]

> From: Kyle Plummer <kyle at breezy.com>
>
> Although not directly a linux question?  I have a client who is thinking
> about using Oracle, IBM or MS SQL for a large DB project.  His question
> to me is which is a more stable/reliable/scalable database?
>
> Thanks for your input.
>
> Kyle
>

I've done a good number of database driven projects and it is a much more
difficult question that you are thinking about.

Well, my first thought is to forget MS SQL, while the engineering version
is free, they'll get you if you want to do anything interesting. Plus, MS
SQL locks you into Windows and Windows just isn't stable enough, and
requires a reboot -- i.e. the server has to be shut down and restarted --
if you change certain system settings. Not acceptable for an enterprise
database system.

Oracle is a great product but it has its own dialect of SQL that sort of
locks you in. Since, however, it is the 800lb of the SQL market, it is
probably a safe choice. Unfortunately, Oracle need more maintenence than a
newborn. It is not a "set it and forget it" system. You have to constantly
monitor and tune it. It also is very picky about "space" it has a few
levels of indirection, which is flexable, but if you are not careful, you
can be stuck with a situation where it looks like you have enough space,
but Oracle doesn't think so and stops.

DB2 is a good system, but it is sort of a purist SQL database. I don't
have too much experience with it.

MySQL is a joke, don't even consider it. It doesn't support enough "SQL"
to write efficient queries. The speed claims come from simple "selects"
and don't describe its misserable performance on database modifications.
It scales very poorly if you actually modify the database. When you use
the subsystems that offer better scalability, the performance goes to
hell.

PostgreSQL is a very good system with a few caveates (1) You tend to have
more inserts than updates and deletes. (2) You run "VACUUM" regularly.
PostgreSQL has a very good MVCC design, and scales very well under high
load.

Last time I looked, Sybase was pretty good, I haven't used it in a long
time, but I hear they've improved it a lot.


Oracle, DB2, Sybase, MS SQL, and PostgreSQL all scale well. My gut feeling
says Oracle scales best on heavily modified databases. DB2 probably does
the best job at query parsing and execution. PostgreSQL offers a good
balance.

When setting up a system my strategy is to see if there are reasons why
PostgreSQL won't suit the project (i.e. load, types of operations, ability
to run VACUUM regularly, etc.). If PostgreSQL won't do it, I choose Oracle
just because I know it better.

Don't discount "know it better" when it comes to databases. There are
limitations in all the databases, some are really bad (MySQL), but most
are workable if you know how to tune and operate them. All databases will
have trade-offs, if you are working in a contentious environment, someone
will always be able to find a reason why your choice was a mistake, just
make sure *you* understand what the database can do and that it suits your
application.