Managed PostgreSQL connection pooling
Connection pooling lets many client connections share a smaller number of connections to the database. Every connection to PostgreSQL runs as its own process on the database node and counts toward the plan's maximum connections, so applications that open many short-lived or mostly idle connections can run out of connections long before they run out of CPU or memory. A connection pool sits between your clients and the database: it accepts the client connections and runs their queries over a fixed set of server connections to the database.
Managed PostgreSQL connection pools use PgBouncer. You manage them on the database's Connections tab in the Control Panel, under Connection pools, or through the API, as described in How to enable PostgreSQL connection pool using UpCloud API.
Pool size
Pool size is the number of server connections the pool opens to the database, not the number of clients it accepts. Clients beyond the pool size can still connect, but their queries wait until one of the pool's server connections is free. For example, with a pool size of 2, ten clients can connect at the same time, and the database runs at most two of their queries at once.
A pool's size can be from 1 to 10,000, but the sizes of all pools on a Managed Database together can't exceed its connection limit. The connection limit is the plan's maximum connections minus three that are reserved for database maintenance, so a plan with 100 maximum connections can have pools with a total size of up to 97. The Connections tab in the Control Panel shows this limit as Connection limit. Clients that connect to the database directly, without a pool, use connections from the same maximum.
Pool modes
The pool mode decides how long a client keeps a server connection:
- Session: a client keeps a server connection from when it connects until it disconnects, even while it's idle. When every server connection is taken, other clients wait until a client disconnects.
- Transaction: a client uses a server connection only for the length of each transaction, so idle clients don't hold one. This is the most commonly used mode.
- Statement: a server connection is returned to the pool after every statement. Transactions with more than one statement aren't allowed, and a client that starts one is disconnected with the error
transaction blocks not allowed in statement pooling mode.
In transaction and statement modes, consecutive transactions from the same client can run on different server connections, so features that depend on session state, such as SET commands and LISTEN, need session mode. For the full list, see the feature table in PgBouncer's features documentation.
The Control Panel selects Session by default for a new pool. In the API, pool_mode is required.
Database and user
Each pool connects to one logical database, such as defaultdb. It can also be limited to one database user. A pool with a user set accepts only that user, and other users are rejected with login rejected. A pool without a user accepts any database user. When a user is set, the pool's connection URI includes that user's credentials.
Connecting to a pool
Pools use the same hostnames as the Managed Database, including the public and read-only replica hostnames listed in Connecting to Managed PostgreSQL, but on a port of their own. All pools on a Managed Database share that port. To connect, use the pool name in place of the database name. For example, for a pool named pool-1:
postgres://upadmin:{password}@example-mctapmghasah.db.upclouddatabases.com:11570/pool-1?sslmode=requireThe connection URI you get when you create a pool shows its port. Pool names can contain letters, numbers, underscores and dashes, can't start with a dash, and can be up to 63 characters long.
On plans with two or more nodes, connecting through the read-only replica hostname runs your queries on the replica.
A new pool can take a moment to accept connections. If a client gets no such database right after you create a pool, connect again.
Changing and deleting pools
Changing a pool's size doesn't interrupt queries that are running through it. Deleting a pool disconnects every client connected through it, with the error database removed.
PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada, and used with their permission.