Re: Advantages of PostgreSQL over MySQL 5.0

jd@commandprompt.com ("Joshua D. Drake") writes:
> Jimbo1 wrote:
>> Hello there,
>> I'm a freelance Oracle Developer by trade (can almost hear the boos
>> now
>> ;o)), and am looking into developing my own Snowboarding-related
>> website over the next few years. Anyway, I'm making some decisions now
>> about the site architecture, and the database I'm going to need is
>> obviously included. If my site works out, I'm expecting reasonably
>> heavy traffic, so want a database that I'm confident can cope with it.
>> It is out of the question for me to use Oracle, although I am a
>> (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper
>> route, and to that end I'm looking at either MySQL or PostgreSQL.
>
> If Oracle is out of the question, so is MySQL. The technology that
> makes MySQL even reasonably close to production OLTP quality is
> owned by Oracle ;) (Innodb and BDB).

Combine that with the consideration that there is a real paucity of
performance results involving the "production OLTP quality"
subsystems.

The traditional claims concerning MySQL being "way faster" relate to
its use with the "definitely not production OLTP quality" MyISAM
engine.

And it's pretty needful to be even a bit more specific than that...

MySQL will be way faster than anything else if you have applications
designed to specifically harness its strengths, namely...

 - Single user doing a stream of MyISAM updates

   (If there are multiple connections, they quickly "butt heads"
    on MyISAM table locks, so that >1 user suffers *badly*)

 - Performing heavy loads involving often creating fresh connections
   for each query, and submitting small, fairly trivial, select-one-row
   query requests

If any of the assumptions there change, such as:
 - Having multiple concurrent updating processes, or
 - Submitting complex queries,
the "advantage" can pretty quickly evaporate.

PostgreSQL is likely to be way slower if you submit streams of little
queries, each an independent transaction...

- If multiple queries group into a single transaction, some "slowness"
  will go away;

- The more complex the queries, the likelier that the sophisticated
  query planner and optimizer in PostgreSQL will win out;

- The larger the number of concurrent update processes, the greater
  the likelihood that MVCC allows PostgreSQL to chug along at speed
  whilst the MyISAM table locks drag things to a halt...
--
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/lisp.html
Rules of the Evil Overlord #69.  "All midwives will be banned from the
realm.    All   babies   will    be   delivered    at   state-approved
hospitals. Orphans  will be placed  in foster-homes, not  abandoned in
the   woods    to   be   raised    by   creatures   of    the   wild."
<http://www.eviloverlord.com/>