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.
TCP/IP performs a handshake to establish connections so there will always be some per-connection overhead required for this, no matter the service on the other end.
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.
It seems like pretty much everything was connection-based a few decades ago, when those systems were written. This can lead to some behavior in legacy software that causes big problems in cloud environments, or really any environments where connections are transitory.
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.
Part of it is legacy but also, interactions with an RDBMS are often stateful so it's useful to have a session (in which such things as transactions can live, etc). I'd guess there isn't much reason to change this because there are standard and effective workarounds that just haven't yet made their way to things like lambda.
Connection pooling is the most obvious one which is so common it tends to be transparent and built into many DB access libraries. Then there are all sorts of proxies/load balancers/multiplexors. If you think about it, the bits of code that handle web requests in your typical web app/framework/whatnot are very much like lambdas and when you write those, you generally don't have to worry about the cost of DB connections because it's a well-solved problem.
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.