Bulk Insert Into Postgres From Python: COPY vs execute_values

A loader that runs one INSERT and one COMMIT per row pays a network round trip and a disk flush for every row. Switching to COPY FROM STDIN (or execute_values) and committing once turns minutes into well under a second for the same file.

Get started - freeMid and Senior open as you complete Junior projects
Data Engineerpythonpostgresqlcopy

Why row-by-row inserts are slow

for row in reader:
    cur.execute("INSERT INTO sensor_data (...) VALUES (%s, %s, ...)", row)
    conn.commit()

Each loop iteration waits for the database twice: once for the insert, once for the commit, which forces a write-ahead-log flush to disk. At 10,000 rows that is 20,000 waits. The database is idle most of the time.

Option 1: COPY FROM STDIN (fastest)

COPY is Postgres's native bulk-load path. psycopg2 streams a file object straight into it:

with open(path) as f:
    cur.copy_expert(
        "COPY sensor_data (sensor_id, ts, temperature, humidity, pressure, status) "
        "FROM STDIN WITH (FORMAT CSV, HEADER TRUE)", f)
conn.commit()

Give COPY an explicit column list when the table has a SERIAL id, and let HEADER TRUE skip the header row.

Option 2: execute_values (flexible)

When rows need transforming in Python first, psycopg2.extras.execute_values sends many rows per statement:

from psycopg2.extras import execute_values
execute_values(cur, "INSERT INTO sensor_data (...) VALUES %s", rows, page_size=1000)
conn.commit()

It is slower than COPY but far faster than one row at a time.

Commit once (or per large chunk)

Most of the win comes from committing once at the end, or once per large chunk for very big files so a failure does not lose everything.

What you do in the project

You time the slow loader, rewrite load_data() with a bulk path, and load a 50,000-row CSV inside a 5-second budget with every row present.

Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.

Get started - free

What you'll practice

FAQ

What is the fastest way to insert many rows into Postgres from Python?

COPY FROM STDIN, called from psycopg2 with cursor.copy_expert, is the fastest. execute_values from psycopg2.extras is the next best option when rows need processing in Python first.

Why is committing after every insert slow?

Each commit waits for the write-ahead log to be flushed to disk, and each statement is a network round trip. Committing once per batch removes most of that waiting.

COPY vs INSERT in PostgreSQL - when should I use each?

Use COPY for bulk loads of files or large batches. Use INSERT for small numbers of rows, for ON CONFLICT upserts, or when each row needs its own error handling.

Keep learning

How to Optimize a Slow SQL QueryData projectIdempotent ETL Pipeline (INSERT ON CONFLICT)Data projectAWS DynamoDB Batch Write From a CSVData projectData roadmapStep by step to hiredData interview questionsSTAR answersAll Data projectsProjects hub

Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.

Get started - free
Get started - free