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.
   MySQL doesn'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 what is
not available on MySQL).

 - PostgreSQL implements sub selects, for example:
        SELECT * from orders where orderid EXCEPT
           (SELECT orderid from 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.
   MySQL supports 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 compile time, 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


Tue, 18 Jul 2000 21:10:58 +0100
Ferruccio Zamuner <nonsolosoft@diff.org>
MySQL comparison
Ferruccio Zamuner wrote:

>  - MySQL is a fully multi-threaded using kernel threads.
>    PostgreSQL has a number of back-end processes defined at compile time, by
> default 32.

It's better not to mention this. I used to work for a database vendor
with a process model (i.e. like postgres), and we could still beat the
opposition who had a threaded model. The bottom line is that
thread/process isn't that important. We even used to beat the opposition
in number of databases/connections, by a huge margin. It's an
implementation detail. Don't confuse people by throwing it up.

Wed, 19 Jul 2000 13:52:02 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: MySQL comparison
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes:
> Ferruccio Zamuner wrote:
>> PostgreSQL has a number of back-end processes defined at compile time, by
>> default 32.

> thread/process isn't that important. We even used to beat the opposition
> in number of databases/connections, by a huge margin. It's an
> implementation detail. Don't confuse people by throwing it up.

Another point worth making is that this is not a number fixed at compile
time, but just a postmaster parameter with a default of 32.  You can set
it much higher if your system can handle the load.

The reason the default limit is so low is just that we'd like the code
to run out-of-the-box on platforms with small kernel limits on shared
mem or number of semaphores.  The hidden assumption is that someone
who really needs a higher limit will know enough to RTFM ;-)

			regards, tom lane

Wed, 19 Jul 2000 00:51:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: MySQL comparison 
On Tue, 18 Jul 2000, Ferruccio Zamuner wrote:

>  - 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.
Do you mean "user defined functions?"
Hmmm... Sorry, but what does it mean "strored procedure"?
IMHO stroed procedure is somewhat like this:
Proc dd_deleteds()
  SELECT * FROM dd WHERE deleted=1;
END;
And you can call this procedure from client, or from a backend-parsed pl.
A procedure may produce a some columns, and some rows. I think there are
DBMS, who support this. PostgreSQL do it?


--
 nek;)))


Wed, 19 Jul 2000 12:19:16 +0200 (CEST)
Peter Vazsonyi <neko@kredit.sth.szif.hu>
Re: MySQL comparison