Each DB connection costs ~10MB RAM. Without pooling, you create hundreds of connections and the DB crashes.
Why Pooling¶
- TCP handshake + TLS = ~100ms per connection
- PostgreSQL fork per connection = ~10MB RAM
- Max connections limit (default 100)
- Connection reuse = dramatically lower latency
SQLAlchemy Pool¶
from sqlalchemy import create_engine engine = create_engine( “postgresql://user:pass@localhost/db”, pool_size=20, max_overflow=10, pool_timeout=30, pool_recycle=1800, # Recycle after 30 min )
PgBouncer¶
pgbouncer.ini¶
[databases] mydb = host=localhost port=5432 dbname=mydb [pgbouncer] pool_mode = transaction # Recommended max_client_conn = 1000 default_pool_size = 25
Key Takeaway¶
Always pool. SQLAlchemy pool_size=20, PgBouncer for sharing the pool across applications.
databaseconnection poolpostgresqlperformance