For me the biggest limitation is replicating the SQLite across machines. If my app is running on multiple nodes, then we need to write/use some tooling to replicate the database file across nodes. With that comes the problem of figuring out how we want to handle error scenarios like failed replication, partial replication and such other things.

And these are all hairy problems. At that point it might be just simpler to use a centralized Postgres or a proper distributed database.

True but it lacks the tools you’d want to have to deal with larger databases, for example everything is one file and no table-level locking. You can split into different databases and use ATTACH but at that point you might as well install Postgres.

SQLite really shines when you know that a database can only get so large, let’s say you have a paid product that is only ever going to have a moderate number of users.