Introduction to Connection Pooling: PGBouNCER and PGPOOL-II

Preamble

The usual suspects – PgBouncer and pgpool-II

Two products stand out when discussing separate pooling servers in the Postgres context: PgBouncer and pgpool-II. Both products are written in C and appear to be actively maintained, though pgpool-II seems to get more attention because it has more ambitious features. In addition to source code, packages for popular Linux distributions are also offered. Configuration file startup and deployment are both very straightforward. You should send your clients to the pools instead of the actual DB, since both use non-standard ports by default (6432 and 9999, respectively). Based on the available online documentation (PgBouncer in its current version 1.7.2 and pgpool-II in version 3.5.4, the latter with sadly some outdated parts), I compiled the following feature outline so that you can decide for yourself whether it is suitable for your needs.

pgpool-II features

• pooling of connections
• incoming connection queueing
• balancing the load on SELECT statements
  * A built-in Postgres query parser with advanced features, including the use of ‘/*NO LOAD BALANCE*/’ as an Oracle-style hint.
  * Various weights for various servers
  * Function white-listing and black-listing (because functions are called via SELECT)
  * Configurable replication delay threshold; in the event of an overflow, the master will be used.
• Statement-level replication using multiple (master) servers to distribute the same query
  * Compares the quantity of impacted rows
  * Default handling for the timestamp column when performing inserts
  * Constants will replace CURRENT_TIMESTAMP, CURRENT_DATE, and now().
  * A method (table locking) for guaranteeing identical IDs for inserts into tables with serial numbers
• HA attributes
  * Auto failover (customizable scripts for callbacks)
  * Gently add and remove replicas
  * A watchdog that can move a virtual IP address in the event that Pgpool itself fails.
  * Provisioning of replicas with a single click or command (example scripts provided)
• cached querying
  * Relay in-memory or “memcached”
  * Time-based invalidation schemes and DML
• complex options for pool management
  * fundamental pool status using standard SHOW commands
  * for starting, stopping, etc., use the command line utilities pcp_
  * the pgpool_adm extension for SQL-based pool management
  * the pgpoolAdmin web interface.
• SSL assistance
• Optional access control and authentication layer (pg_hba.conf format)
• Most settings have an online configuration reload.

pgpool-II caught out

• only session-based pooling
• pooling for just one cluster
• no queries with multiple statements
• a failover will occur if you stop a backend using pg_terminate_backend()!
• no translations for multi-byte encoding; client must be aware of server encoding

PgBouncer features

• lightweight connection pooling (event-based architecture)
• 3 modes of pooling
  * session (the default)
  * the transaction
  * declaration
• graceful connection re-direction to a new node (for non SSL connections, *nix only)
• can combine numerous clusters and databases
• for example, pausing the pool and queuing incoming connections to restart a database without the clients being aware
• when connecting to the unique “pgbouncer” database, a straightforward management interface
  * compilations of data
• supports SSL
• optional access control and authentication layer (pg_hba.conf format)
• most settings have an online configuration reload

PgBouncer gets caught

• not automated
• real connection limits to the underlying database (max_client_conn, default_pool_size, max_db_connections, max_user_connections, min_pool_size, reserve_pool_size) that are not immediately apparent to the user
• errors in the connect_query (performed before the connection was handed to the client, such as encoding settings, work_mem, etc.) are not taken into account.

Testing performance

For skepticism’s sake, I put my colleague Ants’ claims that PgBouncer is significantly faster than pgpool-II to the test. I also made the decision to perform a quick set of tests using my laptop and every component. A small 13MB “pgbench” in-memory dataset in “select-only” mode was used for the test setup because we only wanted to test the connection overhead here. Pools were set up without SSL so that the tested number of 8 concurrent connections were always cached and no connection re-establishing occurred during the test. “Session pooling” was applied to PgBouncer by default.

 pgbench -i -s 1 bench	# init the bench schema ~13MB for port in 5432 6432 9999 ; do for i in {1..3} ; do 	pgbench --select-only --connect -T300 -c8 -j2 -p $port bench done done

A side note: Before I could really start testing, I encountered a distro-specific issue where connections started to break after a while, necessitating the modification of some kernel parameters.

Results were as follows, with the usual YMMV caveat:

• no pooling, average TPS 356
• TPS average for Pgpool-II: 3939 (10x overall improvement)
• PgBouncer’s average TPS of 6626 represents a 17x improvement over Pgpool.

Summary

PgBouncer and pgpool-II, two well-known and tried-and-true products, offer a good way to take advantage of performance’s low-hanging fruit (a very noticeable difference when performing very brief and simple transactions), as well as to add some flexibility to your setup by hiding the database from direct access, making it simpler to perform minor maintenance. However, due to its lightweight architecture and superior performance, PgBouncer would be my choice for the majority of use cases (no replicas or using external HA solutions).

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.