Deadlocks aren't generally too much of an issue with an MVCC DB like Postgres (though it certainly can happen if you use explicit locks and such), but yeah ultimately it's all about the kind of workload we're talking about. I honestly think of it much less like a database and more like a high-performance synchronization mechanism that can cross process boundaries. There's many times where you want to coordinate the behavior of many independent processes (like horizontally-scaled services), such as rate-limiting your requests to an external service. Implementing a distributed rate-limiting algorithm with a RDBMS is a pretty poor choice, because the data used for any synchronization is ephemeral, unimportant, and short-lived (and this, ACID guarantees are pretty irrelevant), and you would incur a lot of contention overhead for shared resources. You might even get bottlenecks that prevent you from achieving the rate you're trying to limit yourself to. Something like Valkey gives you simple, fast atomic writes with built-in TTL mechanisms, which is perfect for a rate-limiting system. There are many other examples like this where it makes a lot of sense.

Or consider even something incredibly simple like, I dunno, a realtime user counter. Valkey has an atomic primitive operation for this built-in (INCR). With an RDBMS, this is not trivial as you scale up the number of clients, because either you have to deal with a ton of row-level contention (if the counter is stored in a single row that gets updated), or you end up hammering your disk with increasingly large aggregate operations (if there's a row-per-user and the total is computed across them, somehow).

As I said, it's really just very different workloads where they are valuable.