Re: Which database part 2 - Mailing list pgsql-advocacy

Beware bias. I'm unashamedly a PostgreSQL fan. I'm trying to be fair to
MySQL, but...

Kaarel wrote:
> This is the follow up for my post a few days ago. First I want to thank
> everybody for their great replies. I must admit that I did ask the same
> question in MySQL list and also had many replies. From their perspective
> the only thing that PostgreSQL has and MySQL does not have is more
> features. In fact here's a short summary of the ideas from MySQL list:
>
> -MySQL is simple, powerful, indestructible.

Simple, yes. The other two...I would be very frustrated using MySQL
because of the limited query syntax. I hear it's getting better, but I
don't watch very closely.

And "simple" doesn't necessarily mean it's easier to use. PostgreSQL has
a lot more features, but I do not find that they get in your way if you
don't use them. That's certainly not always true...Oracle's features are
very much a mixed bag. Very, very useful in some cases (almost a
necessity) but there's definitely a lot of overhead to them in that you
notice them even if you aren't using them.

> -PostgreSQL is very highly featured, but not as fast and not as rugged.

The "not as fast" is debatable. The benchmarks the MySQL people talk
about are quite old. I would bet, that with any reasonable amount of
concurrency, PostgreSQL would do considerably better. But I haven't done
the work to back it up.

The "not as rugged" is a big fat lie. In fact, I believe it's the other
way - MySQL fails the "Durability" part of ACID even now; it doesn't
sync to disk before every commit returns, and it should.

> -MySQL ran on NT with no fuss, while you needed cygwin and whatnot to
> run PostgreSQL.

That's true. There are people working on fixing it, though.

> -PostgreSQL seemed to require more administration than MySQL.

I don't buy this. Installing PostgreSQL is easy. Administration is not
hard...it basically boils down to running a script nightly that does a
"vacuum analyze" and an online dump of all your databases for backup
purposes. You certainly can do more, but I wouldn't say you have to, any
more than you do with MySQL.

> -If you need to work with extremely large databases (multi GB) I would
> go with MySQL.  It scales to large files extremely well.

PostgreSQL does also.

> -There seems to be much less support for PostgreSQL than MySQL, be it
> from books or other users.

That's true. MySQL seems to be more widely used.

> -MySQL has better support, larger community and better documentation.

Hmm. More != better. It's debatable.

I find that a lot of the MySQL community follows some very poor
practices. Obvious things like not using foreign keys...when you don't,
you have to worry that every join condition will cause rows to silently
disappear from your query. When you use foreign keys, you are
_guaranteed_ to not have to worry about things like that. And many other
examples where they do stuff in their application that the database
server should be doing. In part this comes from MySQL not having these
features for a long time. But now that it has more of them, they still
are not using them properly.

This certainly is not true of everyone who uses MySQL, but it's enough
so that I look on any project that uses MySQL with a lot more skepticism
than one that uses PostgreSQL. The same for PHP...they've got some good
code out there (PEAR) but most people don't use it. So when I find a
PHP/mySQL project, I tend to assume it's crap. It's not necessarily so,
but it's a pretty good bet.

>
> These are randomly orderered and posted by various MySQL list users. I
> thought it would be nice for part 2 to have PostgreSQL users comment
> these replies.
>
> 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? I found particularly
> intresting the heap table type which is being stored entirely in memory
> not on disk drive.

Because PostgreSQL has one good one. MySQL's initial table types were
just not very good. My impression is that the InnoDB and
BerkeleyDB-backed tables are the ones you'd want to actually use. I
believe the heap one exists because MySQL does not have as good caching
as PostgreSQL.

> Kaarel
> kaarel@future.ee

Scott