SQLite is used more in resource-constrained environments. If I had much memory to waste, I would've used a local PostgreSQL installation to begin with.

Note that advanced vector-embedding querying techniques for approximate nearest neighbor search inevitably always need an absurd amount of memory, so it typically doesn't make sense to use them in a resource-constrained environment to begin with.

It's also used plenty in the cloud, for a variety of reasons. Check out the first linked article for something I wrote on that: https://www.maragu.dev/blog/go-and-sqlite-in-the-cloud

I have used SQLite with embedded 2MB flash and 8MB RAM. The microSD card was used as swap for producing reports and serving them over HTTP.

Next embedded model version moved to 8MB flash and 64MB RAM with the same software infrastructure.