[YSQL] Server side connection management

[kneeraj]

Description

Jira Link: DB-5180

Design Doc: WIP. To be shared.

The server-side connection management feature greatly enhances the capacity of a YugabyteDB cluster to handle a significantly larger number of connections compared to the current limitations. Moreover, this feature enables efficient routing of queries to the appropriate nodes, eliminating unnecessary node hopping and resulting in reduced latency during query execution. It needs to be ensured that addition of such values does, neither adversely affect the performance of the database nor introduces new sql limitations in YugabyteDB.

The key functional and related non functional goals are:

• Scaling large number of database connections
• Database aware query routing
• No regression in terms of sql limitations
• Non functional performance goals
  • Marginal performance drop in single connection latency
  • Idle connections should not adversely affect the active workload performances
  • Increasing connections should not lead to exponential degradation in performance

Phase 1 - Scaling and non functional performance requirements

Feature	Status	Comments
Source code and build Integration of Odyssey with YugabyteDB	⬜️	Odyssey being used as a base product which will be modified as per YugabyteDB's need
Start cluster with connection management enabled	⬜️	Startup flags to opt-in or opt-out. Default is to not start the connection manager for now
Using single server connection pool for multiple database/user combination	⬜️	Ensuring error free context switching on the pooled server connection when database/user combination changes
Security passthrough	⬜️	Modify Odyssey to get authentication done on the database side instead of on the Odyssey
Ensure zero sql limitation	⬜️	Upstream Odyssey has certain sql feature limitation in transaction mode. YugabyteDB enhances Odyssey to eliminate such limitations

Phase 2 - Support for query routing and Metrics

Feature	Status	Comments
Database aware query routing	⬜️	For colocated databases redirect the queries to the node hosting the leader tablet for that database
Metrics	⬜️	Connection manager's usage, performance and health related stats

Phase 3 - Memory tracking

Feature	Status	Comments
Memory tracking	⬜️	Track memory usage of the connection manager and enforce memory usage limits