Database Connection Pooling: Save $6K/Year

Introduction

Database connection pooling is one of the highest-ROI optimizations you can make. We recently helped a client save $6,000/year just by implementing PgBouncer. Here's how.

The Problem: Connection Overhead

Every database connection has overhead:

• Memory: 5-10MB per connection on PostgreSQL
• CPU: Connection establishment takes ~100ms
• Limits: Cloud databases have connection limits

Real-World Example

Our client had:

• 10 application servers
• 50 connections per server
• 500 total connections to a db.m5.large RDS instance

The database was hitting connection limits, so they planned to upgrade to db.m5.xlarge ($200/month more).

The Solution: PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL that:

• Maintains a pool of database connections
• Multiplexes application connections to pool connections
• Dramatically reduces actual database connections needed

Before PgBouncer

[App Server 1] ----50 connections---->
[App Server 2] ----50 connections---->  [Database: 500 connections]
[App Server 3] ----50 connections---->
...

After PgBouncer

[App Server 1] ----50 connections---->
[App Server 2] ----50 connections---->  [PgBouncer: 50 pool] ----> [Database: 50 connections]
[App Server 3] ----50 connections---->
...

Implementation Guide

Step 1: Install PgBouncer

# Ubuntu/Debian
sudo apt-get install pgbouncer

# Amazon Linux
sudo yum install pgbouncer

Step 2: Configure pgbouncer.ini

[databases]
mydb = host=your-rds-endpoint.amazonaws.com port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool settings
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
min_pool_size = 5

# Timeouts
server_idle_timeout = 60
client_idle_timeout = 0

Step 3: Configure Authentication

Create /etc/pgbouncer/userlist.txt:

"myuser" "md5hash_of_password"

Step 4: Update Application Connection String

Change from:

postgresql://user:pass@rds-endpoint:5432/mydb

To:

postgresql://user:pass@pgbouncer-host:6432/mydb

Pool Modes Explained

Session Mode

• Connection held for entire session
• Safest but least efficient
• Use for legacy applications

Transaction Mode

• Connection returned after each transaction
• Good balance of safety and efficiency
• Recommended for most applications

Statement Mode

• Connection returned after each statement
• Most efficient but may break multi-statement transactions
• Use only if you understand the implications

Results

After implementing PgBouncer:

Annual Savings: $2,400 + avoided upgrade =$6,000+

Best Practices

1. Start with transaction mode for most applications
2. Monitor pool saturation using PgBouncer stats
3. Set appropriate timeouts to prevent connection leaks
4. Run PgBouncer close to your application to minimize latency
5. Use multiple PgBouncer instances for high availability

Common Issues and Solutions

"No more connections allowed"

Increase max_client_conn or add more pool connections.

Prepared statements not working

Transaction mode doesn't support prepared statements across transactions. Use session mode or disable prepared statements.

Authentication failures

Ensure userlist.txt has correct md5 hashes. Use: echo -n "passworduser" | md5sum

Conclusion

Connection pooling is a quick win that pays dividends immediately. PgBouncer is battle-tested, lightweight, and can be implemented in a few hours.

Need help optimizing your database? Contact us for a free assessment.