Connection pooling optimization

[srtalbot]

• [x] Do we want to use RDS proxy connection pooling across Zitadel, the API, and the application client?
• [x] Review costs

[patheard]

I would recommend we start by setting up RDS Proxy for Zitadel's database cluster to manage the IdP DB connection pooling. RDS proxy is used by other CDS services and removes the need for connection pooling at the application level which has the following benefits:

• Reduces load on database as a shared pool of connections is managed.
• Reduces load on the application as no connection pool needs to be maintained.
• Improves scaling speed and stability.
• Improves failover speed if there are database issues.

It may also help address the errors we were seeing last week around database host lookup failures:

⚠️ It does have the following considerations though:

• Does not work with Aurora Serverless v1 (cannot be used by the app RDS cluster until it is upgraded to Serverless v2).
• Adds a new cost of $0.015 per RDS Proxy per ACU-hour (min 8 ACU), which would be:

  8 ACU * 24 hours/day * 30 days/month * $0.015 ACU/hour = $86.4/month # per environment