[PHP3] Mysql vs PostgreSQL WITH Addendum

Subject:   PostgreSQL vs Mysql comparison  WITH ADDENDUM
     Date: Sun, 03 Oct 1999 22:55:46 -0400
     From: Scott Perkins <2scott@bellsouth.net>    Atlanta, GA   770 973-3860
       To: MySQL mailing list <mysql@lists.mysql.com>, postgresql-general
<pgsql-general@postgresql.org>, "php3@lists.php.net" <php3@lists.php.net>


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 (multiprocessor ready)

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




==================================================================================

POSTGRESQL FEATURES:

THIS INFO PASTED  from the PostgreSQL website: 10/99

PostgreSQL is :

(1)Relational, can be extended to handle complex objects, rules. Includes
features such as declarative queries in SQL, query optimization, concurrency
control, transactions, and multi-user support.

(2)Highly Extensible. PostgreSQL allows user-defined operators, types,
functions, and access methods.

(3) Object-relational. Some have used the term "object-relational" to describe
PostgreSQL because it supports some object-oriented features such as
inheritance.

How does any of the above contrast with MYSQL?



===============================================================
===============================================================
ADDENDUM - PASTED 10/3/99  INFO FROM THE WEB SITES  10/99
===============================================================
===============================================================
MYSQL FEATURES

The following list describes some of the important characteristics of MySQL
THIS INFO PASTED  from the MYSQL website  in 10/99 :

    Fully multi-threaded using kernel threads. That means it easily can use
multiple CPUs if available.
    C, C++, Eiffel, Java, Perl, PHP, Python and TCL APIs. See section 20 MySQL
client tools and APIs.
    Works on many different platforms. See section 4.2 Operating systems
supported by MySQL.
    Many column types: signed/unsigned integers 1, 2, 3, 4 and 8 bytes long,
FLOAT, DOUBLE, CHAR, VARCHAR,
    TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET and ENUM types. See
section 7.2
    Column types.
    Very fast joins using an optimized one-sweep multi-join.
    Full operator and function support in the SELECT and WHERE parts of queries.
Example:

    mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name
               WHERE income/dependents > 10000 AND age > 30;

    SQL functions are implemented through a highly-optimized class library and
should be as fast as they can get! Usually
    there shouldn't be any memory allocation at all after query initialization.
    Full support for SQL GROUP BY and ORDER BY clauses. Support for group
functions (COUNT(),
    COUNT(DISTINCT), AVG(), STD(), SUM(), MAX() and MIN()).
    Support for LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
    You can mix tables from different databases in the same query (as of version
3.22).
    A privilege and password system which is very flexible and secure, and which
allows host-based verification.
    Passwords are secure since all password traffic when connecting to a server
is encrypted.
    ODBC (Open-DataBase-Connectivity) for Windows95 (with source). All ODBC 2.5
functions and many others.
    You can, for example, use Access to connect to your MySQL server. See
section 16 MySQL ODBC Support.
    Very fast B-tree disk tables with index compression.
    16 indexes per table are allowed. Each index may consist of 1 to 16 columns
or parts of columns. The maximum index
    length is 256 bytes (this may be changed when compiling MySQL). An index may
use a prefix of a CHAR or
    VARCHAR field.
    Fixed-length and variable-length records.
    In-memory hash tables which are used as temporary tables.
    Handles large databases. We are using MySQL with some databases that contain
50,000,000 records.
    All columns have default values. You can use INSERT to insert a subset of a
table's columns; those columns that are
    not explicitly given values are set to their default values.
    Uses GNU Automake, Autoconf, and libtool for portability.
    Written in C and C++. Tested with a broad range of different compilers.
    A very fast thread-based memory allocation system.
    No memory leaks. Tested with a commercial memory leakage detector (purify).
    Includes isamchk, a very fast utility for table checking, optimization and
repair. See section 13 Maintaining a
    MySQL installation.
    Full support for the ISO-8859-1 Latin1 character set. For example, the
Scandinavian characters @ringaccent{a},
    @"a and @"o are allowed in table and column names.
    All data are saved in ISO-8859-1 Latin1 format. All comparisons for normal
string columns are case insensitive.
    Sorting is done according to the ISO-8859-1 Latin1 character set (the
Swedish way at the moment). It is possible to
    change this in the source by adding new sort order arrays. To see an example
of very advanced sorting, look at the
    Czech sorting code. MySQL supports many different character sets that can be
specified at compile time.
    Aliases on tables and columns as in the SQL92 standard.
    DELETE, INSERT, REPLACE, and UPDATE return how many rows were changed
(affected).
    Function names do not clash with table or column names. For example, ABS is
a valid column name. The only
    restriction is that for a function call, no spaces are allowed between the
function name and the `(' that follows it. See
    section 7.30 Is MySQL picky about reserved words?.
    All MySQL programs can be invoked with the --help or -? options to obtain
online assistance.
    The server can provide error messages to clients in many languages. See
section 9.1 What languages are supported
    by MySQL?.
    Clients connect to the MySQL server using TCP/IP connections or Unix
sockets, or named pipes under NT.
    The MySQL-specific SHOW command can be used to retrieve information about
databases, tables and indexes. The
    EXPLAIN command can be used to determine how the optimizer resolves a query.



Known errors and design deficiencies in MySQL

You cannot build in another directory when using MIT-pthreads. Since this
requires changes to MIT-pthreads, we are     not likely to fix this.
    BLOB values can't ``reliably'' be used in GROUP BY or ORDER BY or DISTINCT.
Only the first
    max_sort_length bytes (default 1024) are used when comparing BLOBbs in these
cases. This can be changed     with the -O max_sort_length option to mysqld. A
workaround for most cases is to use a substring:     SELECT DISTINCT
LEFT(blob,2048) FROM tbl_name.

    Calculation is done with BIGINT or DOUBLE (both are normally 64 bits long).
It depends on the function which  precision one gets. The general rule is that
bit functions are done with BIGINT precision, IF, and ELT() with    BIGINT or
DOUBLE precision and the rest with DOUBLE precision. One should try to avoid
using bigger unsigned    long long values than 63 bits (9223372036854775807) for
anything else than bit fields!

    Before MySQL 3.23 all numeric types where treated as fixed-point fields.
That means you had to specify how many    decimals a floating-point field shall
have. All results was returned with the correct number of decimals.     All
string columns, except BLOB and TEXT columns, automatically have all trailing
spaces removed when retrieved.

For CHAR types this is okay, and may be regarded as a feature according to ANSI
SQL92. The bug is that in MySQL, VARCHAR columns are treated the same way.

    You can only have up to 255 ENUM and SET columns in one table.
    Before MySQL 3.23.2 an UPDATE that updated a key with a WHERE on the same
    key may have failed because the key was used to search for records and the
same     row may have been found multiple times:

    UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;

    A workaround is to use:

    mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;

This will work because MySQL will not use index on expressions in the WHERE
clause.

safe_mysqld re-directs all messages from mysqld to the mysqld log. One problem
with this is that if you execute mysqladmin refresh to close and reopen the log,
stdout and stderr are still redirected to the old log. If you use --log
extensively, you should edit safe_mysqld to log to `'hostname'.err' instead of
`'hostname'.log' so you can easily reclaim the space for the old log by deleting
the old one and executing mysqladmin refresh.

In the UPDATE statement, columns are updated from left to right. If you refer to
a updated column, you will get the updated value instead of the original value.
For example:

    mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1

    will update KEY with 2 instead of with 1.

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