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...")

Verification

• Migration that previously took hours completes in minutes
• You can see batches being processed in real-time with progress output
• Check row counts after: SELECT COUNT(*) FROM table

Example

Real-world migration of 9,563 users from SQLite to PostgreSQL:

from psycopg2.extras import execute_values
import sys

sys.stdout.reconfigure(line_buffering=True)
BATCH_SIZE = 500

# Fetch from SQLite
sqlite_cur.execute('SELECT user_id, username, avatar_url, verified FROM users')
rows = sqlite_cur.fetchall()
data = [(r['user_id'], r['username'], r['avatar_url'], bool(r['verified']))
        for r in rows]

# Batch insert to PostgreSQL
for i in range(0, len(data), BATCH_SIZE):
    batch = data[i:i+BATCH_SIZE]
    execute_values(pg_cur, '''
        INSERT INTO users (user_id, username, avatar_url, verified)
        VALUES %s
        ON CONFLICT (user_id) DO UPDATE SET
            username = COALESCE(EXCLUDED.username, users.username),
            avatar_url = COALESCE(EXCLUDED.avatar_url, users.avatar_url)
    ''', batch)
    pg_conn.commit()
    print(f"Processed {min(i+BATCH_SIZE, len(data))}/{len(data)} users...")

Result: 9,563 users migrated in ~20 seconds instead of ~2.5 hours.

Notes

• execute_values() constructs a single INSERT with multiple VALUES, drastically reducing round-trips
• executemany() is deceptively slow—it still sends individual statements
• For very large datasets (>100k rows), consider COPY command or copy_expert()
• The page_size parameter controls memory usage vs. batch efficiency
• Always commit after each batch for long migrations (allows progress tracking and partial recovery)

SQLite to PostgreSQL Syntax Differences:

When migrating, also watch for these SQL differences:

• INSERT OR IGNORE → ON CONFLICT DO NOTHING
• INSERT OR REPLACE → ON CONFLICT DO UPDATE SET ...
• MAX(a, b) (SQLite) → GREATEST(a, b) (PostgreSQL)
• MIN(a, b) (SQLite) → LEAST(a, b) (PostgreSQL)
• ? placeholders → %s placeholders
• AUTOINCREMENT → SERIAL or GENERATED ALWAYS AS IDENTITY

References

• psycopg2 execute_values documentation
• PostgreSQL COPY for bulk loading