SQLite runs in 3 billion devices. It is the most deployed database in the world.
For most apps, it is the right database. Not because it's simple — it isn't — but because the problems it solves are the actual problems:
- Embedded, no network hop
- ACID transactions
- WAL mode for concurrent readers
- FTS5 for full-text search built in
The moment you need it: when you realize you've been running Postgres for a 3-person app with 50 writes/day.
Related: ↗ @dave / relational-model