[Python] Batch insert data using psycopg2 vs psycopg3

Currently i am inserting to postgres database using psycopg2. Data is large and also the write frequency is high, so my database has WAL disabled and few other optimizations for faster writes.

When i use psycopg2 with execute_values, i am able to write batch of 1000 rows in 0.1-0.15 seconds.

from psycopg2.extras import execute_values


self.engine = create_engine(f'postgresql+psycopg2://postgresassword@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)


I uninstalled psycopg2 and installed psycopg 3.2. and used psycopg3's executemany function like this:

import psycopg

self.engine = create_engine(f'postgresql+psycopg://postgresassword@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});""" # stored variable

with self.engine.connect() as conn:
with conn.cursor() as cur:
cur.executemany(insert_sql, batch_data) # Pass the batch data directly
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)


My psycopg3 code is way slower! it takes 8-20 seconds to insert the same batches.

Continue reading...