Relationship between max pool, num init children, and max connections

Gurjeet Singh (last updated: 22 August 2012)

max_pool parameter configures how many connections to cache _per child_. So if num_init_children is configured to 100, and max_pool is configured to 3, then pgpool can potentially open 300 (=3*100) connections to the backend database.

A child process opens a new backend connection only if the requested [user,database] pair is not already in the cache. So if the application uses only one user to connect to only one database, say [pguser1,pgdb1], then each child will continue to reuse the first connection and will never open a second connection, so in effect pgpool will open no more than 100 backend connections even though max_pool is set to 3.

But if the application uses more than one pair of [user,database] connection parameters, then each child will cache the connection it might already have for another pair, and open a new backend connection for the requested pair.

For eg., if the application now uses these 4 pairs: [user1,db1] [user1,db2] [user2,db1] [user2,db2] to connect to pgpool, then each child process can cache up to 3 connections for the first 3 different pairs it receives connection requests for. But as soon as it receives a request for the 4th pair that it does not yet have a connection for, then it will disconnect the oldest connection in the cache and open a new connection for the 4th pair.

As we already know that there's no guarantee as to which child process will handle an incoming connection request, max_pool tries to improve the performance a little bit by caching connections of different pairs, in the hopes that an incoming connection request might match one of the connections cached by the child process. But this also causes an explosion in the number of connections that pgpool would request from the database.

So, in order to guarantee that the application connection requests are never rejected, and that the connection requests wait until a database connection is available, the following condition should be met:

max_pool*num_init_children <= (max_connections - superuser_reserved_connections)

If the application uses superuser connections (which is not recommended), then the condition is reduced to:

max_pool*num_init_children <= max_connections

Setting max_pool to 1 will guarantee that the number of database connections opened by pgpool child processes never exceeds the num_init_children value. If for performance reasons, as explained above, you do wish to set max_pool to more than 1, then max_connections will also have to be increased accordingly so that application connection requests do not get denied.