10 ms·

I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.

by Thaxll 6y ago

kstrauser 6y ago
Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios. For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished. Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan). PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.
- tomnipotent 6y ago
  > PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks. I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).
Shish2k 6y ago
In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)
acdha 6y ago
MyISAM was historically notably faster for simple SELECTs on the primary key and SELECT COUNT(*). InnoDB had ranged from slightly faster to much slower than Postgres depending on exactly what you’re doing. Once you’re doing more than trivial queries the MySQL optimizer tends to fall apart, often in weird ways with arbitrary thresholds where performance goes from decent to terrible after a table grows by 5%.

I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.

by Thaxll 6y ago

I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.

Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios. For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished. Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan). PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.

tomnipotent 6y ago
> PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks. I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).

Shish2k 6y ago

In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)

acdha 6y ago

MyISAM was historically notably faster for simple SELECTs on the primary key and SELECT COUNT(*). InnoDB had ranged from slightly faster to much slower than Postgres depending on exactly what you’re doing. Once you’re doing more than trivial queries the MySQL optimizer tends to fall apart, often in weird ways with arbitrary thresholds where performance goes from decent to terrible after a table grows by 5%.