I wonder why all the predominant RDBMSes are heavyweight connection based, and why this is presumably so hard to change. There's nothing in SQL that requires this to be the case, at least conceptually.
Beyond that, MySQL for example pre-allocates a fixed amount of memory for read, join and sort buffers per connection to efficiently handle different types of queries, though the buffer sizes are all tunable to optimize performance. There is also some OS-scheduler overhead involved in creating and running a separate thread per connection. A thread-cache optimizes the case where lots of short-lived connections are being created. (I dont have direct experience with PostgreSQL but I read that it forks a separate process per connection, which would presumably incur a significantly higher overhead than threads.)
Beyond this, any more specific discussion requires quantifying connection 'weight' to dispel/confirm your superstition that 'all the predominant RDBMSes are heavyweight'. In an apples-to-apples benchmark, it's quite possible that the connection-weight of some RDBMSes might actually be lighter than other database engines. I dont know myself, but would be interested if anyone has any such data to share on this.
The problem really boils down to the tight coupling between the HTTP layer and the rest of the stuff all the way down the stack. If you assume connections are always long-lived and stateful, you can make performance and memory optimizations by reserving memory buffers and threads for the sole use of a single connection, and using threadlocals for storing session data.
But if each connection gets its own thread (or thread pool), idle connections lock up resources and can prevent new connections from being opened, because all of the threads are currently allocated to existing connections. And setting up every new connection is a bit expensive and wasteful if it's only going to be used for one request.
I think the industry trend toward transitory connections, and even transitory application instances a la Kubernetes pods, is a good thing for a lot of reasons. If nothing else, the knowledge that connections can be short-lived leads to more fault-tolerant software because the business logic layer cannot depend on assumptions about the HTTP layer. The big downside is that a lot of older stuff isn't really suitable for that kind of world, serverless or not, and it can be really hard to refactor.
Crappy application-servers pump queries like nobodies business, so you need to shave every byte of them you can.