[NTLUG:Discuss] MYSql vs. postgresql

[kbrannen@gte.net]

Terry Hancock wrote:
> On Monday 17 February 2003 11:35 am, 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
>>migrate to a different machine when hardware needs upgrading?  What
>>about front end clients for each?  Since both are included in my distro
>>(RH8.0), I would like your input before we begin setting things up.
> 
...
> Anyway, I have heard from PostgreSQL partisans that it supports a far more 
> complete set of SQL syntax.  However, most of the things that have 
> traditionally been used to assert PostgreSQL's superiority (like 
> transactions), have now been implemented in MySQL, so the completeness gap is 
> slowly closing. On the other hand, I have not heard any claims that 
> PostgreSQL is closing the performance gap on MySQL.  The remaining syntax 
> additions you get with PostgreSQL are apparently pretty esoteric, as I have 
> not heard any justifications of their application that made any sense to me.
...

Having used both extensively, let me add to the above, because there are real 
differences.

For the longest time, transactions were missing from mysql, but it now seems 
to have them.  For e-commerce, that is extremely important.  So it's more even 
there now.  Mysql is behind in other things, such as no sub-selects, you don't 
have to "lock" the DB (or at least tables) to do backups, and a number of 
SQL92 and SQL99 features are missing from mysql but present in Postgresql. 
Mysql does not have journalling, while Postgresql does; so pull the power cord 
on a mysql DB machine with care. :-)

If you start the getting a large number of hits, the tests I've read about say 
Postgresql scales better, while mysql falls to its knees after a certain 
number of simultaneous users (it was something like 20 on the machine in the 
test).

The speed issue has been addressed in recent Postgresql releases.  Mysql is 
still faster for just queries (though by not as much as it used to be), but if 
you have lots of inserts/update/deletes, then Postgresql actually starts to do 
better.  Postgresql also does very well under heavy loads.

Also be aware that porting between them can be done (I've done it), however, 
mysql has a number of "extensions" that are not ANSI standard, but are quite 
useful (e.g. their time/date functions).  But to be fair, most DBs do this as 
the ANSI standard is really weak in this area.

And if you will be doing work that requires lots of locking (directly or 
indirectly), you'll find Postgresql does a little better job of dealing with 
it.  Mysql is not bad or unacceptable, but Postgresql just is more SQL correct 
and straight-forward (I think mysql now does it at the page level, while 
Postgresql does row-level locking).

So if you need or are used to something that is more complete (like Oracle), 
go with Postgresql.  OTOH, if you just need a DB that is mostly for queries 
only (few inserts/updates), not on a high volume website, and you'd like ease 
of use/admin--pick Mysql.  They are both tools, each have their advantages and 
disadvantages.  As someone else pointed out, this can be a highly volatile 
discussion, :-)  nevertheless, you're just picking a tool.  As always, figure 
out your goals, requirements, and restrictions first, then picking the tool 
will be much easier.  Both are good DBs in their own context.

HTH,
Kevin