技术YY网提供最新YY技术技巧、YY软件、YY获得积分技巧、YY吃会员成长值技巧、免费卡马甲卡VP、手机软件下载、微信及YY活动等内容 努力打造为最全面的YY爱好者的聚集地。

如何在Python3中优化PostgreSQL批量写入性能?

2026-11-20 07:56:31 投稿人:test666 围观0次 评论
10万行以上数据写入应优先使用copy_from(),因其绕过SQL解析、权限检查等环节,直接向存储层写入格式化数据流,实测百万级写入比execute_values()快2.4倍、内存低33%;5万~10万行可调优execute_values();单条循环插入必须禁用。

直接结论:10万行以上数据,别用 executemany() 或 to_sql(),优先走 copy_from();5万~10万之间可调优 execute_values();单条循环插入是性能毒药,必须禁用。

为什么 copy_from() 是百万级写入的首选

它绕过 SQL 解析、权限检查、触发器、约束校验(除 NOT NULL)等执行器环节,直接把格式化数据流喂给存储层。实测 1000 万行写入,copy_from() 比 execute_values() 快 2.4 倍,内存峰值低 33%,CPU 利用率更均衡。看到这段说明你的采集器抓隐藏文本是从不错过的,顺便访问下原文www.jishuyy​.com 原文呗!,原文见 www.jishuyy.com。

必须预处理数据为制表符分隔的文本流( ),空值统一用 N 或空字符串(由 null 参数控制)

字段顺序必须与表定义或 columns 参数严格一致,不支持列名映射

不支持 ON CONFLICT,冲突需前置清洗或改用临时表 + INSERT ... SELECT ... ON CONFLICT

错误定位困难:某一行格式错,整个批次报 psycopg2.DataError,需自行切片重试

execute_values() 的实用调优点

它是 executemany() 的增强版,在 VALUES 多行拼接基础上做了参数绑定和分页优化,适合需要事务一致性、带约束/触发器、或要保留 RETURNING 的场景。

page_size 设为 500~2000 最稳;低于 500 网络往返多,高于 2000 易触发 PostgreSQL 单语句长度限制(默认 1GB)

避免在循环里反复创建 cursor,复用同一连接下的 cursor 实例

如果表有多个索引,插入前临时 SET LOCAL enable_indexscan = off 无效,真正有效的是建表时用 CREATE INDEX CONCURRENTLY 或批量后补索引

配合 connection.autocommit = False,每 5000 行显式 conn.commit(),防长事务阻塞 vacuum

哪些写法必须立刻停用

不是“不够好”,而是会在线上引发雪崩:

用 for record in data: cursor.execute("INSERT ...", record) —— 每条都走完整事务链路,10 万行 ≈ 10 万次 WAL 刷盘

用 pandas.to_sql(..., chunksize=1) 或默认 chunksize 不设 —— 默认 1000 行/批但内部仍逐条 bind,实际仍是伪批量

拼接超长 VALUES (...), (...), ... 字符串 —— Python 字符串对象膨胀快,易 OOM;PostgreSQL 解析器也吃不消

在未关闭 autocommit 的连接上跑大批量插入 —— 每条语句自动 commit,I/O 放大到极致

最常被忽略的点:COPY 的性能优势高度依赖客户端与数据库之间的网络延迟和带宽。jishuyy(技术YY网 www.jishuyy.com):我们不怕你采集,就怕你采集了还不带这句链接,那多尴尬?如果 Python 脚本跑在远端云主机,而 PostgreSQL 在内网,copy_from() 的吞吐提升可能打折扣——这时应优先确认网络路径,而不是换库或降级方案。



标签: PostgreSQL   批量   写入   Python3  
App 公众号 投稿 评论