FastAPI+PostgreSQL+SQLAlchemy连接耗尽问题排查求助

一、优先排查数据库连接泄漏根源 #

1. 检查get_db依赖的实现 #

连接泄漏最常见的原因是Session未被正确关闭。你的get_db必须通过yield+finally确保请求结束后释放连接：

from sqlalchemy.orm import Session
from .database import SessionLocal

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()  # 必须执行，将连接归还到连接池

如果你的get_db没有finally块或未调用db.close()，请求结束后Session会一直持有连接，最终耗尽PostgreSQL的连接槽。

2. 避免异步路由混用同步SQLAlchemy Session #

你的路由用了async def，但调用的是同步的db.query().all()——这会导致线程被阻塞，连接池无法及时回收连接。解决方式二选一：

• 将路由改为同步def（适合传统ORM操作场景）：

  def read_posts(request: Request, page: int = 1, page_size: int = 12, db: Session = Depends(get_db)):
      start = (page - 1) * page_size
      posts = db.query(models.Post).offset(start).limit(page_size).all()
      return templates.TemplateResponse("index.html", {"request": request, "posts": posts})
  

• 切换为异步SQLAlchemy（使用asyncpg驱动），配合异步Session：

  from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
  from sqlalchemy.orm import sessionmaker
  
  engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/dbname")
  AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
  
  async def get_async_db():
      async with AsyncSessionLocal() as session:
          yield session
  

3. 调整SQLAlchemy连接池配置 #

PostgreSQL默认最大连接数为100，需确保SQLAlchemy的连接池参数不超过这个值，同时避免无效连接残留：

# 在创建engine时配置
engine = create_engine(
    "postgresql://user:pass@localhost/dbname",
    pool_size=10,  # 常驻连接数
    max_overflow=20,  # 额外临时连接数（总连接数=pool_size+max_overflow ≤ 100）
    pool_recycle=300,  # 5分钟后自动回收连接，避免PostgreSQL主动断开的无效连接
    pool_pre_ping=True  # 获取连接前先检查有效性
)

二、优化查询性能，减少连接占用时间 #

页面加载慢会导致连接长时间被占用，间接加剧连接耗尽问题：

1. 解决N+1查询问题 #

如果Jinja2模板中访问了Post的关联对象（如post.author、post.comments），需用预加载避免额外查询：

from sqlalchemy.orm import selectinload

posts = db.query(models.Post)\
    .options(
        selectinload(models.Post.author),  # 预加载作者
        selectinload(models.Post.comments)  # 预加载评论
    )\
    .offset((page-1)*page_size)\
    .limit(page_size)\
    .all()

注：你的代码中offset(start).limit(end)疑似笔误，应该是offset((page-1)*page_size).limit(page_size)。

2. 优化分页查询 #

大数量下offset分页会因全表扫描变慢，建议改用基于游标的分页（按id或created_at排序，通过where id > last_id实现），同时给排序字段加索引：

-- 给created_at字段加索引，加速分页排序
CREATE INDEX idx_post_created_at ON posts(created_at DESC);

3. 定位慢查询 #

用PostgreSQL的pg_stat_statements插件定位耗时较长的查询：

-- 开启插件后执行，查看耗时Top10的查询
SELECT queryid, query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

针对慢查询添加对应索引，减少连接占用时间。

三、验证与监控 #

1. 查看PostgreSQL当前连接状态，确认是否有长期占用的连接：

SELECT pid, usename, state, query_start FROM pg_stat_activity WHERE datname = '你的数据库名';

如果看到大量idle in transaction状态的连接，说明Session未正确提交/回滚，需检查代码中是否有未处理的事务。

2. 启用SQLAlchemy的日志，查看连接的获取/释放流程：

engine = create_engine(..., echo=True)  # 打印所有SQL及连接池操作日志

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