Which database...

[wdr at theworld.com (Bill Ricker N1VUX)]

> > 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?

How big is Big?

Big and MS SQL? I don't think so. 

We're using IBM UDB DB2 at work, but other groups use Oracle.  We're moving
our Solaris/Sybase  apps to AIX/DB2.

I use MySQL for my LAMP stuff, away from work.  MySQL has caught up
feature-wise and has growing commercial use. 

> 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, 

Yup.

> Oracle is a great product but it has its own dialect of SQL that sort of
> locks you in. 

True. All vendors SQL are subtly different but Oracle is DIFFERENT.

> Since, however, it is the 800lb of the SQL market, it is
> probably a safe choice. 

Yes.

> 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. 

And doesn't have auto-tune features that UDB has.

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

The DB2 Cookbook is the great source for DB2 after-market documentation.
 http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM
The IBM redbooks are good too, but this book is free and comes with
bind-it-yourself instructions, and is updated for each release.
 
> MySQL is a joke, don't even consider it. 

That sounds like it's based on the previous release.

> It doesn't support enough "SQL"
> to write efficient queries. 

MySQL 4 or MySQL 5?  Their roadmap gets them to real already or soon,
depending on what real means.


> 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.

All DBs need to be tuned differently for heavy-update-use versus load-at-night
/ read-only-daytime use and everything  in between.  Depends what Heavy means
...

> 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.

PostgreSQL has a problem compared to MySQL -- there are THREE companies trying
to do commercial support, and NONE has a control on the intellectual property.
MySQL can prevent forking, so is perhaps less pure F/LOSS but is a better
commercial bet for stability in the enterprise.
 
> 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.

Sybase is about the same ... and declining market share. Not something I want
to be moving to.
 
> 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.

Pretty true, I guess, although I don't want to think about what it takes to
scale MS SQL to enterprise. Sybase has some cool stuff for vector indexes for
optimizing certain static queries, if you need it -- that's the only reason
they scale really big.
 
> When setting up a system my strategy is to see if there are reasons why
> PostgreSQL won't suit the project 

If he has to ask the question here, he probably needs good, reliable
commercial support. Which would be why PostgreSql isn't ready for im.

 
> Don't discount "know it better" when it comes to databases. There are
> limitations in all the databases, 

Yes, you want your DBA to be really on top of the chosen tool. DOn't have each
project pick a DB, use one for everything.

> some are really bad (MySQL), 

I think this is old news ... MySQL has really grown up ...

> 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.

Right on!  Implicate Mgt in the choice and live with it ;-)

Bill