There's gonna be a lot of judgement calls here, but I think a local Postgres install is the happy medium. A basic local install is super quick and easy to do, and it also has a lot of good scalability options. SQLite is okay as long as you stay small, but it doesn't scale up very well.

If I suddenly get big and my database is Postgres, then I can spin up my own dedicated server, or switch over to RDS after all, optimize concurrent queries, hire a DBA with scaling expertise, etc. Most of that isn't an option with SQLite. I'd have to switch to a completely different database engine. Despite the promises from ORM writers, I've never seen this go smoothly, and it would have to be done at the worst possible time for it.