0

My employer currently have multiple Postgres instances (one per customer, having a unique site code) which are physically separated on customer premises. Each customer has 1-4 databases running inside an instance, with each database containing 20+ schemas.

We're trying to pull these databases together under a single Postgres instance, either on premise or with a cloud provider (think RDS), so that we can simplify access/control. Customer will continue to access the database however we will introduce a shared RESTful service layer, where the specific database will be targeted using a site code added as part of each API's URL path. In order to meet our SLA on performance (90% under 1s) and to keep resource usage down, we intend to make use of connection pooling in the service layer.

The main issue we're facing is that Postgres requires a database during connection meaning that we will have to have a connection pool per customer which will use a lot of resources inefficiently. As we're not using the resources efficiently we may then need to have more instances of the service layer, introducing more cost and complexity to the architecture. Although it's possible to effectively "shard" customers across multiple instances of the service layer it doesn't address the real problem which is the inefficient use of connections!

Is there another solution I've not considered? Perhaps a way to have a single connection pool where requests are forwarded to specific databases? Or perhaps dynamic connection pools which can dynamically resize based on load?

Thanks

raven-king
  • 101
  • 1
  • One database per customer is an unusual design that won't scale. What happens if you get a million customers? Can you put all customers onto one database? Having multiple connection pools probably doesn't mean you need more app server instances. Have a look at the RDS Proxy service to see if you think it might help https://aws.amazon.com/rds/proxy/ – Tim Jun 13 '22 at 19:21
  • @Tim Thanks for the info! Like many services with AWS I was just not aware this existed! We're unfortunately stuck with the 1-4 db per customer due to legacy and I don't think there's a way to avoid it without a massive amount of effort, unless anybody knows of a way to condense multiple databases, each with multiple schemas, into a single database, with little effort :-) – raven-king Jun 14 '22 at 10:09
  • Sounds like you need a new version of the service that's made to scale, if you want to scale. Possibly a complete rewrite. – Tim Jun 14 '22 at 19:30

1 Answers1

0

AWS RDS Proxy might be a practical way to reduce connection requirements.

Tim
  • 31,888
  • 7
  • 52
  • 78