How to Manage Connection Pools Limited Availability

Managed Databases are currently in limited availability. Learn more.

When a client connects directly to a PostgreSQL database, the server forks a process to handle authentication tasks and subsequent communication. Each individual connection requires roughly 10 MB of RAM and uses this memory until the connection is closed. In addition to the memory cost, the total number of connections is fixed and when all connections are used, additional clients cannot connect.

Connection pooling mitigates performance issues by funneling client connections into a pooling application, thereby reducing the number of processes a database has to handle at any given time. The pooling application then passes on a limited number of connections to the database and queues additional connections to be delivered as space becomes available. With connection pooling, applications won’t need to manage the actual connection. They will only need to connect to the pool and disconnect once the task is complete.

A connection pool may be useful if your database:

  1. Typically handles a large number of idle connections
  2. Has wide variability in the possible number of connections at any given time
  3. Is dropping connections due to max connection limits
  4. Is experiencing performance issues due to high CPU usage

It is also possible to prioritize databases within a cluster by managing the number of processes that are available to serve client connections to databases. For example, a user with a the smallest instance type (1GB-1vCPU) who has two databases in the cluster could group connections based on database requirements as follows:

database_a => connection_pool_1 (12/22 connections)
database_b => connection_pool_2 (7/22 connections)

In this example, database_a has a larger subset of processes dedicated to it when compared to database_b.

Behind the scenes, DigitalOcean’s managed PostgreSQL clusters use PgBouncer, a popular PostgreSQL connection pooling application. Clusters provide 25 connections per 1 GB of RAM. While 3 connections are reserved per cluster for maintenance, all remaining connections can be allocated to connection pools.

Creating a Connection Pool

To add a connection pool, follow the Databases link in the control panel, which leads to the database home page.

From there, click the database to which you’d like to add a connection pool, then click the Connection Pools tab at the top. If you haven’t added any connection pools yet, click Create a Pool. This will open a form to input some basic settings about the pool:

Screenshot of pool creation form

Pool Name

The Pool Name doesn’t affect how your pool functions, but it must be unique and it cannot be edited one once the pool is created. To rename a pool you must delete it, create a new one, and update the connection information in your application.

Once you’ve named the pool, select the database you’re creating the pool for, as well as the user on that database the pool will use to authenticate.

Pooling Modes

Next, select the mode of your connection pool. Transaction mode is the most common option and is selected for you by default. This setting cannot be modified after creation, so if you’re not sure which to choose, we recommend transaction mode for the general use case.

Below is a detailed description of each connection pooling mode available for DigitalOcean PostgreSQL Databases:

  • Transaction

    This mode allows each client to use the pool for the duration of a single transaction at a time. If more transactions are being sent in than your pool has available connections, the additional transactions are queued to be run once connections are available.

    Transaction mode is useful when you have a large number of clients that maintain idle connections. They can maintain their connection to the pool without taking up a connection with PostgreSQL, meaning you get all the benefits of a low number of active connections while avoiding the need to terminate idle clients.

  • Session

    This mode allows a client to continue issuing requests until it disconnects from the database, maintaining that client’s connection the entire time. If more clients connect than your pool is configured to handle, those additional clients are queued up to connect once an existing client disconnects.

    Session mode is most useful if your application makes use of prepared statements, advisory locks, listen/notify, or other features which operate on a session level rather than a transaction level.

  • Statement

    This mode is the most restrictive and only allows a single statement at a time before moving on to the next in the queue. This means queries with multiple statements are not allowed and will fail.

    Statement mode is useful primarily in situations where you’re forcing autocommit for each client, meaning each transaction is limited to a single statement. That’s because transactions with multiple statements will be rejected by the pool.

Pool Size

The maximum pool size varies based on the size of the cluster. We recommend starting with a pool size of about half your available connections, then adjusting based on performance. While there’s no perfect formula for determining how large your pool should be, there are a few good guidelines to keep in mind:

  1. A large pool will stress your database at similar levels as that number of clients would alone.
  2. A pool that’s much smaller than the number of clients communicating with the database can act as a bottleneck, reducing the rate at which your database receives and responds to transactions.

If you’re seeing slow query responses, we recommend checking the CPU usage on the database’s Overview tab. If CPU usage is high, we recommend decreasing your pool size. If the CPU usage is low, we recommend increasing your pool size.

When you’re ready, click Create Pool. Once a pool exists, you’ll see the number of backend server connections remaining and a table of all the pools that exist and their settings.

Screenshot of pool creation form

Connecting to the Pool

Once you’ve created a connection pool, you must use its specific information to force clients to connect to it. This is available by clicking the Connection details link, which provides the data in three formats: Connection parameters, Connection string, and Flags.

Screenshot of pool creation form

For more information on SSL modes, see How to Secure Clusters.

Your database user and password is a plaintext part of the connection settings, so ensure it’s not exposed publicly.