The Pgpool II Community releases a major version every year and minor versions several times in a year. In the last few releases, a lot of emphasis and focus was given to improving the existing functionality of pgpool II, making it more stable, robust and able to handle complex real-world scenarios. The features that have gone through a major overhaul are watchdog, which provides high availability for pgpool II instances; pgpool II performance with extended queries, making backend node failover Quorum aware; and other useability features. As a result of the work done in the last few years, pgpool II is more and more stable, resilient, and ready to handle real workloads.
Before I list the use cases where EDB recommends the usage of pgpool II, the following is a brief description of the recommend pgpool II features.
1 - Connection Pooling
Pgpool-II saves connections to the PostgreSQL servers and reuses the connections whenever a new connection with the same properties (i.e. username, database, protocol version) is requested. This reduces connection overhead and improves the system's overall throughput.
2- Load Balancing
If you have a Postgres cluster set up with streaming replication with primary and multiple read-only nodes, Pgpool II provides read scalability by distributing the read queries across the read-only nodes and write queries to the primary node.
3- Watchdog HA
The Pgpool II Watchdog provides high availability between pgpool II nodes. If the primary pgpool II node goes down, the client queries are automatically shifted to a new pgpool II primary. One of the standby nodes is automatically made the primary node when the pgpool II primary node goes down.
Pgpool II Watchdog handles the split brain scenario using the Quorum.
4- Backend Node Failover
The Pgpool II provides the functionality of automatically managing the backend node failover and it also supports the Quorum capability. EDB recommends EFM for database failover. In scenarios where EFM cannot be used, pgpool II can also be used to manage failover of database nodes.
Pgpool II supports other features like replication which are currently not part of EDB's pgpool II support policy.
Use Cases
1- Pgpool II load balancing can be used when the user is looking for read scalability. The pgpool II load balancing feature will distribute the read queries across the standby nodes and write queries will be sent to the primary node. The specific configuration on how the load should be divided across nodes is configurable in pgpool II.
2- The Pgpool II watchdog feature is recommended if the user has deployed multiple pgpool II nodes. The watchdog feature will provide HA between pgpool II nodes by automatically switching the queries to pgpool II stand-by when the primary pgpool II node goes down. The pgpool II instances can be deployed on the same machine as the client application or database server or they can be deployed on a separate machine; the architecture is dependent on the user workload.
3- The pgpool II connection pooling feature can be deployed when the user has several client connections generating incoming traffic, and the connection pooling feature can be used to utilize the existing connection from the pool instead of spawning a new connection for every client.
4- In use cases where the user needs both read scalability and HA, the pgpool II load balancing feature and watchdog feature can be used together to provide the desired functionality.
5- In cases when EFM cannot be used to provide database node failover, the pgpool II backend node failover feature can be used to manage backend node failover.