Even if your lambda drops a connection after it destroys itself, a sudden influx of visitors to your site may spawn enough lambda processes to overload your DB.
What happens then is dependent on the database, but generally means that new lambdas do not get to connect.
Anyhow, you don’t really want your database to magically more around regardless, so you’d be sort-of locked in to one service regardless of what you do.
But postgres on one service is pretty much the same as on another, so if you wanted to migrate it would be fairly easy.
However, this is not the case for all databases. Furthermore, modern database vendors are extremely motivated to address the serverless usecase