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.
I think everyone loves the concept of a distributed database, just not the added latency and reduced feature set vs a local PostgreSQL instance/cluster.