A PostgreSQL extension that provides native connection pooling capabilities directly integrated into PostgreSQL.
Before build and install pg_connpool you should ensure following:
- PostgreSQL version is 9.4 or higher
- GNU Make 3.81+
- Libevent 2.0+
- pkg-config
- OpenSSL 1.0.1+ for TLS support
- (optional) c-ares as alternative to Libevent's evdns
- (optional) LDAP libraries
- (optional) PAM libraries
Typical installation procedure may look like this:
$ git clone
$ cd pg_connpool
$ ./autogen.sh
$ ./configure
$ make
# Copy extension files manually
$ cp src/pg_connpool.so `pg_config --pkglibdir`/
$ cp src/pg_connpool.control `pg_config --sharedir`/extension/
$ cp src/pg_connpool--1.0.sql `pg_config --sharedir`/extension/
$ psql DB -c "CREATE EXTENSION pg_connpool;"
You can supply one or more command-line options to configure. Run
./configure --help to list the available options and the environment
variables that customizes the configuration.
Additional packages required: autoconf, automake, libtool, pandoc
PostgreSQL creates a separate backend process for each client connection, which provides excellent isolation but comes with significant overhead:
- Memory Usage: Each backend process consumes 2-10MB of memory
- Context Switching: High connection counts lead to CPU overhead
- Connection Limits: PostgreSQL has hard limits on concurrent connections
- Resource Contention: Too many connections can degrade performance
Historically, connection pooling has been handled externally:
- Application-level pooling: Built into application frameworks
- External poolers: Third-party poolers running as separate processes
- Proxy solutions: HAProxy, cloud-native load balancers
While effective, these solutions introduce:
- Additional infrastructure complexity
- Network latency overhead
- Configuration management challenges
- Monitoring fragmentation
This extension brings connection pooling directly into PostgreSQL, offering:
- Native Integration: No external processes or proxies required
- Built-in Load Balancing: Master process distributes connections across multiple workers
- Intelligent Routing: Automatic failover and health monitoring
- Reduced Latency: Eliminates proxy network hops
- Simplified Architecture: One less component to manage
- PostgreSQL-aware: Deep integration with PostgreSQL internals
- Horizontal Scaling: Multiple pooler workers for high-throughput scenarios
pg_connpool is a PostgreSQL extension that brings connection pooling capabilities directly into PostgreSQL. It provides efficient connection management, monitoring, and statistics collection with native PostgreSQL integration.
-
Connection Pool Statistics: Real-time monitoring of active, idle, and maximum connections per pool
-
Master Process: Dedicated master process for managing and distributing connections across workers
-
Multiple Worker Processes: Configurable number of worker processes for horizontal scaling
-
Configurable Parameters: Customizable worker count and configuration file path
-
Native Connection Pooling: Advanced connection pooling engine built for PostgreSQL
-
Peer Communication: Poolers can communicate with each other for load distribution
The extension provides minimal configuration parameters:
-- Set number of worker processes (default: 1)
-- Only set > 1 if you need multiple poolers for high throughput
SET pg_connpool.worker_count = 4;
-- Set pooler configuration file path (required)
SET pg_connpool.config_file = '/path/to/pooler.ini';Add these to your postgresql.conf for persistent configuration:
# Only set worker_count > 1 if you need multiple poolers for high throughput
pg_connpool.worker_count = 4
pg_connpool.config_file = '/path/to/pooler.ini'
Note: Pool sizes, timeouts, and other pooling parameters are configured directly in the pgbouncer configuration file (pooler.ini).
-- Start the master and worker processes
SELECT pg_connpool_start_worker();This will start:
- Single worker mode (
worker_count = 1): Only one worker process (no master needed) - Multiple worker mode (
worker_count > 1): One master process + multiple worker processes - Each worker gets a unique configuration with different ports and socket paths
Note: Only increase worker_count if you need multiple poolers for high throughput scenarios.
┌─────────────────────────────────────────────────────────────────┐
│ PostgreSQL Server │
├─────────────────────────────────────────────────────────────────┤
│ ┌─────────────────┐ ┌──────────────────────────────────────┐ │
│ │ Client Apps │ │ pg_connpool │ │
│ │ │ │ Extension │ │
│ │ ┌───────────┐ │ │ ┌─────────────────────────────────┐ │ │
│ │ │ SQL │──┼────┼──│ SQL Interface │ │ │
│ │ │ Queries │ │ │ │ • pg_connpool_stats() │ │ │
│ │ └───────────┘ │ │ │ • pg_connpool_reset() │ │ │
│ └─────────────────┘ │ │ • pg_connpool_start_worker() │ │ │
│ │ │ • pg_connpool_info view │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌─────────────────────────────────┐ │ │
│ │ │ C Extension Module │ │ │
│ │ │ (pg_connpool.c) │ │ │
│ │ │ • GUC Parameters │ │ │
│ │ │ • Statistics Collection │ │ │
│ │ │ • Worker Management │ │ │
│ │ │ • Config Generation │ │ │
│ │ └─────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ ┌───────────────────────────────────────────────────────────┐ │
│ │ Background Worker Processes │ │
│ │ │ │
│ │ ┌─────────────────────────────────────────────────────────┐ │ │
│ │ │ Master Process │ │ │
│ │ │ • Connection Distribution │ │ │
│ │ │ • Health Monitoring │ │ │
│ │ │ • Failover Management │ │ │
│ │ └─────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ Worker │ │ Worker │ │ Worker │ ... │ │
│ │ │ Process 1 │ │ Process 2 │ │ Process N │ │ │
│ │ │ │ │ │ │ │ │ │
│ │ │ • Port 6432 │ │ • Port 6433 │ │ • Port 643X │ │ │
│ │ │ • Unique │ │ • Unique │ │ • Unique │ │ │
│ │ │ Config │ │ Config │ │ Config │ │ │
│ │ │ • Peer │ │ • Peer │ │ • Peer │ │ │
│ │ │ Comm. │ │ Comm. │ │ Comm. │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │ │ │ │
│ │ └────────────────┼────────────────┘ │ │
│ │ │ │ │
│ │ ┌─────────────────────────────────────────────────────────┐ │ │
│ │ │ Connection Pool Engine │ │ │
│ │ │ • Connection Pool Management │ │ │
│ │ │ • Connection Lifecycle │ │ │
│ │ │ • Protocol Handling │ │ │
│ │ │ • Authentication │ │ │
│ │ └─────────────────────────────────────────────────────────┘ │ │
│ └───────────────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────────────┐ │
│ │ PostgreSQL Backends │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ Backend │ │ Backend │ │ Backend │ ... │ │
│ │ │ Process 1 │ │ Process 2 │ │ Process N │ │ │
│ │ │ │ │ │ │ │ │ │
│ │ │ • Query │ │ • Query │ │ • Query │ │ │
│ │ │ Execution │ │ Execution │ │ Execution │ │ │
│ │ │ • Memory │ │ • Memory │ │ • Memory │ │ │
│ │ │ Context │ │ Context │ │ Context │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ └───────────────────────────────────────────────────────────┘ │
│ ▲ │
│ │ │
│ Connection Routing │
│ │ │
└─────────────────────────────────────────────────────────────────┘
▼
┌─────────────────────────────────────────────────────────────────┐
│ Target Databases │
│ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────────────────┐ │
│ │ Database │ │ Database │ │ Database │ │
│ │ A │ │ B │ │ C │ │
│ │ │ │ │ │ │ │
│ └─────────────┘ └─────────────┘ └─────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
- C Extension Module: Core functionality implemented in
pg_connpool.c - Master Process: Manages and distributes connections across multiple worker processes
- Multiple Worker Processes: Each runs on unique ports with individual configurations
- Connection Pool Engine: Native connection pooling implementation
- SQL Interface: Functions and views for monitoring and control
- Dynamic Configuration: Automatic generation of worker-specific config files
- Reduced Infrastructure: No separate pooler processes to manage
- Lower Latency: Direct in-process connection management
- Unified Monitoring: Pool metrics available through standard PostgreSQL views
- Simplified Deployment: Extension installation vs. separate service setup
- PostgreSQL Integration: Leverages PostgreSQL's background worker framework
- Horizontal Scaling: Multiple worker processes for increased throughput
- Load Distribution: Built-in master process for optimal resource utilization
Monitor your connection pools using pgbouncer's built-in admin interface:
# For single worker (worker_count = 1)
psql -h 127.0.0.1 -p 6433 -U pgbouncer pgbouncer
# For multiple workers (worker_count > 1), connect to individual workers
psql -h 127.0.0.1 -p 6433 -U pgbouncer pgbouncer # Worker 1
psql -h 127.0.0.1 -p 6434 -U pgbouncer pgbouncer # Worker 2
psql -h 127.0.0.1 -p 6435 -U pgbouncer pgbouncer # Worker 3
# Show pool statistics
SHOW POOLS;
# Show client connections
SHOW CLIENTS;
# Show server connections
SHOW SERVERS;Note: Port 6432 is reserved for the base configuration. Workers start from port 6433.
- Extension fails to load: Ensure pg_connpool shared library is built correctly
- Background worker not starting: Check PostgreSQL logs for error messages
- Connection issues: Check pgbouncer admin console for pool status
Check PostgreSQL logs for pg_connpool related messages:
tail -f /var/log/postgresql/postgresql-*.log | grep pg_connpoolThe extension uses the standard PostgreSQL extension build system (PGXS):
# Development build
make clean
make
make install
# Run tests (if available)
make installcheckpg_connpool.c- Main extension codepg_connpool.control- Extension metadatapg_connpool--1.0.sql- SQL objects definitionMakefile- Build configurationsrc/- Connection pooling source code
This module available from GitHub under the same license as PostgreSQL and supports PostgreSQL 9.4+.
Contributions are welcome! Please follow PostgreSQL development guidelines and ensure all tests pass before submitting changes.
Sagar Shedge sagar.shedge92@gmail.com