35

I'm busy porting some MySQL specific code to Postgresql in order to use it with Heroku. Just wondering if there is any specific reason why Heroku went with Postgresql over MySQL? Performance, architecture, etc?

UPDATE: From a heroku blog post:

At Heroku, we believe PostgreSQL offers the best mix of powerful features, data integrity, speed, standards compliance, and open-source code of any SQL database on the planet.

markquezada
  • 8,444
  • 6
  • 45
  • 52
  • 1
    One of the said powerful features could be PostgreSQL's PostGIS extension, providing a very powerful spatial capability. We're using it as an ancillary DBMS, with MongoDB as our primary key-value store. – wulfgarpro Dec 07 '12 at 04:24

6 Answers6

28

Postgres is better than mysql in many ways. You can read these posts about migration rails app from mysql to postgres. Though mysql is more popular than postgres, but instagram is using postgres maybe due to these reasons. I feel postgres is much more mature and robust than mysql. Whereas mysql is more easy and simple to use.

Migrating MySQL to PostgreSQL in Rails « m i n d l e v

Converting Rails application data from MySQL to PostgreSQL

For data migration, a handy little script: Rake task to transfer a Rails database, say from MySQL to Postgres and back again

zengr
  • 38,346
  • 37
  • 130
  • 192
  • 1
    Thanks for the links. I don't think it's even remotely true to say that most startups choose postgres over mysql though. I work at a startup that uses MySQL (and cassandra and mnesia). A number of big players (like Yahoo! and Amazon) also use MySQL. – markquezada Dec 09 '10 at 03:03
  • Yeah that's not true. Facebook and Twitter uses MySQL even in the early days. – Joshua Partogi Dec 09 '10 at 03:07
  • my bad. After a little research mysql is more popular. I have worked for 2 startups in the valley and both of them used postgres. So, had my biased there. Updating the answer. – zengr Dec 09 '10 at 03:20
  • 6
    Yeah, I suspect that many startups use MySQL without thinking about it much. And then regret it. The place I work uses it, the last place I worked used it. – El Yobo Dec 09 '10 at 04:05
  • 1
    Awarded you the answer because of the great links you posted. Thanks. – markquezada Dec 14 '10 at 04:40
  • The first link is broken, btw – Carlos Martinez Nov 17 '19 at 20:04
12

Compare the length of this list of MySQL gotchas to this list of postgresql gotchas. MySQL is much more likely to mess you up.

El Yobo
  • 14,823
  • 5
  • 60
  • 78
7

Generally speaking I find pgsql lends itself to 24/7 operations better than mysql. Also, it seems to have fewer footguns build into it. I think if you're more familiar with one or the other, that's more likely to influence your decision than anything else.

Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21
  • 5
    +1 for use of the term "footgun". MySQL is much better if you turn on all strictness options to make it pretend it's a real database, rather than silently incrementing timestamps or truncating data that's longer than the specified varchar length, etc. Postgres just works properly out of the box. – El Yobo Dec 09 '10 at 03:44
5

Well, performance on Postgres is generally better for complex querys, like the kind generated by ORMs. Also, postgres just tends to be more "solid". This is ancedotal but the postgres servers I've managed have always been much less troublesome than mysql, which likes to randomly crash once in a while, occasionally corrupting a table on the way down.

Tyler Eaves
  • 12,879
  • 1
  • 32
  • 39
  • I've heard (anecdotally) that postgres is more solid as well. I wonder what else they based their decision on. – markquezada Dec 09 '10 at 02:51
  • 3
    Yea, that's exactly how I'd put it: solid. This whole system, docs included, just reeks of solid engineering, whereas MySQL always was more "eh, that's good enough for the web". – Tyler Eaves Dec 09 '10 at 02:54
1

I'm moving one of my client's Rails apps from Heroku Postgresql to Mysql (the ClearDB addon) at the moment. PostgreSQL is a stricter and more "correct" database service in many ways, which appears to be why Heroku endorses it. However, I find that the Rails framework more or less smoothes over the differences, and the majority of Rails apps won't grow big enough for Mysql's performance issues to become a dealbreaker.

On the other hand, PostgreSQL (in particular Heroku's offering) has a couple sharp disadvantages for a startup app:

  • The price. To prevent weekly "Database connection lost" errors when working with Heroku Postgres, you have to shell out $200 / month. On ClearDB, you get a promise of 100% uptime (we haven't had chance to evaluate this claim yet) for the $10 / month plan, which also offers 1 GB of space -- more than we'll ever need.
  • Column ordering. When doing Rails development, I refer to the schema.rb database schema file every 5 minutes, and to keep this file tidy and readable I find it crucial to be able to specify what order columns should be added in. MySQL supports specifying the order of columns; PostgreSQL doesn't. In many cases, this feature is unimportant, but because of how Rails / ActiveRecord works, my life becomes much harder when I can't reorder columns.
Topher Hunt
  • 4,404
  • 2
  • 27
  • 51
0

Not contradicting any of the above advice, but if for some reason you really want or need to stick with MySQL, I recently noticed that Heroku is now offering a free MySQL add-on, called ClearDB:

https://addons.heroku.com/cleardb

Haven't used it, so I can't vouch for it. Heroku claims 99.95% uptime for the free version (versus 100% uptime claimed for any of the paid versions,) and they disclaim the free version as being "Production Ready." They claim ClearDB is "native, unmodified MySQL."

Adrien Lamothe
  • 1,477
  • 1
  • 11
  • 14