My take from reading some docs is that you've got to partition your data properly, likely per-user. Then hopefully most of that users interactions are within the same datacentre.
When I asked on Discord, someone from Cloudflare confirmed that DO is indeed the only way to do tenancy-based sharding (you give the DO a name to obtain a handle to the specific DO instance to talk to), but the DX experience between DO and D1 is quite stark; D1 has better in DX in many ways but can’t scale, DO can scale but has terrible DX.
That being said, the pipe dream statement is accurate IMO. I do think they'll get there, but like you said - a lot of the ideas put forward around a DB per customer just aren't present at all in the DX.
If I were to hazard a guess, Durable Objects will slowly gain many of the features and DX of regular workers. Once they reach parity workers will begin end-of-life (though I'd guess the workers name will be kept, since 'Durable Objects' is terribly named IMO).
This is kind of what happened (is happening) with pages right now. Workers gained pretty much all of their features and are now the recommended way to deliver static sites too.
For me, this can't come quickly enough. The DX of workers with the capability of DO is game changing and one of the most unique cloud offerings around.
It'll bring a few new challenges - getting visibility across that many databases for example, but it completely removes a pretty big chunk of scaling pain.
I dont't think your take makes sense. I'll explain why.
Cloudflare's doc on D1's service limits states that paid plans have a hard limit on 50k databases per paid account. That's roomy for sharding, but you still end up with a database service that is hosted in a single data center whose clients are served from one of the >300 data centers, and whose cache misses still require pulling data from the primary region. Hypothetically sharding does buy you less write contention, but even in read-heavy applications you still end up with all >300 data centers having to pull data from the primary region whenever a single worker does a write.
Read replicas for lower latency and sessions for partitions per user like you mention.