I am a big user of SQLite, we use it everywhere in our software. But I think I would like something in between Postgres and SQLite.

A few issues I have with it:

- Migrations are a real PITA, you often need to copy whole tables over since the DDL available is so limited. Even basic things that are not breaking change like dropping a foreign key is not possible

- Single file doesn't scale that well, you will see select performance degradation, vacuuming will be extremely long/take a lot of disk space. We started splitting our databases in multiple files and using ATTACH but you lose a lot of nice things like FK support.

- No parallelism except on sorting, this one is annoying when you have compute heavy select (like searching on compressed data). It will max out a core but it can't spawn workers for the select. It can for the ordering part so it's not really a "technical" limitation.

Still grateful it exists though.