[olug] DB Servers

Wed Mar 13 02:06:48 UTC 2002

[Resent since I don't think this ever made it through, due to the mailing
list troubles.]

At Thu, 07 Mar 02, Unidentified Flying Banana Mike Peterson, said:
> What database do you use most often and why?

I personally prefer PostgreSQL for general use, though which database
you choose really depends on your specific needs.

> MySQL
> PostgreSQL
> InterBase
> Any Others that Are Open Source

I would also mention SAP DB and Berkeley DB.  Berkeley DB isn't an
SQL database, but it's extremely common, and depending on your
application needs, might be a much better choice than an SQL database.

> Are the databases you use as good for what you use them for as Oracle,
> Sybase, SQL Server, Progress, or others?

You forgot DB2. ;-)

In general, provided you have a solid understanding of what you're doing
and what your requirements are, I've found open source Databases to be
as usable as commercial ones.  For the vast majority of tasks requiring
a relational database, I'll use PostgreSQL, or possibly MySQL.  For
simpler database tasks, or applications requiring an embedded database,
you can't hardly go wrong with Berkeley DB.

However, there are still places where the currently available open
source databases aren't yet appropriate.  For truly massive and
massively loaded databases, you'd be crazy not to use DB2 or Oracle.
Not only do they currently have more "enterprise" level features, such
as more advanced clustering and fail over, but in that type of
situation, you want and need the kind of support they can provide (the
kind where you can call up at 4am with a serious problem and immediately
talk to an actual engineer who will find a way to solve your problem,
whatever it takes).

Of course, these situations are very rare. ;-)

> Of those that use MySQL or PostgreSQL,
> 
> Is one better than the other?

This question has been known to start flame wars of the same intensity as
vi vs. Emacs, X vs. Command Line, and Linux vs. *BSD.

Personally, I usually prefer PostgreSQL, and I think it is a generally
superior database to MySQL, but the question of which is "better' can
only be made in the context of a specific problem.

> Why?

I'm glad you asked.  Both databases support slightly different features
which can help make the decision.

MySQL is blazingly fast when you are doing primarily database reads.
However, the default table type, MyISAM, only supports table level
locking, which means that if you are doing many database updates, it
will quickly slow to a crawl.  There is a new table type, InnoDB, that
supports row level locking and rudimentary transactions, but you take a
major performance hit when you use it.  It has decent SQL Standard
compliance, although it's not stand out.

While in the proper situations, it can be the fastest available
solution, this speed comes at a price.  MySQL doesn't support Views,
Sub-selects, Stored Procedures, Triggers, Full Outer Joins, or Foreign
Keys.  And, as mentioned above, it's most commonly used, and fastest,
table type doesn't support transactions.  Additionally, MySQL tends to
sometimes suffer considerable slow-downs when faced with very complex
queries.

Due to the lacking features in MySQL, it's hard to consider it a "true"
relational database.  It's often called "an SQL interface to a
file-system", and that remark isn't far from the truth.  For many web
accessible databases, where the frequency of reads outnumbers the
frequency of updates by many orders of magnitude (I worked on one
project where the number of reads was around 70 times the number of
writes, and MySQL was an excellent fit for it), MySQL can be an
excellent solution.

PostgreSQL used to have a reputation for being slow and buggy.  However,
since the release of the 7.x versions, it has shed that reputation, and
rapidly become a database to be reckoned with.  It is fully ACID
compliant database supporting Transactions, Foreign Keys, MVCC
(MultiVersion Concurrency Control ("better than row level locking")),
Views, Sub-selects, Stored Procedures, Unicode, Full Outer Joins,
Triggers, and more.  It is also one of the most standard compliant
Databases available, with regards to ANSI SQL.  PostgreSQL also supports
object oriented (object-relational) extensions and geometric data types.

Over the past few PostgreSQL releases, the programming team has made
stability, speed improvements, and the removal of limitations their top
priorities.  Because of this, PostgreSQL has become very solid, able to
keep with most of the competition in speed, and has had most of it's
limitations (like the infamous 8k row limit. . . it now supports
significantly larger data sizes than MySQL (and most other databases)
for many data types) removed.

If you are planned on building an application that will eventually run
on Oracle or DB2, or if you are attempting to move an application that
currently uses Oracle to DB2 to an open source Database, PostgreSQL will
make your life much easier than MySQL, as it supports more of the
features that commercial databases do.

I currently consider PostgreSQL to be the most featureful and advanced
open source database available.  In general, I've found it suits my
purpose more frequently than anything else.

> Do they both support transactions?

As mentioned above, PostgreSQL is fully ACID compliant, and has full
support for transactions.  MySQL has more limited support, but provided
you use the InnoDB table type, it can support transactions.

>Are they interchangeable or not?

Sort of.  Theoretically, if you use ANSI compliant SQL, your application
will work on any database.  Realistically, that doesn't always happen.
Many databases vary in how well they support the SQL standard, and
nearly all databases provide some extensions that go beyond it.  If you
either code or make use of a database abstraction layer (such as Perl's
DBI module), and you do a little research before designing your
database, it's usually not terribly hard to make an application
reasonably portable between databases.

One thing to be careful of is to ensure that the databases you are
targeting support the same features.  For example, MySQL doesn't support
foreign keys, so if your application relies on them, it will be
difficult to port to MySQL.  Another example was when a friend and I
were porting a little website from some proprietary database (I don't
recall which), and the old database didn't have native support for
boolean types.  When the database application was originally designed,
they improvised and used a non-standard type, which we had to convert,
and then rewrite some of the code in order to use normal boolean types.

I hope this helps answer a few of your questions. ;-)

I've included here a handful of URLs which do various comparisons on
databases.  The first three are fairly unbiased, while the last is a
specific critique on MySQL.

http://www.geocities.com/mailsoftware42/db/
http://www.phpbuilder.com/columns/tim20000705.php3
http://www.webtechniques.com/archives/2001/09/jepson/
http://openacs.org/philosophy/why-not-mysql.html

--
Christopher

-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_

For help contact olug-help at olug.org - run by ezmlm
to unsubscribe, send mail to olug-unsubscribe at olug.org
or `mail olug-unsubscribe at olug.org < /dev/null`
(c)1998-2002 OLUG http://www.olug.org

-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_