In your k8s example, by running a k8s cluster when you don't need one, a cost you pay is the overhead.
In the Cockroach serverless case, costs that come to mind include vendor lock-in when you evolve a pattern of production traffic that is hard to migrate to other solutions, and security and compliance challenges due to the virtualized instances running on shared clusters. In many cases these tradeoffs may be worthwhile. My point is that looking at it only in the dimension of scaling up and down, doesn't tell the whole story. The OP doesn't talk about tradeoffs, so in the comment section we must.
Further, once you're experiencing the types of success that demands a superior solution the priority to invest in such a solution is clear.
It isn't "lock-in" - there's enough experience and capability in the market to solve those problems _once you have to_. Solutions like this let you decide whether the right time is at start-up or scale-up.
Serverless is not just about auto scaling up from 1 to n, it's about autoscaling down from 1 to 0.
If Cockroach provides a robust SQL DB at a marginal cost of ~$0/mo for small request volumes, that is a real value add over running your own pg server.
Not having to deal with administration or backups is another big value add.
This offering looks like it compares very nicely to say running an rds instance with auto backups enabled.