What is pgpool-II?

Pgpool-II is a middleware between the PostgreSQL server and the PostgreSQL database client. It provides the following features:

• connection pool

  pgpool-II keeps the connection to the PostgreSQL server and uses the same parameters (for example: usernames, databases, and Protocol versions) are reused when they are connected. It reduces connection overhead and increases the overall throughput of the system.

• copy

  pgpool-II can manage multiple PostgreSQL servers. Activate the replication function and create a real-time backup on two or more PostgreSQL nodes. In this way, if one node fails, the service can continue without interruption.

• Server Load balancer

  If the database is copied, if a SELECT query is executed on any server, the same result is returned. Pgpool-II utilizes the replication function to reduce the load on each PostgreSQL server. It distributes select queries to all available servers, enhancing the overall throughput of the system. Ideally, the read performance should be proportional to the number of postgresql servers. The load balancing function works best when a large number of users execute many read-only queries at the same time.

• connections exceeding the limit

  PostgreSQL limits the current maximum number of connections. When this limit is reached, the new connection will be rejected. Increasing the maximum number of connections increases resource consumption and has a negative impact on the overall system. Pgpoo-II also supports limiting the maximum number of connections, but it puts the connection into the queue instead of returning an error immediately.

• parallel query

  when using parallel query, data can be divided into multiple servers, therefore, a query can be executed simultaneously on multiple servers to reduce the overall execution time. Parallel query is very effective when querying large-scale data.

Pgpool-II uses the protocol between the PostgreSQL frontend and backend programs and transmits messages between the frontend and backend programs. Therefore, a (front-end) database application considers pgpool-II as the actual PostgreSQL database, while the latter service process considers pgpool-II as a client. Because pgpool-II is transparent to servers and clients, existing database applications can use pgpool-II without modification.

What is pgpool-II?