Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

PostgreSQL 9.1 vs MySQL 5.6 with InnoDB. I do know that PostgreSQL offers better SQL features.

I am currently using MySQL 5.6 lab release (the one with InnoDb full text search feature)(not in production). I still find some features missing especially WRT GIS features, activation of triggers for on cascade deletion etc.

I have been testing out PostgreSQl 9.1 and am impressed by the support it has for GIS(PostGIS 2.0).

Now, Which of the above is better for scaling? Scaling out as well as Scaling up?

I need to scale for :

  • around 100k users(the users will increase)
  • with about 500 concurrent users(will increase say 1000-10000 concurrent).
  • The DB has about 2 million entries(constantly accessed) right now which will increase.
  • With constant writes/reads to the table with 2 million entries right now.
  • SQL, right now, is not too complex with a join to a max of 3 tables.
  • I am thinking of horizontal as well as vertical scaling.
share|improve this question
2  
MySQL version 5.6 is not yet GA, not production ready. And to what scale do you have to scale? How many concurrent users and what size and complexity of the application/sql/database? –  Frank Heikens Aug 1 '12 at 9:53
3  
I wouldn't expect any scaling problems (in neither direction) with this setup. Choose the one that fits your needs better (GIS, staff expertise etc.) –  dezso Aug 1 '12 at 10:30
1  
PostgreSQL has always been better when it comes to concurrent reads and writes compared to MySQL (see here for an old comparison tweakers.net/reviews/657/6). I don't know if that is still the case with 5.5 and later. I never heard the claim that InnoDB is better at supporting transactions than Postgres (it might be faster when it comes to reading especially with 5.6 but that is still to be seen - especially with the scaling enhancements that come with Postgres 9.2) –  a_horse_with_no_name Aug 1 '12 at 12:25
1  
You might want to read this regarding 9.2: rhaas.blogspot.de/2012/04/did-i-say-32-cores-how-about-64.html –  a_horse_with_no_name Aug 1 '12 at 12:28
3  
Your needs are very light. Pick your database based on the features you need. If GIS and strict standards-compliant SQL are important to you, PostgreSQL is probably a good choice. If you want to have very flexible replication topologies, MySQL is a better choice. –  Aaron Brown Aug 1 '12 at 18:17
show 6 more comments

1 Answer

In scalability, part of the problem is that this is pretty heavily use-case dependent. There is no scaling solution that hits every use case equally. For example, Slony is very helpful for scaling out in some cases, but is a bear in others. For example, Slony lets you replicate only part of your database. On the other hand, Slony lets you replicate only part of your database.... Overall, MySQL seems a little easier to scale out of the box in the workloads it supports but PostgreSQL seems to offer more advanced tools which you can take quite a bit further.

As far as which is more scalable in common use cases, now that Postgres-XC has been released, PostgreSQL is the clear winner here. We have Slony, Bucardo, Streaming Replication, PGPool, etc. on the replication side. We have Postgres-XC on the scale out clustering side. We are extremely scalable now.

However, all these come with complexity costs. These costs appear a bit higher on the Pg side, but the flexibility is there to scale however you need to.

Scaling up right now is also a major PostgreSQL goal. 9.2 makes some important gains in that regard.

share|improve this answer
    
Thanks for the insight. +1. –  ThinkingMonkey Sep 3 '12 at 11:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.