Python Psycopg Bulk Insert and Copy: A Practical Guide

When you need to insert thousands or millions of rows into PostgreSQL from Python, a simple loop of execute() calls quickly becomes a bottleneck. The python psycopg bulk insert and copy techniques covered here—executemany(), execute_values(), and COPY—give you a range of options that trade off speed, memory, and code complexity. Understanding which one fits your workload is the key to writing fast, maintainable data-loading code.

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.
• execute_values(): builds a single multi-row INSERT statement from a list of tuples.
• COPY: uses PostgreSQL's native bulk-loading protocol, either via copy_from() (psycopg2) or copy() (psycopg3).

Each method has different characteristics in terms of round trips, memory usage, and error handling. The choice depends on the size of your dataset, the need for per-row error isolation, and whether you're 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's 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 execute, but it batches the client-side preparation. In psycopg2, it's implemented as a loop under the hood, so it does not reduce the number of round trips. In psycopg3, executemany() may pipeline the statements, but it still doesn't build a single multi-row statement.

Use executemany() when you have a moderate number of rows (up to a few thousand) and you want the simplest possible code. It's also useful when you need to reuse the same query with different parameters, such as in a loop that also performs other operations.

Using execute_values() for Faster Multi-Row Inserts

execute_values() is a psycopg2 extension (also available in psycopg3 via psycopg.rows or the execute method with a list of tuples) that constructs a single INSERT statement with multiple VALUES clauses. This reduces the number of round trips to one, which can significantly improve performance for medium-sized batches.

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 full set of value groups. The page_size parameter controls how many rows are included per statement; psycopg2 splits the data into pages to avoid exceeding PostgreSQL's parameter limit (which is 65535 by default).

execute_values() is a good middle ground. It's faster than executemany() for most workloads because it reduces network overhead, but it still constructs the SQL on the client side, which can become memory-heavy for very large lists. 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 data directly from a file or a file-like object into a table, bypassing SQL parsing and planning. 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 CSV-like 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 using the sep parameter. For more control, copy_expert() lets you write a full COPY statement, including options like CSV and HEADER.

psycopg3: copy method

psycopg3 simplifies this with a copy() method that works directly with a file-like object:

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:
        copy.write(f.read())

conn.commit()

COPY is the fastest method because it uses PostgreSQL's native binary or text format and minimizes client-side processing. It's ideal for initial data loads, migrations, and any scenario where you're moving large volumes of data (millions of rows) into a table.

Comparing Performance and Tradeoffs

The table below summarizes the key differences between the three approaches:

executemany sends one round trip per row, which is acceptable for a few hundred rows but becomes slow for thousands. execute_values reduces round trips to one per page, but it builds the entire SQL string in memory, which can be problematic for very large lists. COPY streams data in chunks, so memory usage stays low even for millions of rows, but it's all-or-nothing: if any row fails, the entire statement is rolled back.

Handling Large Data and Memory Usage

When dealing with large datasets, memory is often the limiting factor. executemany and execute_values require the entire dataset to be in memory as a list of tuples. If you're reading from a file or a generator, you can process in chunks to avoid loading everything at once.

For execute_values, use the page_size parameter to control how many rows are sent per statement. This keeps the memory footprint of the generated SQL bounded. For example:

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 from a file-like object, which is inherently memory-efficient. If you're generating data on the fly, you can create a custom file-like object that yields rows as needed, but it's simpler to write to a temporary file or use a StringIO buffer that you flush periodically.

Error Handling and Transaction Control

Error handling differs significantly between the methods. With executemany, each row is executed separately, so a failure on one row does not prevent others from being inserted (unless you're in a transaction that you roll back). With execute_values, the entire page is a single statement; if one row violates a constraint, the whole page fails. With COPY, the entire 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 is easier because you can catch errors per row. For execute_values and COPY, you need to pre-validate data or use a staging table with constraints that allow you to identify bad rows after the fact.

Here's an example of handling errors with executemany in psycopg2:

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)
    except psycopg2.IntegrityError as e:
        print(f"Skipping row {row}: {e}")
        conn.rollback()  # rollback the failed statement
    else:
        conn.commit()  # commit each successful row

For bulk operations, you'll often want to wrap everything in a single transaction for atomicity. In that case, commit once at the end, but be aware that a single failure will roll back everything. Use savepoints if you need finer-grained control.

Choosing Between psycopg2 and psycopg3

The choice of driver affects which methods are available. psycopg2 is the classic driver, widely used in production. psycopg3 is the newer version with a more modern API and better support for the COPY protocol. If you're starting a new project, psycopg3 is worth considering, but psycopg2 remains a safe choice for legacy codebases.

Key differences for bulk inserts:

• psycopg2 has execute_values in psycopg2.extras; psycopg3 does not have it directly, but you can achieve similar results using psycopg.rows or by building the SQL yourself.
• psycopg3's copy() method is more flexible and supports binary format, while psycopg2's copy_expert requires manual SQL.
• psycopg3 uses server-side binding for parameters, which can improve performance for executemany in some cases.

For most bulk-loading scenarios, COPY is the best choice regardless of driver. 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'll sacrifice speed. execute_values is a compromise that works well for medium-sized datasets.

When you're ready to implement bulk inserts, start with COPY if your data is clean and you need maximum throughput. Use execute_values when you need a balance of speed and simplicity, and reserve executemany for small datasets or when you need to handle errors row by row. Understanding these tradeoffs will help you write data-loading code that scales with your data.