Python Psycopg Bulk Insert and Copy: A Practical Guide
Learn practical bulk insert options in Python with psycopg: executemany(), execute_values(), and COPY. This guide explains when each method makes sense and how to handle memory and errors.

When you need to insert thousands or millions of rows into PostgreSQL from Python, running a simple execute() loop for each row quickly becomes a bottleneck. The techniques covered here—executemany(), execute_values(), and COPY—offer different tradeoffs among speed, memory, code complexity, and error handling. Choosing the right one for your dataset makes data-loading code faster and easier to maintain.
Choosing the Right Bulk Insert Method in psycopg
psycopg (both psycopg2 and psycopg3) provides several ways to insert multiple rows. The three most common are:
executemany(): sends the same SQL statement repeatedly with different parameter sets. In psycopg2 this is a loop; in psycopg3 pipeline mode can reduce round trips.execute_values(): builds a multi-rowINSERTstatement per page of tuples. This is a psycopg2 helper; psycopg3 has no direct equivalent.COPY: uses PostgreSQL's native bulk-loading protocol, either throughcopy_from()orcopy_expert()in psycopg2, orcopy()in psycopg3.
Each method has different characteristics in terms of network behavior, memory usage, and error handling. The choice depends on the size of your dataset, the need for per-row error isolation, and whether you are using psycopg2 or psycopg3.
Using executemany() for Simple Bulk Inserts
executemany() is the most straightforward approach. It takes a SQL template and a sequence of parameter tuples, then executes the statement for each tuple. Here is a basic example with psycopg2:
import psycopg2
conn = psycopg2.connect('dbname=test user=postgres')
cur = conn.cursor()
data = [
(1, 'Alice', 30),
(2, 'Bob', 25),
(3, 'Carol', 35),
]
cur.executemany(
'INSERT INTO users (id, name, age) VALUES (%s, %s, %s)',
data
)
conn.commit()executemany() sends each row as a separate statement, so in psycopg2 it does not reduce the number of round trips compared with a manual loop. In psycopg3, using executemany() inside a pipeline can reduce round trips, but the method still does not build one multi-row INSERT.
Use executemany() when you have a moderate number of rows (up to a few thousand) and you want the simplest possible code. It is also convenient when you need to run other statements between inserts.
Using execute_values() for Faster Multi-Row Inserts
execute_values() is a psycopg2 extension in psycopg2.extras; it is not part of psycopg3. It constructs one INSERT statement with multiple VALUES groups per page. This reduces the number of round trips and can significantly improve performance for medium-sized batches.
In psycopg3, you can build a similar multi-row statement yourself with psycopg.sql, or use COPY for large loads.
from psycopg2.extras import execute_values
cur.execute('CREATE TEMP TABLE users (id int, name text, age int)')
data = [
(1, 'Alice', 30),
(2, 'Bob', 25),
(3, 'Carol', 35),
]
execute_values(
cur,
'INSERT INTO users (id, name, age) VALUES %s',
data,
page_size=100
)
conn.commit()The %s placeholder in the SQL template is replaced by the value groups. The page_size parameter controls how many rows are included per statement; psycopg2 splits the data into pages to keep the generated SQL manageable and to stay within PostgreSQL's bind-parameter limit (65535 by default).
execute_values() is a good middle ground. It is faster than executemany() for most workloads because it reduces network overhead, but it constructs SQL on the client side, which can become memory-heavy if you pass a very large list at once. Use it when you have a few thousand to a few hundred thousand rows and you want a simple, reliable approach.
Using COPY for the Fastest Bulk Loads
For the highest throughput, PostgreSQL's COPY protocol is the way to go. It streams table data from a file or file-like object and bypasses SQL parsing and planning for each row. psycopg2 exposes this via copy_from() and copy_expert(), while psycopg3 has a built-in copy() method.
psycopg2: copy_from and copy_expert
import psycopg2
from io import StringIO
conn = psycopg2.connect('dbname=test user=postgres')
cur = conn.cursor()
# Create a StringIO buffer with tab-separated data
buffer = StringIO()
buffer.write('1\tAlice\t30\n')
buffer.write('2\tBob\t25\n')
buffer.write('3\tCarol\t35\n')
buffer.seek(0)
cur.copy_from(buffer, 'users', columns=('id', 'name', 'age'))
conn.commit()copy_from() expects tab-separated values by default, but you can specify a different delimiter with sep. For more control, copy_expert() lets you write a full COPY statement, including options like CSV and HEADER.
psycopg3: copy method
psycopg3 provides a copy() method that works with a file-like object and streams data:
import psycopg
conn = psycopg.connect('dbname=test user=postgres')
cur = conn.cursor()
with open('users.csv', 'r') as f:
with cur.copy('COPY users (id, name, age) FROM STDIN WITH (FORMAT CSV)') as copy:
for line in f:
copy.write(line)
conn.commit()COPY can use PostgreSQL's text, CSV, or binary formats and is usually the fastest method for large data movement because it avoids client-side SQL construction and per-row parsing. It is ideal for initial data loads, migrations, and scenarios where you are moving millions of rows into a table.
Comparing Performance and Tradeoffs
The table below summarizes the main differences for a typical psycopg2 setup; psycopg3's pipeline mode can change the network behavior of executemany().
| Method | Network behavior | Memory usage | Error handling | Best for |
|---|---|---|---|---|
| executemany | One statement per row | Low, especially with a generator | Per row if you use autocommit or savepoints; otherwise a failed row can abort the transaction | Small datasets, simple code |
| execute_values | One statement per page | Medium; SQL is built in memory | Per page | Medium batches, up to about 100k rows |
| COPY | Single streaming COPY command | Low (streamed) | Per COPY operation; all-or-nothing | Large datasets, bulk loads |
Round trips are not the only factor. COPY avoids SQL parsing and planning and streams data, which is why it usually wins for large loads. execute_values() builds SQL in memory, so passing a multi-million-row list at once can be expensive. executemany() keeps per-statement memory low but sends many statements.
Handling Large Data and Memory Usage
Memory is often the limiting factor. If you are reading from a file or generator, process rows in chunks instead of loading the entire dataset into one list. execute_values() accepts a list of tuples, and page_size controls how much SQL is generated per statement when you also batch the outer loop.
from psycopg2.extras import execute_values
def row_generator():
for i in range(1000000):
yield (i, f'user{i}', i % 100)
cur.execute('CREATE TABLE users (id int, name text, age int)')
# Process in batches of 5000
batch = []
for row in row_generator():
batch.append(row)
if len(batch) >= 5000:
execute_values(cur, 'INSERT INTO users VALUES %s', batch, page_size=5000)
batch.clear()
if batch:
execute_values(cur, 'INSERT INTO users VALUES %s', batch, page_size=5000)
conn.commit()For COPY, you can stream directly from a file-like object, which is inherently memory-efficient. If you are generating data on the fly, write to a temporary file or flush a StringIO buffer periodically.
Error Handling and Transaction Control
Error handling depends on transaction boundaries as much as on the method. With executemany(), each row is a separate statement, so you can catch an error for one row and continue if you use autocommit or set a savepoint before each row. In a normal transaction, an error aborts the transaction; you must roll back or restore a savepoint before executing further statements.
With execute_values(), each page is a single statement; one bad row makes the whole page fail. With COPY, the whole copy operation is atomic: any error rolls back all rows in that copy.
This has practical implications. If you need to identify and skip invalid rows, executemany() with autocommit or savepoints is the easiest option. For execute_values() and COPY, pre-validate the data or load into a staging table, then inspect or move valid rows afterward.
Here is a simple psycopg2 example that commits each successful row so an invalid row can be skipped:
import psycopg2
conn = psycopg2.connect('dbname=test user=postgres')
cur = conn.cursor()
data = [
(1, 'Alice', 30),
(2, 'Bob', 25),
(3, 'Carol', 35),
]
for row in data:
try:
cur.execute('INSERT INTO users VALUES (%s, %s, %s)', row)
conn.commit()
except psycopg2.IntegrityError as e:
print(f'Skipping row {row}: {e}')
conn.rollback()This pattern is useful when you need per-row isolation, but committing each row prevents it from being a high-speed bulk load. For a true bulk load, wrap the work in one transaction and commit once at the end; if one statement fails, you will normally roll back the whole transaction. Use savepoints if you need finer-grained control without committing each row.
Choosing Between psycopg2 and psycopg3
psycopg2 is the classic driver and is widely used in existing projects. psycopg3 is the newer version with a more modern API, pipeline mode, and a first-class COPY interface. If you are starting a new project, psycopg3 is worth considering; psycopg2 remains a safe choice for legacy codebases.
Key differences for bulk inserts:
- psycopg2 has
execute_values()inpsycopg2.extras; psycopg3 does not include that helper. Build the same multi-row statement withpsycopg.sql, or useCOPY. - psycopg3's
copy()method provides a modern way to writeCOPYdata. psycopg2'scopy_expert()also accepts a fullCOPYstatement and gives similar control. - psycopg3 supports pipeline mode, which can batch
executemany()statements and reduce round trips.
For most bulk-loading scenarios, COPY is the best choice regardless of driver because it leverages PostgreSQL's native capabilities and avoids the overhead of SQL construction and parsing. If you need per-row error handling, executemany() is simpler, but you lose speed. execute_values() is a compromise that works well for medium-sized datasets on psycopg2.
Start with COPY when your data is clean and you need maximum throughput. Use execute_values() when you need a balance of speed and simplicity on psycopg2, and reserve executemany() for small datasets or row-by-row error handling. Understanding these tradeoffs will help you write data-loading code that scales with your data.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.