I am still getting many and many question about which database you should choose.
Well I don’t think is really needed do comparison of all the features they have side-by-side as the web is already full of this kind of comparison.
What I think is that most people are comparing MySQL with PostgreSQL but they are indeed very different.
Comparing apple with pear
You can find a lot of comparison between those two DBMS engine… but I think is like compare apples with pears : they are completely different!
The main difference is that PostgreSQL is fully ACID compliant and MySQL no. I already know that now many and many follower will think that it is not true as InnoDB made MySQL fully ACID compliant, and well it is true, but…. you have a great fully ACID compliant Engine that you can’t hot backup…
Some years ago I had an interesting and large Zabbix installation on MySQL.
I still have a screen shot that report the following data:
Number of hosts (monitored) 1662 Number of items (monitored) 82532 Number of triggers (enabled) 14118
as you can see it can’t of course considered a small installation!
Well now all is fine until you don’t face an issue, and it is very important have a hot backup utility in place.
Then I managed to migrate the database on InnoDB (as they want to keep the database on MySQL) and they bought InnoHotBackup for something like 800 USD. As far I know that database still up and running and they managed to do a couple or restore due some issues.
Now without that utility InnoDB can’t be hot backed up!!! In the meaning InnoBase (the vendor of that great utility) has been bought by Oracle and… you need to buy a licence from Oracle to have the same kind of functionality and… I can say that an Oracle One Edition cost more or less like a MySQL license… for this reason now MySQL is no more used on my setup.
Performance performance performance….
Now many people want to know which database is better.
Well all my setup are running on PostgreSQL and I can say that they run without any kind of issue, with a good backup policy and restore tested at scheduled period on a spare server.
PostgreSQL need to be tuned to run faster than MySQL but of course nobody that read my website is here looking for a Next-> Next ->Next ->Finish setup 🙂
If you are interested to see some perf chart there is a very well done presentation at this address.
The conclusion of their test reflect exactly my thoughts and then:
- PostgreSQL and MySQL is almost same performance when used as Zabbix DB ( I would say that PostgreSQL is a bit better as it run with less IO waits)
- MySQL consume less CPU (but the partitioning don’t support foreign keys and is less sophisticated than PostgreSQL)
- PostgreSQL is more stable in Heavy I/O Situation
- PostgreSQL offer a hot backup and point in time restore
- PostgreSQL is fully ACID
Now if you are curious go to check the presentation 🙂
