AWS Lambda Best Practices For Connection Pooling

[bestickley]

Hi, I'd like to document AWS Lambda best practices for connections with this library. Based on the article, Handling AWS Lambda database connections I've come up with the following config:

{
  // https://solidstudio.io/blog/aws-handle-database-connection
  max: 1, // number of connections
  idle_timeout: 1, // seconds
}

Know a better configuration? Please share!

[porsager]

Would be nice..

I think It'd be best if you could use the certificate they provide https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.SSL.html

[bestickley]

Great point, @porsager. Let me specify this to connection pooling guidance.

[jamesopstad]

I'm looking for advice on using this library with AWS Lambda and PgBouncer. I currently have PgBouncer set to transaction mode, which seems to be the best option for connecting from lambda.

Is it safest to create the sql instance inside the handler function and explicitly call .end() or to create the sql instance outside the handler function and use a very short idle_timeout? With lambda, there is the risk of zombie connections if they are kept alive between invocations because of the freeze/thaw process.

I see from the docs that it is recommended to set prepare: false when using PgBouncer in transaction mode. Is it also recommended to set fetch_types: false? I'm not clear on the impact of this.

Are there any other settings I need to consider? Sequelize has very detailed documentation on usage with AWS Lambda (https://sequelize.org/docs/v6/other-topics/aws-lambda/) so it might be worth including something similar.

Thanks in advance.

[jamesopstad]

Oh - you can do const sql = postgres() at the top level if that looks better for you. Even have it in an imported file as suggested in the docs. Postgres.js is lazy, so no connection happens until you actually make a query 👍

Ah, I see. I had assumed that after calling sql.end() the instance could no longer be used.

By the way, I tried this and it was as I suspected. Declaring sql at the top level and then calling sql.end() in the handler resulted in a 'CONNECTION_ENDED' error on the next invocation.

[porsager]

Well that must mean the same instance is handling several requests, so why end the connection?

[jamesopstad]

It comes back to my original question really. I'm not sure if it's safe to keep database connections open between requests. Lambda reuse is non-deterministic and my understanding is that this point I raised could be a concern:

With lambda, there is the risk of zombie connections if they are kept alive between invocations because of the freeze/thaw process.

I could be wrong though. If connections can survive the freeze/thaw process without issue then keeping the connections open makes sense. There is quite a lot of conflicting advice out there so I'm just reaching out for clarity on the best approach.

[porsager]

ah I see.

I think the only sane approach would be doing actual benchmarks. I can't imagine it would make sense to close the connection after each request tbh. A low idle timeout of 1 second sounds reasonable

[arjunyel]

@jamesopstad what did you end up deciding?