PgBouncer on Vultr Managed Databases for PostgreSQL
Updated on January 9, 2025PgBouncer is a high-performance connection pooler for PostgreSQL that helps optimize database connections, especially in high-traffic environments. It sits between client applications and the PostgreSQL database, reducing the overhead of creating and destroying connections by pooling them. This allows multiple clients to share a smaller number of database connections, significantly improving the efficiency of resource usage and reducing latency.
It supports different pooling modes, including session pooling, transaction pooling, and statement pooling, each offering different trade-offs in terms of connection reuse. It is lightweight and designed to have minimal memory and CPU overhead, making it well-suited for systems where the number of client connections to the database can vary significantly.
Different Modes of Pooling
Session Pooling: A client connection is assigned a PostgreSQL backend connection for the entire duration of the client session. Once the client connects, the same backend connection is maintained for the entire session, even if the client executes multiple queries. This method is ideal for applications that maintain long-lived connections, where reusing a connection for the entire session can improve performance.
Transaction Pooling: Each database connection is assigned to a client only for the duration of a single transaction. Once the transaction is completed, the backend connection is returned to the pool and can be reused by another client. This method is useful for applications that perform many short-lived transactions, as it allows for maximum connection reuse without the overhead of maintaining an open connection for the entire session.
Statement Pooling: Allows a client to borrow a backend connection only for the duration of a single query (statement). After the statement is executed, the backend connection is returned to the pool, and the client can reuse a different connection for subsequent queries. This method provides the highest level of connection reuse and is suitable for applications with many lightweight queries that don't require maintaining any session or transaction state.
PgBouncer With Vultr Managed Databases for PostgreSQL
Vultr Managed Databases for PostgreSQL comes with PgBouncer pre-enabled and configured, allowing users to easily create connection pools to optimize database connections, extension-level configuration for PgBouncer cannot be modified. This built-in connection pooling functionality helps enhance performance by reducing the overhead of creating and closing database connections, which is particularly beneficial in environments with high query volumes.
By default, 3 connections are reserved for management with any subscription plan of Vultr Managed Databases for PostgreSQL. Each subscription plan has a fixed upper limit on the number of connections. For example, the base plan includes a total of 100 connections, with 3 reserved, leaving 97 available for client connections, in situations where more number of connections are required the users have to upgrade their subscription plan.
When a request is sent to the default port of the database, it is handled directly by PostgreSQL. However, if a request is directed to one of the connection pool ports, it is routed through PgBouncer. To connect to PostgreSQL via PgBouncer, users must utilize the port specified on the connection pool page.
