多Python脚本连接PostgreSQL如何避免过高资源消耗

核心问题分析 #

当前的性能/资源矛盾本质来自两个极端方案的缺陷：

1. 单脚本持长连接：总连接数随脚本数量线性增长，PostgreSQL为每个连接创建独立进程，内存/CPU开销随连接数上升快速增加
2. 单查询短连接：每次查询都要经历TCP握手、数据库认证、进程初始化、连接销毁全流程，额外开销占比极高

最优方案：部署PgBouncer全局连接池 #

该方案完全适配多独立Python脚本的场景，几乎无需修改业务代码，即可将PostgreSQL侧总连接数控制在极低水平：

• 安装PgBouncer作为PostgreSQL的前置连接代理，选择事务级 pooling 模式，该模式下单个PG长连接可以在多个脚本的事务之间复用，连接利用率最高
• 配置PgBouncer的底层PG连接上限为15_{20（完全覆盖20}30个爬虫脚本的并发需求），上层可以承接数百个客户端的连接请求
• 只需将所有Python脚本中数据库连接的端口改为PgBouncer默认监听的6432，其余逻辑无需改动，即可实现连接复用，性能比短连接方案提升10倍以上

备选方案：单脚本进程内连接池 #

如果不想引入额外中间件，可以将原有的单次连接上下文管理器改造为连接池复用模式，单个脚本最多占用1_{2个长连接，总连接数可以控制在30}40的合理范围，参考实现代码如下：

import psycopg2
import psycopg2.extras
from psycopg2 import pool
import atexit

DATABASE_CONNECTION = {
    "host": configuration.path.database.environment,
    "database": configuration.postgresql.database,
    "user": configuration.postgresql.user,
    "password": configuration.postgresql.password,
    "keepalives": 1,
    "keepalives_idle": 30,
    "keepalives_interval": 10,
    "keepalives_count": 5
}

# 脚本启动时全局初始化连接池，单爬虫脚本最多2个连接完全够用
connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=2,
    **DATABASE_CONNECTION
)

class PooledConnection:
    def __enter__(self):
        self.conn = connection_pool.getconn()
        self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        return self.cursor
    
    def __exit__(self, err_type, err_value, traceback):
        if err_type and err_value:
            self.conn.rollback()
        else:
            self.conn.commit()
        self.cursor.close()
        # 连接放回池内复用，不销毁
        connection_pool.putconn(self.conn)
        return False

# 脚本退出时自动关闭所有池内连接
atexit.register(connection_pool.closeall)

额外优化建议 #

• 批量操作：爬虫的INSERT/UPDATE操作尽量用psycopg2.extras.execute_values、execute_batch批量提交，单批次处理100~1000条数据，大幅减少数据库交互次数，降低连接占用时长
• 避免长事务：不要在数据库事务中执行网页爬取、IO等待等耗时操作，尽量先爬完数据再集中写库，减少连接占用时间
• 连接参数调优：开启TCP保活配置，避免空闲连接被防火墙/操作系统断开，导致偶发的连接失效错误

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