At a certain scale, you'll want replication or replication, which SQLite doesn't really do AFAIK. At a scale below that, you'll probably want to be able to have multiple web servers talking to one database server, which SQLite doesn't really do either. I also think SQLite's performance during heavy write workloads is worse than PostgreSQL's?
Basically, AFAIK, SQLite becomes problematic once you need more than one computer to handle requests.
Yeah, there are SQLite forks or products built on SQLite which have these sorts of features, but SQLite doesn't. They remains reasons why someone may want to use another database (such as PostgreSQL, or an SQLite fork, or a database which uses SQLite as a storage engine) instead of SQLite.
Honestly though, if I need these sorts of distribution features, I would probably prefer the database to have them built in. I don't really see the point in using SQLite at that scale.
Basically, AFAIK, SQLite becomes problematic once you need more than one computer to handle requests.