PostgreSQL vs Mysql comparison - Mailing list pgsql-general

fm Scott Perkins  Atlanta, GA

Guys I'd just like to say what a "luxury" problem it is to have the choice
between two mature and proven products.  In this world of Gatesville there is
obviously plenty of market share to go around and I don't view PostgreSQL and
Mysql as competitors but complementors.  Nevertheless, it is competitivism sp?
that is sometimes inspiring this discussion.  Let's recognize this as GOOD.

Having said that and I am still interested in learning even more about both.
I think a fair analogy is something like the world of auto racing.
Locally among linux devotees we have preliminarily concluded that Mysql is kind
of the Dragster King and PostgreSQL is sort of the
Lemans-Slalom-Winding-Roadrace-
King.  They each are winners. . . And you can't say which is better.

It's like "is a saw or a hammer better ?"  . .  FOR WHAT? you say . . Exactly!

No one can argue that there are clear cut environments where one will perform
better and that is most likely a reflection of early design objectives.

Clearly each are specialists, just like the cars.  Fortunately for us as
developers, something I have not seen mentioned because I don't think there is a
difference is that both camps are dynamically and aggressively moving forward
such that a big problem as we have seen is keeping up with the improvements.
Things could be worse - huh?

Lets focus on updating the relative comparison info so when we make the choice
our informed decisions are based accurately.

In this dialog, one of the most concise summaries of the two products
was located at :

http://www.toodarkpark.org/computers/dbs.html

Which admittedly contains a "dated comparison between msql, mysql, and
postgresql."

The format I like.  Let's point out the inaccuracies that may now exist and
correct them.

Surely we can find an impartial location to post the updated document if not in
the archives of these discussions.  At least at that point we can apply our own
individual degrees of significance to the agreed upon differences.

I have pasted the full text below relative to MYSQL AND POSTGRESQL
let's try to use this as a working document and update it.
Thanks, Scott



Low-Cost Unix Database Differences   - Author Unknown

MySQL:
-------
Pros:
* Extremely fast.
* Regular expression support.
* Quite a few datatypes available.
* Support staff very knowledgable and helpful.
* Supports identity/auto-increment columns, similar to sequences.
* C-based API very similar to Msql; aids in porting Msql apps to MySQL. A small
shell script is included that does the basic port ( via sed )
* CHAR/VARCHAR/TEXT columns handled canse insensetively.
* Low-cost, often free, for most users. Commercial support available.
* BLOB support stores the BLOBs in the table.
* Supports user-defined functions in C and derivatives.
* Multithreaded

CONS:

* No support for transactions ( begin transaction, rollback, commit ). One must
explicitly lock a table and unlock it when finished.

* No foreign key support.

* No trigger support.

* No subselects.

* No views.

* No sequence support. No, auto_increment columns are not sequences.

* Auto_increment columns will give the next available number upon insert,
possibly messing up what little referential integrity constraints your
application attempts to enforce.

* Only one automatically-updated column per table; one cannot have a timestamp
and auto_increment column in the same table and have both be updated.

* User-defined functions, even the most basic ones, must be in C and
derivatives.

* No on-line recovery; one must bring down the database server and run the
'isamchk' utility on their datafiles.

* Hacked-up SQL functions such as REPLACE INTO.

* Indexes must be created at table-creation time. To add a new index/key, the
table (and its data) must be backed up and reimported. Work around: create a new
table, insert into new_table select * from old_table, drop old_table, alter
table new_table rename old_table.

* Does not handle dates correctly. One can insert '1999-02-31 01:01:01' into a
datetime column, mysql accepts it.

* Does not handle date manipulation properly; select '1999-08-14 11:32:00' - 7
== '1992'





PostgreSQL:
-----------

Pros:

* Object-relational database.
* Free for all uses. Commercial support available.
* Transactions supported ( begin transaction, rollback, commit )
* Sequences supported.
* Triggers supported.
* Subselects supported.
* Unions supported
* Views supported.
* User-defined functions can be in C and derivatives, PL/TCL, PL/PgSQL, or SQL.
* Regular expressions supported.
* Foreign keys supported via an add-on module called "refint".
* Multiversion concurrency control ( MVCC ).
* Handles dates correctly; one CANNOT insert '1999-02-31 01:01:01' into a
datetime column.
* Handles date manipulation correctly through the use of timespan datatypes:
select DATE('1999-08-14'::date - '7 days'::timespan) == 1999-08-07
* Supports the use of querying on 'inherited' datatypes; one can query on only
the date portion of a datetime column.
* Supports online recovery via VACUUM.
* Supports the creation of indexes after table creation.
* Inheritance support; create table a ()... create table b inherits from a.
* Support for large objects/BLOBs.



Cons:

* Due to foreign key support not being fully 'inside' of PostgreSQL, it does not
support the REFERENCES keyword. One must manually create two triggers ( one on
the parent, one on the child ) to get foreign key support.

* A bit slower for INSERTS/UPDATES when compared to MySQL. Then again,
PostgreSQL has to check constraints and triggers and such prior to doing the
insert/update, which slows it down.

* On large tables, VACUUM can take a while to return.

* SQL syntax gets a little odd for certain columns, as you'll notice above:
 SELECT ... '1999-08-14'::date - '7 days'::timespan. When comparing
columns/values of different data types, one column must be 'translated' into
another column via this ( or similar ) syntax.

* On-line backups done via pg_dump don't seem to properly dump views; views come
out as a table, not as a query on 1+ tables.

* Basic installation does a sync after every insert/update, slowing down the
system in general. While this can be disabled via an argument, new users may not
realize this and blame poor performance on Postgres.

* Large-object ( BLOB ) support is space consuming. One must first pull the
large object out of the database and into the filesystem. This is, imho, really
no better than storing the path to the file in question; you'd skip the
retrieval from the database portion of the query and wouldn't have to unlink()
the exported file when finished.

* Single Threaded

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