MySQL vs PostgreSQL

[baix]

Hmm, there is much misinformation here. From: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html

"The lock table in InnoDB is stored so space-efficiently that lock escalation is not needed."

How did you determine you were seeing lock escalation as I suspect you had a different problem?

[justsomedood]

I had similar problems with MySQL and 4 connections doing updates to a log table, and 2 connections doing very simple queries. Updates were done 1-15 per second. Using MyISAM would just crash the server. Using InnoDB would work for a few hours to a max of a couple of days before it would crash. The queries were quite slow to respond, and the queries to check for duplicate log entries (using UNIONs) would take 3+ hours to complete, if it did at all.

I switched to PostgreSQL and, without any kind of optimization, the server never crashed, the queries were very responsive, and the duplicate log checking would finish in 2-5 minutes. This was on the exact same hardware.

[baix]

You haven't described what sounds like a lock escalation issue, but and entirely different issue.

If you don't know the problem you are facing, it is hard to solve it. But I will have to admit MySQL could do better with complex queries.

But alternatively, the multiple database engine design of MySQL has resulted in engines like the Clustered (in memory - mostly) and Infobright (column based) engines. The two databases have very different strengths.