Connection pooling in AWS across lambdas

Question

We know lambdas are charged by the execution time. So now I want to connect to SQL Server DB from lambda. If I create a connection in each lambda, it would be heavy for lambda.

Is there any best way to maintain my SQL connections alive in one place and will be able to use across my all lambdas. Or at least alive for one lambda for multiple executions.

I know, lambdas should be treated like stateless, but still, I am looking for a better solution for this issue. I have searched over many sites but no luck. I am finding less help and references for AWS in google.

Circular

Thank you so much for the quick response @mjwills. Is it possible to maintain the connection alive for the same lambda for multiple executions? – Circular

Answer 1

Yes, there is a better solution.

Basically, you are right, Lambdas should be treated as stateless.

However, in AWS Lambda there is concept of container reuse. Which means that if you invoke Lambdas on a frequent basis then it is highly possible that the same container which served your previous request will be used for serving your current request. And if that happens, then you get the previous Execution Context which includes all declarations and database connections (except the handler code) as is from the previous execution.

Following is what is documented for AWS Lambda container reuse

After a Lambda function is executed, AWS Lambda maintains the Execution Context for some time in anticipation of another Lambda function invocation. In effect, the service freezes the Execution Context after a Lambda function completes, and thaws the context for reuse, if AWS Lambda chooses to reuse the context when the Lambda function is invoked again. This Execution Context reuse approach has the following implications:

• Any declarations in your Lambda function code (outside the handler code, see Programming Model) remains initialized, providing additional optimization when the function is invoked again. For example, if your Lambda function establishes a database connection, instead of reestablishing the connection, the original connection is used in subsequent invocations. We suggest adding logic in your code to check if a connection exists before creating one.

For more details check here

Vita

thank you for the answer. So you mean to say, I just have to check whether the connection is established before I use it. I don't need to write any special code for it? – Circular

Right. You don't need to do anything special – Vita

@Circular the database connection needs to be in a variable or structure with global scope. If scoped only to the handler function, it would go out of scope after each invocation. – Hardesty

How does this help to answer the question? Container is not a pool. You have no control over it unless you keep calling it periodically resulting into more execution time and hence cost. – Goldie

Yes @Suyash, you are right that container is not a pool. But the major concern was 'Is there any best way to maintain my SQL connections alive' and that can get addressed by this answer. And to answer your cost concern, although AWS does not publish any official numbers about the time they keep Lambda warm, my experience is even after 30 minutes it usually is warm. So going by that I can ping Lambda periodically every 15 minutes which means 96 invocations a day (2880 invocations/month) and that's trivial enough number to get me any extra charges considering 1 Million requests/month are free. – Vita

But now the question is, how and when do I close the connection without any connection leakage? Although I use oracle but I assume this would be a problem to tackle in this scenario right? Can someone help me understand how I can close the connection when I'm opening the connection outside lambda handler. – Wintertime

Answer 2

AWS announced RDS Proxy, exactly for this use case, when you need a connection pool for a lambda architecture:

https://aws.amazon.com/about-aws/whats-new/2020/06/amazon-rds-proxy-generally-available/

RDS Proxy now supports SQL Server:

https://aws.amazon.com/about-aws/whats-new/2022/09/amazon-rds-proxy-rds-sql-server/

Lonnylonslesaunier

Answer 3

I don't know about how it would work with C# in lambda but I suspect that it should work the same way as node or somewhat similiar.

Each lambda function keeps the connection available for some time after it has been called, so if you can that lambda function again it reuses that connection but I had the same problem where every lambda function would be able to read and write to the database and that just cause the number of connections to jump up.

The way I handled connection pooling is by having a single lambda function that's responsible for connecting to the database and all the other lambda functions call this one to get what they want from the database and because that lambda reuses the connection it worked like a charm; I've attached a part of the design I used that might clarify what I wrote

Indemnification

By using this approach we can run into the same problem because concurrent invocations will start up new instances of the lambda leading to the creation of additional connections. – Vibes