I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc.
It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS).
If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
Recent SQLite versions have STRICT mode where they forbid that.
https://www.sqlite.org/stricttables.html
> or too-long data in a too-short column
You can use a constraint for that:
CREATE TABLE test (
name TEXT NOT NULL CHECK(length(name) <= 20)
) STRICT;For most business applications used by more than one user there are usually some expectations that data will be retained and the application will be available.