Pgpool-II Introduction

An introduction to pgpool-II.

• This article follows article pooling-Pgbouncer-vs-Pgpool-II, and describes Pgpool-II's pooling functionality.
• Pgpool-II is often used for pooling as part of a bundle, where it's other features are being used in a cluster, e.g replication, load balancing, automatic failover, parallel query.
• Pgpool-II provides session pooling only.

• Pgpool-II pooling consists of multiple processes.
• Main process - parent process of all other processes.
• creates multiple child processes.
• Child process - accepts connections from clients and performs the connection management.
• Worker process - detects streaming replication delay.
• pcp process - used by Pgpool-II itself.
• https://www.pgpool.net/docs/42/en/html/tutorial-arch.html.

• A "pool" in Pgpool-II is the set of cached connection(s) that a child process stores that can be re-used for connections.
• The process can only store 1 connection per user:database pair and can store up to max_pool connections of unique pairs.
• The child process builds the cache up from previous connections that it served.
• If a new connection is hosted by another process, it does not have access to the other process' cache and pool.
• Therefore, there is a random based element of whether a connection is hosted by a process with a matching connection cache, meaning a connection can be re-used.

Pgpool-II Config definitions

• num_init_children

• Number of preforked Pgpool-II server child processes.

• It is also the maximum number of concurrent client connections that Pgpool-II will handle at any time.

• All new connections after num_init_children number of active concurrent connections get queued in the listen queue of Pgpool-II and eventually they can fail with connection timeout depending on client settings.

• Default 32.

• max_pool

• Maximum number of unique database + user pair connections that each child process can keep cached to the database as idle connections (after the process has facilitated those pair connections).

• These cached connections can be potentially reused, depending on database + user pair occurrences for new connections (IF a new connection uses a database + user pair that is cached AND that same child process picks up the new connection).

• Max number of database connections (active & idle) = num_init_children * max_pool.

• These values should be kept in line with the following formula.

• num_init_children * max_pool <= (max_connections - superuser_reserved_connections) (no query canceling needed).

• To see num_init_children and max_pool demonstrated in depth, please see article Pgpool-II-max-connections.