With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore.
Anyhow, I see your main point: SQLite is indeed "Lite", so it makes sense to use in embedded or small single-consumer cases. I just don't think those are very interesting tools to make in 2021, we expect collaboration and concurrency, so PostgreSQL wins in my imagination-space. I am a web developer though, so I am inherently biased.
I also am not the person you were just discussing with
WebSQL didn’t die as a standard because noone wanted client SQL on the web, it died because SQLite was the only reasonable way of providing it, preventing multiple really-independent implementations.
A good example is that SQLite is used inside many video games. You wouldn't put Postgres in your game engine.
How lightweight?
SQLite itself is around 600 KB. That's with all features enabled. I believe you can get it it down to about half that if you disable features you don't need. RAM usage is typically under a dozen or so KB.
It doesn't even require that you have an operating system. All you need for a minimal build is a C runtime that includes memcmp, memcpy, memmove, memset, strcmp, strlen, and strncmp, and by default also malloc, realloc, and free although it has provisions for providing different memory allocators, and you have to provide some code to access storage if you are running on a system that doesn't have something close to open, read, write, and the like.
This means it can even be used on many embedded systems far too small to run a Unix.
Heck, it is not even too unreasonable to compile SQLite to webasm and include it on a web page if for some reason you need client side SQL on the web.