Pgpool-II manages a pool of PostgreSQL servers to achieve some features that are not available with single PostgreSQL installation. The features include:
Pgpool-II provides a high availability (HA) feature by using multiple PostgreSQL servers so that it automatically removes broken server from the server pool to continue the database task. This is called automatic failover. Also Pgpool-II offers an HA feature for Pgpool-II itself, called Watchdog (see Chapter 4 for more details). Moreover Pgpool-II hires sophisticated quorum algorithm to avoid false positive errors and split brain problem to make the whole HA system highly reliable. See Section 5.14.6 for more details.
Pgpool-II distributes read queries over multiple PostgreSQL servers to gain higher performance. This feature is called load balancing. Write queries are sent to either the primary server (in streaming replication mode) or all servers (in native replication mode and snapshot isolation mode). See Section 3.3.2 for more details about these mode. In any case, Pgpool-II automatically distinguish the read queries from the write queries.
Besides these essential features, Pgpool-II also provides useful features such as:
Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.
Pgpool-II can perform online recovery of database node by executing one command. When the online recovery is used with the automated fail over, a detached node by fail over is possible to attach as standby node automatically. It is possible to synchronize and attach new PostgreSQL server too.
There is a limit on the maximum number of concurrent connections with PostgreSQL, and new connections are rejected when this number is reached. Raising this maximum number of connections, however, increases resource consumption and has a negative impact on overall system performance. Pgpool-II also has a limit on the maximum number of connections, but extra connections will be queued instead of returning an error immediately. However, you can configure to return an error when the connection limit is exceeded (4.1 or later).
Watchdog can coordinate multiple Pgpool-II, create a robust cluster system and avoid the single point of failure or split brain. To avoid the split brain, you need at least 3 Pgpool-II nodes. Watchdog can perform lifecheck against other pgpool-II nodes, to detect a fault of Pgpool-II. If active Pgpool-II goes down, standby Pgpool-II can be promoted to active, and take over Virtual IP.
In memory query cache allows to save a pair of SELECT statement and its result. If an identical SELECTs comes in, Pgpool-II returns the value from cache. Since no SQL parsing nor access to PostgreSQL are involved, using in memory cache is extremely fast. On the other hand, it might be slower than the normal path in some cases, because it adds some overhead of storing cache data.
Pgpool-II speaks PostgreSQL's backend and frontend protocol, and relays messages between a backend and a frontend. Therefore, a database application (frontend) thinks that Pgpool-II is the actual PostgreSQL server, and the server (backend) sees Pgpool-II as one of its clients. Because Pgpool-II is transparent to both the server and the client, an existing database application can be used with Pgpool-II almost without a change to its source code.
Pgpool-II works on Linux, FreeBSD, and most of the UNIX-like architectures. Windows is not supported. Supported PostgreSQL server's versions are 7.4 and higher (some of the features may not work with older versions of PostgreSQL). You must also make sure that all of your PostgreSQL servers are using the same major version. In addition to this, we do not recommend mixing different PostgreSQL installation with different build options: including supporting SSL or not, to use --disable-integer-datetimes or not, different block size. These might affect part of functionality of Pgpool-II. The difference of PostgreSQL minor versions is not usually a problem. However we do not test every occurrence of minor versions and we recommend to use exact same minor version of PostgreSQL.
There are some restrictions to using SQL via Pgpool-II. See Restrictions for more details.