mySQL or PostgreSQL?

[Derek Neighbors]

> The answer is it depends. On what, features. MySQL is lightweight fast
> and fairly simple. PostgreSQL is more feature rich, supports true
> transactions and is just about as fast as MySQL.

Note when you add database features to MySQL you have to switch from ISAM
to InnoDB types and at that point performance degrades.  In fact, I think
you could find benchmarks showing MySQL w/ InnoDB being slower than
Postgres.

That said, if you want web display only go MySQL with ISAM it will be
lightning fast.  If you are going to do real database type work, you will
need to use MySQL InnoDB.  We can then say MySQL and Postgres perform the
same.  I say this because the difference is probably small enough that
cheap hardware prices could over come any differences either way.

So we will talk features.

> If you are just attempting to learn MySQL is fine, if you are doing
> anything where the data matters PostgreSQL is the way to go.

Becareful with this statement.  Because MySQL has poor ANSI SQL compliance
it teaches bad habits.  Even in instances where they have added proper
ANSI support their manuals still show you how to do it the "MySQL" way. 
The first time you try to use your new leet SQL skills on another database
you will be immensely frustrated.

Places I see MySQL falling short for Enterprise Applications.


1. It has "broad" ANSI SQL support plus its own "extensions".  Read this
as it makes you learn a non portable way to write queries.  Every database
does this to a degree.  MySQL seems particularly bad about this.

2. To my knowledge MySQL is not ACID compliant, Postgres is.

3. Views are not supported in MySQL.  Anyone that uses database at an
Enterprise level knows w/o views you are dead.

4. Unicode support does not exist for MySQL.

5. Stored Procedures are not supported in MySQL.

6. UNION is now supported but UNION ALL and EXCEPT are not (as far as I know)

The downside to many of the features like row level locking, transactions,
unions, etc that are available in MySQL 4.x and such is that they are less
than 6 months old (and so do not have a large proven track record, yet) 
That is not to say they dont work or they are inferior, just like all
things a little age and testing can be a good thing to build character.

As far as User Interfaces go, I think its pretty equal.  Most of the
popular DB tools support MySQL and Postgres both.  pgAdmin, pgAccess,
Tora, Data Architect are just a few.

-Derek