PostgreSQL v MySQL 5.1
perl at aaroncrane.co.uk
Thu Dec 22 00:37:27 GMT 2005
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
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
- 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
- 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
More information about the london.pm