Where can I get a good understanding of the limitations of SQLite for production use cases?

I’ve used it frequently for prototyping, but the dialect is different enough from Postgres, that I usually switch over pretty early. But for production, it seems people are really advancing it as a solution for applications, which doesn’t fit with the “When To Use” guide on the website. https://www.sqlite.org/whentouse.html

The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point.

But... actual guidance as to how to use it there is still pretty thin on the ground!

Short version: use WAL mode (which is not the default). Only send writes from a single process (maybe via a queue). Run your own load tests before you go live. Don't use it if you're going to want to horizontally scale to handle more than 1,000 requests/second or so (though vertically scaling will probably work really well).

I'd love to see more useful written material about this. I hope to provide more myself at some point.

Here are some notes I wrote a few months ago: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/