Mysql Vs. Postgresql

[m***@yahoo.com]

Perhaps posted it on wrong forums initially as I got only one response
(which was helpful but I was looking for more). Please help if you can!

===============================================

Hi Database Gurus,

Not to start a war among fanatics, but I just wanted to get honest
opinion/advise of smart folks like you about this. We are about to begin
development for a data intensive web based application and are faced with
the choice of database software. We (at least currently) are focusing on
free software. Some basic research has indicated both Postgresql and Mysql
as the best of lot.

Can people please advise as to pros/cons of each of these ? I am looking
from the following angles:

a.. Performance (Scalability)
b.. Reliability
c.. Ease of use
d.. Extensibility and portability (If we were to migrate to some other
database engine in the future, for example Oracle)
e.. Feature rich-ness
f.. Rich-ness of other tools available around the database engine
g.. Anything else that I have missed.

Are there any pointers to any online work that has done comparisons
between these two ?

Please do reply. Any help/guidance will be truly appreciated.

Thanks in advance!

=====================================================================
Email : myoda0001 AT yahoo.com
Website: http://www.eruditionhome.com [Excellent site for data mining
tutorials and resouces]
=====================================================================

[Laurenz Albe]

Post by m***@yahoo.com
Not to start a war among fanatics

To avoid that, I only reply to this newsgroup.

Post by m***@yahoo.com
Can people please advise as to pros/cons of each of these ? I am looking
a.. Performance (Scalability)
b.. Reliability
c.. Ease of use
d.. Extensibility and portability (If we were to migrate to some other
database engine in the future, for example Oracle)
e.. Feature rich-ness
f.. Rich-ness of other tools available around the database engine
g.. Anything else that I have missed.

Before my company decided to go for PostgreSQL, we did some extensive
feature research and rather sketchy performance measures.

Performance is comparable, I couldn't see any dramatic differences (I used
InnoDB tables on MySQL as transactions are imperative).

Ease of use is comparable. There are ready-made RPMs that work.
Administration of PostgreSQL is (contrary to a wide-spread opinion) not
significantly more difficult. There are probably more options you can
tweak, but you don't have to do it.

In portability PostgreSQL is clearly better. Its standard compliance is
in my opinion unsurpassed (I know no database system that fully implements
the standard). On the Oracle angle, the stored procedure language PL/pgSQL
of PostgreSQL is a clone of Oracle's PL/SQL, which makes porting easy.

In feature-richness PostgreSQL wins hands down.

Other tools: probably MySQL will do better there as it is much more
popular and in wider use than PostgreSQL.

Anything else you may have missed: Oracle bought InnoDB.

Yours,
Laurenz Albe

[Peter]

I suggest you take a look at wikipedia, in particular:

http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
http://en.wikipedia.org/wiki/Comparison_of_SQL_syntax

Post by m***@yahoo.com
Perhaps posted it on wrong forums initially as I got only one response
(which was helpful but I was looking for more). Please help if you can!
===============================================
Hi Database Gurus,
Not to start a war among fanatics, but I just wanted to get honest
opinion/advise of smart folks like you about this. We are about to begin
development for a data intensive web based application and are faced with
the choice of database software. We (at least currently) are focusing on
free software. Some basic research has indicated both Postgresql and Mysql
as the best of lot.
Can people please advise as to pros/cons of each of these ? I am looking
a.. Performance (Scalability)
b.. Reliability
c.. Ease of use
d.. Extensibility and portability (If we were to migrate to some other
database engine in the future, for example Oracle)
e.. Feature rich-ness
f.. Rich-ness of other tools available around the database engine
g.. Anything else that I have missed.
Are there any pointers to any online work that has done comparisons
between these two ?
Please do reply. Any help/guidance will be truly appreciated.
Thanks in advance!
=====================================================================
Email : myoda0001 AT yahoo.com
Website: http://www.eruditionhome.com [Excellent site for data mining
tutorials and resouces]
=====================================================================

[The|Godfather]

Here are some links by me too:
I needed also to compare some features of mySQL and PostgresSQL for a
certain project.

PostGres is the most Oracle look-alike in the free-world
databases.MySQL used to be a runner-up behind PostGres,but as from
version 5.0 and 5.1 it has many new must-have features:
http://www.databasejournal.com/features/mysql/article.php/3592726

First,read these 2 articles with all the forum posts in them (huge) ,
dedicated mainly to comparison between the two databases:
http://www.phpbuilder.com/columns/tim20001112.php3
http://www.phpbuilder.com/columns/tim20000705.php3

then:
http://www.databasejournal.com/features/mysql/article.php/3288951
http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html
http://sql-info.de/mysql/gotchas.html#1_1 ( a bit outdated )

Some things I found frustrating about mySQL:

They do not offer the the FETCH .. syntax for more than one row at a
time, i.e. you will have to loop and fetch 1 by 1 row. In PostGres you
can fetch as many rows as you want.

The LOB fieldsa are just ordinary Fields in mySQL , i.e. it is NOT so
flexible and it has some memory limitations as well. In PostGres they
are much like the Oracle LOBs ,where you have many options on them.


Both PostGres and mySQL do NOT provide any array interface for
manipulating large SELECT-like result statements. mySQL can be used
with an 3rd party library to emulate arrays:
http://datacharmer.blogspot.com/2005/11/mysql-5-general-purpose-routine_28.html


You are probably going to use some API to access mySQL or PostgresSQL .
For mySQL C API I can tell you that there are some frustrating
limitations ,but it is good anyway. Their APIs both support most of the
new features. If you need more info about that ( I am using mySQL C API
right now ) , email me.

Cheers,
Dragomir Stanchev