psycopg2-batch-insert-optimization

psycopg2 Batch Insert Optimization

Problem

When inserting thousands of rows into PostgreSQL over a network connection, row-by-row inserts are extremely slow. Each INSERT requires a round-trip, and with network latency of ~50-100ms, inserting 10,000 rows takes 10+ minutes.

The naive approach of using cursor.executemany() doesn't help much—it still sends individual statements.

Context / Trigger Conditions

• Inserting >100 rows into PostgreSQL via psycopg2
• Each insert taking ~1 second or more
• Network latency to database (especially Cloud SQL, RDS, remote databases)
• Migration scripts running for hours
• executemany() not providing expected speedup

Solution

Use execute_values() from psycopg2.extras:

from psycopg2.extras import execute_values

# Instead of this (SLOW):
for row in data:
    cursor.execute("INSERT INTO table (a, b, c) VALUES (%s, %s, %s)", row)

# Or this (STILL SLOW):
cursor.executemany("INSERT INTO table (a, b, c) VALUES (%s, %s, %s)", data)

# Use this (FAST):
execute_values(cursor, """
    INSERT INTO table (a, b, c)
    VALUES %s
    ON CONFLICT (id) DO NOTHING
""", data, page_size=500)
conn.commit()

Key Parameters:

• page_size: Number of rows per batch (default 100, try 500-1000)
• The VALUES %s placeholder is replaced with multiple value tuples

For UPSERT operations:

execute_values(cursor, """
    INSERT INTO users (user_id, username, email)
    VALUES %s
    ON CONFLICT (user_id) DO UPDATE SET
        username = EXCLUDED.username,
        email = COALESCE(EXCLUDED.email, users.email)
""", user_data, page_size=500)

Progress Monitoring for Long Migrations:

import sys
sys.stdout.reconfigure(line_buffering=True)  # Force unbuffered output

BATCH_SIZE = 500
for i in range(0, len(data), BATCH_SIZE):
    batch = data[i:i+BATCH_SIZE]
    execute_values(cursor, query, batch)
    conn.commit()
    print(f"Processed {min(i+BATCH_SIZE, len(data))}/{len(data)} rows...")