标签：#psycopg2

The LedgerSync pipeline was inserting 1.5 million rows into PostgreSQL using pandas.to_sql() . It took four minutes per run. I switched to psycopg2's COPY command and it dropped to 18 seconds. Same data, same schema, same machine. That is not an optimization tip. It is the difference between a pipeline that fits in an Airflow schedule and one that does not. This article is about patterns like that: the ones that matter when you are building pipelines that run on a schedule, not when you are writing ad-hoc queries. Loading Data: to_sql vs execute_values vs COPY There are three ways to write rows from Python into PostgreSQL, and the performance gap between them is significant. pandas to_sql issues one INSERT statement per row by default, or a multi-row INSERT with method="multi" . It is the easiest to write and the slowest for any serious volume. psycopg2 execute_values batches many rows into a single multi-row INSERT VALUES statement. About 5x faster than to_sql for medium-sized loads. psycopg2 COPY streams rows directly to PostgreSQL using its native bulk-load protocol. No statement parsing, no row-by-row overhead. For LedgerSync at 1.5M rows, this was the one that mattered. import psycopg2 import io import pandas as pd conn = psycopg2 . connect ( " host=localhost dbname=proj_db user=proj_user password=proj_pass " ) def bulk_copy ( df : pd . DataFrame , table : str , columns : list [ str ]): buf = io . StringIO () df [ columns ]. to_csv ( buf , index = False , header = False ) buf . seek ( 0 ) with conn . cursor () as cur : cur . copy_from ( buf , table , sep = " , " , columns = columns ) conn . commit () print ( f " Loaded { len ( df ) } rows into { table } " ) Use COPY for initial loads and large backfills. For incremental daily writes of a few thousand rows, execute_values is fine and gives you more control over conflict handling: from psycopg2.extras import execute_values def bulk_insert ( rows : list [ dict ], table : str ): if not rows : return columns = list