Picking Up PostgreSQL

I’ve been a MySQL user for a long time. But since many years ago, I’ve been thinking about switching to adopt PostgreSQL, partly because how it looked like MySQL was headed toward commercialization (and indeed as we all know now they are owned by Oracle). What attracted me to PostgreSQL was the relative purity and completeness of its SQL implementation, and lesser sense of commercialization in its project.

PostgreSQL has generally been reputed to have had a lot more focus on robustness and data integrity. This is important. In the early ages of MySQL, there was no ACID support, which was a major bummer to me.

However, in those early ages, PostgreSQL was slow, and MySQL was blazingly fast. If you’re developing web application, speed was important, and the lack of ACID support could be compensated through application algorithms. MySQL caught up with some of PostgreSQL strengths, but PostgreSQL was slow in catching up with those of MySQL.

A month or so ago, I re-examined PostgreSQL again. I was looking at implementing Request Tracker 4 (RT4) with fulltext indexing. It was supposed to support MySQL, but apparently because of a bug in RT4’s code, I couldn’t get it setup. So, as I observed in the mailing list about people moving to PostgreSQL (for reasons possibly not related to this), I decided to try out PostgreSQL again.

Not too long before, I was also evaluating MySQL Cluster. For me, this is the next big thing to do. We can build highly robust high-availability infrastructure for network, load balancers, servers, and web applications, but it all comes to nought when you realize the database was a single-point-of-failure. MySQL Cluster would be the answer to this problem. MySQL Cluster isn’t all that new. But in its earlier incarnations, it had a couple of difficult constraints that all its data needed to fit in RAM. If you had a 16GB-sized database, you needed a server to contain all 16GB of it, or 2 servers of 8GB if data was partitioned into two groups. Although nowadays servers with 16GB of RAM are commonplace, it was still a capacity limitation that I didn’t like. MySQL Cluster evolved, and now only its indices need to fit in RAM.

Then, RT4 came along. My objective to setup fulltext search incides meant I had little choice but to turn to PostgreSQL. It’s quite a bit of a learning curve. The way authentication and authorization is done is different from MySQL. But it’s not something that will take you more than a few hours to figure out and have everything setup nicely.

PostgreSQL, unfortunately, still doesn’t have decent out-of-the-box solutions for high-availability clustering. Version 9.1, currently in beta, will have support for synchronous replication, but it still isn’t quite the same thing as the high-availability solution offered by MySQL Cluster.