9 ms·

What are the scaling differences between MySQL and PostgreSQL? That's the main reason we haven't shifted and we have a new project coming up that I've been inte

by marquis 14y ago

What are the scaling differences between MySQL and PostgreSQL? That's the main reason we haven't shifted and we have a new project coming up that I've been interested to use PostgreSQL with as one our developers prefers it, but are we opening a whole new can of worms on that front?

chimi 14y ago
PostgreSQL is massively more scalable than MySQL.
- spudlyo 14y ago
  Definitely. When your application needs hundreds of read slaves in order to scale the load, PostgreSQL has always been everyone's first choice due to it's mature and historically awesome replication system. That's why companies who need to scale big (YouTube, Facebook, Yahoo, LinkedIn, Wikipedia, Twitter etc) all have hundreds (if not thousands) of PostgreSQL machines in their infrastructure.
  - joshhart 14y ago
    Im going to assume this is sarcasm since Facebook is built entirely on sharded MySQL. Don't want anyone else to get confused in case it doesn't come through for them.
    - chimi 14y ago
      Facebook, et al, use memcache and a plethora of other products to improve the scalability of MySQL. Here's one scalability comparison between the two: http://tweakers.net/reviews/649/7 http://tweakers.net/reviews/649/7
      - AaronBBrown 14y ago
        That is a comparison from 2006 using MySQL 5.0.20 (ancient!) and as such is completely irrelevant to any discussion on performance in 2012. Dramatic scalability improvements went into late 5.1.x releases, and then even more in 5.5.
  - deleted 14y ago
    [deleted]
    - jcdavis 14y ago
      They all are, GP has chosen to be unfortunately sarcastic.
  - tensor 14y ago
    The fact that everybody uses a particular technology does not in fact answer the question of which scales better or which cannot scale. I don't know about others here, but I would welcome actual objective information on this topic rather than sarcasm and an appeal to popularity.
    - spudlyo 14y ago
      The fact that everybody uses a particular technology does not in fact answer the question of which scales better or which cannot scale. The fact that many large companies and organizations have successfully scaled a particular technology is a useful yardstick, and while it does little to answer the question of which scales better, it does, by the process of elimination, help answer the question of which cannot scale. I don't know about others here, but I would welcome actual objective information on this topic rather than sarcasm and an appeal to popularity. I had hoped that others here would enjoy the sarcasm in the context of the full-throated yet fact-free advocacy of the parent, the text of which somehow still remains legible on an off-white background.
  - twerquie 14y ago
    The technology stacks for these companies, by and large, were selected a decade ago or more. I'm no more surprised they chose MySQL as their database as I am to see that they chose PHP or Java as their core language (in some cases).
- AaronBBrown 14y ago
  Objective citation, please?
- einhverfr 14y ago
  Obviously it depends on what you are doing. However, if you are trying to keep everything in one consistent environment it does win out. Also I expect that with 9.2 we will probably see a release of Postgres-XC which will kick the pants off any MySQL sharding solution out there. Postgres-XC is a way to abstract database shards such that referential integrity etc. is enforced between shards. The best comparison to date would be to Teradata's clustering system. Better yet, Postgres-XC is just a set of patches on PostgreSQL, and is on-the-wire compatible, so you can swap it out when your database gets too big. This will also bring an ability to concurrently query different shards and aggregate the results without the application having to know anything about the sharding....
kstrauser 14y ago
For vertical scaling, PostgreSQL is generally seen to be much faster. That varies by workload, of course, and I'm sure you could tailor a benchmark that shows MySQL to be dramatically faster than PostgreSQL. But in every benchmark or sample set I've come across, PostgreSQL tends to scale linearly with the number of processor cores and handles ridiculously unlikely concurrency demands with ease. MySQL has a reputation, deserved or not, for being fast for single-connection, read-only workloads but not so quick when writes or multiple users are added in. Under load, PostgreSQL seems to be faster than the quick-but-dangerous MyISAM MySQL table type, and almost always faster than the slow-but-safer InnoDB table type. I can't speak to horizontal scaling, as my little database with maybe 50 million rows never comes close to loading out its aging 4-core server, even when being hammered by lots of web visitors running complicated reports and internal clients performing all sorts of CRUD operations.
- einhverfr 14y ago
  I have one customer who uses MySQL because they wrote their app around the query cache. The data is aggregated and then fed into PostgreSQL for real processing, and eventually reports generated and shipped back to MySQL....
MBCook 14y ago
I've never used PostgreSQL at my jobs outside of initial "could we switch" testing. I know of a couple of benefits that MySQL has/had over PostgreSQL. 1. The commands are very user friendly. In MySQL you can issue commands like "show tables" and "show databases". The last time I used PostgreSQL, the commands were much more esoteric. Things like "\dt". It adds a good hill to the learning curve 2. MySQL is everywhere. Basically anywhere you go on the 'net you can find people who know MySQL, quirks and all. It's very easy to get help, and it's the DB that most tutorials (for other things, like Rails, PHP, etc) use. MySQL is also the standard database you can find on any web host. 3. Replication was a big deal. MySQL has had replication built in for many years, and it's quite easy to use. At my last job we would have been quite happy to go to PostgreSQL for the performance, but we needed replication. At the time, there were solutions for PostgreSQL in outside projects such as Slony. Since we didn't have any PostgreSQL experience we didn't want to expend all the resources on testing and configuring everything. In the last two years or so PostgreSQL has gained an official in-tree replication solution. This makes understanding how to get replication up and running much easier. 4. MyISAM is small and fast. When machines were much slower, that could be a real benefit. Of course you sacrificed all sorts of consistency to get that. At this point, I'd say PostgreSQL's biggest problem is mindshare. MySQL has been improving the whole time, and some of the worst warts are gone. It may not be the best tool for the job, but MySQL is just so common. It's trivial to find help, advice, employees familiar with it, programs that support it, etc. I'd really like to use PostgreSQL in production so I could get more experience with it. It has some amazing capabilities. But our needs are relatively simple and we have lots of MySQL experience so there is no reason for us to look at switching right now.
- einhverfr 14y ago
  As for "show tables", there's a big difference here. "show tables;" is a server-side command. \dt is a command you give psql to say "ask the server what tables there are and list them." \d stands for describe. If you use a graphical client, it will have to query the system catalogs itself which you can do if you want. As for replication, the real challenge is that replication is not a one-size-fits-all thing. Slony, Bucardo, and the built-in streaming replication have different limitations. For example you can't replication from 9.0 to 9.1 with streaming replication but you can with Slony and I would expect you could with Bucardo too. So with the out of tree replication systems you can actually have a zero-downtime upgrade as long as you block writes during the upgrade of the master. With streaming replication, assuming pg_upgrade is supported for your upgrade, you only have a short downtime window, but it is downtime. Also streaming replication is all or nothing. With Slony you can replicate different pieces of your database. On the other hand, with Slony, you can (accidentally) replicate only a piece of your database. So the differences give you a lot of flexibility there, but you need to have a clear idea of what you need and why you need it before choosing a replication solution. Slony is perfect for what Affilias uses it for, while streaming replication wouldn't meat their needs. Also I don't think that PostgreSQL suffers from a lack of mindshare. Ever since I have been building apps on open source db's, PostgreSQL has been regarded as the go-to database for complex business apps. The sorts of things people do with the database are different. There is consequently a huge "dark community" if you will (in the sense that you don't see them). This was driven home to me when I went to the Malaysian Government Open Source Software convention last year. There were two booths (Oracle and one other) which had the MySQL logo, and a few more that were offering MySQL services, while I counted at least 5 that were using the PostgreSQl logo. Most of those were reselling EnterpriseDB's PostgreSQLPlus but it when I looked at the level of interest and the number of government deployments I was asked about, it was clear which people were usually choosing for complex work. As for user-friendliness, MySQL used to be much more user-friendly than PostgreSQL but I don't think that's true anymore. The command-line tools are more full-featured and the in-app help is better on PostgreSQL. \? brings up a list of psql client-side commands and what they do for example, and \h [command] will give you a summary of the syntax of an SQL command. I use \h quite a bit when I am trying to remember the specific syntax of a new feature, or when I am doing something I rarely do (like ALTER TABLE). I think that since about PostgreSQL 7.3, PostgreSQL has been at least as easy to use as MySQL, and quite frankly far more robust. Finally, the PostgreSQL planner is awesome. I have not had to rewrite a query to get around planner limitations since 8.1.
jeffdavis 14y ago
"What are the scaling differences between MySQL and PostgreSQL?" The best advice I have is that using a different database system, particularly ones as different as MySQL and PostgreSQL (which are essentially opposites) is like going to a foreign country. You'll find things you like and things that you don't like, and it will have a real impact on your quality of life, but it's hard to say in a few words the things that will really matter to you. You don't have time to live in every country in the world, but you owe it to yourself to at least visit a few that come highly recommended. Whatever you do, don't try to make the new country into your old country. You will just be disappointed. The same goes for database systems.

by marquis 14y ago

chimi 14y ago

PostgreSQL is massively more scalable than MySQL.

spudlyo 14y ago
Definitely. When your application needs hundreds of read slaves in order to scale the load, PostgreSQL has always been everyone's first choice due to it's mature and historically awesome replication system. That's why companies who need to scale big (YouTube, Facebook, Yahoo, LinkedIn, Wikipedia, Twitter etc) all have hundreds (if not thousands) of PostgreSQL machines in their infrastructure.
- joshhart 14y ago
  Im going to assume this is sarcasm since Facebook is built entirely on sharded MySQL. Don't want anyone else to get confused in case it doesn't come through for them.
  - chimi 14y ago
    Facebook, et al, use memcache and a plethora of other products to improve the scalability of MySQL. Here's one scalability comparison between the two: http://tweakers.net/reviews/649/7 http://tweakers.net/reviews/649/7
    - AaronBBrown 14y ago
      That is a comparison from 2006 using MySQL 5.0.20 (ancient!) and as such is completely irrelevant to any discussion on performance in 2012. Dramatic scalability improvements went into late 5.1.x releases, and then even more in 5.5.
- deleted 14y ago
  [deleted]
  - jcdavis 14y ago
    They all are, GP has chosen to be unfortunately sarcastic.
- tensor 14y ago
  The fact that everybody uses a particular technology does not in fact answer the question of which scales better or which cannot scale. I don't know about others here, but I would welcome actual objective information on this topic rather than sarcasm and an appeal to popularity.
  - spudlyo 14y ago
    The fact that everybody uses a particular technology does not in fact answer the question of which scales better or which cannot scale. The fact that many large companies and organizations have successfully scaled a particular technology is a useful yardstick, and while it does little to answer the question of which scales better, it does, by the process of elimination, help answer the question of which cannot scale. I don't know about others here, but I would welcome actual objective information on this topic rather than sarcasm and an appeal to popularity. I had hoped that others here would enjoy the sarcasm in the context of the full-throated yet fact-free advocacy of the parent, the text of which somehow still remains legible on an off-white background.
- twerquie 14y ago
  The technology stacks for these companies, by and large, were selected a decade ago or more. I'm no more surprised they chose MySQL as their database as I am to see that they chose PHP or Java as their core language (in some cases).
AaronBBrown 14y ago
Objective citation, please?
einhverfr 14y ago
Obviously it depends on what you are doing. However, if you are trying to keep everything in one consistent environment it does win out. Also I expect that with 9.2 we will probably see a release of Postgres-XC which will kick the pants off any MySQL sharding solution out there. Postgres-XC is a way to abstract database shards such that referential integrity etc. is enforced between shards. The best comparison to date would be to Teradata's clustering system. Better yet, Postgres-XC is just a set of patches on PostgreSQL, and is on-the-wire compatible, so you can swap it out when your database gets too big. This will also bring an ability to concurrently query different shards and aggregate the results without the application having to know anything about the sharding....

kstrauser 14y ago

For vertical scaling, PostgreSQL is generally seen to be much faster. That varies by workload, of course, and I'm sure you could tailor a benchmark that shows MySQL to be dramatically faster than PostgreSQL. But in every benchmark or sample set I've come across, PostgreSQL tends to scale linearly with the number of processor cores and handles ridiculously unlikely concurrency demands with ease. MySQL has a reputation, deserved or not, for being fast for single-connection, read-only workloads but not so quick when writes or multiple users are added in. Under load, PostgreSQL seems to be faster than the quick-but-dangerous MyISAM MySQL table type, and almost always faster than the slow-but-safer InnoDB table type. I can't speak to horizontal scaling, as my little database with maybe 50 million rows never comes close to loading out its aging 4-core server, even when being hammered by lots of web visitors running complicated reports and internal clients performing all sorts of CRUD operations.

einhverfr 14y ago
I have one customer who uses MySQL because they wrote their app around the query cache. The data is aggregated and then fed into PostgreSQL for real processing, and eventually reports generated and shipped back to MySQL....

MBCook 14y ago

I've never used PostgreSQL at my jobs outside of initial "could we switch" testing. I know of a couple of benefits that MySQL has/had over PostgreSQL. 1. The commands are very user friendly. In MySQL you can issue commands like "show tables" and "show databases". The last time I used PostgreSQL, the commands were much more esoteric. Things like "\dt". It adds a good hill to the learning curve 2. MySQL is everywhere. Basically anywhere you go on the 'net you can find people who know MySQL, quirks and all. It's very easy to get help, and it's the DB that most tutorials (for other things, like Rails, PHP, etc) use. MySQL is also the standard database you can find on any web host. 3. Replication was a big deal. MySQL has had replication built in for many years, and it's quite easy to use. At my last job we would have been quite happy to go to PostgreSQL for the performance, but we needed replication. At the time, there were solutions for PostgreSQL in outside projects such as Slony. Since we didn't have any PostgreSQL experience we didn't want to expend all the resources on testing and configuring everything. In the last two years or so PostgreSQL has gained an official in-tree replication solution. This makes understanding how to get replication up and running much easier. 4. MyISAM is small and fast. When machines were much slower, that could be a real benefit. Of course you sacrificed all sorts of consistency to get that. At this point, I'd say PostgreSQL's biggest problem is mindshare. MySQL has been improving the whole time, and some of the worst warts are gone. It may not be the best tool for the job, but MySQL is just so common. It's trivial to find help, advice, employees familiar with it, programs that support it, etc. I'd really like to use PostgreSQL in production so I could get more experience with it. It has some amazing capabilities. But our needs are relatively simple and we have lots of MySQL experience so there is no reason for us to look at switching right now.

einhverfr 14y ago
As for "show tables", there's a big difference here. "show tables;" is a server-side command. \dt is a command you give psql to say "ask the server what tables there are and list them." \d stands for describe. If you use a graphical client, it will have to query the system catalogs itself which you can do if you want. As for replication, the real challenge is that replication is not a one-size-fits-all thing. Slony, Bucardo, and the built-in streaming replication have different limitations. For example you can't replication from 9.0 to 9.1 with streaming replication but you can with Slony and I would expect you could with Bucardo too. So with the out of tree replication systems you can actually have a zero-downtime upgrade as long as you block writes during the upgrade of the master. With streaming replication, assuming pg_upgrade is supported for your upgrade, you only have a short downtime window, but it is downtime. Also streaming replication is all or nothing. With Slony you can replicate different pieces of your database. On the other hand, with Slony, you can (accidentally) replicate only a piece of your database. So the differences give you a lot of flexibility there, but you need to have a clear idea of what you need and why you need it before choosing a replication solution. Slony is perfect for what Affilias uses it for, while streaming replication wouldn't meat their needs. Also I don't think that PostgreSQL suffers from a lack of mindshare. Ever since I have been building apps on open source db's, PostgreSQL has been regarded as the go-to database for complex business apps. The sorts of things people do with the database are different. There is consequently a huge "dark community" if you will (in the sense that you don't see them). This was driven home to me when I went to the Malaysian Government Open Source Software convention last year. There were two booths (Oracle and one other) which had the MySQL logo, and a few more that were offering MySQL services, while I counted at least 5 that were using the PostgreSQl logo. Most of those were reselling EnterpriseDB's PostgreSQLPlus but it when I looked at the level of interest and the number of government deployments I was asked about, it was clear which people were usually choosing for complex work. As for user-friendliness, MySQL used to be much more user-friendly than PostgreSQL but I don't think that's true anymore. The command-line tools are more full-featured and the in-app help is better on PostgreSQL. \? brings up a list of psql client-side commands and what they do for example, and \h [command] will give you a summary of the syntax of an SQL command. I use \h quite a bit when I am trying to remember the specific syntax of a new feature, or when I am doing something I rarely do (like ALTER TABLE). I think that since about PostgreSQL 7.3, PostgreSQL has been at least as easy to use as MySQL, and quite frankly far more robust. Finally, the PostgreSQL planner is awesome. I have not had to rewrite a query to get around planner limitations since 8.1.

jeffdavis 14y ago

"What are the scaling differences between MySQL and PostgreSQL?" The best advice I have is that using a different database system, particularly ones as different as MySQL and PostgreSQL (which are essentially opposites) is like going to a foreign country. You'll find things you like and things that you don't like, and it will have a real impact on your quality of life, but it's hard to say in a few words the things that will really matter to you. You don't have time to live in every country in the world, but you owe it to yourself to at least visit a few that come highly recommended. Whatever you do, don't try to make the new country into your old country. You will just be disappointed. The same goes for database systems.