node-postgres: readme suggests done() which is incorrect

Final Note for now: We stopped using connection pooling. Amazon handles this very badly. Basically we now always call client.release(true) to terminate any connection.

This will be best practice until Amazon improves their container handling with Lambda. If you don’t do this you are dealing with all sorts of issues (one is that many open connections are just sticking around).

Yep, setting context.callbackWaitsForEmptyEventLoop = false; seems to resolve this.

Can you confirm @brianc ?

Thanks @lukas-gitl. Will probably give a try with something similar to what Pratik J. said then.

var dbConnection;

exports.handler = function(event, context){
if(!dbConnection){
//init db connection
}
//otherwise reuse the connection
};

This thread was incredibly useful. I had exact same issue. Couple of things for the people using pg module with lambda. Note: I didn’t use connection pool as it is not useful with lambda.

1. Create Client and Connection as local variable not as global var client = new pg.Client(connectionString);
2. Call the client.end() before calling context.done or callback

@benoittgt No, you’re not loosing error mgm. You are still doing the connecting inside the lambda function. The variable just lives outside. E.g. you could have a boolean check flag isConnected.

Ahh ok.

Then what is happening would be correct, to disconnect the client immediately.

Lambda’s are small containers that are deployed and your piece of code is executed against, exists for short period of time. Once that piece of code is executed the process is killed. There’s nothing to share the client with to pool a connection.

So if I was using express I would just call done(); which would close the current connection but keep it in the client for pooling.

Thanks for clearing that up ❤️

Yeah calling done(client) up there is gonna disconnect the client immediately after the query. You can do that but you lose the advantage of using a pool at all. The reason to use a pool is because the overhead of establishing a connection to postgres initially is 20x as much work as dispatching a single query. I’d remove the client argument and just call done() - it wont close the connections to postgres right away (by default there’s a 1 minute timeout) but it wont open more than pg.defaults.poolSize connections either - it will reuse them. The default pool size is 20.

One thing I can see is I’m not sure exactly how aws lamda works. If every instance of a lambda script spins up its own worker process then each worker process will have its own pool, so you can end up with number-of-workers * 20 connections to postgres which might be too many. Depending on your database resources you will eventually exhaust connections - in my experience I try to keep connections to under 200. In this situation you can either reduce the pool size for each worker: pg.defaults.poolSize = 2 which will get you past the hump. Regardless of poolSize if you have enough requests simultaneously to lambda you will exhaust your connections - bascially if your lamda process group size is unbounded you have to have a way to funnel your queries through some more bounded endpoint…there is also pgbouncer but I’ve had bug reports on it from time to time - YMMV.

Hope this was helpful.