psycopg2与psycopg3批量插入PostgreSQL的性能差异问题求助

大家好，我最近在处理PostgreSQL的高频批量写入场景时遇到了一个头疼的性能问题，想请各位帮忙分析下原因。

先说说我的场景：我要往数据库写入大量数据，而且写入频率很高，所以已经给数据库做了关闭WAL等一系列优化来提升写入速度。

之前用psycopg2的时候，性能表现一直很好——用execute_values批量插入1000条数据，只需要0.1-0.15秒。当时的代码是这样的：

from psycopg2.extras import execute_values
import time
from sqlalchemy import create_engine
import logging

logger = logging.getLogger(__name__)

# 初始化数据库连接池
self.engine = create_engine(f'postgresql+psycopg2://postgres:password@localhost/postgres', pool_size=DB_POOL_SIZE, max_overflow=20)

def insert_data_todb(self, table_name, batch_data):
    try:
        t1 = time.perf_counter()
        insert_sql = f"""INSERT INTO {table_name} ({self._market_snapshot_columns_str}) VALUES %s;"""
        with self.engine.connect() as conn, conn.connection.cursor() as cur:
                execute_values(cur, insert_sql, batch_data)
        t2 = time.perf_counter()
        logger.info(f"Inserted {len(batch_data)} records in {t2 - t1} seconds")
    except Exception as ex:
        logger.error(f"Error inserting batch data into {table_name}:")
        logger.exception(ex)

后来我卸载了psycopg2，换成了psycopg 3.2，并且改用psycopg3的executemany方法来实现批量插入，结果性能直接崩了——插入同样的1000条数据，居然要花8-20秒，完全没法接受。我的psycopg3代码如下：

import psycopg
import time
from sqlalchemy import create_engine
import logging

logger = logging.getLogger(__name__)

# 初始化数据库连接池
self.engine = create_engine(f'postgresql+psycopg://postgres:password@localhost/postgres', pool_size=DB_POOL_SIZE, max_overflow=20)

def insert_data_todb(self, table_name, batch_data):
    try:
        t1 = time.perf_counter()
        placeholders = ', '.join(['%s'] * len(batch_data[0]))
        insert_sql = f"""INSERT INTO {table_name} ({self._market_snapshot_columns_str}) VALUES ({placeholders});"""
        
        with self.engine.connect() as conn:
            with conn.cursor() as cur:
                cur.executemany(insert_sql, batch_data)
        t2 = time.perf_counter()
        logger.info(f"Inserted {len(batch_data)} records in {t2 - t1} seconds")
    except Exception as ex:
        logger.error(f"Error inserting batch data into {table_name}:")
        logger.exception(ex)

我实在搞不懂为什么psycopg3的性能会差这么多，是不是我的用法不对？有没有什么优化的方法能让psycopg3的批量插入性能追上甚至超过psycopg2？麻烦各位大佬指点一下，谢谢！

备注：内容来源于stack exchange，提问作者Nitesh Tosniwal