Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~6 min readPython
#psycopg#PostgreSQL#bulk insert#COPY
A diagram showing three paths for inserting data into a PostgreSQL database, with the COPY path highlighted as the fastest.

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-row INSERT statement per page of tuples. This is a psycopg2 helper; psycopg3 has no direct equivalent.
  • COPY: uses PostgreSQL's native bulk-loading protocol, either through copy_from() or copy_expert() in psycopg2, or copy() 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:

python
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.

python
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

python
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:

python
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().

MethodNetwork behaviorMemory usageError handlingBest for
executemanyOne statement per rowLow, especially with a generatorPer row if you use autocommit or savepoints; otherwise a failed row can abort the transactionSmall datasets, simple code
execute_valuesOne statement per pageMedium; SQL is built in memoryPer pageMedium batches, up to about 100k rows
COPYSingle streaming COPY commandLow (streamed)Per COPY operation; all-or-nothingLarge 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.

python
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:

python
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() in psycopg2.extras; psycopg3 does not include that helper. Build the same multi-row statement with psycopg.sql, or use COPY.
  • psycopg3's copy() method provides a modern way to write COPY data. psycopg2's copy_expert() also accepts a full COPY statement 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 tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.