Genuine question: why would anyone prefer MySQL over Postgres?
I suspect it has a large market share in the "started as a trivial PHP program, grew to large application, too committed to MySQL to migrate to something else" market, too. There's that one website with the blue F logo that went this path, but there's probably thousands more.
That's not the same thing as anyone actually preferring it, but that's two possible reasons why there's an awful lot of MySQL deployments out there.
For example I have two versions of Postges running on my machine right now because Debian does not automatically upgrade between them - you have to export your data, and reimport it into the new version.
https://www.pontikis.net/blog/update-postgres-major-version-...
MySQL just works. There are issues with it, but they are not as major as people make them out to be. Sometimes people like "technical purity" over just get the job done.
Right up until one of your users tries to use an emoji, apparently.
My understanding is replication and setting up MySQL is still easier than Postgres; it definitely was 10 years ago, that's why it's part of LAMP. The data loss issues were fixed forever ago when it switched to InnoDB.
Google AdWords and Facebook both use(d) MySQL a lot and I assume they knew about Oracle and Postgres at the time they were built…
Why would you assume that? Both of the companies were tiny and full of children when these decisions were made. And Oracle was expensive and a non-starter for explosive horizontal scale.
(Where they had a point because MySQL hadn't switched to InnoDB yet.)
Also, it's strange to conflate PG with Debian issues.
PG is great but its still has a lot of issues.
People use MySQL because it's easy, and they want to spend their effort in other places.
In the last 6 years, I've spent approximately 2 hours administering PostgreSQL, and I'd be the one who had to do it.
Don’t confuse lazy with easy.
- Connection 1 executes "select * from foo". It begins at row 1 and starts a table scan.
- When the reader is at row 500,000, connection 2 executes "select * from foo". It starts returning results beginning at row 500,000, so both connections are sharing the output of the reader.
- When the reader gets to row 1,000,000, the first query is finished. It's seen all the rows. The reader starts back at row 1, and continues feeding results to the second query until it gets back to run 499,999. Then the second query is also finished.
Even though 2 queries were running, the DB backend was only doing the work of 1. With that setup, you could have 100 "select * from foo" queries running simultaneously without increasing the database load at all.
I don't know if MySQL supports that kind of thing now. In any case, that's an example of some of the incredible engineering PostgreSQL uses to be correct and fast.
For data warehouses this is a different story, but god help you if you are table scanning in OLTP context.
Well, one and a half. Or the work of one query, but one and a half times.