5

We are using an RDS instance in AWS, it is POSTGRES 13. We have Node-based Lambdas that return and post data to the database. Currently they are opening and closing a single connection per transaction and we want to optimise this by implementing connection pooling.

There is an AWS service specifically designed for handling DB connections called AWS RDS Proxy BUT it isn't compatible with POSTGRES 13. We are looking for alternative approaches and would really appreciate any insight in this regard.

We were thinking about pulling the DB connection outside of the Lambda handler so that it maintains state (until the Lambda gets removed) BUT this wouldn't be able to close its connection to the DB and we were concerned about filling up all the available connections. This may or may not be the case.

Much thanks,

Sam

jtoberon
  • 8,706
  • 1
  • 35
  • 48
SamBrick
  • 681
  • 1
  • 8
  • 33
  • 1
    Josh from AWS here. Apologies that you even have to make this decision because RDS Proxy doesn't support PG13 yet. We recognize that Proxy needs to support all of the engine versions that RDS itself supports, and we're hard at work on this problem. For others who land on this page in the future, until Proxy supports the same versions as RDS itself, please check the RDS Proxy docs for a list of supported versions. – jtoberon Sep 29 '21 at 18:37
  • @jtoberon Thanks for the info. Do you have any idea how long it might be for support of PG13 at all? – SamBrick Sep 30 '21 at 08:15
  • Unfortunately, I can't share this information in this forum. Engine versions and AWS regions are our top priority though. – jtoberon Sep 30 '21 at 18:19
  • RDS Proxy support for MySQL 8 and PostgreSQL 12 just shipped, and official docs have been updated. – jtoberon Nov 23 '21 at 05:07
  • RDS Proxy support for PostgreSQL 13 just shipped, and official docs have been updated. – jtoberon Apr 04 '22 at 23:19
  • 1
    RDS Proxy for MariaDB up through 10.5 just shipped. At this point, the gaps in engine versions supported by RDS open source and Aurora are PG14 and MariaDB 10.6. I'm going to stop updating comments here, so please check the RDS Proxy official docs going forward. – jtoberon Jul 26 '22 at 22:24

2 Answers2

6

Here's some random thoughts that might help.

  • Is there any special reason that you need to use PostgreSQL 13? If you could go back to 10/11 then you would be able to use RDS Proxy and then your problems would be solved
  • Similarly, if you wanted to use RDS Aurora then you would be able to combine this with RDS Proxy
  • You can use PgBouncer (or another third-party proxy - there's loads out there) but to get this to work with RDS is going to be quite painful. Most of them will require you to spin up an ECS Instance, run the proxy on this, and then connect to the proxy - this is deliberately vague, as each proxy works slightly differently
  • You could increase the number of database connections allowed, but this is generally a bad idea as it's just kicking the can down the road
  • You could look into horizontal or vertical scaling solutions, but again these won't solve the underlying problem, just make it not so apparent and take more concurrent hits before it rears up again
  • You could stick a queue in front of your database, SNS or SQS, and only have one connection. This is fine if you're only ever writing data to the database and you're not too bothered about how long it actually takes to complete. However, it introduces an entirely different architecture, and one that defeats many of the good points of using RDS in the first place
  • If you need to read data then you can use read replicas, but this is just another form of horizontal scaling/ sharding
  • Similarly, you could ditch using Lambdas, as these are the "angry swarm of bees" that cause the problem in the first place, but again, it's a big architectural change

In my experience getting a connection pooling solution should be good enough to solve most use cases, but there are still limits when you're combining two technologies together that aren't 100% compatible.

Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
  • Thanks @Richard Hansell - I really appreciate your responses and insight, it's super useful. We are thinking of potentially a different approach. This would involve pulling the DB connection code outside of the Lambda handler. In this way the container for the Lambda would still maintain the connection until the Lambda was deleted. When this happens the connection would eventually close. The concurrency abilities of Lambdas and the potential number of max connections on Postgres RDS (with our box and memory size) should mean that the solution can easily handle the number of requests (for now) – SamBrick Aug 10 '21 at 09:13
  • 1
    @SamBrick make sure you have something to close the DB connections that the Lambdas open, as it won't be closed automatically when the Lambda container is frozen. We went down this path and it's not ideal - too many open connections if you have lambdas with high concurrency. We are currently looking to switch to pgBouncer connection polling instead. – Slav Sep 24 '21 at 09:11
  • 1
    @Slav Thanks for the feedback. We don't have a high concurrency at this point so it hasn't been a problem so far. Do you have a sense of how long the open connections that get left open eventually take to close? – SamBrick Sep 30 '21 at 08:14
  • 1
    Not sure, but long enough to lock us out of the DB server on several occasions, thus having to reboot it. We have a daemon to close stale connections that have been idle for more than a minute. – Slav Sep 30 '21 at 12:57
0

RDS proxy now supports for Postgres 13

https://aws.amazon.com/about-aws/whats-new/2022/04/amazon-rds-proxy-supports-postgresql-major-version-13/

Selvakumar Ponnusamy
  • 5,363
  • 7
  • 40
  • 78