Heh. You realize that many people disagree about the answer to this, and have good reasons. (Keep in mind that many people actually use MariaDB – a fork of MySQL – in place of MySQL itself.)
Personally, I always use PostgreSQL if my customer is not providing the database. Note that it always requires adjustment of its config file, postgresql.conf, to take advantage of high-powered servers.
That said, it sounds like you have queries that need optimization. Particularly the addition of appropriate indices on your timestamp columns to allow the DB to skip full table scans for your complex charts. Those indices should already exist for Ignition’s Tag History subsystem, but need to be manually created for transaction groups.
The bottom line is that you need to study the queries that Postgres is running to determine which ones are causing trouble, and why. You can reconfigure Postgres to log all queries so you can see what’s running, then use ‘EXPLAIN’ to study how Postgres is solving them. If you’ve never worked with such tools, you might want to purchase some consulting time from EnterpriseDB, the major commercial sponsor of Postgres.