Re: Postgres vr.s MySQL- style differences?

On May 28, 2007, at 9:29 AM, Brian Hurt wrote:
> I'm still new to databases (though not to computing in general),
> and I'll admit that I haven't ever really used MySQL. But
> listening in to the various conversations comparing MySQL to
> Postgres, one recurring theme I've seen is that accessing the
> different databases requires 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.
>
> Take, for example, inserting large numbers of records into a
> table. As someone who was raised on Postgres (so to speak), when
> loading large numbers (more than 10 or so) records into a table I
> immediate start reaching for a copy command. And our code base is
> littered with copies- from simple programs that given a CSV file
> and a table name burst-load a table, to complicated libraries that
> use multithreaded queues and switch between copies and
> transactional inserts depending upon how much data needs to be
> copied. But someone used to MySQL would just start firing off
> lone, non-transactional insert statements, and their code base
> wouldn't contain such programs or libraries.

And that someone would have horrid performance in *any* ACID RDBMS.
This is one of the things I hate about MySQL: it teaches people how
to abuse an RDBMS.

Of course, that came around and bit them in the rear when they put
InnoDB in: since it's (mostly) ACID, inserts suddenly started to
suck. So to make their users happy they then added a bunch of knobs
that let you dial-back the ACIDity.

> Or take the multi-table joins. I used to laugh at the idea of 27-
> table joins, until I found one in our code. I was doing a
> complicated query against a view, which was joining together
> several other views, which were joining together several other
> views, which were joining together views where were joining
> together tables. No, I'm not exagerating for comedic effect, the
> views really did go four layers deep. Why? Because Postgres just
> handled it, so there was no real pushback for making views of views
> of views of views. I only noticed because the complicated query
> was somewhat slow (not intolerably slow, just somewhat slow) and
> went investigating why. If we were working with a database that
> didn't handle multi-table joins nearly so well, we probably would
> have redesigned the table structure at some point to limit the
> depth of the views, and the number of tables in the query.

I have a story about the small-time mentality that MySQL and some
other tools generate. Someone posted what he didn't like about Ruby
On Rails on a Joel On Software forum. One of the beefs was that rails
won't pick up RI information from the database, because the author of
rails has this hair-brained idea that the database should be nothing
but a stupid data dump (if that's really what he wanted, why the heck
didn't just use flat files??) Of course, this resulted in a big
flamewar between what were largely professional DBAs (from various
RDBMS backgrounds) and the rails fanboys. One of the threads was
talking about what features you need when you have large numbers of
tables... to which one of the rails fans replied:

"I guess I could see that if you had a *really* complex database,
with say, 30 tables."

> My point here is to try to explain why moving code between MySQL
> and Postgres, in either direction, always seems fraught with
> difficulty. And why the new database always seems to be of less
> value than the old database. If you've already spent the time and
> effort to avoid synchronization problems outside the database, and
> to limit the number of tables you're joining in your queries, then
> not having to do these things has zero value. On the other hand,
> if you haven't already special cased inserts, having to now go back
> and special case inserts is a cost. Likewise, if you've already
> paid the cost to use copy instead of inserts on inserting, not
> having to do so is a minor advantage- but if you have given little
> thought to limiting the number of tables you join (barring
> performance problems), or synchronization issues (beyond an
> occassional begin and commit), you're looking at a large cost on
> switching.

Switching databases always has some kind of cost, because none of
them work the same. The problem with MySQL is that it has the ability
to act *very* different from any other database. Shunning ANSI SQL is
one example, but there's things like silently truncating data, mis-
handling nulls, etc. (google:"mysql gotchas" for a good starting
list). Similarly, moving from DB2, MSSQL, or Sybase to PostgreSQL or
Oracle is easier than the other way around, because PostgreSQL and
Oracle are MVCC, which means you spend *far* less time worrying about
locking issues. With non-MVCC databases, you have to be very
conscious of what the access pattern is for each table and make sure
not to break that.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)