We can all dream about the day we beat the speed of light. But, yeah, "tradeoffs". Single instance, single-node DBMSs like PostgreSQL work well in the beginning. PostgreSQL is the most popular OLTP database for good reasons. But, Distributed SQL databases are built for all the cases where the application needs a higher scale than fits cost-effectively on single machine, guaranteed consistency without pushing complexity to the application code, higher resilience to failures with less effort and cost, replication across regions/datacenters/CSPs, and configurable data placement policies for low-latency access around the globe and for data regulation & sovereignty control.
The point about strong consistency and 2PC cross-shard is a good one. Even among other "auto-sharding" distributed relational databases, not all of them provide that.
So, you think that the 99.99% of businesses around the world who are not in the cloud hyperscaler business should all build their systems as if they had the engineering resources of cloud hyperscalers? I've seen first-hand how enterprises with Web 2.0 envy made costly, multi-year mistakes in technology choices and architecture with that approach. Be inspired by the engineering prowess of FB, AWS, Netflix, etc., for sure, but don't make the mistake of taking the approach of a $470B (AWS) or $86B (FB) company directly when your company's situation is nothing like that. Stated another way, you can't coach exactly like Pep Guardiola if you don't have his resources and his players. Existing enterprises have a totally different starting point. Enterprises simply can't follow the hyperscaler hegemony for their digital transformation strategies and expect to win. Also, think about who is served by building inefficient, accidental distributed databases underneath your SaaS applications when that can be avoided by using a proven technology alternative already exists. It's the CSPs you're paying in the monthly cloud bill for every byte stored, every byte computed and every byte moved.
If you're really confident that your SaaS application will only have that single workload (single table group-by) into perpetuity, it's a workable choice. But, that's a big bet against the reality of the diverse workloads required today in scale-up SaaS applications. Sure, you can stitch together specialized stores to make your own accidental distributed database for your app, but the simpler, more straight-forward and lower-latency alternative available. Yes, I'm a SingleStore employee, but it is worth considering the argument on its merit. It's also worth trying for free in production. The free tier for production is not time-bound, just capacity-bound.