I think this is a false dichotomy to try to frame there is a Sqlite vs Postgres situation going on. Sqlite is amazing super fast single writer database without network service so it can be used locally as a library mostly while Postgres is a full fledged RDBMS. Both have use-cases that the given service is a better option and there are many other software products out there to be used for similar purposes.

Notable SQLite use cases: https://www.sqlite.org/famous.html

Postgres does not have a similar page: https://www.postgresql.org/about/press/faq/