Wednesday, April 20, 2011

Dropping MySQL, and adopting PostgreSQL

Finally I had the time to dig a bit this issue, and finally, decided to drop all MySQL support in favor of PostgreSQL.

Despite PostgreSQL is not as known nor as used as MySQL, it has MANY advantages:
  • Referential Integrity: MySQL's default engine (MyIsam) does NOT support any kind of referential integrity, which could lead to corruption and inconsistencies in DB.
  • On the other hand, the other MySQL's engine which does support referential integrity (InnoDB) is very much slow. In my case, only to create a table with a referenced FOREIGN KEY to another table takes ~1 second! And cascade deleting/checking/updating also takes much more time then postgre takes.
  • Extended Data Types: With PostgreSQL you don't have only a few data types like you have in MySQL. With PostgreSQL you have, cidr (for IP addresses), money, and other data types, as well as user defined data types.
  • Prepared Statements: As stated in my previous post, MySQL had support for prepare statements, but you couldn't not give a name to them (unless you use my class, or code it by yourself), but with PostgreSQL, you can even give natively a string name! (even inside client).
  • Performance: In my case, with my databases, and googling a bit, you can see that PostgreSQL has much better overall performance than MySQL.
To sum up, I though that since Oracle bought MySQL, it would be improved a lot, but it seems only a commercial action rather than a really interest in MySQL from Oracle.

No comments: