MySQL vs PostgreSQL (was "RE: MySQL install from source??")

[Keary Suska]

on 4/3/02 11:18 AM, rbrandt@sbdsl.com purportedly said:

> 1) MySQL is similar to Filemaker - they are flat files that you can
> do relational things with.
> 2) PostgreSQL is similar to Oracle - they are the 'real deal'
> relational databases.
> 
> Where MySQL falls over relationally (so I'm told) is when you try to
> do outer join SQL calls, populating an array from two tables in one
> call.  I believe you can do it in MySQL, but it's really slow.  Also,
> MySQL doesn't do transactions (rollback), although someone told me
> that that's coming.

Comparisons are difficult, but being rather familiar with both, I can clear
up some misconceptions. MySQL *is* an RDBMS by definition. It is not at all
like FileMaker in the sense of being a flat-file system with kludges to
emulate relational operations. There have been critiques to the way that
MySQL stores data (single file for each table), but data storage and
retrieval is not a function of the definition of RDBMS. The definition of
RDBMS was modified by its creator to include ACID compliance, but IIRC this
is not a requirement but something that should exist in a "proper" RDBMS.
The lack of ACID compliance is the only real argument against MySQL being an
RDBMS, but it is not entirely valid.

That being said, MySQL does not support higher end functions such as stored
procedures or triggers. It currently has transaction support using a
Berkeley DB system, which is in alpha or beta, but appears unlikely to be as
efficient as a system with transactions built into the core DB engine, such
as in Postgres.

MySQL is very fast--much faster than Postgres, however benchmarks I have
seen and from my own experience are comparing MySQL sans transactions, and
of course transactions introduce a significant overhead. But MySQl does not
scale well. Postgres begins to beat the pants off MySQL at higher loads
because the MySQl core has a soft ceiling for the number of concurrent
connections, which causes connections to be queued under heavy activity.
Postgres does not have this limitation providing it is configured properly
and the host system has sufficient resources.

MySQL is fast, easier to use and administer. Its flexibility from a schema
standpoint is better than Postgres. For instance, in Postgres, you can't
delete or change the data type (even to increase or decrease the size of a
char/varchar field) of a column without rebuilding the table completely.
Because MySQL does not have any kind of server-side programming, you cannot
offload any of the functionality of an application to the DB engine.

Postgres is more powerful and robust, and has comparable functionality to
the "big boys" such as Oracle. It however lacks the really high-end features
such as encrypted communications and replication (clustering Postgres can
only be done currently by kludging with triggers on every object--not just
tables, but also sequences and others). It scales very well. Postgres has
various server-side programming languages including its own which is
syntactically similar to Oracle's PL/SQL, as well as Perl and TCL (however,
the "additional" languages do not allow executing queries). Thus you can
offload a significant portion of the functionality of an application to the
database engine.

Conclusion: MySQL is great for small, quick-and-dirty projects where speed
and flexibility is more important than reliability. Postgres is best for
larger projects where reliability is a must and the higher end functionality
is needed.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"