Skip to main content

Connection Pool

The process of opening connections in a database comes at a significant cost in terms of computing resources and time. An effective strategy to mitigate these costs is the use of a connection pool, which maintains a set of open connections that can be reused — reducing the overhead associated with frequently opening and closing connections.

Figure 1 - Connection Pool
Figure 1 - Connection Pool

PgBouncer​

PgBouncer is the database connection management component of PostgreSYS, acting as an intermediary between clients and the PostgreSQL database instance. Its main features include:

  • Flexibility: On the same server, it is possible to configure connection pools for databases hosted on different servers.
  • Availability: Allows configuration changes and updates without interrupting active client connections in the pools.
  • Optimization: Reduces the overhead caused by excessive connection handling on the PostgreSQL server, improving resource usage.
  • Ease of Use: Simple and efficient configuration.
  • Security: Supports multiple authentication methods.
  • Performance: Ideal for environments with a high number of concurrent clients.

Connection Reuse​

PgBouncer reuses connections, to optimize system efficiency and performance. There are three modes of connection reuse modes:

  1. Session: The connection is assigned to the client for the entire session. Upon disconnection, the connection is returned to the pool. This is the default mode.
  2. Transaction: The connection is used only during a transaction. When the transaction is completed, by a COMMIT or ROLLBACK, the connection is returned to the pool.
  3. Statement: The connection is returned to the pool immediately after an SQL statement is executed. This mode does not support multi-statement transactions.

Authentication​

PgBouncer acts as an intermediary between client applications and the PostgreSQL server, handling authentication and connection management. It ensures that only authorized clients can access the databases within the instance.

PgBouncer supports the same authentication mechanisms as PostgreSQL, including host-based access control.

Authentication Process​

When a client requests a connection to a PgBouncer pool, the following steps are performed:

  1. Credential Verification PgBouncer checks whether the provided username and (if required) password are sufficient for authentication.

  2. Lookup in userslist.txt PgBouncer examines the userslist.txt file (or another configured file), which contains username/password tuples:

      • If the username is found and the password matches, authentication succeeds. PgBouncer assigns an existing connection from the pool if available, avoiding a new connection to PostgreSQL.
    • ❌ If the username is not found, PgBouncer proceeds to the next step.

  3. Pass-through Authentication (Auth Query) If configured, PgBouncer connects to PostgreSQL using a predefined user (from userslist.txt) and runs an authentication query:

      • If the query retrieves the correct password and it matches the client's input, authentication succeeds.

  4. Connection Assignment After successful authentication, PgBouncer checks for a cached connection matching the username/database pair:

      • If found, the connection is reused.
    • ❌ If not, PgBouncer opens a new connection to the PostgreSQL server, provided it does not exceed the configured connection limits.