[NTLUG:Discuss] MYSql vs. postgresql
David
david at hayes-family.org
Thu Feb 20 23:15:18 CST 2003
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
More information about the Discuss
mailing list