EDB pgPool II

Query routing and connection pooling for Postgres-based digital business solutions

EDB pgPool II

EDB pgPool II

pgPool II provides robust query routing and connection pooling for Postgres-based solutions. pgPool’s query routing capability can be used to create read-scalable Postgres clusters that complement EFM’s high availability capabilities.

Overview

Architecture

EDB pgPool II Architecture

Using pgPool for read scalability

Using pgPool for read scalability

pgPool provides read scalability for Postgres streaming replication clusters, consisting of a master node and one or several read replicas. pgPool parses the queries and reroutes the read queries to the available read replicas in the cluster. The pgPool configuration file allows the DBA to control the load balancing by adjusting the "weight" setting for every backend node. The weight parameter is used to determine the ratio of distributing the read queries among the available read replicas - write queries are always sent to the master node. The pgpool configuration can also be adjusted to prevent read queries being sent to the master node.

Benchmarks have shown that pgPpool can achieve significant scalability for read-intensive workloads. For example, a cluster with five read replicas can result in three to four times as many read queries being processed without impacting the write performance on the master.

When not to use pgPool

When not to use pgPool

It is not advisable to use pgPool when the database can fit in the system memory. Using pgPool when the database fits in memory causes additional overhead. pgPool performance is better with the simple query protocol as compared to with extended query protocol, however enhancements were made to pgpool II 3.5 to improve performance with extended query protocol.

EDB recommends pgPool for several use cases

  • Load balancing​ (read only)​
  • Connection pooling
  • High availability​ (3.5 and above only)​
  • Connection limits

pgPool provides other capabilities, such as replication, that EDB does not recommend using at this point as they are not mature enough for production environments.

pgPool, pgBouncer and the multi-string JDBC driver

pgPool, pgBouncer and the multi-string JDBC driver

pgBouncer is a lightweight connection pooler that provides connection pooling to a single database server only. It does not provide for query routing, load balancing or of high availability support.

The Postgres JDBC driver also provides a multi-string failover capability and a capability to differentiate between read-only connections and read/write connections. The application will try to connect to each database server in the list of connection strings, until it finds a server that accepts the connection request. If the application can differentiate between read and read/write connections, than this also provides a powerful and scalable load balancing and high availability mechanism.

Resources