SQL Bulk Insert and Upsert Patterns That Scale

Loading data one row at a time is the most common performance mistake in SQL applications. An ORM loop that inserts 10,000 rows individually generates 10,000 round trips to the database, 10,000 parse-plan-execute cycles, and 10,000 index updates. Bulk operations collapse that into a handful of operations that complete in a fraction of the time.

This article covers the practical patterns for inserting and upserting data efficiently in PostgreSQL, with notes on where MySQL and SQL Server differ.

The cost of single-row inserts

# Slow: one INSERT per row
for user in users:
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s)",
        (user.name, user.email)
    )

Each iteration sends a statement over the network, the database parses it, plans it, executes it, updates indexes, writes to the WAL, and sends a response. For 10,000 rows, this takes 5-15 seconds on a typical network. The same data loaded in bulk takes under 200 milliseconds.

Multi-row INSERT

The simplest improvement is a multi-row VALUES clause:

INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

One statement, one round trip, one parse-plan-execute cycle. Most databases support hundreds or thousands of rows in a single VALUES clause. PostgreSQL has no hard row limit, but the query string has a parameter limit of roughly 65,535 when using parameterized queries through a driver.

In application code, build the batch:

const values = users.map((u, i) => `($${i*2+1}, $${i*2+2})`).join(', ');
const params = users.flatMap(u => [u.name, u.email]);

await client.query(
  `INSERT INTO users (name, email) VALUES ${values}`,
  params
);

For very large datasets (50,000+ rows), split into batches of 1,000-5,000 to keep memory usage reasonable and avoid long-running transactions that block other writers.

COPY: the fastest path

PostgreSQL’s COPY command is purpose-built for bulk loading. It bypasses the SQL parser entirely and streams data in a compact binary or CSV format:

COPY users (name, email) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);

From application code, use the driver’s COPY support:

import io

buffer = io.StringIO()
for user in users:
    buffer.write(f"{user.name}\t{user.email}\n")
buffer.seek(0)

cursor.copy_from(buffer, 'users', columns=('name', 'email'))

COPY is 5-10x faster than multi-row INSERT for large datasets. It is the tool to reach for when loading millions of rows.

For MySQL, the equivalent is LOAD DATA INFILE. For SQL Server, it is BULK INSERT or the bcp utility.

Upserts with ON CONFLICT

An upsert inserts a row if it does not exist or updates it if it does. Before PostgreSQL 9.5, this required application-level retry loops or advisory locks. Now it is a single statement:

INSERT INTO products (sku, name, price, updated_at)
VALUES ('ABC-123', 'Widget', 29.99, now())
ON CONFLICT (sku)
DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at;

EXCLUDED refers to the row that was proposed for insertion. The ON CONFLICT clause names the unique constraint or column(s) that define a conflict. If a row with sku = 'ABC-123' already exists, it is updated instead of causing a duplicate key error.

To skip conflicts entirely without updating:

INSERT INTO products (sku, name, price)
VALUES ('ABC-123', 'Widget', 29.99)
ON CONFLICT (sku) DO NOTHING;

MySQL uses ON DUPLICATE KEY UPDATE and SQL Server uses MERGE for similar operations.

Conditional upserts

Sometimes you only want to update if the new data is actually newer:

INSERT INTO inventory (product_id, quantity, last_sync)
VALUES (42, 150, '2026-07-01 12:00:00')
ON CONFLICT (product_id)
DO UPDATE SET
  quantity = EXCLUDED.quantity,
  last_sync = EXCLUDED.last_sync
WHERE inventory.last_sync < EXCLUDED.last_sync;

The WHERE clause on the DO UPDATE prevents stale data from overwriting fresh data. This is essential when processing events that may arrive out of order.

Bulk upserts

Combine multi-row INSERT with ON CONFLICT for bulk upserts:

INSERT INTO products (sku, name, price, updated_at)
VALUES
  ('ABC-123', 'Widget', 29.99, now()),
  ('DEF-456', 'Gadget', 49.99, now()),
  ('GHI-789', 'Doohickey', 9.99, now())
ON CONFLICT (sku)
DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at;

This is atomic: all rows are inserted or updated in a single transaction. For very large bulk upserts, batch in groups of 1,000-5,000 rows.

Partial indexes for selective conflicts

Sometimes conflicts should only be detected under certain conditions. Use a partial unique index:

CREATE UNIQUE INDEX idx_active_subscriptions
ON subscriptions (user_id)
WHERE status = 'active';

INSERT INTO subscriptions (user_id, plan, status)
VALUES (42, 'pro', 'active')
ON CONFLICT (user_id) WHERE status = 'active'
DO UPDATE SET plan = EXCLUDED.plan;

A user can have multiple subscription rows (cancelled, expired), but only one active subscription. The partial index enforces this, and the upsert respects it.

Batch sizing and transaction strategy

Large bulk operations need to balance throughput against lock duration and memory usage.

Wrap batches in explicit transactions:

BEGIN;
INSERT INTO events (...) VALUES ... ; -- 1000 rows
INSERT INTO events (...) VALUES ... ; -- next 1000 rows
COMMIT;

Recommended batch sizes:

• Multi-row INSERT: 1,000-5,000 rows per statement
• COPY: 10,000-100,000 rows per COPY command
• Transaction size: 10,000-50,000 rows before committing

Larger transactions hold locks longer and use more WAL space. Smaller transactions have more commit overhead. Profile with your workload to find the sweet spot.

Disable indexes during initial loads. If you are loading into an empty table, drop non-essential indexes, load the data, then recreate them. Building an index once on the final dataset is far cheaper than updating it incrementally for every row.

DROP INDEX idx_events_created_at;
COPY events FROM '/tmp/events.csv' WITH (FORMAT csv);
CREATE INDEX idx_events_created_at ON events (created_at);

RETURNING for side effects

PostgreSQL’s RETURNING clause lets you capture the results of a bulk insert or upsert without a second query:

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING id, email, xmax = 0 AS was_inserted;

The xmax = 0 trick distinguishes inserts from updates. Rows that were freshly inserted have xmax = 0; rows that were updated have a non-zero xmax.

Key takeaways

Stop inserting rows in loops. Use multi-row VALUES for small batches, COPY for large loads, and ON CONFLICT for upserts. Batch your operations, size your transactions deliberately, and remember that the fastest bulk operation is one that avoids unnecessary index maintenance. These patterns work at any scale, from a CSV import of 500 rows to a nightly sync of 50 million.