8

From your experience, which database serve is more difficult to scale? MySQL or Postgres? I know MySQL has several out-of-the-box scaling/clustering features, but with Postgres there is none that comes out-of-the-box. CMIIW.

EDIT:

Sorry for the confusion here, my question is referring to scaling out (horizontal scaling), i.e clustering, sharding, etc. I know both can scale horizontally, but which one is easier to implement?

Thank you for sharing.

Joshua Partogi
  • 455
  • 6
  • 17

3 Answers3

10

While this question appears to go against the "Avoid asking subjective or argumentative questions" tenet of the site I can't resist trying an answer.

It depends.

Are talking about a single server configuration that scales to very large datasets?
Both can work in this situation depending on the dataset, but neither will probably perform very well without custom configuration and proper planning. In my experience when working on large datasets with lots of writes I've found Postgres has fewer conditions that cause blocking and overall performance was better.

Are you talking about multi server configurations that scale to many slaves for many readers?
MySQL has historically been considered the leader in this space since it came with asynchronous replication built in. This is no longer the case if you're not opposed to using the newest DB software; Postgres now also has this built in with the release of 9.0. My experiences with MySQL's replication have been more than adequate to this point.

Are you talking about multi server configurations that scale to many masters for many writers?
This is by far the most difficult way to scale either product and many times can be avoided through the use of failover servers. If you really do need to scale out for high availability of master servers addons/alternate installs cannot be avoided. For MySQL there is MySQL Cluster NDB which has an open source option or a commercial version. For Postgres there are many addons that can get you varying levels of HA and pooling

In the long run the scaling of your database typically comes down to design planning. If your application is designed with scale in mind then the db system that matches best to your developers is often the best choice.

Ed.
  • 426
  • 2
  • 9
3

PostGre has more features and configuration options.

Thus it's likely it's more difficult to scale, but it's also likely that it will scale better, that is, if properly configured.

Generally, MySQL will probably still be faster, on the other hand, the real question is how much do you value data integrity, and what features do you need, and do you want to depend on Oracle (now owns MySQL) ?

Also, there are licensing questions, like do you really want to buy a MySQL license just because you don't want to publish your application under the GPL ?
With PostGreSQL, this is no problem, which is a very big plus.

Quandary
  • 1,024
  • 4
  • 19
  • 36
2

MySQL is A LOT easer to scale out, clustering and replication are both native and really easy to manage and implement (even for someone that doesn't really have databases knowledge).

PostgreSQL only supports basic replication for now, and nothing official about clustering (technically you can via 3rd party tools, but it becomes a hell to manage).

I would never choose Postgresql if I plan to scale out.

Edit: very old answer take with a grain of salt as postgres and mysql have changed a lot since this was answered.

CommonSenseCode
  • 103
  • 4
Kedare
  • 1,786
  • 4
  • 20
  • 37
  • So Postgres is only good for monolithic database? – Joshua Partogi Nov 30 '10 at 01:18
  • They are both good at single server database, at you to see what specific features of MySQL or PostgreSQL you need – Kedare Dec 02 '10 at 15:28
  • 2
    MySQL is certainly a lot easier, but the problem with MySQL is that it doesn't check data integrity. Besides, PostGreSQL fully supports clustering and replication. See: http://wiki.postgresql.org/wiki/Clustering and http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling – Quandary Apr 09 '11 at 21:07
  • 7
    There are certainly plenty of solutions for scaling PostgreSQL "out", and many of the largest sites and systems out there use just this. MySQL is going to be easier to scale out *if you already know mysql*, and postgresql is going to be easier to scale out *if you already know postgresql*. If you know neither, they are probably fairly equal as in how hard it is, and you should look at the other properties of the databases to choose one. – Magnus Hagander Oct 24 '11 at 10:35
  • I wonder if this answer is still relevant it was answered almost 8 years ago – CommonSenseCode Feb 23 '18 at 08:47
  • I will update my answer when I have time :) I made an article a few months ago about this if you want to take a look : https://blog.kedare.net/blog/choosing-mysql-or-postgresql.html For now what I can say is that PostgreSQL improved its scaling out capability and it's going to the right direction (Let's see PG11 or PG12 for huge changes), but there is still nothing as good as for example Galera cluster or just what MySQL gives with (re-)failoverable systems based on GTID – Kedare Feb 23 '18 at 09:56
  • Also I recommend you watching this : https://mirrors.dotsrc.org/fosdem/2018/H.1302%20(Depage)/postgresql_replication_in_2018.mp4 – Kedare Feb 23 '18 at 11:17