[NTLUG:Discuss] MYSql vs. postgresql

[David]

On Mon, Feb 17, 2003 at 01:35:22PM -0600, Wayne Dahl wrote:
> Hi guys.  My wife and I are considering starting a business and will
> need a database.  Any recommendations in the Linux realm?  What are the
> pros and cons of both MYSql and postgresql?  How hard are they to

I suppose _someone_ ought to say something nice about PostgreSQL.  

I've used both, and have now settled on PostgreSQL for my projects.  

It appears to me that the primary advantage of Mysql is performance;
it's faster for some types of operations, typically reads, and most
DB's do a lot more reads than writes.  

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.

-- 
David Hayes
david at hayes-family.org