Great question—this is a super common gotcha with SQLAlchemy’s connection pooling, especially when paired with PostgreSQL. Let’s break down exactly what’s happening and how to rein in those idle connections.

First: Why 15-20 Connections When pool_size=5? #

The default pool_size=5 doesn’t cap your total connections at 5—here’s why:

1. Default max_overflow is 10
   SQLAlchemy’s default connection pool (QueuePool) has two key settings:

   • pool_size: The number of persistent connections the pool keeps open at all times.
   • max_overflow: The number of temporary extra connections the pool can create when all persistent connections are in use.

   By default, max_overflow=10, so your total possible connections are 5 + 10 = 15—which lines up almost exactly with the 15-20 idle connections you’re seeing. Those extra 10 connections hang around as idle until they’re either reused or eventually cleaned up.

2. Multi-process deployment (e.g., Gunicorn/uWSGI workers)
   If your app runs on multiple worker processes (super common for web apps), each worker gets its own independent connection pool. So 2 workers × (5 + 10) = 30 total possible connections. Even if only half are active/idle, that’s 15—perfectly matching your numbers.

3. session.close() doesn’t close the database connection
   When you call dbsession.close(), you’re just returning the connection back to the pool, not terminating it. The connection stays open in PostgreSQL as idle until the pool reuses it or recycles it.

How to Limit Idle Connections and Cap Total Connections #

Here’s what you can do to fix this:

1. Tune Your Pool Parameters #

Adjust these when creating your SQLAlchemy engine to control connection limits:

• Lower max_overflow (or set it to 0) to eliminate temporary extra connections:

  from sqlalchemy import create_engine
  
  engine = create_engine(
      "postgresql+psycopg2://user:pass@host/db",
      pool_size=5,
      max_overflow=0  # No extra connections allowed
  )
  

  Now your total connections will never exceed 5 per worker process.

• Set pool_recycle to idle connections after a timeout:

  engine = create_engine(
      "postgresql+psycopg2://user:pass@host/db",
      pool_size=5,
      max_overflow=2,
      pool_recycle=300  # Recycle connections after 5 minutes (300 seconds)
  )
  

  This tells SQLAlchemy to close and recreate connections that have been idle for 5 minutes, preventing stale idle connections from piling up.

2. Use Context Managers for Session Management #

Always use with statements to ensure sessions are properly closed (and connections returned to the pool) even if exceptions occur:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

with Session() as session:
    # Run your queries
    results = session.execute("SELECT * FROM pg_stat_activity")
    session.commit()
# Session is automatically closed here, connection goes back to the pool

This avoids "leaked" sessions that hold onto connections longer than needed.

3. Check for Leaked Sessions #

If you’re still seeing too many connections, audit your code for places where sessions aren’t being closed. You can also inspect the pool’s status directly to see active/idle connections:

print(engine.pool.status())

This will show how many connections are in use, idle, or overflowed.

4. Adjust Worker Count (If Multi-Process) #

If you’re using multiple workers, reduce the number of workers or lower pool_size per worker to keep total database connections within PostgreSQL’s limits. For example, 4 workers × pool_size=3 × max_overflow=2 = 20 total connections—easier to manage.

Final Notes #

PostgreSQL’s pg_stat_activity will show all open connections, including those held by SQLAlchemy’s pool. The key is to align your pool settings with your deployment architecture (single vs multi-process) and application needs to avoid unnecessary idle connections.

内容的提问来源于stack exchange，提问作者Kevin Vasko