"real cloud providers" most definitely charge based on rows read/written. Many startups / side projects choose the on-demand billing model because they don't want a fixed $x / mo when they don't need it. Some of them also have pre-provisioned options, and it seems likely that Planetscale will probably end up doing something similar.
The point is that the market has shown there is a huge appetite for alternative database billing models other than a fixed cost per month. From my limited personal interactions, I'm aware of 10-20 developers who are using Planetscale in large part because of their billing model. They would have never considered a SQL database before because of the fixed cost.
Those nosql options (probably the most popular in the world) also have the issue that row sizes are different, and if you're super cost conscious, you can change your architecture to take advantage of it. For example with Planetscale, you could store a lot more in JSON columns instead of other tables to reduce costs if that was your primary objective.
Is your frustration that you'd like to use Planetscale or a managed Vitess, but you are worried about locking yourself into a pricing model that you don't think will work for you?
https://aws.amazon.com/dynamodb/pricing/
https://firebase.google.com/docs/firestore/pricing
https://cloud.google.com/bigquery/pricing#on_demand_pricing