In previous project we were using DRBD to achieve high avalibility on postgresql 8 database. DRBD offers file system replication – all changes to a file system are mirrored to a file system residing on another server.
After many problems with DRBD split brain we decided to find better replication solution.
As postgresql didn’t had synchronus master-slave replication at this time we decided to go for MySql as we already had experience in MySql replication.
Next step was to migrate from postgresql to MySql. During this I found two minor cons of MySql I wasn’t aware of before:
MySql’s strange unicode support
MySql stores UTF-8, using 3 bytes per character (not 4 as you’d expect). It means that it doesn’t support any Unicode characters which would require 4 bytes in UTF-8.
Someone might ask who will need to store those kind of characters anyway. Here is our example.
We were using hashing mechanism based on generated seed. Seed was string generated from numbers in rage from 0 to integer maximum value. So some of our seeds for passwords had 4 bytes characters in it. No way to store it in Mysql.
No support for miliseconds in MySql
MySQL doesn’t support millisecond / microsecond precision. There is not type to store such values. Only way is to use String to store such timestamp. But this will be less eficient that integers in timestamp and more difficult to map in ORM.
PostgreSQL has an interval data type. It is capable of storing an interval of up to 178,000,000 years. Precision is maximum 14 digits. So we can measure time precisions from year to microsecond precision.
So finally we abandoned MySql and decided to wait for new Postgresql version that came with repolication:
Finally we implemented PostgreSQL’s replication using build in features of 9.1 version with use of readable standby (pgpool)
As postgresql reached his maturity level long time ago and now is even more reach in features that MySql I would recommend it for opensource projects. Another important thing is that it’s free. No special community versions and licencing problems and all the features are always there.