基于Python实现大型数据集向PostgreSQL的高效迁移与加载

一、先把连接复用这件事焊死 #

绝对不要每次插入都新建/关闭PostgreSQL连接，初始化时建立1个或固定数量的长连接，全程复用直到所有迁移完成再关闭。用SQLAlchemy的话，直接创建Engine复用连接池就行，不用手动来回开关连接。

二、分批处理+边处理边插（替代全量预处理后插入） #

• 从Access分批读数据：别一次性把所有数据拽到内存里，用Access的TOP+OFFSET或者按主键分段（比如按ID拆成0-10000、10001-20000这种区间），每次只读一批。
• 单批处理完立刻插：每读一批就做预处理，然后直接批量插入PostgreSQL，循环到所有数据处理完。既不会爆内存，也不用等全量预处理完再动手，能省不少时间。
• 用PostgreSQL原生COPY命令插：这比pandas的to_sql快好几倍，大数据量下差距尤其明显。用psycopg2的copy_from方法就行，把预处理后的DataFrame转成内存文件对象（比如StringIO），直接用COPY导入，示例代码：

import psycopg2
from io import StringIO
import pandas as pd

# conn是提前建好的PostgreSQL长连接
cur = conn.cursor()
# 假设df是当前批次预处理后的数据
output = StringIO()
df.to_csv(output, sep='\t', header=False, index=False)
output.seek(0)
cur.copy_from(output, '目标表名', columns=df.columns, sep='\t')
conn.commit()

三、多线程/多进程要这么玩才有用 #

• 多线程适合IO密集型任务（数据库读写），但别让线程共享连接，每个线程单独从连接池拿一个连接用。
• 拆分任务：按Access的数据分段给每个线程分配任务，比如100万条拆成10批，每个线程处理一批的读、预处理、插入。
• 别搞全局共享：每个线程处理自己的批次数据，不要在多线程里操作同一个DataFrame，避免锁冲突拖慢速度。
• 控制线程数量：别开太多，一般和CPU核心数或者PostgreSQL允许的最大连接数匹配（10-20个足够，太多反而会因为连接竞争变慢）。

四、预处理能省则省 #

• 尽量让Access先做一部分预处理：比如用Access SQL直接过滤无效数据、计算简单字段，少读点原始数据到内存里，省得在Python里再折腾。
• 用矢量化操作：别用Python循环逐行处理，用pandas、numpy的矢量化方法，速度能提一大截。

五、其他能提速的小技巧 #

• 手动控制事务提交：批量插入时别开自动提交，每批插完再手动commit一次，减少事务开销。
• 临时关索引和约束：迁移前先禁用目标表的索引、外键约束，迁完再重建。边插边更新索引巨慢，重建索引反而快得多。
• 先转CSV再导入：如果预处理逻辑不复杂，直接用Access把数据导出成CSV，再用PostgreSQL的COPY命令直接导，这是最快的路子之一，后续预处理可以放到PostgreSQL里做。

内容的提问来源于stack exchange，提问作者Maxwell Amoako Agyapong