核心内容摘要
两年半社区lnd1.2.3.ask：共建数字家园，点亮智慧生活
在大数据处理场景中Python与PostgreSQL的组合因其稳定性与扩展性成为主流选择。
然而当面对百万级甚至千万级数据写入时不同方法的选择会直接影响性能表现。
本文通过实测对比三种主流方案揭示不同场景下的最优解。
性能测试环境硬件配置AWS EC2 r
4xlarge实例16核64GB内存数据库版本PostgreSQL
1
0数据规模单表1000万条记录每条记录含10个字段总大小约
2GB测试指标写入速度条/秒、内存占用、CPU利用率
三种主流方案深度对比方案1executemany()批量插入基础方案importpsycopg2frompsycopg
extrasimportexecute_valuesdefexecutemany_insert(data):connpsycopg
connect(...)cursorconn.cursor()sqlINSERT INTO test_table VALUES %sexecute_values(cursor,sql,data,templateNone,page_size
conn.commit()性能表现写入速度约8,200条/秒内存占用峰值12GB处理1000万条数据时CPU利用率单核满载约100%适用场景数据量较小10万条简单字段类型无JSON/数组等复杂类型开发环境快速验证优化建议调整page_size参数实测
为最佳区间配合连接池使用如DBUtils.PooledDB方案2COPY命令高性能方案fromioimportStringIOimportpandasaspddefcopy_insert(df):connpsycopg
connect(...)cursorconn.cursor()outputStringIO()df.to_csv(output,sep\t,headerFalse,indexFalse)output.seek(
cursor.copy_from(output,test_table,null)conn.commit()性能表现写入速度约19,500条/秒PostgreSQL官方实测数据内存占用峰值8GB处理1000万条数据时CPU利用率多核并行约300%利用率关键优势绕过SQL解析层直接写入数据文件支持事务批量提交减少I/O操作天然支持复杂数据类型JSONB/数组等
注意事项数据格式要求严格需处理特殊字符转义错误处理较复杂需捕获psycopg
DataError不支持ON CONFLICT等高级SQL语法方案3pandas.to_sql()便捷方案fromsqlalchemyimportcreate_engineimportpandasaspddefpandas_insert(df):enginecreate_engine(postgresql://...)df.to_sql(test_table,engine,if_existsappend,indexFalse,chunksize
性能表现写入速度约3,200条/秒内存占用峰值15GB处理1000万条数据时CPU利用率单核中等负载约60%适用场景数据预处理复杂需大量pandas操作开发效率优先小批量数据更新性能瓶颈逐条生成INSERT语句频繁的客户端-服务器通信缺乏连接复用机制
进阶优化方案
预处理语句Prepared Statementsdefprepared_insert(data_batches):withconnection.cursor()ascursor:pg_conncursor.connection pg_cursorpg_conn.cursor()pg_cursor.execute( PREPARE my_insert (BIGINT, TEXT, NUMERIC) AS INSERT INTO test_table VALUES ($1, $2, $
)forbatchindata_batches:pg_cursor.execute(BEGIN)forrowinbatch:pg_cursor.execute(EXECUTE my_insert (%s, %s, %s),row)pg_cursor.execute(COMMIT)性能提升解析开销降低70%适合重复执行相同结构的插入操作支持ON CONFLICT等复杂逻辑
多线程并行写入fromconcurrent.futuresimportThreadPoolExecutordefparallel_copy(df_list):defprocess_chunk(df):connpsycopg
connect(...)cursorconn.cursor()outputStringIO()df.to_csv(output,sep\t,headerFalse,indexFalse)output.seek(
cursor.copy_from(output,test_table,null)conn.commit()withThreadPoolExecutor(max_workers
asexecutor:executor.map(process_chunk,df_list)性能表现4线程时写入速度达28,000条/秒需注意数据库连接数限制事务隔离级别设置磁盘I/O成为新瓶颈
实测数据对比方案写入速度条/秒内存占用CPU利用率复杂度executemany()8,20012GB100%★☆☆COPY命令19,5008GB300%★★☆pandas.to_sql()3,20015GB60%★★★预处理语句14,00010GB150%★★☆多线程COPY28,00012GB400%★★★★
最佳实践建议百万级数据优先使用COPY命令配合连接池管理千万级数据采用多线程COPY方案注意调整max_prepared_transactions参数增大shared_buffers建议设为物理内存的25%优化checkpoint_completion_target建议
9实时更新场景预处理语句连接池组合复杂ETL流程pandas预处理COPY最终写入
性能调优参数# postgresql.conf 关键参数 max_connections 200 shared_buffers 16GB work_mem 64MB maintenance_work_mem 1GB max_wal_size 4GB checkpoint_completion_target
9 bgwriter_lru_maxpages 1000结语在PostgreSQL
1
0的测试环境中COPY命令展现出碾压性优势其写入速度是传统executemany()方案的
4倍。
对于超大规模数据导入结合多线程与预处理语句的混合方案可将性能提升至接近理论极限。
实际生产环境中建议根据数据特征字段复杂度、更新频率等选择最适合的方案并通过监控工具如pgBadger持续优化。