Re: Which database part 2

> > -PostgreSQL is very highly featured, but not as fast and not as
> > rugged.
>
> This is incorrect.  Postgresql is slower in the common MySQL
> configuration, i.e. read-only database with a high simple,
> poorly-written query load.  Postgresql is faster with complex
> queries, transactions, and read-write databases.

I'm ashamed to admit it, but I've been running with MySQL databases
for about 5 years now.  That said, complex queries for MySQL is
defined as two or three table joins, beyond that, MySQL starts to fall
on its face.  Single table queries, it's good at.

> And both databases are good for 99.5% uptime, so I don't know where
> MySQL gets "less rugged" from.  Frankly, this sounds like FUD from
> MySQL AB rather than any kind of real experience.

This I can't attest to, however.  At Cisco, MySQL would regularly fall
flat on its face (Solaris) and would go into a death spiral taking
100% of the CPU, and not allowing any new connections.  In my own
experiences, MySQL regularly (~once a month) does the same thing
(Linux back in the day, now FreeBSD).  MySQL's done this for ages and
ages if you push it.  At ApacheCon I spoke with a few folks _from_
MySQL AB who said that when they talk to customers, they hear reports
of people with this problem, but their developers can never seem to
reproduce this and deny that it's a problem.

PostgreSQL, on the other hand, has been a rock.  The _only_ time I've
had a problem with PostgreSQL is when I had a few errant applications
leaving transactions open causing queries to build up until it reaches
its max connection limit.  I can't emphasize the stableness of
PostgreSQL compared to MySQL: the difference is non-trivial and the
difference is much greater than 0.5% difference in uptime.  Downtime
in PostgreSQL is upgrading between major versions, in MySQL it's
regular, irritating, requires running myisamchk several times with
several options, running myisamchk regularly regardless of crashes and
you still find inconsistencies, and unless you have a monitor pinging
your MySQL database, you're likely going to be hosed until someone
notices and repairs/restarts MySQL.

In terms of simple query speed, the biggest difference between the two
is overhead for creating a new connection.  In PostgreSQL it takes
longer than MySQL (thread vs. process creation time), but once a
connection is established, PostgreSQL is so feature rich, that
depending on what it is that you're trying to do, I bet you can get
PostgreSQL to outpace MySQL.

> > -PostgreSQL seemed to require more administration than MySQL.
>
> True as well, although with some databases the admin is entirely at
> installation time.  On the flip side, PostgreSQL is more
> configurable by a trained DBA than MySQL is.

Ehh...  I may have different experiences here, but once you get a
PostgreSQL database up and running, it's a fire and forget type thing:
I don't generally have to go back and visit the DBs once they're
rolling (I do hourly VACUUM ANALYZEs and nightly backups via cron).

> > -If you need to work with extremely large databases (multi GB) I
> > would go with MySQL.  It scales to large files extremely well.
>
> MySQL may scale well (I haven't tried).  PostgreSQL is proven to
> scale extremely well; as evidence: the .ORG domain; the previous
> version of SourceForge; several databases on the Performance list
> with up to 2TB of data.
>
> And if MySQL is so scalable, why are they adopting SAP-DB?

Um, MySQL has an upper limit of about 30M rows in real life.  I've
taken PostgreSQL to about 1B and had no noticeable problems other than
1B rows takes more diskspace than 30M rows (funny how that works).

> > -There seems to be much less support for PostgreSQL than MySQL, be
> > it from books or other users.
>
> Books true, other users false.  And how many books do you need,
> exactly?  There are about a dozen for PostgreSQL.

This is true and unfortunate, but there isn't as big of a need for
PostgreSQL documentation given the quality of PostgreSQL's
documentation.  It really is quite good, IMO.

> > -MySQL has better support, larger community and better documentation.
>
> The only one of those which is anything but entirely subjective is
> the "larger community".  However, I will point out that MS Access
> has a very large community; that doesn't mean I'd consider using it.
> MySQL has slightly more documentation.  On the other hand, the
> official docs are full of marketing by MySQL AB, so choose your
> preference.

If support == tradeshow presence, then I believe that.  PostgreSQL
isn't developed as a commercial product that's open source, PostgreSQL
is an open source project that has commercial support.  If you use
your database in a product, you have to pay MySQL AB, you don't with
PostgreSQL (something to keep in mind).

> > I have been reading a little documentation and mail-lists from both
> > sides. I noticed one interesting thing about MySQL: there are
> > different table types with different properties. Why doesn't
> > PostgreSQL have differently oriented/optimized table types?

Because each of MySQL's table types has a specific niche, but none of
them do things particularly well (though I liked the innodb table
format on MySQL because of its transaction bits... just don't pull the
plug though).

PostgreSQL has very good speed, and transaction support with a single
table type, why add more?  If you need something that's not WAL
backed, use a temp table and PostgreSQL really flies (in fact, if you
wanted to compare query speeds between MySQL and PostgreSQL, compare
temp table speeds since they're closer to on par then).

> How about a sum up:
> PostgreSQL is the DBA's database.
> MySQL is the web designer's database.

I wouldn't be so kind as to describe MySQL that way.  I'd sum it up as
closer to MySQL users are willing to buy into the hype (MySQL spends a
lot of effort in hyping/promoting their DB, PostgreSQL doesn't) and
don't do their due diligence to investigate other DB's.  I have MySQL
around for two reasons, legacy (I didn't know better once upon a
time), and because of MySQL's SHOW TABLE syntax for examining the DB.
MySQL is the Windows of the free databases (even though MySQL's not
for commercial use in products) and PostgreSQL's closer to
FreeBSD/Linux.

-sc

--
Sean Chittenden