How isn’t RDS Aurora Serverless from AWS, available in both MySQL-and Postgres-compatible flavors, exactly what you are looking for?
Provisioning of the compute part of the database is still at the level of "ACU" which essentially map to the equivalent underlying EC2s. The scale up/down of serverless V1 is clunky and when we tested, there was visible pauses in handling transactions in progress when a scaling event occurred.
There is a "V2" of serverless in beta that is much closer to "seamless" scaling, I assume using things like Nitro and Firecracker under the covers to provision compute in a much more granular way.
I'll look into it next time I'm building something.
In my experience shared servers for MySQL (and I assume Postgres) never work because I always have to set at least some custom configurations very early into the development cycle.
How does data get loaded? Can it use s3 as an external source similar to foreign data wrappers?
But, if you're going to run with it, can you make it as fast as a "real" database on a cloud instance?
For me the one I really want is "serverless" SQL databases. On every cloud platform at the moment, whatever cloud SQL thing they're offering is really obviously MySQL or Postgres on some VMs under the hood. The provisioning time is the same, the way it works is the same, the outage windows are the same.
But why is any of that still the case?
We've had the concept of shared hosting of SQL DBs for years, which is definitely more efficient resource wise, why have we failed to sufficiently hide the abstraction behind appropriate resource scheduling?
Basically, why isn't there just a Postgres-protocol socket I connect to as a user that will make tables on demand for me? No upfront sizing or provisioning, just bill me for what I'm using at some rate which covers your costs and colocate/shared host/whatever it onto the type of server hardware which can respond to that demand.
This feels like a problem Google in particular should be able to address somehow.