Michael Tremer

Infrastructure #3: MySQL -> PostgreSQL
by Michael Tremer, January 2, 2014

Another big change we did in the last month was to migrate from MySQL to PostgreSQL as the database backend of most of the IPFire (web) infrastructure. Why did we do this?

Features: MySQL is a very simple database system. It comes with the most basic stuff you need, but when you need more fancy features, you are screwed. A very easy example in the past was the lack of fulltext indexes for the InnoDB engine. The database is also not able to handle complex data structures out of the box (like storing geo coordinates and being able to work with them; or storing IP addresses).

Performance: MySQL is fast when the database is small and easy. It comes with a query cache and other things that make accessing the data in the database fast. The replication is a complete nightmare. It is always slow and tends to break. As we are relying on having the same data at mutliple sites at the same time (or at least as quickly as possible), this was starting to become painful.

Licensing: Last but not least, the MySQL project is becoming a closed source project. They have not reached that point yet, but Oracle, who bought it from Sun and who is not that well known for running real Open Source projects, is pursuing a path I personally don’t agree with. Luckily, there is a fork called MariaDB.

All these points from above made me look out for alternatives. Finally, we went with PostgreSQL – a database system that is not only fast and stable, but just as well supported as MySQL is. I think, that PostgreSQL is the way better database system out there in comparison to MySQL and the reason why MySQL is still the de-facto standard is that it comes with almost every shared hosting plan of any hosting provider.
At least in our environment, it solves all the issues from above and we are happy that we made the step.

So, besides our LDAP server, the PostgreSQL server is becoming the most important place where we store all our data. Some applications like the web application that is running our website, this planet, Bugzilla and many things more have already moved; others need some work and will follow soon.

Posted: January 2, 2014 • 729 views