My main concern is data loss and corruption.
I know that HN and Reddit hates MySQL and praises PostgreSQL, but their arguments make sense to me:
- Team more familiar with MySQL. It's better to use something that you know the defects and workarounds than to use something that's better in theory but you don't know how to tame it.
- MySQL has more documentation and training resources. There are excellent, up to date O'Reilly books about it from the development, optimization and administration perspectives. Just from O’Reilly there is: Learning SQL [focused on MySQL], Learning MySQL [not to be confused with the former], High Performance MySQL, Efficient MySQL Performance, MySQL Cookbook. And all of them are up to date. The official docs are also very good. On the PostgreSQL side, no such resources, there seems to be no recent good books about it - the O'Reilly book is about a very old version and the Packt ones don't count, Packt books are usually sh..t. Also, the official docs seem meh (example: the MONEY type should not be used, but you wouldn't know this from the manual [https://www.postgresql.org/docs/14/datatype-money.html], there's not even any mention on it being bad; you'd know it's bad only if you had read https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money
- Most complaints online seem to be about MySQL 5, which is very old and which problems mostly been fixed in modern versions, or about installations running without SQL strict mode on.
- PostgreSQL is also not immune to problems: https://about.gitlab.com/blog/2017/02/10/postmortem-of-database-outage-of-january-31/
However, I experienced, multiple times, data loss and corruption with MySQL and MariaDB, as recently as 2017. That makes me uneasy to fully trust it.
What do you think, from a perspective of data integrity and reliability, about storing mission critical financial data in *modern* MySQL? Can it be trusted? Anecdotes about ancient versions will not be useful. Is anyone here running it for this kind of workload? Is PostgreSQL still considered safer than MySQL in 2022 or has MySQL caught up in that department? The main worry is not about features or speed, the main worry is data loss and corruption.
Edit: formatting
Now, in terms of requirements, it sounds like you need a DB that:
- Is highly available - replicated (easily?) - Secure - Open Source (free?) - Well documented / supported
Whatever your requirements are, you need to prioritize them and do some discovery work. Typically, you can rank the technology across categories and pick the one that best suits your needs. No DB is perfect for your application and they all require some level of expertise to keep working as expected. Lastly, and oftentimes, you use other layers in the stack and architectures to bridge any gaps in the core technology.
Good luck