But I guess for Amazon, inefficiency translates to $$$, so wouldn't really make sense to do this.
I wonder that more generally about cloud as well. For many things making the backends multitenant would be far cheaper for the customers and just a bit more expensive for the providers ... there's the security aspect (but is it really that different ? Even with VMs the kernel and hypervisors still need to not leak information, and even with Physical servers the "reboot and recovery" and remote control mechanisms are often still avenues for security exploits)
This low level separation is easy and cheap to implement for the providers, but (it seems to me) much more expensive for the customers.
But I guess that's pretty much true for all cloud stuff. It provides automation, but at an operational cost, which is really not the trade off I'd like to make.
In this case, Aurora Serverless gets the name because it will scale to 0 which is useful for things like Lambda or Batch functions that only get called sporadically.