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.