Bulk Operations and COPY

~12 min · operations, bulk

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

One INSERT per row vs one INSERT per batch

1000 separate INSERT statements take 1000 round trips. One INSERT with 1000 rows takes one round trip. The speedup is typically 50-100×. For really large data loads, COPY is another order of magnitude faster than multi-row INSERT.

The hierarchy of speed

Single-row INSERT in a loop — slowest, one round trip each.
Multi-row INSERT — fast, one round trip per batch.
COPY FROM STDIN — fastest, streams data directly into the table without parsing each row as a separate statement.
COPY with binary format — fastest of all; bypasses text parsing entirely.

When to use which

Routine application writes: multi-row INSERT (most ORMs do this implicitly). Big imports, ETL, seeding: COPY. Loading a billion rows: COPY in binary format with \copy or pgloader.

Code

psql's \copy for CSV import·bash

# Load a CSV directly into a table
\copy products (sku, name, price) FROM 'products.csv' WITH (FORMAT csv, HEADER true)

# Server-side COPY (requires file accessible to the server process)
COPY products (sku, name, price) FROM '/var/data/products.csv' WITH (FORMAT csv, HEADER true);

Multi-row INSERT (the application default)·python

import psycopg
rows = [...]  # list of (sku, name, price) tuples

with conn.cursor() as cur:
    cur.executemany(
        "INSERT INTO products (sku, name, price) VALUES (%s, %s, %s)",
        rows,
    )
# psycopg 3 batches these efficiently; psycopg 2 needed execute_values

COPY from a Python iterable (psycopg)·python

import psycopg
with conn.cursor() as cur:
    with cur.copy("COPY products (sku, name, price) FROM STDIN WITH (FORMAT csv)") as copy:
        for sku, name, price in rows:
            copy.write_row((sku, name, price))

External links

Exercise

Import a 100k-row CSV into a Postgres table three ways: row-by-row INSERTs, multi-row INSERTs (batches of 1000), and COPY. Time each. Document the speed ratio.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousPagination: OFFSET vs Keyset Next →VACUUM and Autovacuum in Plain English

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.