做数据类项目的人几乎都写过这么一段代码读配置、拼连接串、cursor.execute、commit、关连接本地跑得飞快扔到常驻服务里跑两周凌晨开始零星报错日志里只有一句含糊的超时。我见过太多项目栽在Python 连接 PG 数据库这件看起来最不需要思考的事情上——不是不会连而是连上了之后对连接的生命周期、事务边界、批量写入的成本完全没有概念。这篇东西写给已经会用 Python 操作 PostgreSQL、但还没把这条链路吃透的人也写给刚开始接手数据同步、报表导出、后台任务这类活儿的朋友。下面这些内容不谈语法入门只讲选型依据、参数含义、踩坑现场和能被直接抄走的写法。1. 选驱动这件事先看清三条路的边界Python 连 PostgreSQL 的库不止一个网上一搜能出来七八种新人最容易犯的错是随手pip install第一个搜到的包然后在项目里用两年直到某天遇到性能瓶颈或者异步改造需求才发现换不掉。驱动选择不是审美问题它决定了你后面能拿到什么能力能不能用服务端预编译、能不能走 COPY 协议、能不能在协程里跑、出错时的异常类型细不细。这一节把三条主流路径的边界讲清楚选之前先对上自己的场景。1.1 psycopg2 和 psycopg3 不只是版本号之差psycopg2是历史最久的 C 扩展驱动也是绝大多数老项目里的默认选项。它底层走 libpqPG 官方客户端库所以连接参数、SSL 行为、认证方式跟psql命令行完全一致这一点在排查问题时非常值钱——你在 Python 里连不上用同样的参数跑一遍psql能立刻判断问题在代码还是在网络或认证。它的成熟度毋庸置疑缺点是设计停留在同步时代接口风格偏老游标上下文管理器只负责关闭游标不负责提交事务这个语义差异坑过不少人。psycopg3是同一个作者团队重写的版本包名就叫psycopg。它最大的变化是原生支持异步同时把参数绑定从客户端拼字符串改成可以走服务端绑定server-side binding也就是真正意义上的 prepared statement。此外它还支持 pipeline 模式把多条语句攒起来一次发出去减少网络往返。接口层面with conn:块退出时会自动提交或回滚with conn.cursor()的行为也更符合直觉。两者怎么选我给一个粗糙但实用的判断场景建议理由老项目、依赖链已锁定继续用 psycopg2换驱动收益低、回归成本高新项目、纯同步业务直接用 psycopg3少一层历史包袱后续加异步不用重写需要异步但不想引第二个库psycopg3 的 async 接口同步异步同一套 API团队心智负担小极端吞吐的写入/读取管道单独评估 COPY 与 asyncpg协议层实现差异会体现出来有一点必须提前说清楚psycopg2和psycopg3不能装在同一个环境里互相导入模块名虽然不同psycopg2vspsycopg但很多间接依赖会同时拉这两个装完之后import报奇怪的错八成是环境里两个都在。遇到这种情况老老实实建个干净虚拟环境重装比在依赖树里刨半小时快得多。1.2 异步场景asyncpg 与 psycopg3 async 的取舍如果服务本身跑在 asyncio 上FastAPI、aiohttp、或者自研的协程调度同步驱动会把事件循环堵死这是硬伤必须换异步方案。这时候有两条路。asyncpg是纯异步实现不走 libpq自己实现了 PG 的 wire protocol。带来的好处是性能确实好尤其在大量小查询和高并发短连接场景下连接建立和查询往返的开销都更小。代价是它的接口跟同步驱动差别较大占位符是$1、$2这种编号形式而不是%s没有游标这个概念取而代之的是 prepared statement 缓存类型转换逻辑也是自己的一套某些扩展类型需要手动注册编解码器。psycopg3的 async 接口则是同步 API 的镜像await conn.execute(...)、async with conn.cursor()写法几乎不用重新学。它的绝对性能不如 asyncpg但对大多数业务系统来说完全够用而且从同步迁到异步的改动量小得多的。我的经验是如果项目已经在异步栈上并且需要读写大量数据、对延迟敏感选 asyncpg如果只是想让接口不阻塞、不想让团队学两套 APIpsycopg3 的 async 更省事。别为了追那几个百分点的性能指标把一个稳定的同步项目硬改成异步——改成异步之后调试难度、栈信息可读性、第三方库兼容性都会变差这笔账要算进去。1.3 ORM 底下那层连接到底是谁用 SQLAlchemy 或者别的查询构建器时很多人以为自己不直接连数据库实际上 ORM 只是包了一层最底下还是某个驱动。SQLAlchemy 的连接串postgresqlpsycopg2://...或者postgresqlpsycopg://...里的后面就是驱动名写错了会在create_engine的时候直接抛ModuleNotFoundError。这一层要特别注意的地方是ORM 自带连接池池参数pool_size、max_overflow、pool_recycle、pool_pre_ping配不好出问题的表现和手写连接池一模一样但因为隔了一层排查会更绕。尤其是pool_pre_ping它会在借出连接前先发一个探测语句代价是每次借出多一次往返收益是能自动剔除已失效的连接。对于跨网络、中间有负载均衡或防火墙空闲超时的环境这个参数几乎是必开的否则你会看到闲置十分钟后第一次请求必失败这种诡异现象。还有一点值得提醒ORM 里手动执行原生 SQL 的时候事务边界由 session 控制别在session外面又自己commit一次两边打架会导致提交时机不可预期。用 ORM 就用它的模式用完记得close()session 不关就相当于连接不还。2. 一次握手背后的成本账大部分人对建立连接很贵这句话只有抽象认知不知道贵在哪所以也不会认真对待连接复用。PG 的连接成本比 MySQL 这类线程模型的数据库高一个量级原因在于它的进程模型——每来一个连接服务端postmaster进程就fork出一个独立的 backend 进程专门服务这个连接。这个进程有自己的内存空间基线几 MB 起步加上各种缓冲区、排序内存实际占用会更高。所以连接数不是随便开它直接对应服务端的进程数和内存占用。2.1 连接参数里真正需要手动设置的没几个一个典型的连接串长这样import psycopg DSN ( host10.0.0.12 port5432 dbnameapp_db userapp_rw password*** connect_timeout5 application_namebatch_sync_worker keepalives_idle30 keepalives_interval10 keepalives_count3 sslmoderequire options-c statement_timeout30000 -c timezoneUTC -c search_pathapp,public ) with psycopg.connect(DSN) as conn: with conn.cursor() as cur: cur.execute(select now()) print(cur.fetchone())这里每个参数都不是装饰品。connect_timeout5的意思是 TCP 连接和认证阶段最多等 5 秒不设的话默认可能等到操作系统层面的超时几十秒起步在服务启动阶段会表现为卡住不动。application_name是排查神器它会让你的连接在服务端视图里带上名字后面第 5 节会详细用到。keepalives_*三个参数管的是 TCP 保活对于长连接穿过中间网络设备的场景不设的话连接可能被静默丢弃客户端还以为自己活着写数据的时候才发现已经断了。options这个参数最容易被忽略但价值最高。它把服务端的会话级 GUC 参数直接塞进连接初始化里一次搞定。statement_timeout给一个防呆上限避免某条烂 SQL 把连接挂死timezone统一成 UTC把时区问题在入口处掐死search_path固定 schema 搜索顺序避免同名表被解析到错误的位置。这三条加上去能消掉相当一部分在我机器上是好的类问题。2.2 PG 为什么对连接数这么敏感前面提到 PG 每连接一个进程这带来两个直接后果。第一连接数上限max_connections是个硬墙撞上去就是FATAL: sorry, too many clients already此时新请求全部失败连管理员想连进去 kill 会话都可能连不上得留余量。第二每次建连都要 fork 进程 走完整认证流程如果是 SCRAM 认证还有多轮哈希计算这个开销相对于一条简单查询来说是压倒性的。做个粗算假设单次查询业务耗时 2ms建立连接TCP 认证 fork实测往往在 10ms 到 50ms 量级取决于网络和认证方式。也就是说如果每次请求都新建连接即使查询本身极快总耗时也会被建连开销主导。这就是为什么短连接 每条都新建的写法在压测时数字难看——不是数据库慢是你在为每条 SQL 付一次握手钱。理解了这一点连接池的意义就不只是复用对象而是把这段固定开销从请求路径上摘掉。一个健康的服务进程启动时建好几个连接放进池子之后所有请求都从池里借请求路径上就没有建连成本了。2.3 时区、编码与 search_path三个不报错但会出错的设置这三个设置共同的特点是配错了不会立刻报错但会在某个具体场景下产出错误结果而且往往过了很久才被发现。时区方面PG 的timestamp without time zone不存时区信息timestamp with time zone简称 timestamptz存的是 UTC 内部值加会话时区用于显示。Python 侧datetime有没有tzinfo又是一回事。典型事故是服务端会话时区是本地时区Python 侧写入带时区的 datetime读回来做比较时又当成 naive 处理结果差了 8 小时。最省心的做法是两头统一连接参数里-c timezoneUTCPython 侧统一用带时区的datetimedatetime.now(timezone.utc)数据库字段统一用timestamptz。编码方面现代 PG 集群默认 UTF8一般不用管。但如果对接的是老库、或者是 GBK 编码的历史数据表取出来就是乱码或者直接抛UnicodeDecodeError。这时候可以在连接参数里显式指定client_encoding但更该做的是把数据迁移到 UTF8编码转换只是止血。search_path的问题在多 schema 项目里特别常见。默认$user, public如果应用表在appschema 下而public下恰好有个同名视图或旧表你的查询会静默命中错误对象。这不是假设我见过报表数据和线上数据对不上的案例最后查出来是search_path顺序问题。显式写死-c search_pathapp,public或者干脆所有 SQL 都带 schema 前缀二选一别靠默认值。3. 连接池的容量怎么定出问题长什么样池子大小是问得最多、答得最含糊的问题。网上常见的回答是CPU 核数 × 2 磁盘数这个公式来自另一套连接模型的语境直接搬到 PG 上会偏大。PG 的连接是进程你的并发瓶颈通常不在数据库能不能同时处理这么多连接而在你的应用有多少并发真正需要同时持有连接。池子开太大服务端连接数被打满所有人一起等开太小请求排队吞吐上不去。这一节给一个能落地的推算方法以及两种典型故障的现场特征。3.1 池子上限的一次完整推算推算要自顶向下做不能拍脑袋。举个例子一个后台服务部署 4 个实例进程或容器每个实例内部又开了 8 个处理线程/协程数据库max_connections配的是 100。第一步先扣掉服务端必须保留的部分管理员连接、监控采集、备份任务、运维手工登录保守留 20 个。第二步算应用可用总量100 - 20 80。第三步算每实例预算80 / 4 20。这 20 是硬上限不能超。第四步落到池参数如果每个实例 8 个并发处理单元池子上限设 8 到 12 就够了剩下的作为突发余量。设成 20 也不会更快因为并发单元只有 8 个多出来的连接只会闲置占内存。第五步考虑扩展如果未来要扩到 8 个实例每实例预算变成 10池子上限就得压到 10 以下。所以池子上限这个值应该跟着部署规模一起进配置中心而不是硬编码在代码里。这一点很关键——很多事故是扩容之后忘了改池参数8 个实例每个还按 20 开直接把服务端打满。还有一个反直觉的结论值得说对于 OLTP 类短查询池子开小一点、让请求排队往往比开大更好。因为连接太多会让服务端的进程调度、共享缓冲区争用变严重吞吐反而下降。真正的扩容手段是优化查询和加索引不是加连接。3.2 泄漏与耗尽的现场特征对比池子的两类故障表现相似但根因完全不同分清才能对症。特征连接泄漏容量不足报错内容取连接超时PoolTimeout/pool exhausted同样是取连接超时时间分布随时间推移越来越频繁重启后缓解高峰期集中出现低峰正常服务端连接数持续增长不回落直到打满稳定在上限附近波动典型原因异常路径未归还、cursor 未关、生成器提前退出池子上限小于实际并发需求处理方向查代码里的资源释放路径调参或减少单请求持有时长泄漏的排查有很实用的招给连接池加一个记录借出位置的机制在借出时记下调用栈超时未归还时把栈打出来。psycopg 的池子支持配置回调或者简单点在业务代码里包一层函数借出时记录traceback.extract_stack()归还时清掉。跑一天看谁借了不还一目了然。另一个隐蔽的泄漏源是长事务。有些代码借了连接开事务中间做了一堆业务处理调外部接口、算数据、写文件最后才提交。这段时间连接一直被占着业务一慢池子就被拖干。正确做法是把事务窗口压到最短数据准备好之后再借连接进去就写写完立刻提交归还。3.3 借还姿势上下文管理器的正确用法手写try/finally归还连接当然可以但漏写概率不低。用上下文管理器是最省心的方式只是要注意不同库的语义差别。psycopg3 的连接池用法from psycopg_pool import ConnectionPool pool ConnectionPool( conninfoDSN, min_size2, max_size10, timeout5, # 借连接最多等 5 秒 max_idle300, # 空闲超过 5 分钟回收 max_lifetime1800, # 连接最长活 30 分钟防止中间设备掐断 kwargs{autocommit: False}, ) with pool.connection() as conn: # 退出时提交或回滚并归还连接 with conn.cursor() as cur: cur.execute(update orders set status%s where id%s, (paid, 1024))这里max_lifetime是很多人不设、然后在生产环境吃亏的参数。中间如果有负载均衡或者网络设备存在空闲超时一条活了两小时的老连接可能在服务端已经被清掉了客户端却还拿着不放下一次使用就报连接已关闭。设一个小于中间设备超时时间的max_lifetime让池子主动做新老更替能消掉这类偶发故障。psycopg2 的ThreadedConnectionPool需要注意它的getconn()和putconn()必须配对如果中间抛异常而没在finally里归还连接就永久丢了。所以用它的时候老老实实写try/finallyfrom psycopg2 import pool as pg_pool p pg_pool.ThreadedConnectionPool(2, 10, dsnDSN) conn None try: conn p.getconn() with conn.cursor() as cur: cur.execute(select count(*) from orders) total cur.fetchone()[0] conn.commit() finally: if conn is not None: p.putconn(conn) # 无论成功失败都要归还SQLAlchemy 那边则是session的close()和pool_pre_ping、pool_recycle的组合思路一样让池子有能力剔除坏连接让代码保证用完归还。4. 写入场景里那些慢在不该慢的地方读操作慢通常是索引和查询计划的问题写操作慢则大量来自写法。同样是把十万行数据塞进一张表不同写法的耗时能差一到两个数量级而很多人的第一反应是数据库性能不行然后去加机器。这一节把三种写入方式的成本结构摊开讲顺带说清事务边界怎么划。4.1 逐条插入、批量插入与 COPY 的差距先看三条路的本质差异。逐条execute加commit每条 SQL 一次网络往返一次事务提交。PG 每次提交都要保证 WAL 落盘synchronous_commiton时这是最贵的部分。十万行就是十万次往返加十万次落盘慢是必然的。逐条execute放在一个事务里网络往返还是十万次但落盘只有一次。这一下就能快好几倍说明事务边界的影响比很多人想象的大。批量execute_values或executemanypsycopg3 的executemany内部会做流水线优化把多行拼成一条insert ... values (...),(...),(...)发出去往返次数降到 行数 / 批大小。批大小要控制太大会撞上服务端的语句长度限制和内存问题太小吃不到收益。经验值在 500 到 5000 行之间具体看单行宽度。COPY走 PG 专门的批量导入协议绕开 SQL 解析和逐行规划把数据流式灌进去。这是最快的方案数量级的领先。psycopg3 提供了cursor.copy()配合copy.set_types做两向转换psycopg2 用copy_expert。# psycopg3 的 COPY 写入注意 write_row 需要按列类型序列化 with pool.connection() as conn: with conn.cursor() as cur: with cur.copy(copy app.orders (id, amount, created_at) from stdin) as copy: for row in rows: copy.write_row(row) # row 为元组顺序与列表一致三个方案的选择不用纠结看数据量和频率方案适用规模额外成本备注单条 execute 每行提交几行、低频极慢只适合交互式操作批量 execute 单事务千到十万级需处理批大小通用性最好能带on conflictCOPY十万级以上不能用on conflict需先落到临时表再合并导入/同步类任务首选COPY 的局限要提前知道它不支持ON CONFLICT所以做增量同步时通常的套路是先COPY到一张临时表create temp table ... (like target)再用一条insert ... select ... on conflict do update合并进目标表最后清临时表。多一步但整体仍然远快于逐条 upsert。4.2 事务边界划在哪里才不会拖垮库事务边界不只是性能问题还是数据库整体健康问题。PG 用的是 MVCC 加清理机制长事务会阻止旧版本行被回收导致表膨胀、查询变慢、磁盘暴涨。一个挂着不提交的事务状态idle in transaction能造成的破坏往往超过它本身执行的那几条 SQL。划边界的三个原则。第一事务里只放数据库操作不放外部调用。调接口、读文件、算哈希这些全部挪到事务外面数据准备齐了再进事务。第二批处理要分批提交不要一个事务吞几十万行。每批几千到一万行提交一次既能控制回滚范围也不会让 WAL 一次性涨太多。第三显式设置statement_timeout和idle_in_transaction_session_timeout让服务端主动帮你收拾挂死的会话。后者尤其重要它会在会话处于idle in transaction超过设定时间时直接断开是对付代码疏漏的最后一道闸。-- 会话级兜底建议写进连接参数 SET statement_timeout 30s; SET idle_in_transaction_session_timeout 60s; SET lock_timeout 5s;lock_timeout是另一个值得加的参数。它在等锁超过指定时间后直接报错而不是无限等下去。没有它一个被锁住的语句会一直占着连接慢慢把池子耗干形成雪崩。加上之后至少能在几秒内失败并释放资源。4.3 冲突处理与 RETURNING 的实用写法增量同步几乎一定会遇到主键或唯一键冲突处理方式有三种查一下再插两次往返还有并发竞态、捕获异常再改插异常处理成本高还可能污染事务、直接ON CONFLICT一条语句解决。显然选第三种。sql insert into app.orders (id, amount, status, updated_at) values (%s, %s, %s, %s) on conflict (id) do update set amount excluded.amount, status excluded.status, updated_at excluded.updated_at where app.orders.updated_at excluded.updated_at returning id, (xmax 0) as inserted 这段有几个值得说明的点。excluded指代本次尝试插入的那行数据用它取值是标准写法。where子句让更新有条件——只有新数据更新时才覆盖避免旧数据把新数据盖掉这在乱序到达的同步场景里是保命的。returning (xmax 0)是个常用技巧能区分这行是插入的还是更新的做统计很方便虽然它依赖内部实现细节不适合写进强契约的逻辑里做日志和监控足够用。还有个小提醒ON CONFLICT需要目标列上有唯一索引或主键否则会报没有匹配的唯一约束。做分表或者临时表的时候很容易忘报错信息其实写得很清楚照着补索引就行。5. 报错现场排查从客户端日志摸到服务端会话排查数据库问题最有效的方法是分层定位先确定是连不上还是连上了报错再确定是客户端问题还是服务端问题最后才去读具体报错。跳过前面两步直接看异常文本很容易被表象带偏。这一节按故障层次展开给一套能照着走的排查链路。5.1 连不上拒绝连接、认证失败、超时的区分方法这三类错误在 Python 里最终都表现为一个异常但含义完全不同处理方向也完全不同。connection refusedTCP 层就被拒绝了说明目标端口上没有服务在监听或者中间有设备主动拒绝。检查方向是网络连通性、端口映射、服务是否启动。可以用nc -zv host port快速确认端口通不通比看 Python 栈快。timeout或connection timed out包发出去了没有响应。常见原因是防火墙丢包、安全组没放行、或者connect_timeout设得太短而认证本身较慢。这里的排查重点是网络策略不是密码。认证失败password authentication failed或no pg_hba.conf entryTCP 通了服务端也响应了卡在认证。前者的意思是账号密码或认证方式不对后者的意思是服务端的访问控制配置里没有覆盖你这个来源地址。这两个报错信息都很明确照着改就行但要注意一个坑改完pg_hba.conf需要 reload 才生效忘了 reload 会让你以为改错了。排查这类问题的标准动作是拿 Python 里用的那套参数原封不动地用psql连一次。psql连不上问题在环境psql能连上问题在代码——通常是连接串拼错了、环境变量没读到、或者用了不同的配置文件。这一步能省掉大量猜测。# 参数完全对齐只用来验证连通性 psql host10.0.0.12 port5432 dbnameapp_db userapp_rw sslmoderequire -c select 15.2 连上了但语句报错类型与编码两类高频问题连上之后的报错里类型和编码占了大多数。类型方面最典型的是把字符串塞进数值列、把 naive datetime 塞进 timestamptz 列、以及numeric读出来是Decimal而代码里当float用。前两个会直接报错反而好办第三个不报错但会出精度问题——Decimal做除法、做json.dumps都会抛异常很多人这时候才第一次意识到numeric映射过来不是 float。jsonb字段也常出问题。psycopg 对jsonb会自动做序列化/反序列化但如果你把一个Decimal或者datetime放进 dict 里再写进去标准 JSON 编码器不认这些类型会报序列化错误。解决办法是传Jsonb包装并配自定义编码器或者写入前先把值转成字符串/浮点。编码问题表现为UnicodeDecodeError或者存进去是问号。检查顺序是数据库server_encoding、客户端client_encoding、Python 里字符串的实际编码、以及中间有没有做过.encode()又.decode()的操作。老系统里常见的是某处用了默认编码做了一次不必要的转换把 UTF8 数据搞坏了这种要靠定位具体字段逐个比对才能找到。还有一类错误值得单说唯一约束冲突、外键约束冲突这些在 psycopg 里是具体异常类型UniqueViolation、ForeignKeyViolation但关键是——发生这类错误后当前事务已经处于中止状态必须rollback才能继续执行下一条语句否则后面所有语句都会报当前事务已中止。这个行为坑过无数人表现为第一条报错之后全是错。import psycopg from psycopg import errors try: with conn.cursor() as cur: cur.execute(insert into app.orders (id) values (%s), (1024,)) conn.commit() except errors.UniqueViolation: conn.rollback() # 必须回滚否则连接不可继续使用 # 走更新分支5.3 常驻服务里的隐性故障idle in transaction 与连接假死最难受的故障不是报错而是有时候不对。两类典型一是连接池慢慢被idle in transaction的会话占满二是连接看起来还活着但实际已经断了。排查第一类靠application_name加上服务端视图。给每个服务设一个能识别的名字出问题时直接查select pid, application_name, state, now() - state_change as duration, left(query, 80) as last_query from pg_stat_activity where datname app_db and state idle order by duration desc limit 20;看state idle in transaction且duration很长的行就是挂着不提交的会话。query字段能看到它最后执行的是什么顺着去代码里找对应的位置。这类会话的来源通常是借了连接开事务中间做了别的耗时操作或者异常路径上没回滚也没归还。排查第二类现象是报server closed the connection unexpectedly或者connection already closed且多发生在连接闲置一段时间后的第一次使用。原因通常是中间网络设备有连接跟踪表空闲久了把表项清了两端却不知道。对策前面提过设keepalives_*让 TCP 层主动探活设池的max_lifetime让连接定期换新开pool_pre_ping让借出前先验活。三个措施任选两个基本就能压住。最后分享一个我在常驻服务里一直保留的习惯给数据访问层加一层薄薄的计时日志记录每次数据库操作的耗时、影响行数、以及连接是从池里借的还是新建的。日志不用打全量采样 1% 或者只记录超过阈值的那部分就够了。这套东西平时看着没用一旦出问题能让你在几分钟内判断出是某条 SQL 变慢了还是池子借不出来了比事后加日志再复现快得多。我在实际使用中体会最深的一点是Python 连 PG 这条链路上的绝大多数问题都不是 API 用错而是对连接的资源属性没有建立起感觉——它是稀缺的、有状态的、需要被显式管理的。把连接池参数、事务边界、超时兜底这三件事在项目初期定好后面能省掉的排查时间远超当初花的那几个小时。 
长河君 · 2026/11/3 21:24:03 · 阅读 1.2k Python 连 PostgreSQL：驱动、连接池、事务边界与批量写入优化
做数据类项目的人几乎都写过这么一段代码读配置、拼连接串、cursor.execute、commit、关连接本地跑得飞快扔到常驻服务里跑两周凌晨开始零星报错日志里只有一句含糊的超时。我见过太多项目栽在Python 连接 PG 数据库这件看起来最不需要思考的事情上——不是不会连而是连上了之后对连接的生命周期、事务边界、批量写入的成本完全没有概念。这篇东西写给已经会用 Python 操作 PostgreSQL、但还没把这条链路吃透的人也写给刚开始接手数据同步、报表导出、后台任务这类活儿的朋友。下面这些内容不谈语法入门只讲选型依据、参数含义、踩坑现场和能被直接抄走的写法。1. 选驱动这件事先看清三条路的边界Python 连 PostgreSQL 的库不止一个网上一搜能出来七八种新人最容易犯的错是随手pip install第一个搜到的包然后在项目里用两年直到某天遇到性能瓶颈或者异步改造需求才发现换不掉。驱动选择不是审美问题它决定了你后面能拿到什么能力能不能用服务端预编译、能不能走 COPY 协议、能不能在协程里跑、出错时的异常类型细不细。这一节把三条主流路径的边界讲清楚选之前先对上自己的场景。1.1 psycopg2 和 psycopg3 不只是版本号之差psycopg2是历史最久的 C 扩展驱动也是绝大多数老项目里的默认选项。它底层走 libpqPG 官方客户端库所以连接参数、SSL 行为、认证方式跟psql命令行完全一致这一点在排查问题时非常值钱——你在 Python 里连不上用同样的参数跑一遍psql能立刻判断问题在代码还是在网络或认证。它的成熟度毋庸置疑缺点是设计停留在同步时代接口风格偏老游标上下文管理器只负责关闭游标不负责提交事务这个语义差异坑过不少人。psycopg3是同一个作者团队重写的版本包名就叫psycopg。它最大的变化是原生支持异步同时把参数绑定从客户端拼字符串改成可以走服务端绑定server-side binding也就是真正意义上的 prepared statement。此外它还支持 pipeline 模式把多条语句攒起来一次发出去减少网络往返。接口层面with conn:块退出时会自动提交或回滚with conn.cursor()的行为也更符合直觉。两者怎么选我给一个粗糙但实用的判断场景建议理由老项目、依赖链已锁定继续用 psycopg2换驱动收益低、回归成本高新项目、纯同步业务直接用 psycopg3少一层历史包袱后续加异步不用重写需要异步但不想引第二个库psycopg3 的 async 接口同步异步同一套 API团队心智负担小极端吞吐的写入/读取管道单独评估 COPY 与 asyncpg协议层实现差异会体现出来有一点必须提前说清楚psycopg2和psycopg3不能装在同一个环境里互相导入模块名虽然不同psycopg2vspsycopg但很多间接依赖会同时拉这两个装完之后import报奇怪的错八成是环境里两个都在。遇到这种情况老老实实建个干净虚拟环境重装比在依赖树里刨半小时快得多。1.2 异步场景asyncpg 与 psycopg3 async 的取舍如果服务本身跑在 asyncio 上FastAPI、aiohttp、或者自研的协程调度同步驱动会把事件循环堵死这是硬伤必须换异步方案。这时候有两条路。asyncpg是纯异步实现不走 libpq自己实现了 PG 的 wire protocol。带来的好处是性能确实好尤其在大量小查询和高并发短连接场景下连接建立和查询往返的开销都更小。代价是它的接口跟同步驱动差别较大占位符是$1、$2这种编号形式而不是%s没有游标这个概念取而代之的是 prepared statement 缓存类型转换逻辑也是自己的一套某些扩展类型需要手动注册编解码器。psycopg3的 async 接口则是同步 API 的镜像await conn.execute(...)、async with conn.cursor()写法几乎不用重新学。它的绝对性能不如 asyncpg但对大多数业务系统来说完全够用而且从同步迁到异步的改动量小得多的。我的经验是如果项目已经在异步栈上并且需要读写大量数据、对延迟敏感选 asyncpg如果只是想让接口不阻塞、不想让团队学两套 APIpsycopg3 的 async 更省事。别为了追那几个百分点的性能指标把一个稳定的同步项目硬改成异步——改成异步之后调试难度、栈信息可读性、第三方库兼容性都会变差这笔账要算进去。1.3 ORM 底下那层连接到底是谁用 SQLAlchemy 或者别的查询构建器时很多人以为自己不直接连数据库实际上 ORM 只是包了一层最底下还是某个驱动。SQLAlchemy 的连接串postgresqlpsycopg2://...或者postgresqlpsycopg://...里的后面就是驱动名写错了会在create_engine的时候直接抛ModuleNotFoundError。这一层要特别注意的地方是ORM 自带连接池池参数pool_size、max_overflow、pool_recycle、pool_pre_ping配不好出问题的表现和手写连接池一模一样但因为隔了一层排查会更绕。尤其是pool_pre_ping它会在借出连接前先发一个探测语句代价是每次借出多一次往返收益是能自动剔除已失效的连接。对于跨网络、中间有负载均衡或防火墙空闲超时的环境这个参数几乎是必开的否则你会看到闲置十分钟后第一次请求必失败这种诡异现象。还有一点值得提醒ORM 里手动执行原生 SQL 的时候事务边界由 session 控制别在session外面又自己commit一次两边打架会导致提交时机不可预期。用 ORM 就用它的模式用完记得close()session 不关就相当于连接不还。2. 一次握手背后的成本账大部分人对建立连接很贵这句话只有抽象认知不知道贵在哪所以也不会认真对待连接复用。PG 的连接成本比 MySQL 这类线程模型的数据库高一个量级原因在于它的进程模型——每来一个连接服务端postmaster进程就fork出一个独立的 backend 进程专门服务这个连接。这个进程有自己的内存空间基线几 MB 起步加上各种缓冲区、排序内存实际占用会更高。所以连接数不是随便开它直接对应服务端的进程数和内存占用。2.1 连接参数里真正需要手动设置的没几个一个典型的连接串长这样import psycopg DSN ( host10.0.0.12 port5432 dbnameapp_db userapp_rw password*** connect_timeout5 application_namebatch_sync_worker keepalives_idle30 keepalives_interval10 keepalives_count3 sslmoderequire options-c statement_timeout30000 -c timezoneUTC -c search_pathapp,public ) with psycopg.connect(DSN) as conn: with conn.cursor() as cur: cur.execute(select now()) print(cur.fetchone())这里每个参数都不是装饰品。connect_timeout5的意思是 TCP 连接和认证阶段最多等 5 秒不设的话默认可能等到操作系统层面的超时几十秒起步在服务启动阶段会表现为卡住不动。application_name是排查神器它会让你的连接在服务端视图里带上名字后面第 5 节会详细用到。keepalives_*三个参数管的是 TCP 保活对于长连接穿过中间网络设备的场景不设的话连接可能被静默丢弃客户端还以为自己活着写数据的时候才发现已经断了。options这个参数最容易被忽略但价值最高。它把服务端的会话级 GUC 参数直接塞进连接初始化里一次搞定。statement_timeout给一个防呆上限避免某条烂 SQL 把连接挂死timezone统一成 UTC把时区问题在入口处掐死search_path固定 schema 搜索顺序避免同名表被解析到错误的位置。这三条加上去能消掉相当一部分在我机器上是好的类问题。2.2 PG 为什么对连接数这么敏感前面提到 PG 每连接一个进程这带来两个直接后果。第一连接数上限max_connections是个硬墙撞上去就是FATAL: sorry, too many clients already此时新请求全部失败连管理员想连进去 kill 会话都可能连不上得留余量。第二每次建连都要 fork 进程 走完整认证流程如果是 SCRAM 认证还有多轮哈希计算这个开销相对于一条简单查询来说是压倒性的。做个粗算假设单次查询业务耗时 2ms建立连接TCP 认证 fork实测往往在 10ms 到 50ms 量级取决于网络和认证方式。也就是说如果每次请求都新建连接即使查询本身极快总耗时也会被建连开销主导。这就是为什么短连接 每条都新建的写法在压测时数字难看——不是数据库慢是你在为每条 SQL 付一次握手钱。理解了这一点连接池的意义就不只是复用对象而是把这段固定开销从请求路径上摘掉。一个健康的服务进程启动时建好几个连接放进池子之后所有请求都从池里借请求路径上就没有建连成本了。2.3 时区、编码与 search_path三个不报错但会出错的设置这三个设置共同的特点是配错了不会立刻报错但会在某个具体场景下产出错误结果而且往往过了很久才被发现。时区方面PG 的timestamp without time zone不存时区信息timestamp with time zone简称 timestamptz存的是 UTC 内部值加会话时区用于显示。Python 侧datetime有没有tzinfo又是一回事。典型事故是服务端会话时区是本地时区Python 侧写入带时区的 datetime读回来做比较时又当成 naive 处理结果差了 8 小时。最省心的做法是两头统一连接参数里-c timezoneUTCPython 侧统一用带时区的datetimedatetime.now(timezone.utc)数据库字段统一用timestamptz。编码方面现代 PG 集群默认 UTF8一般不用管。但如果对接的是老库、或者是 GBK 编码的历史数据表取出来就是乱码或者直接抛UnicodeDecodeError。这时候可以在连接参数里显式指定client_encoding但更该做的是把数据迁移到 UTF8编码转换只是止血。search_path的问题在多 schema 项目里特别常见。默认$user, public如果应用表在appschema 下而public下恰好有个同名视图或旧表你的查询会静默命中错误对象。这不是假设我见过报表数据和线上数据对不上的案例最后查出来是search_path顺序问题。显式写死-c search_pathapp,public或者干脆所有 SQL 都带 schema 前缀二选一别靠默认值。3. 连接池的容量怎么定出问题长什么样池子大小是问得最多、答得最含糊的问题。网上常见的回答是CPU 核数 × 2 磁盘数这个公式来自另一套连接模型的语境直接搬到 PG 上会偏大。PG 的连接是进程你的并发瓶颈通常不在数据库能不能同时处理这么多连接而在你的应用有多少并发真正需要同时持有连接。池子开太大服务端连接数被打满所有人一起等开太小请求排队吞吐上不去。这一节给一个能落地的推算方法以及两种典型故障的现场特征。3.1 池子上限的一次完整推算推算要自顶向下做不能拍脑袋。举个例子一个后台服务部署 4 个实例进程或容器每个实例内部又开了 8 个处理线程/协程数据库max_connections配的是 100。第一步先扣掉服务端必须保留的部分管理员连接、监控采集、备份任务、运维手工登录保守留 20 个。第二步算应用可用总量100 - 20 80。第三步算每实例预算80 / 4 20。这 20 是硬上限不能超。第四步落到池参数如果每个实例 8 个并发处理单元池子上限设 8 到 12 就够了剩下的作为突发余量。设成 20 也不会更快因为并发单元只有 8 个多出来的连接只会闲置占内存。第五步考虑扩展如果未来要扩到 8 个实例每实例预算变成 10池子上限就得压到 10 以下。所以池子上限这个值应该跟着部署规模一起进配置中心而不是硬编码在代码里。这一点很关键——很多事故是扩容之后忘了改池参数8 个实例每个还按 20 开直接把服务端打满。还有一个反直觉的结论值得说对于 OLTP 类短查询池子开小一点、让请求排队往往比开大更好。因为连接太多会让服务端的进程调度、共享缓冲区争用变严重吞吐反而下降。真正的扩容手段是优化查询和加索引不是加连接。3.2 泄漏与耗尽的现场特征对比池子的两类故障表现相似但根因完全不同分清才能对症。特征连接泄漏容量不足报错内容取连接超时PoolTimeout/pool exhausted同样是取连接超时时间分布随时间推移越来越频繁重启后缓解高峰期集中出现低峰正常服务端连接数持续增长不回落直到打满稳定在上限附近波动典型原因异常路径未归还、cursor 未关、生成器提前退出池子上限小于实际并发需求处理方向查代码里的资源释放路径调参或减少单请求持有时长泄漏的排查有很实用的招给连接池加一个记录借出位置的机制在借出时记下调用栈超时未归还时把栈打出来。psycopg 的池子支持配置回调或者简单点在业务代码里包一层函数借出时记录traceback.extract_stack()归还时清掉。跑一天看谁借了不还一目了然。另一个隐蔽的泄漏源是长事务。有些代码借了连接开事务中间做了一堆业务处理调外部接口、算数据、写文件最后才提交。这段时间连接一直被占着业务一慢池子就被拖干。正确做法是把事务窗口压到最短数据准备好之后再借连接进去就写写完立刻提交归还。3.3 借还姿势上下文管理器的正确用法手写try/finally归还连接当然可以但漏写概率不低。用上下文管理器是最省心的方式只是要注意不同库的语义差别。psycopg3 的连接池用法from psycopg_pool import ConnectionPool pool ConnectionPool( conninfoDSN, min_size2, max_size10, timeout5, # 借连接最多等 5 秒 max_idle300, # 空闲超过 5 分钟回收 max_lifetime1800, # 连接最长活 30 分钟防止中间设备掐断 kwargs{autocommit: False}, ) with pool.connection() as conn: # 退出时提交或回滚并归还连接 with conn.cursor() as cur: cur.execute(update orders set status%s where id%s, (paid, 1024))这里max_lifetime是很多人不设、然后在生产环境吃亏的参数。中间如果有负载均衡或者网络设备存在空闲超时一条活了两小时的老连接可能在服务端已经被清掉了客户端却还拿着不放下一次使用就报连接已关闭。设一个小于中间设备超时时间的max_lifetime让池子主动做新老更替能消掉这类偶发故障。psycopg2 的ThreadedConnectionPool需要注意它的getconn()和putconn()必须配对如果中间抛异常而没在finally里归还连接就永久丢了。所以用它的时候老老实实写try/finallyfrom psycopg2 import pool as pg_pool p pg_pool.ThreadedConnectionPool(2, 10, dsnDSN) conn None try: conn p.getconn() with conn.cursor() as cur: cur.execute(select count(*) from orders) total cur.fetchone()[0] conn.commit() finally: if conn is not None: p.putconn(conn) # 无论成功失败都要归还SQLAlchemy 那边则是session的close()和pool_pre_ping、pool_recycle的组合思路一样让池子有能力剔除坏连接让代码保证用完归还。4. 写入场景里那些慢在不该慢的地方读操作慢通常是索引和查询计划的问题写操作慢则大量来自写法。同样是把十万行数据塞进一张表不同写法的耗时能差一到两个数量级而很多人的第一反应是数据库性能不行然后去加机器。这一节把三种写入方式的成本结构摊开讲顺带说清事务边界怎么划。4.1 逐条插入、批量插入与 COPY 的差距先看三条路的本质差异。逐条execute加commit每条 SQL 一次网络往返一次事务提交。PG 每次提交都要保证 WAL 落盘synchronous_commiton时这是最贵的部分。十万行就是十万次往返加十万次落盘慢是必然的。逐条execute放在一个事务里网络往返还是十万次但落盘只有一次。这一下就能快好几倍说明事务边界的影响比很多人想象的大。批量execute_values或executemanypsycopg3 的executemany内部会做流水线优化把多行拼成一条insert ... values (...),(...),(...)发出去往返次数降到 行数 / 批大小。批大小要控制太大会撞上服务端的语句长度限制和内存问题太小吃不到收益。经验值在 500 到 5000 行之间具体看单行宽度。COPY走 PG 专门的批量导入协议绕开 SQL 解析和逐行规划把数据流式灌进去。这是最快的方案数量级的领先。psycopg3 提供了cursor.copy()配合copy.set_types做两向转换psycopg2 用copy_expert。# psycopg3 的 COPY 写入注意 write_row 需要按列类型序列化 with pool.connection() as conn: with conn.cursor() as cur: with cur.copy(copy app.orders (id, amount, created_at) from stdin) as copy: for row in rows: copy.write_row(row) # row 为元组顺序与列表一致三个方案的选择不用纠结看数据量和频率方案适用规模额外成本备注单条 execute 每行提交几行、低频极慢只适合交互式操作批量 execute 单事务千到十万级需处理批大小通用性最好能带on conflictCOPY十万级以上不能用on conflict需先落到临时表再合并导入/同步类任务首选COPY 的局限要提前知道它不支持ON CONFLICT所以做增量同步时通常的套路是先COPY到一张临时表create temp table ... (like target)再用一条insert ... select ... on conflict do update合并进目标表最后清临时表。多一步但整体仍然远快于逐条 upsert。4.2 事务边界划在哪里才不会拖垮库事务边界不只是性能问题还是数据库整体健康问题。PG 用的是 MVCC 加清理机制长事务会阻止旧版本行被回收导致表膨胀、查询变慢、磁盘暴涨。一个挂着不提交的事务状态idle in transaction能造成的破坏往往超过它本身执行的那几条 SQL。划边界的三个原则。第一事务里只放数据库操作不放外部调用。调接口、读文件、算哈希这些全部挪到事务外面数据准备齐了再进事务。第二批处理要分批提交不要一个事务吞几十万行。每批几千到一万行提交一次既能控制回滚范围也不会让 WAL 一次性涨太多。第三显式设置statement_timeout和idle_in_transaction_session_timeout让服务端主动帮你收拾挂死的会话。后者尤其重要它会在会话处于idle in transaction超过设定时间时直接断开是对付代码疏漏的最后一道闸。-- 会话级兜底建议写进连接参数 SET statement_timeout 30s; SET idle_in_transaction_session_timeout 60s; SET lock_timeout 5s;lock_timeout是另一个值得加的参数。它在等锁超过指定时间后直接报错而不是无限等下去。没有它一个被锁住的语句会一直占着连接慢慢把池子耗干形成雪崩。加上之后至少能在几秒内失败并释放资源。4.3 冲突处理与 RETURNING 的实用写法增量同步几乎一定会遇到主键或唯一键冲突处理方式有三种查一下再插两次往返还有并发竞态、捕获异常再改插异常处理成本高还可能污染事务、直接ON CONFLICT一条语句解决。显然选第三种。sql insert into app.orders (id, amount, status, updated_at) values (%s, %s, %s, %s) on conflict (id) do update set amount excluded.amount, status excluded.status, updated_at excluded.updated_at where app.orders.updated_at excluded.updated_at returning id, (xmax 0) as inserted 这段有几个值得说明的点。excluded指代本次尝试插入的那行数据用它取值是标准写法。where子句让更新有条件——只有新数据更新时才覆盖避免旧数据把新数据盖掉这在乱序到达的同步场景里是保命的。returning (xmax 0)是个常用技巧能区分这行是插入的还是更新的做统计很方便虽然它依赖内部实现细节不适合写进强契约的逻辑里做日志和监控足够用。还有个小提醒ON CONFLICT需要目标列上有唯一索引或主键否则会报没有匹配的唯一约束。做分表或者临时表的时候很容易忘报错信息其实写得很清楚照着补索引就行。5. 报错现场排查从客户端日志摸到服务端会话排查数据库问题最有效的方法是分层定位先确定是连不上还是连上了报错再确定是客户端问题还是服务端问题最后才去读具体报错。跳过前面两步直接看异常文本很容易被表象带偏。这一节按故障层次展开给一套能照着走的排查链路。5.1 连不上拒绝连接、认证失败、超时的区分方法这三类错误在 Python 里最终都表现为一个异常但含义完全不同处理方向也完全不同。connection refusedTCP 层就被拒绝了说明目标端口上没有服务在监听或者中间有设备主动拒绝。检查方向是网络连通性、端口映射、服务是否启动。可以用nc -zv host port快速确认端口通不通比看 Python 栈快。timeout或connection timed out包发出去了没有响应。常见原因是防火墙丢包、安全组没放行、或者connect_timeout设得太短而认证本身较慢。这里的排查重点是网络策略不是密码。认证失败password authentication failed或no pg_hba.conf entryTCP 通了服务端也响应了卡在认证。前者的意思是账号密码或认证方式不对后者的意思是服务端的访问控制配置里没有覆盖你这个来源地址。这两个报错信息都很明确照着改就行但要注意一个坑改完pg_hba.conf需要 reload 才生效忘了 reload 会让你以为改错了。排查这类问题的标准动作是拿 Python 里用的那套参数原封不动地用psql连一次。psql连不上问题在环境psql能连上问题在代码——通常是连接串拼错了、环境变量没读到、或者用了不同的配置文件。这一步能省掉大量猜测。# 参数完全对齐只用来验证连通性 psql host10.0.0.12 port5432 dbnameapp_db userapp_rw sslmoderequire -c select 15.2 连上了但语句报错类型与编码两类高频问题连上之后的报错里类型和编码占了大多数。类型方面最典型的是把字符串塞进数值列、把 naive datetime 塞进 timestamptz 列、以及numeric读出来是Decimal而代码里当float用。前两个会直接报错反而好办第三个不报错但会出精度问题——Decimal做除法、做json.dumps都会抛异常很多人这时候才第一次意识到numeric映射过来不是 float。jsonb字段也常出问题。psycopg 对jsonb会自动做序列化/反序列化但如果你把一个Decimal或者datetime放进 dict 里再写进去标准 JSON 编码器不认这些类型会报序列化错误。解决办法是传Jsonb包装并配自定义编码器或者写入前先把值转成字符串/浮点。编码问题表现为UnicodeDecodeError或者存进去是问号。检查顺序是数据库server_encoding、客户端client_encoding、Python 里字符串的实际编码、以及中间有没有做过.encode()又.decode()的操作。老系统里常见的是某处用了默认编码做了一次不必要的转换把 UTF8 数据搞坏了这种要靠定位具体字段逐个比对才能找到。还有一类错误值得单说唯一约束冲突、外键约束冲突这些在 psycopg 里是具体异常类型UniqueViolation、ForeignKeyViolation但关键是——发生这类错误后当前事务已经处于中止状态必须rollback才能继续执行下一条语句否则后面所有语句都会报当前事务已中止。这个行为坑过无数人表现为第一条报错之后全是错。import psycopg from psycopg import errors try: with conn.cursor() as cur: cur.execute(insert into app.orders (id) values (%s), (1024,)) conn.commit() except errors.UniqueViolation: conn.rollback() # 必须回滚否则连接不可继续使用 # 走更新分支5.3 常驻服务里的隐性故障idle in transaction 与连接假死最难受的故障不是报错而是有时候不对。两类典型一是连接池慢慢被idle in transaction的会话占满二是连接看起来还活着但实际已经断了。排查第一类靠application_name加上服务端视图。给每个服务设一个能识别的名字出问题时直接查select pid, application_name, state, now() - state_change as duration, left(query, 80) as last_query from pg_stat_activity where datname app_db and state idle order by duration desc limit 20;看state idle in transaction且duration很长的行就是挂着不提交的会话。query字段能看到它最后执行的是什么顺着去代码里找对应的位置。这类会话的来源通常是借了连接开事务中间做了别的耗时操作或者异常路径上没回滚也没归还。排查第二类现象是报server closed the connection unexpectedly或者connection already closed且多发生在连接闲置一段时间后的第一次使用。原因通常是中间网络设备有连接跟踪表空闲久了把表项清了两端却不知道。对策前面提过设keepalives_*让 TCP 层主动探活设池的max_lifetime让连接定期换新开pool_pre_ping让借出前先验活。三个措施任选两个基本就能压住。最后分享一个我在常驻服务里一直保留的习惯给数据访问层加一层薄薄的计时日志记录每次数据库操作的耗时、影响行数、以及连接是从池里借的还是新建的。日志不用打全量采样 1% 或者只记录超过阈值的那部分就够了。这套东西平时看着没用一旦出问题能让你在几分钟内判断出是某条 SQL 变慢了还是池子借不出来了比事后加日志再复现快得多。我在实际使用中体会最深的一点是Python 连 PG 这条链路上的绝大多数问题都不是 API 用错而是对连接的资源属性没有建立起感觉——它是稀缺的、有状态的、需要被显式管理的。把连接池参数、事务边界、超时兜底这三件事在项目初期定好后面能省掉的排查时间远超当初花的那几个小时。