Connection pooling

Connection pooling helps optimize database performance by efficiently managing client connections to your RayDB PostgreSQL cluster. It reduces the overhead of opening and closing connections, improves scalability, and enhances resource utilization.

Why Use Connection Pooling?

• Reduces Connection Overhead: Establishing a new connection is expensive; pooling reuses connections.
• Improves Query Performance: Reduces latency by maintaining persistent connections.
• Prevents Connection Exhaustion: Limits the number of open connections to prevent overloading the database.

Enabling and Configuring Connection Pooling

RayDB uses PgBouncer as the default connection pooler to efficiently manage database connections. Users can activate or deactivate PgBouncer as needed.

Activating PgBouncer

1. Log in to the RayDB Dashboard.
2. Navigate to the Cluster Settings.
3. Locate the Connection Pooling section.
4. Toggle Enable PgBouncer to activate connection pooling.

Configuring PgBouncer

PgBouncer operates in transaction mode by default, ensuring efficient pooling and reduced connection overhead. Default settings include:

pool_mode = transaction  # Available options: session, transaction, statement
max_client_conn = 100
default_pool_size = 20

Connecting via PgBouncer

After enabling PgBouncer, use the provided pooled connection string from the RayDB Dashboard:

psql "postgres://user:password@cluster-hostname:6432/database"

Best Practices for Connection Pooling

• Enable PgBouncer for high-concurrency applications.
• Limit max connections at the database level to avoid excessive resource usage.
• Monitor connection usage using PostgreSQL’s pg_stat_activity:

  SELECT datname, usename, state, count(*) FROM pg_stat_activity GROUP BY datname, usename, state;

• Use PgBouncer for high-concurrency applications.
• Limit max connections at the database level to avoid excessive resource usage.
• Tune pool settings based on workload, such as default_pool_size and max_client_conn.
• Monitor connection usage using PostgreSQL’s pg_stat_activity:

  SELECT datname, usename, state, count(*) FROM pg_stat_activity GROUP BY datname, usename, state;

For more insights, refer to Query Optimization.