PostgreSQL v MySQL 5.1

[Aaron Crane]

Paul Makepeace writes:
> MySQL 5.x boast several new features that seem to somewhat quash the
> previous pro-Pg arguments: MySQL now has views, subselects, constraints,
> and even a procedural language. And MySQL has long had a standard
> easy-to-implement replication system, in contrast to Pg's stated "one
> size will never fit all so you don't get any as standard".
> 
> Has anyone looked into the differences in detail, and/or have any
> practical experience? I'm less interested in religious positions

I don't have any significant practical experience with MySQL newer than
version 4.0, or with PostgreSQL newer than 7.4, but I do follow both
MySQL and PostgreSQL quite carefully.  And I'll try to avoid any
religious arguments.

Some specific feature comparisons between MySQL 5.1 and PostgreSQL 8.1:

  - As you point out, PostgreSQL has no replication out of the box.

  - Out of the box, MySQL still defaults to MyISAM, a table type that:

    - Imposes a small arbitrary limit on key length (1000 bytes without
      recompiling)
    - Doesn't support transactions
    - Doesn't check foreign key constraints
    - Corrupts or loses data if the server dies during a write

    OK, I know that it's easy to change a table's type.  But this really
    grates on me, especially given that, at least historically, the
    feature limitations of MyISAM were what allowed MySQL to benchmark
    so well.

  - As far as I can tell, MySQL doesn't allow a stored function to return
    a row, or a result set.  PostgreSQL stored functions can return
    rows, or sets, or sets of rows.

  - MySQL cannot index the whole of a TEXT or BLOB column; the limit is
    1000 bytes for MyISAM, or 767 bytes for InnoDB.

  - MySQL has a variety of limitations in its support for foreign-key
    constraints (in InnoDB):

    - They can't contain TEXT or BLOB columns
    - Triggers aren't activated by cascaded foreign-key actions
    - No deferred checking

  - In MySQL, subqueries in the FROM clause cannot be correlated with
    items in the outer query.

  - MySQL has only a partial implementation of updatable views; lots of
    useful things aren't yet done.

    On the other hand, PostgreSQL doesn't have updatable views at all;
    it makes you fake them with rewrite rules that translate data
    modifications into queries on the underlying tables.  (But at least
    that means you can update your PostgreSQL views now, rather than
    waiting for version N+k.)

I could go on listing differences between MySQL and PostgreSQL for quite
a long time, but I'm not going to.  Each has its own set of SQL
extensions (though with quite a lot of overlap between them).  My
experience is that MySQL has more seemingly-arbitrary limitations and
apparently-gratuitous ANSI incompatibilities than PostgreSQL.  I don't
think the addition of large checkbox features like triggers and views in
newer versions has changed that.

As for speed, I've seen little evidence that either MySQL+InnoDB or
PostgreSQL is significantly faster than the other.  PostgreSQL 8.1 seems
much faster on some complex queries than MySQL 4.0 with MyISAM, but I
haven't done any comparisons against newer MySQL releases.

This may be useful, even though it only covers MySQL 4.1 and PostgreSQL
8.0:

  http://troels.arvin.dk/db/rdbms/

-- 
Aaron Crane