MySQL comparison - Mailing list pgsql-hackers

Hi,

I was following some newsgroups and mailing lists dedicated to web authoring
and I've found many messages where people ask for a
compare table between MySQL (that they are using) and PostgreSQL.

Then I was looking at MySQL documentation (info format) and I've found a very
bad page in comparisons chapter, "How *MySQL* compares
with PostgreSQL":
"`PostgreSQL' has some more advanced features like user-defined types,
triggers, rules and some transaction support. However, PostgreSQL lacks
many of the standard types and functions from ANSI SQL and ODBC. See the
`crash-me' web page (http://www.mysql.com/crash-me-choose.htmy) for a
complete list of limits and which types and functions are supported or
unsupported."

I understand that MySQL is not a RDBMS but a SQL front end to indexed
archives, but people don't understand this.

So I suggest a simply comparison list between PostgreSQL and MySQL, something
to do for pure user information and not for flame war
about DBMS religion.

I've started to prepare this list, and I'll try to keep it updated.
Please help me to maintain my intentions.
Here there is the first version:

1) PostgreSQL is a real RDBMS. This means:- transaction SQL commands available and working: BEGIN, COMMIT, ROLLBACK.
MySQLdoesn't.
 
- PostgreSQL is a Object-Oriented-DataBase-Management-System:
user defined class, functions, aggregates.  MySQL isn't.
- PostgreSQL has a quite good set of ANSI SQL-92 commands:
SELECT INTO
CREATE/DROP VIEW  MySQL hasn't these commands yet.  (please help me to say what is not supported yet in PostgreSQL and
whatis
 
not available on MySQL).
- PostgreSQL implements sub selects, for example:       SELECT * from orders where orderid EXCEPT          (SELECT
orderidfrom packages where shipment_date='today');  MySQL lacks sub-selects.
 
- PostgreSQL has referential integrity SQL commands available and working:
FOREIGN KEYS, CREATE/DELETE/UPDATE TRIGGERS.  MySQL hasn't.
- PostgreSQL implements stored procedures in SQL, Perl and TCL in standard
source distribution. Developers are able to add more
languages for stored procedures.  MySQL doesn't support stored procedures.
- PostgreSQL has embedeed SQL-C precompiler.  MySQL doesn't.
- PostgreSQL support three types of indixes (B-TREE, R-TREE, HASH), and user  can choose which use for his purpose.
MySQLsupports ISAM only archives.
 
- PostgreSQL implements via Multi Version Concurrency Control (MVCC) a
multiversion  locking model.  MySQL supports table lock model as unique transaction protection.
  The main difference between multiversion and lock models is that in MVCC
locks acquired for querying (reading) data don't
conflict with locks acquired for writing data and so reading never blocks
writing and writing never blocks reading.
  PostgreSQL supports many locking models and two kind of trasnsaction
isolation: row-level locking is possible via "SELECT for
UPDATE" SQL command.
- MySQL implements OUTER JOINS, PostgreSQL doesn't yet.
- MySQL is a fully multi-threaded using kernel threads.  PostgreSQL has a number of back-end processes defined at
compiletime, by
 
default 32.
  (please, someone could explain to me which implicantions this could have in
a multi CPU environment?)  (please, someone could explain to me which implicantions this could have on
the number of front-end concurrent processes and
number of open connections to PostgreSQL?)
- MySQL has support tools for data recovery.  PostgreSQL doesn't trash any data by itself, so you need to backup as
for
any other user mistakes.

(I would like to say about the PostgreSQL stability and reliability, but I
fear to open flames on this point. Are there any
suggestions?)


Both PostgreSQL and MySQL are available for many operating systems;
both of them are released in Open Source now: MySQL under GPL since few days,
PostgreSQL is born as Open Source project under BSD
licence.
Both of them have:
DBI, DBD front end for Perl programming languange, 
PHP drivers
ODBC drivers
C/C++ front-end libraries 

(I know that PostgreSQL has JDBC driver, I don't know about MySQL)

Conclusions:
MySQL could be a good tool for any project that doesn't need transactions and
data integrity.
For all other applications PostgreSQL is the only game in the city.


Please send me directly by email, any corrections and improvements. I'll post
here next release of this simple list before to
publish it.

--

I've also looked at crash-me test from MySQL site, I'm sure that
it could be interesting to discuss about it and about how much
this test is reliable.

crash-me test URL: http://www.mysql.com/crash-me-choose.html




Thank you in advance,                   \fer