[PHP3] Re: PostgreSQL vs Mysql comparison - Mailing list pgsql-general

On Sat, 2 Oct 1999, Scott Perkins wrote:

> You make a good point about available admin and other facilities etc.
>
> I am interested in some of the forum functionalities that seem to be "off the
> shelf" with Mysql but I have not done my homework yet with PostgreSQL.  There
> must me some good reasons why PostgreSQL beat out
> Mysql for product of the year  or best database or most popular among
> NIX/NUX users or something recently.

Because the Postgres mailing list was informed of the vote and apparently
had more members that voted than the MySQL list did. To put awards based on web
polls in perspective, "Hank, the angry drunken dwarf" won the "most
beautiful person in show business" award on People Magazines website
earlier this year. In other words, I wouldn't take the voting or the award
for most popular database very seriously given the voting methodology...

> I am just trying to clearly differientiate the significant differences
> and decide which are important relative to specific projects.

Transaction support, transaction support, transaction support...

Don't forget sub-selects, views, row-level locking, stored procedures,
triggers, foreign keys, cascading updates/deletes, and probably a couple
other things that escape me at the moment.

MySQL is great for what it is: a small, exceedingly fast, easy to
administer, stable SQL-server for enviroments where the majority of access
consist of text reads (like a lot of web sites).

It is not as stellar if you are doing things that are best accomplished by real
transactions at the database level rather than being forced to program
around the lack of them (such as complex financial transactions,) nor is
it the best choice in enviroments where database views, stored
procedures/triggers are of significant necessity/benefit, in high
read/write concurrency enviroments, enviroments with a high percentage of
BLOB data(if anyone has seen any SQL implementation that truly handles
this well let me know,) or enviroments requiring realtime database
replication/redundancy, etc.

Postgres is certainly significantly slower than MySQL according to the
MySQL benchmarks(somewhat tellingly IMO the Postgres site doesn't have any
benchmarks of their own other than some feature comparisons with a couple
of different SQL servers that don't include MySQL.)

The use of Postgres doesn't fix the BLOBs or database replication/redundancy
issue. It may address high concurrency read/write issues to some extent
with row-level locking(as opposed to MySQL, which only supports
potentially much more lock contentious table-level locking.) What Postgres
certainly does have is a richer feature set. While this is at least
partially responsible for the apparent considerable slow down in Postgres
vs. MySQL, it can make it much easier and faster for database
designers/programmers to develop databases/applications or to do ports
from databases/applications  that use a fully(or almost fully) compliant
ANSI SQL/92 server(such as Oracle,) especially if they are accustomed to
the feature set that such servers offer.

The MySQL docs have a list of missing functionality at:
http://mysql.he.net/Manual_chapter/manual_Compatibility.html#Missing_functions
and discuss some work-arounds, planned additions, etc. To summarize:

    o Sub-selects will probably be available in 3.24.0.

    o Atomic operations, a component of true transaction support
      where a series of operations are performed as a single operation
      will be supported however without rollback capabilities.

    o Stored procedures are planned, but without trigger support.

    o Foreign Key support is planned

    o No mention of cascading updates/deletes I could find, although this
      could be part of real foreign key support(?) and the corresponding
      referential integrity it hopefully provides.

    o No mention of row-level locking

Sometimes, features you think you need, or are told you need aren't really
needed at all. In the cases where you really do need specific functionality, it
*usually* isn't too difficult to program around the lack of, or only
partial support for that functionality. That said, doing this will almost
certainly result in longer development cycles as you first have to
determine if you really need the feature in the first place, and how
badly. If you can't live without it, you must then find or create the
appropriate work-around, and probably adding code to the application to
make it do what the SQL server normally would. This could potentially
slow the application. This can also be a much more error-prone process. As
an example, it would be much easier, safer, and faster(development wise)
to use the native database functionality of a reasonably-written server to
achieve proper transactions support w/commit, rollback and contingency
actions for failures than it would be to write your own from scratch.

So the long and short of it is this: if you are *really* certain that you
need native support for specific features that MySQL doesn't offer
and/or know it would take considerably longer to develop your
application with appropriate work-arounds, and/or know that
work-arounds would slow the application significantly, then you are
probably better off with Postgres. If, on the other hand, you either don't
need those features/are able to program around them without much
difficult/without slowing the application down greatly, and don't want to take
the significant performance hit associated with the use of Postgres, then
MySQL is the way to go.


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net