做数据类项目的人几乎都写过这么一段代码：读配置、拼连接串、cursor.execute、commit、关连接，本地跑得飞快，扔到常驻服务里跑两周，凌晨开始零星报错，日志里只有一句含糊的超时。我见过太多项目栽在"Python 连接 PG 数据库"这件看起来最不需要思考的事情上——不是不会连，而是连上了之后对连接的生命周期、事务边界、批量写入的成本完全没有概念。这篇东西写给已经会用 Python 操作 PostgreSQL、但还没把这条链路吃透的人，也写给刚开始接手数据同步、报表导出、后台任务这类活儿的朋友。下面这些内容不谈语法入门，只讲选型依据、参数含义、踩坑现场和能被直接抄走的写法。

1. 选驱动这件事，先看清三条路的边界

Python 连 PostgreSQL 的库不止一个，网上一搜能出来七八种，新人最容易犯的错是随手pip install第一个搜到的包，然后在项目里用两年，直到某天遇到性能瓶颈或者异步改造需求才发现换不掉。驱动选择不是审美问题，它决定了你后面能拿到什么能力：能不能用服务端预编译、能不能走 COPY 协议、能不能在协程里跑、出错时的异常类型细不细。这一节把三条主流路径的边界讲清楚，选之前先对上自己的场景。

1.1 psycopg2 和 psycopg3 不只是版本号之差

psycopg2是历史最久的 C 扩展驱动，也是绝大多数老项目里的默认选项。它底层走 libpq（PG 官方客户端库），所以连接参数、SSL 行为、认证方式跟psql命令行完全一致，这一点在排查问题时非常值钱——你在 Python 里连不上，用同样的参数跑一遍psql，能立刻判断问题在代码还是在网络或认证。它的成熟度毋庸置疑，缺点是设计停留在同步时代，接口风格偏老，游标上下文管理器只负责关闭游标，不负责提交事务，这个语义差异坑过不少人。

psycopg3是同一个作者团队重写的版本，包名就叫psycopg。它最大的变化是原生支持异步，同时把参数绑定从"客户端拼字符串"改成可以走服务端绑定（server-side binding），也就是真正意义上的 prepared statement。此外它还支持 pipeline 模式，把多条语句攒起来一次发出去，减少网络往返。接口层面，with conn:块退出时会自动提交或回滚，with conn.cursor()的行为也更符合直觉。

两者怎么选，我给一个粗糙但实用的判断：

有一点必须提前说清楚：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；如果只是想让接口不阻塞、不想让团队学两套 API，psycopg3 的 async 更省事。别为了追那几个百分点的性能指标，把一个稳定的同步项目硬改成异步——改成异步之后调试难度、栈信息可读性、第三方库兼容性都会变差，这笔账要算进去。

1.3 ORM 底下那层连接到底是谁

用 SQLAlchemy 或者别的查询构建器时，很多人以为自己"不直接连数据库"，实际上 ORM 只是包了一层，最底下还是某个驱动。SQLAlchemy 的连接串postgresql+psycopg2://...或者postgresql+psycopg://...里的+后面就是驱动名，写错了会在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 = ( "host=10.0.0.12 port=5432 dbname=app_db user=app_rw password=*** " "connect_timeout=5 " "application_name=batch_sync_worker " "keepalives_idle=30 keepalives_interval=10 keepalives_count=3 " "sslmode=require " "options='-c statement_timeout=30000 -c timezone=UTC -c search_path=app,public'" ) with psycopg.connect(DSN) as conn: with conn.cursor() as cur: cur.execute("select now()") print(cur.fetchone())

这里每个参数都不是装饰品。connect_timeout=5的意思是 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 timezone=UTC，Python 侧统一用带时区的datetime（datetime.now(timezone.utc)），数据库字段统一用timestamptz。

编码方面，现代 PG 集群默认 UTF8，一般不用管。但如果对接的是老库、或者是 GBK 编码的历史数据表，取出来就是乱码或者直接抛UnicodeDecodeError。这时候可以在连接参数里显式指定client_encoding，但更该做的是把数据迁移到 UTF8，编码转换只是止血。

search_path的问题在多 schema 项目里特别常见。默认"$user", public，如果应用表在appschema 下，而public下恰好有个同名视图或旧表，你的查询会静默命中错误对象。这不是假设，我见过报表数据和线上数据对不上的案例，最后查出来是search_path顺序问题。显式写死-c search_path=app,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 泄漏与耗尽的现场特征对比

池子的两类故障表现相似但根因完全不同，分清才能对症。

泄漏的排查有很实用的招：给连接池加一个记录借出位置的机制，在借出时记下调用栈，超时未归还时把栈打出来。psycopg 的池子支持配置回调，或者简单点，在业务代码里包一层函数，借出时记录traceback.extract_stack()，归还时清掉。跑一天，看谁借了不还，一目了然。

另一个隐蔽的泄漏源是"长事务"。有些代码借了连接开事务，中间做了一堆业务处理（调外部接口、算数据、写文件），最后才提交。这段时间连接一直被占着，业务一慢，池子就被拖干。正确做法是把事务窗口压到最短：数据准备好之后再借连接，进去就写，写完立刻提交归还。

3.3 借还姿势：上下文管理器的正确用法

手写try/finally归还连接当然可以，但漏写概率不低。用上下文管理器是最省心的方式，只是要注意不同库的语义差别。

psycopg3 的连接池用法：

from psycopg_pool import ConnectionPool pool = ConnectionPool( conninfo=DSN, min_size=2, max_size=10, timeout=5, # 借连接最多等 5 秒 max_idle=300, # 空闲超过 5 分钟回收 max_lifetime=1800, # 连接最长活 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/finally：

from psycopg2 import pool as pg_pool p = pg_pool.ThreadedConnectionPool(2, 10, dsn=DSN) 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_commit=on时），这是最贵的部分。十万行就是十万次往返加十万次落盘，慢是必然的。

逐条execute放在一个事务里：网络往返还是十万次，但落盘只有一次。这一下就能快好几倍，说明事务边界的影响比很多人想象的大。

批量execute_values或executemany（psycopg3 的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 为元组，顺序与列表一致

三个方案的选择不用纠结，看数据量和频率：

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 refused：TCP 层就被拒绝了，说明目标端口上没有服务在监听，或者中间有设备主动拒绝。检查方向是网络连通性、端口映射、服务是否启动。可以用nc -zv host port快速确认端口通不通，比看 Python 栈快。

timeout或connection timed out：包发出去了没有响应。常见原因是防火墙丢包、安全组没放行、或者connect_timeout设得太短而认证本身较慢。这里的排查重点是网络策略，不是密码。

认证失败（password authentication failed或no pg_hba.conf entry）：TCP 通了，服务端也响应了，卡在认证。前者的意思是账号密码或认证方式不对，后者的意思是服务端的访问控制配置里没有覆盖你这个来源地址。这两个报错信息都很明确，照着改就行，但要注意一个坑：改完pg_hba.conf需要 reload 才生效，忘了 reload 会让你以为改错了。

排查这类问题的标准动作是：拿 Python 里用的那套参数，原封不动地用psql连一次。psql连不上，问题在环境；psql能连上，问题在代码——通常是连接串拼错了、环境变量没读到、或者用了不同的配置文件。这一步能省掉大量猜测。

# 参数完全对齐，只用来验证连通性 psql "host=10.0.0.12 port=5432 dbname=app_db user=app_rw sslmode=require" -c "select 1"

5.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 用错，而是对连接的资源属性没有建立起感觉——它是稀缺的、有状态的、需要被显式管理的。把连接池参数、事务边界、超时兜底这三件事在项目初期定好，后面能省掉的排查时间远超当初花的那几个小时。