Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary. When you're using a database to store a few thousand rows of data, Postgres quickly becomes overkill. Postgres is a V8 engine when all you may need to power is a lawn mower.

I personally prefer to use abstractions like ORMs for most of my database interactions, and direct SQL when those abstractions get in the way (by generating expensive queries and not finding an easy fix without a large refactor).

This way, starting out with sqlite (good enough for most websites I reckon, easy to backup) doesn't interfere with any necessary migration to postgres (like when the need for scaling arises). This also makes setting up tests easier (except for the manually written SQL) because starting an application with a temporary in-memory database is a lot faster than starting a full container.

Unless I'm doing native apps, I'll probably always want to reserve the ability to use Postgres. Sometimes that means hooking up a Postgres account and such, but often that just means sticking with sqlite and leaving my options open for when sqlite doesn't work anymore.