Biggest benefit over hosted PostgreSQL is that you get SELECT queries that are measured in microseconds, because SQLite avoids needing network overhead per query.

https://www.sqlite.org/np1queryprob.html

Wouldn’t D1 introduce network overhead?

Yes for writes, but it shouldn't for reads: it looks like it works by replicating the full database down to each edge location where the code is running.

Hope this can give you some concrete answers: https://www.sqlite.org/whentouse.html