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.
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 - freeWhat you'll practice
- Measuring the cost of per-row inserts and commits
- Bulk loading a CSV into Postgres with COPY FROM STDIN
- Batching inserts with execute_values and committing once
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
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