3

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.
Improve this question
11
  • 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
    Commented Aug 1, 2012 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.)
    – András Váczi
    Commented Aug 1, 2012 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)
    – user1822
    Commented Aug 1, 2012 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
    – user1822
    Commented Aug 1, 2012 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
    Commented Aug 1, 2012 at 18:17

1 Answer 1

Reset to default
1

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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