Today I Learned Something: Why people prefer PostgreSQL over MySQL

19 Mar

One of my former colleagues who happens to do heavy administrative work for a big university in Turkey recently sent an e-mail about a systems failure. I copy the e-mail here because it shows an interesting and strong case for why people prefer PostgreSQL over MySQL:

“Hi all,

Today I learned something and I want to share it with the team:

Apparently, no matter what you do, systems always crash. Even if you:

– Use virtualization
– Configure your servers for high availability
– Do very tight security tuning
– Keep your software up to date
– Monitor your systems for failure prediction
– Use decent hardware
– Use a room-sized UPS together with a noisy generator

Your systems can crash anytime, and mother nature will use all of her resources to make sure that it will happen.

System crash always happens. Thats why the recovery procedure itself is at least as important as precautions to prevent a crash.

As some of you might notice, today our beloved Moodle was down for 2 hours. It all started with the failure of the air conditioner in our server room, but it was not the reason. The support staff who went to the server room to fix the air conditioner accidentally plugged out our rack’s main power line. Result: All systems were down. Luckily, they noticed it fast enough and we got power and all our services back in some time like 10 minutes.

Unfortunately, this power outage caused data corruption on our MySQL server. It took about 1.5 hours of work, lots of curse words and some wizardry to get the tables back in healthy state.

Although the data is now back in place, there is a slight possibility that some little piece of information might be lost. If you notice a situation like that, please contact me immediatly so that we can try to save your lost data.

Ah, and the lesson: Whatever you do for availability of your services, make sure that you don’t use software that corrupts your data when something really unexpected happens.

Or if you want to specialize it: DO NOT USE MYSQL FOR ANY MISSON CRITICAL SERVICE.

I learned my lesson and will be migrating our services from MySQL to PostgreSQL in the upcoming weeks.”


Posted by on March 19, 2010 in Programlama, sysadmin


11 responses to “Today I Learned Something: Why people prefer PostgreSQL over MySQL

  1. Can Altıneller

    March 19, 2010 at 15:50

    I have been using postgres for all my projects for the past 2 years. postgres is the closest thing to oracle imho. while the penalty for data security is generally performance, postgres performs better then mysql.

  2. dean

    March 19, 2010 at 17:35

    MySQL and MariaDB , future proof ? Will MySQL skills be relevant in the market place ?

  3. Me

    March 19, 2010 at 20:38

    You don’t say what makes you think Postgres would fare any better? Wondering if you have experience with this sort of situation with pg? I’m curious to learn more.

  4. MattK

    March 19, 2010 at 20:55

    Also curious to know why MySQL is more succeptable to corruption in this example. Is there something in PostgreSQL’s logging that is more fault tolerant?

  5. mschout

    March 19, 2010 at 21:49

    PostgreSQL has Write Ahead Logging (WAL) which, among other things, essentially guarantees that the database is consistent in the case of a crash.

  6. MattK

    March 19, 2010 at 23:32

    WAL is something MySQL lacks? (mildly surprised)

  7. Emre Sevinc

    March 19, 2010 at 23:34

    Hmm, I wonder WAL and MySQL’capability about it, too.

  8. Me

    March 19, 2010 at 23:35

    Were you using InnoDB tables or MyISAM?

  9. Emre Sevinc

    March 19, 2010 at 23:39

    As I said, I’m not the one who had this problem but a colleague of mine. I’ll invite him to the blog and maybe he can share further technical details such as the InnoDB and MyISAM question.

  10. Berk Ulsoy

    March 20, 2010 at 22:52

    If you do not configure a software properly, it will cause disaster eventually. It does not matter if it is a million dollar product, or MySQL or PostgreSQL, or Linux, or Windows or whatever. MySQL does serve mission critical jobs excellently. I am not mentioning sharded installations like Facebook, many huge telecom companies use MySQL for storing data of millions of users and that provide 99.999% uptime per year (which is a requirement in Telecom)

    So, I am more or less sure that this disaster is caused by some misconfiguration.

    By the way, for WAL functionality it is always possible to use binary logging which is there for many years, years before PostgreSQL introduced WAL..

    However with correct parameters, you can make sure data is really written to disk , without relying on binary logs.

  11. Berk Ulsoy

    March 20, 2010 at 23:09

    In fact WAL is not exact match of binary logging , but InnoDB transaction logs should also be considered. The point is, MySQL has functionality of WAL.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: