[NTLUG:Discuss] MYSql vs. postgresql

[Greg Edwards]

David wrote:
> 
> On the other hand, PostgreSQL is a much more complete implementation
> of ANSI SQL.  PG supports:
> 
>  * sub-queries, such as: SELECT * FROM SALES WHERE ITEM_COLOR IS IN
>    (SELECT COLOR FROM ....).  To do this in Mysql, you need to run
>    multiple queries and tie them together in your PHP or Perl script.  
> 
>  * full support for JOINS.  Joins are more than just a cute
>    convenience; they're critical to being able to decompose a database
>    problem into the canonical "normal forms".  Normal forms assure
>    logical consistency in the database, so this can make a huge
>    difference in the quality of your results.
> 
>  * transactions.   BEGIN ... COMMIT, with full rollback in case of
>    error.  
> 
>  * full serializability of operations.  PostgreSQL is ACID-capable.
> 
>  * constraints.  the database can be told how items are related, and
>    will enforce those relationships.  For instance, the color of an
>    item must be chosen from one of the values in the COLORS table.
>    Any attempt to insert a different value will be rejected.
> 
> My reason for choosing completeness over performance is that I can
> always get more performance by buying a faster machine.  But a faster
> system will never cure a basic lack of SQL compliance.  I think it's
> more important to get the functionality right, and then buy the faster
> machine if it turns out to be needed.
> 

I agree with your reasons for using PostgreSQL when it's the right tool 
for the job.  As with any other tool it's a trade-off between features 
and performance.  You should always your needs and use the right tool 
for the job at hand.

If your main access will be from PHP, Perl, Python, Bash, Ruby, Tcl/Tk, 
or any other interpereted language then the sub-queries and joins are 
worth the performance loss.  If your giving lots of users direct access 
to the DB then transactions are a must.

If your going to be using a compiled language like C, or C++ then many 
of these features are no longer necessary.  A sub-query is not magic 
it's just done for you by the DBMS API.

My need is performance #1, DBMS data handling #2, no user's direct DB 
access.  For these reasons I'm going with MySQL on my current project. 
But this is the better tool for my current needs.  None of the available 
DBMS tools (including Oracle and DB2) can claim to be the best tool in 
ALL circumstances.  Although they all try to make that claim.

-- 
Greg Edwards
New Age Software, Inc.
http://www.nas-inet.com