to_sql写pg性能问题跟踪解决

1. 问题描述

有部分数据，从原来写mysql实例，改成写pg实例，用pandas.DataFrame.to_sql方法。

期间发现，同样的数据写mysql和pg，耗时差异相当大
10万+数据量，mysql 1-3秒，pg 2-3分钟
40万+数据量，mysql 5-8秒，pg 9-10分钟

写mysql和pg代码都差不多，写pg代码示例如下

engine = create_engine('postgresql://use:password@host:port/database')

connect = engine.connect()

df.to_sql(table, engine, index=False, if_exists='append')

2. 问题分析

2.1 机器差异

同样的数据，基本相同结构的表，写性能相差那么大，第一直觉是mysql和pg实例差异。

跟运维同事要到pg实例的监控，pg性能完全没有问题，cpu、io、mem负载都很低，不超过10%，没有大的波动，机器性能完全没压榨出来。

2.2 表结构及数据问题

既然机器不是问题，那可能是表结构或者数据特征，对mysql友好，对pg不友好。尽管可能性很小，但还是要尝试一下。

• 将简单的表结构，条数不变，批量写pg性能提升不大
• 将数据类型改成简单类型，过滤处理空值数据，条数不变，批量写pg性能提升不大

测试证明，不是表结构问题，也不是数据问题，很可能就是to_sql本身的问题。

2.3 to_sql方法分析

pandas.to_sql方法提供了很多参数，参考了to_sql的优化方法，做了些尝试，没什么效果。

也有人总结说to_sql方法写pg有问题，建议利用pg的copy from特性，解决批量写pg的性能瓶颈。

3. 优化方法

3.1 to_sql方法调参

调整chunksize，设置dtype参数，等等，耗时变化不大。

但有文章指出，to_sql方法稳定，不会对数据库内存和IO有压力。

3.2 insert拼接

将40万条数据分批写入，每批10000条，拼接insert sql语句，耗时缩短1半多，还是有明显提升。

但这种方法太笨拙，需要把DataFrame数据遍历处理，不通用，性能提升有限。

3.3 pg的copy from特性

有人分享了pg的优化方法，利用StringIO和pg的copy from特征，将DataFrame先用to_csv到string io，然后从string io copy到pg。

示例具体代码如下，没有用copy_from方法，而是copy_expert

import io

def write_to_pg(engine, df, table):
        string_io_data = io.StringIO()
        df.to_csv(string_io_data, sep='|', index=False)
        string_io_data.seek(0)
        with engine.connect() as connection:
            with connection.connection.cursor() as cursor:
                copy_cmd = "COPY {} FROM STDIN HEADER DELIMITER '|' CSV".format(table)
                cursor.copy_expert(copy_cmd, string_io_data)
            connection.connection.commit()

优化前后效果对比：

数据量	mysql(to_sql)	pg(to_sql)	pg(copy_from)
10万	1-3秒	2-3分钟	1-2秒
40万	5-8秒	9-10分钟	3-4秒

copy_expert直接将IO缓冲区数据复制到pg目标表，内存性能提升明显，但对数据库IO有一定压力。

另外，DataFrame的columns必须是和目标表的columns顺序一致，不然会导致数据列错位。也可以用copy_from方法，调用的时候才指定columns。