Re: postgresql vs mysql

On Tue, 2007-02-20 at 15:25, gustavo halperin wrote:
> Hello
>
> I have a friend that ask me why postgresql is better than mysql.
> I personally prefer posgresql, but she need to give in her work 3 or 4
> strong reasons for that. I mean not to much technical reasons. Can you
> give help me please ?

My personal peeves:

It's got a query parser that's dumb as a brick. Basically it seems to
work like this: Got an index? Cool, use it. Complex queries quickly
bog down on large data sets in MySQL. Just read the database forums at
phpbuilder.com or anywhere else that people use mysql a lot and you'll
see request after request to fix up a query performance-wise that
PostgreSQL would run with decent speed. Further, the output of Explain
is damned near useless.

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables. Don't forget that if you've got a really
big table, you need that much space free on the drive to alter the table
for the rewrite that's going to take place.

It swallows column level foreign key contraints and does nothing with
them, no errors nothing, even if you're defining innodb tables. I.e.
this produces not errors:

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

mysql> create table b (a_id int references a(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into a values (1);
Query OK, 1 row affected (0.03 sec)

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

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

That last statement should fail. Or the creation of table b should
throw a warning. Or something.

This is with 5.0.19.

So, innodb tables pay for the sins of the fathers (i.e. myisam tables)
and by extension, so do you.

My main gripe about MySQL is that it teaches you bad habits. It plays
loose and fast with your data, and teaches you to do that too.

If it was a lot faster than PostgreSQL (like it was back in the days of
7.1 or 7.2) it might be worth the effort to overcome its shortcomings,
but it's not.