Connection pooling maintains a set of pre-established database connections that are reused across requests, rather than opening and closing a connection for every query. Opening a PostgreSQL connection involves TCP handshake, TLS negotiation, authentication, and session setup — typically 20-100ms. With 1,000 requests/second, that’s 1,000 new connections/second, each consuming ~5MB of DB server memory. Connection pooling reduces this to tens of persistent connections handling millions of queries.
Core Data Model
-- Pool state tracked per-process (in-memory, not in DB)
-- Each pool manages connections to one database + user combination
class ConnectionPool:
pool_size: int # max total connections
min_idle: int # minimum idle connections to keep warm
max_idle: int # maximum idle connections to retain
acquire_timeout_ms: int # how long to wait for an available connection
idle_timeout_s: int # close idle connections older than this
max_lifetime_s: int # close connections older than this (force refresh)
_idle: deque # available connections, LIFO for connection warmth
_in_use: set # connections currently held by requests
_lock: threading.Lock # protect shared state
_not_empty: threading.Condition # signal when a connection is returnedPool Implementation
import threading
import time
from collections import deque
import psycopg2
class ConnectionPool:
def __init__(self, dsn: str, pool_size: int = 20, min_idle: int = 5,
acquire_timeout_ms: int = 5000, max_lifetime_s: int = 3600):
self.dsn = dsn
self.pool_size = pool_size
self.min_idle = min_idle
self.acquire_timeout_ms = acquire_timeout_ms
self.max_lifetime_s = max_lifetime_s
self._idle: deque = deque()
self._in_use: set = set()
self._lock = threading.Lock()
self._not_empty = threading.Condition(self._lock)
self._total_created = 0
# Pre-warm with min_idle connections
for _ in range(min_idle):
conn = self._create_connection()
self._idle.append(conn)
# Start background maintenance thread
threading.Thread(target=self._maintenance_loop, daemon=True).start()
def _create_connection(self) -> dict:
conn = psycopg2.connect(self.dsn)
conn.autocommit = False
self._total_created += 1
return {'conn': conn, 'created_at': time.monotonic(), 'last_used': time.monotonic()}
def acquire(self) -> psycopg2.extensions.connection:
deadline = time.monotonic() + self.acquire_timeout_ms / 1000
with self._not_empty:
while True:
if self._idle:
# LIFO: take most recently used connection (warmer, less likely stale)
entry = self._idle.pop()
if self._is_expired(entry):
entry['conn'].close()
entry = self._create_connection()
self._in_use.add(id(entry['conn']))
entry['last_used'] = time.monotonic()
return entry['conn']
total = len(self._idle) + len(self._in_use)
if total < self.pool_size:
# Room to grow — create a new connection
entry = self._create_connection()
self._in_use.add(id(entry['conn']))
return entry['conn']
# Pool exhausted — wait for a connection to be returned
remaining = deadline - time.monotonic()
if remaining bool:
age = time.monotonic() - entry['created_at']
return age > self.max_lifetime_s
def _maintenance_loop(self):
"""Periodically close excess idle connections and validate stale ones."""
while True:
time.sleep(30)
with self._lock:
now = time.monotonic()
to_close = []
live_idle = deque()
for entry in self._idle:
age = now - entry['last_used']
if age > 600 and len(live_idle) >= self.min_idle:
to_close.append(entry['conn'])
else:
live_idle.append(entry)
self._idle = live_idle
for conn in to_close:
try:
conn.close()
except Exception:
pass
from contextlib import contextmanager
@contextmanager
def get_connection(pool: ConnectionPool):
conn = pool.acquire()
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
pool.release(conn)PgBouncer: Production-Grade Pooling
Don’t implement connection pooling in your application when PgBouncer exists. PgBouncer is a lightweight PostgreSQL-specific proxy that sits between your application and the database, managing pooling transparently. Three pooling modes: session (one server connection per client session — same as no pooling), transaction (server connection held only for the duration of a transaction — most efficient), statement (connection returned after each statement — cannot use multi-statement transactions).
# PgBouncer configuration (pgbouncer.ini)
[databases]
appdb = host=postgres-primary port=5432 dbname=appdb
[pgbouncer]
pool_mode = transaction # most efficient for stateless apps
max_client_conn = 10000 # clients can open up to 10K connections to PgBouncer
default_pool_size = 25 # PgBouncer opens at most 25 connections to Postgres
min_pool_size = 5
reserve_pool_size = 5 # emergency connections for bursts
reserve_pool_timeout = 3
server_idle_timeout = 600
# Result: 10,000 app connections -> 25 Postgres connectionsKey Interview Points
- PostgreSQL has a hard limit on concurrent connections (typically 100-500). Without pooling, each app thread holds a connection — 100 servers × 50 threads = 5,000 connections, exceeding the limit. PgBouncer multiplexes 5,000 app connections onto 25 database connections.
- LIFO eviction (take the most recently returned connection) keeps a subset of connections warm and lets idle excess connections expire naturally — better than FIFO which would age all connections equally.
- Always rollback on release: if the caller forgets to commit or roll back, returning a connection with an open transaction poisons the next request that uses it.
- Max lifetime prevents connections from staying open indefinitely — databases, proxies, and firewalls may silently close long-lived TCP connections. Proactively cycling connections prevents “broken pipe” errors.
- Pool timeout errors are the signal to increase pool size or scale the database — they indicate the database cannot keep up with query throughput, not a pool implementation bug.
- In transaction mode, PgBouncer cannot support prepared statements or advisory locks (they are session-scoped, not transaction-scoped). Disable prepared statements in your driver when using PgBouncer in transaction mode.
Database connection pooling and RDS scaling design is discussed in Amazon system design interview questions.
Database connection pooling and high-throughput system design is covered in Netflix system design interview preparation.
Database connection pooling and e-commerce backend design is discussed in Shopify system design interview guide.
See also: Atlassian Interview Guide