I’d call Postgres “heavyweight” compared to SQLite. Maintaining a SQLite db is as simple as a file, and then you can use litefs or litestream to back it up.

Maintaining a Postgres server or cluster is hard. But it offers way more functionality.

I agree that the term heavyweight can mean many things to people. I’ve take it as a generic mix of maintenance, complexity of project, ease of use for your purposes.