If you like foreign key constraints to actually be enforced automatically (without turning them on in a pragma and/or expecting that everyone who connects to your database does the same or uses a version compiled with custom flags), transactions not to allow an accidental commit after a syntax error, richer indices than btree ones, enforced typechecking without a CHECK constraint, DML commands other than SELECT telling you what rows were returned, etc., the functionality is simply missing in SQLite. Some of it is for backwards compatibility, some not, but at the end of the day it's just not as pleasant to use as something like Postgres as a SQL database. These have all caused issues I actually ran into while using the database, some of which led to integrity violations that took a long time to fix. Sure, they're documented behaviors, but that does not mean they're good behaviors, and they substantially restrict the situations in which I'm willing to deploy it far more than any scalability issues do.

If, on the other hand, you're just using it as a transactional storage engine, none of this stuff really matters and I understand the praise--hence why I think the two modes of use should be differentiated. The other thread responding to me is basically all about extending the very barebones provided interface with custom, application-level semantics. I agree that SQLite is excellent for this, but you have to remember that this is not the only thing people want out of a database.