Re: Advantages of PostgreSQL over MySQL 5.0

On Wed, 2006-03-22 at 05:06, 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.

We use both where I work, for different things.  We also use Oracle for
our transactional engine.

I submit bug reports for both PostgreSQL and MySQL.  I've asked for help
for both databases.  The PostgreSQL users and developers are
unbelievably knowledgeable and helpful.  When I first started using
PostgreSQL I found something that seemed odd, in that functional indexes
couldn't accept constant arguments, only column names (this was with 6.5
or 7.0 or something like that) and within a few hours Peter Eisenstraut
(I'm sure I spelled his name wrong there) had posted a work around for
me.  I've found a few bugs here and there.  They've all resulted in
changes being made anywhere from very fast for serious bugs, to being
pipelined into the process for the next major release.

I've never had that kind of very fast response from MySQL.  If feels
like those guys really have their hands full meeting all the
requirements for the next version to pay much attention to the smaller
bugs.  For instance, this DDL in MySQL:

mysql> create table a (x int primary key) engine innodb;
Query OK, 0 rows affected (0.13 sec)

mysql> create table b (y int references a) engine innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into b values (10);
Query OK, 1 row affected (0.07 sec)

Executes without a warning or an error.

Now, I shouldn't be able to insert anything in b that's not referencing
an entry in a.  and I used innodb tables.  and I used ansi SQL, and I
got no errors.  So how come my data's incoherent three seconds after
creating the tables the way the spec says should work?  Simple.  MySQL
only implements foreign keys if you do them this way:

create table b (y int, foreign key (y) references a(x)) engine innodb;

I.e. you have to declare fk constraints outside the column defs.  Why?
Because innodb tables and transactions in general are kind of like
red-headed step-children, I guess.  It's not gonna change.  I files a
bug report on it and the answer was basically "yep, that's how it's
supposed to work".  This flies in the face of MySQL's claims that they
are striving towards SQL specification compliance.

In closing, I found that with PostgreSQL I had to invest a fair bit of
time learning how databases were supposed to work, and the more I used
it, the happier and more rewarded I became.

With MySQL, it's really easy to get started.  It's over time you start
thinking "maybe this thing isn't quite right in the head" with some of
the odd behaviour like the one I listed above.  And it's just one of
many.

that said, for content management, MySQL is a GREAT database.  It has
collation per column, something PostgreSQL only gets about halfway to
right now with collation classes.  OTOH, when that gets added to
PostgreSQL, it will likely be done very well.

As for the load handling, anyone who benchmarks a database with a single
thread (which is what a LOT of people do, sadly) should never be allowed
to declare any database fast.  A database that can do exactly one thing
at once fast is not really all that interesting.  A database that can
stay on its feet with 1000+ users hammering away updating and inserting
and selecting and deleting is impressive as hell.  And PostgreSQL is
quickly moving in that direction.

The whole "built in replication" argument smacks of intellectual
laziness to me.  Read the bug reports on mysql.com about it.  There are
scads of reports of it just stopping for no good reason.  And how do you
bring it back online?  Simple.  shut down your entire application,
disconnect all users, and FILE COPY everything to the slave server.
Restart replication, and then reconnect all your users.  This is
enterprise quality?  I hope they come up with a better way than that.

With Slony, I can add and remove replicated sets on the fly, on a living
database system, and the user never even notices.

I can use Point in time Recovery in Postgresql, a feature MySQL
currently lacks, and one I consider more useful than real time
replication for distaster recovery anyway.  After all, replication can't
save you from "delete from tablename" with no where clause.  Again, I
can set up PITR replication on the fly, without shutting down the master
database for even a second.

Lastly, try creating a test app of some kind that runs a lot of queries
(read and write both) on MySQL and PostgreSQL.  Turn it on, let it
settle and place a heavy load on both machines.  Pull the plug.
Assuming PostgreSQL is running on proper hardware (SCSI drives, RAID
controller with BBU, etc) it will come right back up.

MySQL, if it's running on myisam files, will be trashed.  You may spend
quite  alot of time getting it back up, and it will be missing data, and
if you have related tables, you will most certainly have orphans now.

Innodb tables should survive.  But will they still be in MySQL in a
year?  who knows?

I suggest someone put rubber on the road there and test both, including
possibly destructive tests like the power plug being pulled several
times.  See how they both handle a simulation of what load you think
you'll have.  See how much faster each gets when you move it from a
single CPU machine with 512 Meg ram and a single hard drive to an 8 way
Opteron with 16 gigs ram and a 12 drive RAID 1+0 drive set.

Now is the time to do it, before you've "married" your company to one or
the other, and found it wanting.  Waving your hands around magically
declaring one better than the other gets you no where.