Personally, I find the tooling, documentation, familiarity and quality of Postgresql makes it my choice even in situations where SQLite might work.
SQLite is also quite often a CI/CD default when building and testing software where you might want to do some tests without starting an entire RDBMS server.
But I suppose it's different if we think in terms of networked databases, or multiuser.
Suffice to say SQLite is probably at least 6bn active usages in size.
(cf. Daniel Stenberg getting angry emails because everybody and their dog embeds libcurl, sometimes multiple times in the same app)
** 2006-10-31: The default prefix used to be "sqlite_". But then
** Mcafee started using SQLite in their anti-virus product and it
** started putting files with the "sqlite" name in the c:/temp folder.
** This annoyed many windows users. Those users would then do a
** Google search for "sqlite", find the telephone numbers of the
** developers and call to wake them up at night and complain.
** For this reason, the default name prefix is changed to be "sqlite"
** spelled backwards. So the temp files are still identified, but
** anybody smart enough to figure out the code is also likely smart
** enough to know that calling the developer will not help get rid
** of the file.