On Mon, May 28, 2007 at 10:29:59AM -0400, Brian Hurt wrote:
> different styles. And that many of the problems in switching from one
> database to the other is mainly a matter of problems with the style.
> Especially when the style is encoded in software.
Right. This is not only true of PostgreSQL-MySQL comparisons,
though. Even with databases that are rather more compliant with the
ANSI specifications than MySQL, there are plenty of details that you
have to pay attention to. (MySQL is a special case, really, because
in its early history it actively disparaged many of the features that
non-toy databases took to be obvious goods, like referential
integrity and ACID.)
I would argue that IBM tries pretty hard to conform with the ANSI SQL
spec in DB2, for instance. Yet moving from Postgres DB2 (or
conversely) would not be trivial, if you wanted to preserve your
optimizations. DB2's approach to concurrency causes what is called
lock escalation. In most cases, it works well, and the DB2 people
are _really smart_ and have implemented impressive algorithms to
handle this. But if you're used to MVCC, the chances are pretty good
under DB2 that you'll eventually run into lock starvation, and find
that you have strange occasions of suddenly near-stopped performance
unless you go through your application and port it very carefully.
This is why many applications that have some portability layer for
the database seem to be so awful at using the database: they can't
use any of the strengths of any of the underlying databases, because
those strengths are usually the thing that some other database is
really bad at. So you often end up with an application that isn't
truly stellar at anything, and your database system has to be
oversized to perform acceptably. (Note that there's nothing
intrinisically wrong with this approach; you've just optimised your
code for portability at the expense of performance.)
SQL really gives a minimal, rather than maximal portability. It's
nice that PostgreSQL tends to conform with SQL, though, because at
least if you did have to move in or (less likely :) out of it, your
reference point is fairly well-defined.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell