Thread

1. MySQL comparison

   Ferruccio Zamuner <nonsolosoft@diff.org> — 2000-07-18T20:10:58Z

   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
   
   
   

2. Re: MySQL comparison

   Chris <chrisb@nimrod.itg.telstra.com.au> — 2000-07-19T03:52:02Z

   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.
   
   

3. Re: MySQL comparison

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-19T04:51:24Z

   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
   
   

4. Re: MySQL comparison

   Peter Vazsonyi <neko@kredit.sth.szif.hu> — 2000-07-19T10:19:16Z

   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;)))