DB Connection Pooling – Connecting on demand instead of holding connections open

Questions / Help
, ,
1

I’m using an AWS Aurora PostgreSQL Serverless v2 database cluster with Ecto. It can auto-scale between 0.5 and 16 ACUs (Aurora Capacity Units), and is priced per ACU-hour. I’m currently refactoring my GenServers to avoid touching the database unless absolutely necessary (e.g., switching all periodic tasks that use the DB to things that are event-triggered).

Once that’s done, I’ll still have one problem – persistent connections. Even when completely idle, maintaining open connections to the cluster prevents it from scaling down as far as it could otherwise. For example, in my tests, shutting the app down entirely allows the DB to scale down to its minimum 0.5 ACU. But just starting the Repo process, even with no database activity, causes it to scale up to 4 ACU and stay there.

What I’d like is some kind of connection pool that looks to the consumer like a normal pool, but where each “connection” doesn’t actually open a socket until it needs to, and releases it after a given idle period. I found this thread from 7 years ago that seemed to be the beginnings of something like that, but it doesn’t seem to have gone anywhere and my other searches have come up empty.

Is this:

  1. A solved problem? (If so, where can I find info on that?)
  2. A terrible idea? (I get why persistent connections are desirable, but my need for auto-scaling outweighs my need to minimize connection latency.)
  3. Something else?

With serverless/auto-scaling/pay-as-you-go infrastructure becoming more and more prevalent, I think something like this (either the implementation of it, if it doesn’t exist, or some easy-to-find documentation of it, if it does) would be a big benefit to people targeting cloud deployment.

2

How apropos. I started that thread way back then, but gave up on that idea because we stopped using a “database per tenant” and moved to using a “schema per tenant” and that way we were able to reduce the number of databases down to 6, thus making the consistent connection problem a non-issue.

It’s apropos because we’re about to move to Aurora, specifically for the auto scaling of replicas. Very interesting about connections causing it to scale up. I would have thought it would be triggered off of CPU usage. And disappointing to hear that holding connections causes it to not scale down.

Also, I was under the impression that you’re not supposed to connected to the db or replicas directly, but instead go through a proxy (some kind of modified PgBouncer type thing) which allows holding a persistent connection, but routes (transaction) requests to an active replica.

3

Ahh, never mind about the proxy stuff. We’re going to use Aurora RDS, I didn’t catch that you’re using Aurora Serverless.

But hmm, when you think about… PgBouncer is essentially a backend connection cache. The frontend connections (to your app) are persistent. But the backend connections (to your database) are disconnected after a configurable idle timeout. Just make sure it’s in “transaction mode” and not “session mode”!

1 Like
4

Aurora Serverless v2 actually does support proxies, so maybe that’s the way to go. I’ll also look into PgBouncer. It’ll be great not to have to figure it out on the DbConnection side.

Thanks!