> I suppose this is mainly a thought for projects like neondb/cockroachdb/stackgres

StackGres is "just" a platform for running Postgres on Kubernetes. It helps you deploy and manage HA, connection pooling, monitoring, automated backups, upgrades and many other things. That you have a tiny Postgres instance; or hundreds of beefy clusters with many instances is up to you. It's not a distributed database (like the other ones mentioned), it is still "vanilla" Postgres.

Disclosure: Founder of OnGres (company behind StackGres)