posted 2 months ago
I can't answer the first question, but I can offer some notes on the second.
As I've mentioned other places, I have heard rumors that very, very long ago there was a database developed under US Government contract. Back then, taxpayer dollars spent developing software, drugs and other Intellectual Property was public domain so that the people who paid for it could benefit. Assuming it wasn't classified information, anyway. This is also how Prime Computer was founded.
That, of course is no longer true. Everything has to be monetized and you pay once for the government work and again for the product.
Ranting aside, this legendary database became the foundation that Oracle was built on and so the story goes, PostgreSQL.
I cannot (haven't tried) to find the name or full story, but I have worked with a couple of similar databases back in the early 1980s. One was written in FORTRAN, and when I tried to port it, it managed to crash the Fortran compiler, the linker, and the print spooler one after another when building. Probably why Honeywell gave up the minicomputer business early.
This ur-database predated SQL, I think, so there's been a lot of change since then, but yes, PostgreSQL is syntactically a lot like Oracle. They're definitely gone their own separate ways, and PostgreSQL, while still a general database has carved out quite a niche for itself in GIS systems.
In early days, MySQL was lightweight and convenient, while PostgreSQL strove for industrial capability. For example, I don't think MySQL supported transactions until about MySQL version 5. Nevertheless, MySQL was considered more than adequate for a whole raft of applications, and is probably the best-supported backend for many open-source projects to this day.
MySQL has the distinctive feature that it supports multiple database file formats for different purposes. All the way from simple CSV (!) files to InnoDB, which has historically been the most powerful backend. PostgreSQL has only supported one backend organization, and for years it frustrated me that its format would change so radically between even minor releases that you couldn't transfer the binary files from one server to another.
I don't have benchmarks, but the conventional wisdom is that MySQL is good for lightweight quick work and PostgreSQL is good for industrial-scale work¸ but there's a lot of overlap. Several systems I have have bounced back and forth between the two databases as new versions of the app were released. JPA applications are pretty much independent of what database you plug them into also.
I'm not sure if sharding is a feature of MySQL, but it can be used to assist performance by physically splitting the database based on frequency of access. Likewise, if I understand correctly, PostgreSQL has extra powers when maintaining a database across continents.
But by and large, unless you have very specific needs, you just pick whichever one you prefer. Avoid stored procedures and other vendor-specific quirks, and if you do have to switch, you'll be able to migrate relatively painlessly.
The secret of how to be miserable is to constantly expect things are going to happen the way that they are "supposed" to happen.
You can have faith, which carries the understanding that you may be disappointed. Then there's being a willfully-blind idiot, which virtually guarantees it.