Which RDBMS implement a protocol with tagged queries? I don't think that's common. From my, quite fallible, memory, the DB protocols supporting pipelining all just do in-order query execution. As I mentioned before, postgres does support that.
The TCP connection piece of a database connection isn't really expensive - that's the transaction and query execution state. These days (used to be different) it's not that hard to write an application handling 10s-100s of thousands of TCP connections, as long as most of those aren't continually sending data.
Obviously that's where the process-per-connection limitations for postgres come in - having that several 100k processes isn't really a good option. Not even just because of the process overhead, but even just the context switch and process scheduler overheads become more and more debilitating.
But even if postgres' connection model were switched to many-connections-per-process/thread - you still need to have the per-connection state somewhere; obviously transactional semantics need to continue to work. And the per-connection transaction state is where the snapshot scalability limitation the article is talking about was. So the snapshot scalability issue needed to be fixed before a connection model switch really made sense.
Entirely depends on your workload:
These days it's very common to have the hot working set fit into the database server's buffer cache - in which case there will be little IO. And even in cases where you have a lot of IO - a decent NVMe SSD can do several 100k IOPS (with some doing more than 1M IOPS), with per IO latencies in the mid two digit microsecond range.
And because common queries can be processed within fractions of a millisecond, often 500 busy connections can be handled by a few cores.
If even 500 (out of 4500) connections are busy, what's the catch? Isn't this just a lot of scheduled CPU work trying to run concurrently when it would be faster if queued? Fast NVMe IO, or the fact that the queries are a few ms each, are just factors that makes the workload more CPU intensive (vs IO bound), the scenario would be more plausible if you had slow disks :)
I guess being bottlenecked by the network can be one scenario if your DB and app aren't close and/or your queries involve bulk data like GIS shapes...