ThePgpoolII 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 ofpgpoolII, 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 pgpoolII instances;pgpoolII 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,pgpoolII is more and more stable, resilient, and ready to handle real workloads.
Before I list the use cases where EDB recommends the usage of pgpoolII, the following is a brief description of the recommend pgpoolII 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,PgpoolII provides read scalability by distributing the read queries across the read-only nodes and write queries to the primary node.
3- Watchdog HA
ThePgpoolII Watchdog provides high availability between pgpoolII nodes. If the primarypgpoolII node goes down, the client queries are automatically shifted to a newpgpoolII primary. One of the standby nodes is automatically made the primary node when thepgpoolII primary node goes down.
PgpoolII Watchdog handles the split brain scenario using the Quorum.
4- Backend Node Failover
ThePgpoolII 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, pgpoolII can also be used to manage failover of database nodes.
PgpoolII supports other features like replication which are currently not part of EDB'spgpoolII support policy.
1- PgpoolII load balancing can be used when the user is looking for read scalability. The pgpoolII 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 pgpoolII.
2- The PgpoolII watchdog feature is recommended if the user has deployed multiplepgpoolII nodes. The watchdog feature will provide HA betweenpgpoolII nodes by automatically switching the queries topgpoolII stand-by when the primarypgpoolII node goes down. ThepgpoolII 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- ThepgpoolII 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, thepgpoolII 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 pgpoolII backend node failover feature can be used to manage backend node failover.