Connection pooling is one area where NoSQL databases shine. Classic SQL is extremely stateful. It was designed was designed in a very different age when it was expected to mostly be used directly by programmers and non-technical users and be as friendly as possible. The SQL standard defines some state that must be stored along with each session, such as temporary tables, current schema, current timezone etc. This means that sessions have a pretty significant memory overhead and generally require a thread on the server side (and probably the client-side too).
NoSQL databases got rid of many useful things when they replaced SQL with JSON query languages and simplified SQL dialect, but I think they got a net positive when they did away with statefulness and replaced that with a simple request/response APIs. With this small you can easily multiplex many in-flight requests on a TCP single connection, and let both the server and the client handle requests on-demand, without having to allocate any memory and threads that serve most of their time idling away. The client (and supposedly the server, if it's using io_uring) can run all I/O asynchronously and increase available concurrency even more.
In short, you can get more concurrency and better scaling predictability for the same hardware since you don't have to optimize the way your connection pools are balanced across different clients. It's really one of these few things which is pure fun and joy and unicorns and rainbows when I'm designing a service with a NoSQL DB.
NoSQL databases got rid of many useful things when they replaced SQL with JSON query languages and simplified SQL dialect, but I think they got a net positive when they did away with statefulness and replaced that with a simple request/response APIs. With this small you can easily multiplex many in-flight requests on a TCP single connection, and let both the server and the client handle requests on-demand, without having to allocate any memory and threads that serve most of their time idling away. The client (and supposedly the server, if it's using io_uring) can run all I/O asynchronously and increase available concurrency even more.
In short, you can get more concurrency and better scaling predictability for the same hardware since you don't have to optimize the way your connection pools are balanced across different clients. It's really one of these few things which is pure fun and joy and unicorns and rainbows when I'm designing a service with a NoSQL DB.