Socket IO结合PostgreSQL在while循环中出现客户端连接数超限问题

• 绝大多数场景下with语句未释放连接是写法错误导致：如果将数据库连接的with块写在while循环外部，整个任务运行周期（最长4小时）内连接会被持续持有不会释放，完全失去with自动回收的作用
• 如果使用了Flask SQLAlchemy等ORM框架的全局连接池，默认的空闲连接回收阈值设置过高，长周期后台任务持有的空闲连接不会被主动回收
• 未限制SocketIO触发的后台任务并发量，大量任务同时运行时每个任务至少占用1个连接，很快触达PostgreSQL默认100的连接数上限

1. 修正连接使用逻辑 #

将连接创建逻辑放到while循环内部，每次查询单独创建连接、查询完成后立刻自动释放，参考代码如下：

import psycopg2
import time

def get_task_status(task_id: int) -> str:
    # 单次查询单独创建连接，with块结束后自动回收连接
    with psycopg2.connect("your_postgresql_connection_uri") as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT status FROM task_table WHERE id = %s", (task_id,))
            res = cur.fetchone()
            return res[0] if res else "pending"

def background_task(task_id: int):
    while True:
        current_status = get_task_status(task_id)
        if current_status == "target_status":
            # 状态达标后触发前端SocketIO通知逻辑
            break
        time.sleep(2)

如果担心频繁创建短连接的性能开销，可以接入psycopg2.pool实现小容量短连接池，设置最大连接数不超过20，空闲连接超过1秒自动回收即可。

2. 降低数据库访问频率（可选，效果最优） #

使用PostgreSQL原生的LISTEN/NOTIFY机制替代轮询：

• 给任务表增加状态变更触发器，状态更新时主动往指定频道发送NOTIFY消息
• 后台任务启动后仅需建立1次数据库连接，执行LISTEN命令监听对应频道，收到通知后再查询最新状态，全程无轮询开销，单任务生命周期仅占用1个连接

如果不希望修改数据库侧逻辑，可以将固定2秒的轮询间隔改为动态调整：任务启动前30分钟保持2秒间隔，超过30分钟调整为10秒，超过1小时调整为30秒，大幅降低连接创建频次。

3. 源头控制并发量 #

在SocketIO事件入口增加并发控制，比如同时最多运行10个后台任务，超出的任务进入队列排队执行，从根本上避免连接数突增打满上限的问题。

4. 数据库配置调整 #

• 根据服务器配置适当调大PostgreSQL的max_connections参数（默认100，4核8G服务器可调整为200~300）
• 如果使用ORM连接池，设置pool_recycle参数小于PostgreSQL的idle_in_transaction_session_timeout配置，避免无效空闲连接被复用。

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