When using AWS lambda + rds (psql), should I be using pools?

[0radek]

Sorry this is probably a really dumb question, but I'm trying to move my existing express app w/ pg over to lambda + api gateway.

In my current app (deployed on elastic beanstalk) I've got 15 client pool. Now that I want to use lambda, do I still need pools?

Thanks

[nilskassube]

AWS Lambda instances stay alive for some minutes, maybe ten, even if not in active use. Connection pooling will probably be a good idea.

[rpedela]

I assume you will be running 100s or 1000s of lambdas at once. If so, I think it would make more sense to use an external server for pooling such as PGBouncer. Although I would try without any pooling first because it may not matter in practice, depends on the use case.

[brianc]

@rpedela is right. Unfortunately the PostgreSQL server backend doesn't recommend connecting over 100 or so clients (YMMV) but having an unbounded number of clients connected as is the case when using lambda is a recipe for disaster. You'll want to use pgbouncer or something else in between. That's just unfortunately a limitation of posgres itself AFAIK.

[sehrope]

You can't use client side connection pooling with AWS Lambda because your app may be frozen from low activity. When that happens the underlying socket for the connections in your pool would be either be closed or (more likely) time out. Either way you can't rely on them existing when it's thawed.

You should be either directly connecting to the DB each time you need a connection or using an external pool like pgbouncer. Either way, you should immediately close the connection in you Lambda after use.

Note that for simple testing it may seem like the connection pool is working. Only when the freeze / thaw cycle kicks in will you notice the dropped connections and errors. It's best to think about Lambdas as stateless from an external resource perspective. It's fine to cache local data in memory but not external connections that require network sockets or external state.

[john-robi]

In my experiments with Lambda in a web server use case, if I don't call pool.end() then lambda gets upset and waits for the process to complete, then errs out (you can change this with a config option). If I do call pool.end(), then I need to re-create the pool for the next request. So for lambda web-server use case if you are using pools (because presumably you have several DB calls for a single function entry-point), the "safest" use is to create the pool at the beginning of each request, and end() the pool just before returning your results via callback. (All the other reasons above are still valid.)

[ckirkos]

Has anyone had success using global pooling since? Is the consensus still to open and close on each invocation?

[EzekielEnns]

@ckirkos ik this is old but for other people I thought id mention what I found if its not right id love some advice/correction, so the handler gets called asynchronously with our lambdas, so each one of your handlers are going to be called in a separate thread this awesome article talks about diffrent configs for your pool what you'll want to do most likely is configure your pool outside of the handler function using the configs recommended from the article I get something like this

const pool = new Pool({
    min: 0,
    max: 1,
    idle: 10000,
})

exports.handler = async (event) => {/* your code */}

your connection can be configured using the client env parameters found here
so my thinking with this is that since each handler is called asynchronously in the same scaled up container the pool actually becomes useful kinda think about your handler as threads, then depending on your use case you either
request a client via pool.connect() or if you just need to execute one SQL request do a single query via
pool.query('DROP TABLE *') for more info check out the pooling in the docs

[EzekielEnns]

so nobody really cares but I looked online and yeah it gets reused occasionally aws clarifies it right here also did some tests using jmeter and found the behavior to be kinda random so in 100 requests all made relatively close together you'll get about 5 or more api calls running the same base code "the more you know"

[ckirkos]

I think using the global pool is viable, especially with a small idle timeout setting. I would be most comfortable with being able to close connections when AWS decides to shut down the lambda. So far I found that it is possible to hook into SIGTERM events if using a lambda extension.