How many of you have gone from MySQL to Postgresql? Was it worth it?

Question

I'm thinking about moving from MySQL to Postgres for Rails development and I just want to hear what other developers that made the move have to say about it.

I'm looking for personal experiences, not a Mysql v Postgres shootout, just the pros and cons that you yourself have arrived at. Stuff that folks might not necessarily think.

Feel free to explain why you moved in the first place as well.

Answer 1

Oh dear, this could end in tears.

Speaking from personal experience only, we moved from MySQL solely because our production system (Heroku) is running PostgreSQL. We had custom-built-for-MySQL queries which were breaking on PostgreSQL. So I guess the morale of the story here is to run on the same DBMS over everything, otherwise you may run into problems.

We also sometimes needs to insert records Über-quick-like. For this, we use PostgreSQL's built-in COPY function, used similarly to this in our app:

query = "COPY users(email) FROM STDIN WITH CSV"
values = users.map! do |user|
  # Be wary of the types of the objects here, they matter.
  # For instance if you set the id to a string it will error.

  %Q{#{user["email"]}}
end.join("\n")

raw_connection.exec(query)
raw_connection.put_copy_data(values)
raw_connection.put_copy_end

This inserts ~500,000 records into the database in just under two minutes. Around about the same time if we add more fields.

Another couple of nice things PostgreSQL has over MySQL:

• Full text searching
• Geographical querying (PostGIS)
• LIKE syntax is like this email ~ 'hotmail|gmail', NOT LIKE is like email !~ 'hotmail|gmail'. The | indicates an or.

In summary: PostgreSQL is like bricks & mortar, where MySQL is Lego. Go with whatever "feels" right to you. This is only my personal opinion.

Answer 2

Haven't made the switch myself, but got bitten a few times by MySQL's lack of transactional schema changes which apparently Postgre supports.

This would solve those nasty problems you get when you move from your dev environment with sqlite to your MySQL server and realise your migrations screwed up and were left half-done! (No I didn't do this on a production server but it did make a mess of our shared testing server!)

Answer 3

I made the switch and frankly couldn't be happier. While Postgres lacks a few things of MySQL (Insert Ignore, Replace, Upsert stuff, and Load Data Infile for me mainly), the features it does have MORE than make up. Its stored procedures are so much more powerful and it's far easier to write complex functions and aggregates in Postgres.

Performance-wise, if you're comparing to InnoDB (which is only fair because of MVCC), then it feels at least as fast, possibly faster - we weren't able to do some real measurements here due to some constraints, but there certainly hasn't been a performance issue. The complex queries with several joins are certainly faster, MUCH faster.

I find you're more likely to get the correct answer to your issue from the Postgres community. Everybody and their grandmother has 50 different ways to do something in MySQL. With Postgres, hit up the mailing list and you're likely to get lots of very very good help.

Any of the syntax and the like differences are a bit trivial.

Overall, Postgres feels a lot more "grown-up" to me. I used MySQL for years and I now go out of my way to avoid it.

Answer 4

We switched to PostgreSQL for several reasons in early 2007 (or was it the year before?). The main reasons were:

• SQL support - PostgreSQL is much better for complex SQL-queries, for example with lots of joins and aggregates
• MySQL's stored procedures didn't feel very mature
• MySQL license changes - dual licensed, open source and commercial, a split that made me wonder about the future. With PG's BSD license you can do whatever you want.
• Faulty behaviour - when MySQL was counting rows, sometimes it just returned an approximated value, not the actual counted rows.
• Constraints behaved a bit odd, inserting truncated/adapted values. See http://use.perl.org/~Smylers/journal/34246
• The administrative interface PgAdminIII felt more stable and mature than the MySQL counterpart
• PostgreSQL is very solid and crash safe in case of an outage

// John

Answer 5

so, for short- if you need high quality, stability and/or confident then posgresql is a way to go?

unfortunately not too many regular users need that. like me. i just want to setup personal blog's backend db.