If YOUR life is good but you want to continue to make your analysts' life a living hell => Don't change anything
Otherwise, switch to Postgres. At the very least, for the love of god, make sure your MySQL is >=v8.0
Otherwise, switch to Postgres. At the very least, for the love of god, make sure your MySQL is >=v8.0
And using Spectrum or Athena and reading directly from the data lake has it's own pros and cons and overhead costs. I wouldn't agree with your approach in most cases. Much better would be to ETL from postgres into a vanilla redshift DW or just set up a replica postgres to query from. If you're using Redshift Spectrum you should probably take a look at Snowflake as an alternative.
And I'm using spectrum as a data lake solution generically. If you want to run snowflake or some other thing is dependent on if you are in aws, gke, bare metal, etc and if you want to host or not. I prefer not to have to manage hosting and I run in AWS.
The important part is you don't need to tie your web app database to all of your needs. Separation of concerns is the important part and independent scaling for independent needs.
CTEs/WITH clause is the other massive one - almost as important as window functions.
Additionally, EXCEPT/INTERSECT support, better EXPLAIN (it's still shit though), much improved REGEXP and JSON support.