🏊 The Art of PostgreSQL Connection Pooling

Your app crashes at 100 connections? Here's why and how to fix it:

The Problem:
Each PostgreSQL connection uses ~10MB RAM. 100 connections = 1GB just for connections!

The Solution: Connection Pooling

-- Check your current connections
SELECT count(*) as connections,
       state
FROM pg_stat_activity
GROUP BY state;

-- Find connection hogs
SELECT usename, 
       application_name, 
       count(*)
FROM pg_stat_activity
GROUP BY usename, application_name
ORDER BY count(*) DESC;

🎯 Quick Wins:

1. PgBouncer (external pooler)


Transaction mode: 1000+ app connections → 20 DB connections
Session mode: For apps needing prepared statements
Statement mode: Maximum efficiency

2. Application Pooling

// Node.js example
const pool = new Pool({
  max: 20, // Don't exceed 100
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

The Magic Formula:

pool_size = (cpu_cores * 2) + disk_spindles

For most apps: 20-30 connections total!

💡 Pro insight: More connections ≠ better performance. After 50 connections, you're likely making things WORSE due to context switching.

Which pooling method do you use? Share your experience! 👇

#PostgreSQL #ConnectionPooling #Performance #Scaling

@postgres

🏊 The Art of Connection Pooling with PgBouncer

Your app has 1000 users but PostgreSQL dies at 100 connections? PgBouncer is your lifesaver.

The Problem In One Image:

Without PgBouncer:
App (1000 connections) ────────> PostgreSQL 💀
Each connection = 10MB RAM
Total: 10GB just for connections!

With PgBouncer:
App (1000) ──> PgBouncer ──> PostgreSQL (20) 😊
                  Magic!

🚀 15-Minute PgBouncer Setup:
Step 1: Install

# Ubuntu/Debian
sudo apt-get install pgbouncer

# macOS
brew install pgbouncer

Step 2: Configure (/etc/pgbouncer/pgbouncer.ini)

[databases]
; Connect to your database
mydb = host=localhost port=5432 dbname=mydb

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

; THE MAGIC SETTING:
pool_mode = transaction  ; This is the secret sauce!

; Pool sizing (for 4-core server)
default_pool_size = 25
max_client_conn = 1000

Step 3: Create userlist.txt

# Format: "username" "password"
"app_user" "md5hash_of_password"

Step 4: Start it!

sudo pgbouncer /etc/pgbouncer/pgbouncer.ini

🎯 Pool Mode Selection Guide:
Session Mode (Default)


Connection tied to client session
Supports all PostgreSQL features
Pool size ≈ max concurrent users

Transaction Mode (Recommended!)


Connection returned after each transaction
10-100x more efficient
Limitation: No session-level features

Statement Mode (Rarely used)


Connection returned after each statement
Maximum efficiency
Very limited use cases

📊 Real Numbers from Production:

-- Before PgBouncer:
SELECT count(*) FROM pg_stat_activity;
-- 400 connections, server struggling

-- After PgBouncer (transaction mode):
SELECT count(*) FROM pg_stat_activity;
-- 20 connections, server happy!

-- The math:
-- 400 connections × 10MB = 4GB RAM
-- 20 connections × 10MB = 200MB RAM
-- Saved: 3.8GB RAM!

⚙️ Optimal Settings Calculator:

# Your optimal pool size:
pool_size = (cpu_cores * 2) + disk_spindles
# 4 cores + SSD = (4 * 2) + 1 = 9 connections per database

# Maximum connections:
max_connections = expected_users
# Can be 1000+, PgBouncer queues them

# Reserve ratio:
reserve_pool_size = pool_size * 0.25
# Emergency connections

🔥 Common Gotchas & Fixes:
Problem: "PREPARED STATEMENT does not exist"
Fix: Use session mode or disable prepared statements

Problem: "LISTEN/NOTIFY not working"
Fix: Use session mode for those connections

Problem: "Too many connections still!"
Fix: Check for connection leaks in app

💡 Advanced Tricks:

; Aggressive timeout settings
server_idle_timeout = 60
server_lifetime = 3600
query_wait_timeout = 120

; Monitoring
stats_period = 60

; Multiple databases with different settings
production = host=db1 pool_size=50
analytics = host=db2 pool_size=10 pool_mode=session

The Bottom Line:
No PostgreSQL production setup is complete without PgBouncer. It's the $0 solution that saves thousands.

Running without connection pooling? Install PgBouncer TODAY! Your database will thank you. 🙏

#PostgreSQL #PgBouncer #ConnectionPooling #Performance

@postgres