This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: [HACKERS] MySQL comparison
From: Ferruccio Zamuner <nonsolosoft@diff.org>
Date: Tue, 18 Jul 2000 21:10:58 +0100
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
===
Subject: Re: [HACKERS] MySQL comparison
From: Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Date: Wed, 19 Jul 2000 13:52:02 +1000
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.
===
Subject: Re: [HACKERS] MySQL comparison
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 19 Jul 2000 00:51:24 -0400
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 ;-)
===
Subject: Re: [HACKERS] Article on MySQL vs. Postgres
From: Tim Perdue <tperdue@valinux.com>
Date: Mon, 10 Jul 2000 23:00:30 -0700
Randall Parker wrote:
>
> Tim,
>
> Aside: Is your MySQL database running on an ext2 volume or on a ReiserFS volume? I read somewhere that half of SourceForge is now running in Reiser. Is this true?
That's ext2. I don't know if "half" of SF.net is running on Reiser, but
some of the biggest, most critical stuff has been for 6-8 months.
===