Anecdotally:

The main caveat as someone who works on mostly average web CRUD apps, is that "Use PG/SQLite for everything" usually falls flat when the tools I use day to day don't support that use case super well or have rougher edges.

If your framework/ORM/whatever of choice doesn't support the full feature set of that driver compared to Redis/ES/Whatever you're replacing, you'll find yourself going down rabbit holes doing workarounds instead of staying with the "happy path" and just using separate tech for what it's specialised in.

If you already are doing most of these sorts of features by yourself instead of with frameworks, maybe it's fine, but this does start to feel like a time-to-release hindrance if you don't want to fiddle with the minutia.