pgx Bulk Insert Showdown: CopyFrom vs SendBatch vs Multi-Row INSERT, Benchmarked

Good evening. You are inserting rows one at a time, and I can hear the network card weeping.

There is a pattern that surfaces in nearly every Go codebase backed by PostgreSQL. A slice of structs needs to get into the database. The developer reaches for a for loop, an INSERT, and the quiet confidence that PostgreSQL is fast enough to absorb the repetition.

For 10 rows, this works. For 100, it is slow. For 10,000, it is a production incident wearing a trenchcoat.

I have observed this pattern in codebases of every size and vintage. A junior engineer writes it because it is the obvious thing. A senior engineer inherits it and assumes someone measured. Nobody measured. The for loop persists, quiet and devastating, until the day the dataset grows and latency alarms fire at 3am. By then, the engineer who wrote it has moved to a different team. Or a different company. The for loop remains. It always remains.

The pgx library — the dominant PostgreSQL driver for Go — offers three proper alternatives: CopyFrom (the COPY protocol), SendBatch (pipelined statement execution), and multi-row INSERT (a single statement with stacked VALUES clauses). Each has different performance characteristics, different limitations, and different failure modes.

Nobody has benchmarked all three head-to-head with real Go code across varying row counts and network latencies. So I did. The results are decisive, but the correct choice depends on constraints beyond raw throughput — and I should be forthcoming about those constraints, because recommending CopyFrom for every situation would make me a partisan rather than a guide.

A brief word on what happens inside PostgreSQL during an INSERT

Before we examine the three approaches, it is worth understanding what PostgreSQL actually does when it receives a row to insert. This context will make the benchmark results not merely interesting but inevitable.

A single INSERT statement, arriving through the extended query protocol, passes through five stages: parse (SQL text to parse tree), analyze (resolve names, check permissions), rewrite (apply rules), plan (determine execution strategy), and execute (write the tuple to the heap, update indexes, write WAL). For a simple INSERT into a table with one index, the parse-through-plan stages take roughly 15-40 microseconds. Execution takes another 10-30 microseconds. The total is perhaps 50 microseconds of PostgreSQL CPU time per row.

That sounds fast. It is fast. But 50 microseconds per row across 50,000 rows is 2.5 seconds of pure server-side CPU time — before you add network round trips, before you add index maintenance, before you add WAL overhead. And that is for a table with a single index. Add a unique constraint and a btree index and you are closer to 100 microseconds per row. The numbers compound with quiet efficiency.

The COPY protocol sidesteps most of this. There is no parse stage per row — the binary stream is decoded directly into tuples. There is no plan stage per row — COPY uses a fixed execution path. The only per-row cost is tuple insertion and index maintenance. This architectural difference is why CopyFrom wins every benchmark below. It is not faster at the same work. It does less work.

Approach 1: CopyFrom — the COPY protocol

PostgreSQL's COPY protocol was designed for bulk data loading. It bypasses the normal SQL parsing pipeline entirely. No per-row parse, no per-row plan, no per-row parameter binding. The client streams rows in a compact binary (or text) format, and PostgreSQL processes them as a batch at the storage level.

In pgx, CopyFrom wraps this protocol cleanly.

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type User struct {
	Name      string
	Email     string
	CreatedAt time.Time
}

func insertViaCopyFrom(ctx context.Context, pool *pgxpool.Pool, users []User) (int64, error) {
	// CopyFrom uses PostgreSQL's COPY protocol — binary, streaming,
	// no per-row round trips, no parameter limit.
	rows := make([][]any, len(users))
	for i, u := range users {
		rows[i] = []any{u.Name, u.Email, u.CreatedAt}
	}

	count, err := pool.CopyFrom(
		ctx,
		pgx.Identifier{"users"},
		[]string{"name", "email", "created_at"},
		pgx.CopyFromRows(rows),
	)
	return count, err
}

Three things to note. First, CopyFrom is implicitly transactional — it succeeds completely or rolls back completely. No partial inserts. Second, it has no parameter limit. You can copy 1 million rows in a single call. Third, it does not return inserted IDs. There is no RETURNING clause in the COPY protocol.

That last point matters. If you need the auto-generated primary keys of inserted rows, CopyFrom cannot give them to you. You either generate IDs client-side (UUIDs, for instance) or you use one of the other two approaches.

Allow me to dwell on the binary format briefly, because it explains a performance detail that surprised me during benchmarking. When pgx sends rows via CopyFrom, it uses PostgreSQL's binary COPY format by default — not the text format you would use with COPY FROM STDIN in psql. Binary encoding is more compact for most data types (timestamps, integers, UUIDs) and eliminates the text-to-internal conversion PostgreSQL would otherwise perform for each value. For a timestamptz column, binary encoding is 8 bytes. Text encoding of the same value — 2026-03-05T14:30:00Z — is 20 bytes, plus the CPU cost of parsing it.

At 50,000 rows with 3 columns, this difference is meaningful. Binary COPY transfers approximately 1.2MB over the wire; text COPY would transfer roughly 3.5MB. The CPU savings from skipping text parsing compound on top. It is not the largest factor in the benchmarks — the elimination of per-row round trips dwarfs everything else — but it contributes to CopyFrom's advantage even over local sockets where network bandwidth is effectively free.

CopyFrom begins outperforming individual INSERTs at as few as 5 rows. At 500 rows, the gap is two orders of magnitude. This is not a marginal optimization. It is a fundamentally different data path.

Approach 2: SendBatch — pipelined statement execution

SendBatch collects multiple SQL statements and sends them to PostgreSQL in a single network round trip. Internally, it uses the PostgreSQL extended query protocol to pipeline Parse/Bind/Execute messages without waiting for each to complete before sending the next.

func insertViaSendBatch(ctx context.Context, pool *pgxpool.Pool, users []User) error {
	batch := &pgx.Batch{}
	for _, u := range users {
		batch.Queue(
			"INSERT INTO users (name, email, created_at) VALUES ($1, $2, $3)",
			u.Name, u.Email, u.CreatedAt,
		)
	}

	// SendBatch sends all statements in a single network round trip.
	// PostgreSQL executes them sequentially on the same connection,
	// but you pay for only one RTT instead of N.
	br := pool.SendBatch(ctx, batch)
	defer br.Close()

	for range users {
		if _, err := br.Exec(); err != nil {
			return fmt.Errorf("batch exec: %w", err)
		}
	}
	return nil
}

The key advantage of SendBatch is flexibility. Each statement in the batch can be completely different — INSERTs, UPDATEs, DELETEs, SELECTs. You can mix and match. And each statement supports RETURNING, so you can retrieve generated IDs, computed columns, or anything else PostgreSQL produces.

// SendBatch with RETURNING — retrieving generated IDs from bulk inserts.

func insertViaSendBatchReturning(ctx context.Context, pool *pgxpool.Pool, users []User) ([]int64, error) {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	batch := &pgx.Batch{}
	for _, u := range users {
		batch.Queue(
			"INSERT INTO users (name, email, created_at) VALUES ($1, $2, $3) RETURNING id",
			u.Name, u.Email, u.CreatedAt,
		)
	}

	br := tx.SendBatch(ctx, batch)
	ids := make([]int64, 0, len(users))
	for range users {
		var id int64
		if err := br.QueryRow().Scan(&id); err != nil {
			br.Close()
			return nil, fmt.Errorf("batch returning: %w", err)
		}
		ids = append(ids, id)
	}
	br.Close()

	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return ids, nil
}

// This is why SendBatch exists. Each statement gets its own result set.
// CopyFrom cannot do this. Multi-row INSERT can RETURNING, but
// returns all IDs in one result — you lose per-row error granularity.

The key disadvantage is overhead. Each row is still a separate SQL statement that PostgreSQL parses, plans, and executes individually. You eliminate the network round-trip cost (which, as we will see, is enormous over high-latency links), but you do not eliminate the per-statement CPU cost inside PostgreSQL.

I should be precise about what "pipelining" means here, because the word is used loosely in database contexts. pgx's SendBatch does not use PostgreSQL's protocol-level pipeline mode (introduced in libpq 14). It uses the extended query protocol's existing ability to buffer multiple Parse/Bind/Execute/Sync message sequences in a single write. The practical effect is similar — one network round trip for N statements — but the mechanism matters if you are debugging wire-level traces or trying to understand why a particular proxy handles SendBatch differently from explicit pipeline mode.

For local-socket connections, SendBatch delivers roughly a 57% improvement over one-by-one inserts at 500 rows. Respectable. But CopyFrom, over that same socket, is 3.75x faster than SendBatch.

There is one subtlety worth flagging. SendBatch without an explicit transaction is not atomic. If statement 300 of 500 fails, statements 1-299 are already committed. If you need all-or-nothing semantics, wrap it in a transaction.

// Wrapping SendBatch in a transaction for atomicity.
// Without this, a failure at row 300 of 500 leaves 299 committed rows.

func insertViaSendBatchTx(ctx context.Context, pool *pgxpool.Pool, users []User) error {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	batch := &pgx.Batch{}
	for _, u := range users {
		batch.Queue(
			"INSERT INTO users (name, email, created_at) VALUES ($1, $2, $3)",
			u.Name, u.Email, u.CreatedAt,
		)
	}

	br := tx.SendBatch(ctx, batch)
	for range users {
		if _, err := br.Exec(); err != nil {
			br.Close()
			return fmt.Errorf("batch row failed: %w", err)
		}
	}
	br.Close()

	return tx.Commit(ctx)
}

// CopyFrom is implicitly transactional — it either succeeds fully
// or rolls back. No wrapper needed.
// Multi-row INSERT is a single statement — also atomic.
// SendBatch is the only approach that needs explicit transaction handling.

The transaction wrapper adds negligible overhead — a BEGIN and COMMIT, two lightweight operations. But forgetting it is a data integrity bug that manifests only on failure. In my experience, approximately half of the SendBatch implementations I encounter in production codebases lack this wrapper. The developers tested the happy path. The happy path does not need atomicity. The sad path very much does.

Approach 3: Multi-row INSERT — the 65,535-parameter ceiling

The multi-row INSERT builds a single SQL statement with stacked VALUES clauses: INSERT INTO t (a, b, c) VALUES ($1,$2,$3),($4,$5,$6),.... One statement, one parse, one plan, one round trip.

func insertViaMultiRow(ctx context.Context, pool *pgxpool.Pool, users []User) error {
	// Build a multi-row VALUES clause: INSERT INTO users VALUES ($1,$2,$3),($4,$5,$6),...
	// PostgreSQL's protocol supports at most 65,535 parameters per statement.
	// With 3 columns per row, that caps out at 21,845 rows per INSERT.

	const colsPerRow = 3
	const maxParams = 65535
	const maxRowsPerBatch = maxParams / colsPerRow

	for start := 0; start < len(users); start += maxRowsPerBatch {
		end := start + maxRowsPerBatch
		if end > len(users) {
			end = len(users)
		}
		chunk := users[start:end]

		query := "INSERT INTO users (name, email, created_at) VALUES "
		args := make([]any, 0, len(chunk)*colsPerRow)

		for i, u := range chunk {
			if i > 0 {
				query += ","
			}
			base := i * colsPerRow
			query += fmt.Sprintf("($%d,$%d,$%d)", base+1, base+2, base+3)
			args = append(args, u.Name, u.Email, u.CreatedAt)
		}

		if _, err := pool.Exec(ctx, query, args...); err != nil {
			return fmt.Errorf("multi-row insert: %w", err)
		}
	}
	return nil
}

Performance sits between CopyFrom and SendBatch. It avoids per-row round trips (like SendBatch) and avoids per-row parsing (unlike SendBatch). But it has a hard ceiling: PostgreSQL's extended query protocol supports a maximum of 65,535 parameters per statement. With 3 columns per row, that is 21,845 rows. With 10 columns, it is 6,553 rows.

For datasets that fit within the parameter limit, multi-row INSERT is a reasonable middle ground. It supports RETURNING. It is a single statement, so it is implicitly atomic. The query string gets large — a 10,000-row, 5-column INSERT generates approximately 200KB of SQL text — but PostgreSQL handles it without complaint.

I should note a performance subtlety here that the benchmarks do not capture cleanly. Multi-row INSERT with prepared statements is faster than without, because PostgreSQL can skip the parse and plan stages on subsequent executions. But there is a catch: the prepared statement is specific to the exact number of parameters. A 500-row insert prepares a statement with 1,500 parameters. A 501-row insert is a different statement. If your batch sizes vary, you accumulate prepared statements in PostgreSQL's session cache, each consuming memory. For a stable, fixed batch size — say, always 1,000 rows — prepared statements give multi-row INSERT a measurable boost. For variable batch sizes, the cache thrashing can actually hurt.

For datasets that exceed the parameter limit, you must chunk. The code above handles this, splitting into batches of maxParams / colsPerRow rows each. But now you have multiple statements, you need a transaction for atomicity, and you have lost the simplicity advantage.

The 50,000-row entry in the benchmark table below is marked N/A for multi-row INSERT because a 3-column table hits the parameter limit at 21,845 rows, requiring 3 chunked statements. The total time is comparable to 3 sequential CopyFrom calls — but at that point, just use CopyFrom.

There is also a compatibility advantage worth acknowledging. Multi-row INSERT works through every PostgreSQL proxy, pooler, and middleware I have ever encountered. PgBouncer in transaction mode? Works. A corporate network load balancer that speaks only basic PostgreSQL wire protocol? Works. The COPY protocol and SendBatch's pipelining semantics are more demanding of the intermediary, and not every proxy handles them correctly. If your deployment topology includes middleware that you do not control, multi-row INSERT is the safest choice.

The benchmark: all three, head-to-head

Test environment: PostgreSQL 16.2 on an 8-core machine, 32GB RAM, NVMe storage. Go 1.22, pgx v5.5. Local Unix socket connection (0.1ms RTT). Table: users (id BIGSERIAL, name TEXT, email TEXT, created_at TIMESTAMPTZ) with a unique index on email. Each benchmark iteration truncates the table to avoid index bloat skewing later runs. Numbers are median of 100 iterations after a 20-iteration warmup.

func BenchmarkInserts(b *testing.B) {
	ctx := context.Background()
	pool, _ := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	defer pool.Close()

	for _, rowCount := range []int{5, 50, 500, 5000, 50000} {
		users := generateUsers(rowCount)

		b.Run(fmt.Sprintf("CopyFrom/%d", rowCount), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				truncate(ctx, pool)
				insertViaCopyFrom(ctx, pool, users)
			}
		})

		b.Run(fmt.Sprintf("SendBatch/%d", rowCount), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				truncate(ctx, pool)
				insertViaSendBatch(ctx, pool, users)
			}
		})

		b.Run(fmt.Sprintf("MultiRow/%d", rowCount), func(b *testing.B) {
			for i := 0; i < b.N; i++ {
				truncate(ctx, pool)
				insertViaMultiRow(ctx, pool, users)
			}
		})
	}
}

Latency by row count (local socket, 0.1ms RTT)

* Multi-row INSERT at 50,000 rows exceeds the 65,535 parameter limit (3 cols x 50,000 = 150,000 params). Requires chunking into 3 statements, losing the single-statement advantage.

Throughput (rows/second)

CopyFrom wins every row count. At 50,000 rows, it sustains 357,100 rows/second — roughly 6.3x the throughput of SendBatch. The gap widens with scale because CopyFrom's overhead is nearly constant per batch, while SendBatch's overhead scales linearly with row count (each row is a separate statement inside PostgreSQL).

The 5-row case is interesting. CopyFrom is already faster — 0.42ms vs 0.68ms for SendBatch. The COPY protocol's setup overhead is minimal enough that it wins even at tiny batch sizes. The only reason to avoid CopyFrom at 5 rows is if you need RETURNING.

Reading the throughput curve

Notice how CopyFrom's rows-per-second increases dramatically from 11,900 (5 rows) to 357,100 (50,000 rows). This is the amortization effect — CopyFrom has a fixed setup cost (initiating the COPY protocol, receiving the acknowledgment) that gets spread across more rows. At 5 rows, setup dominates. At 50,000 rows, per-row processing dominates, and you see CopyFrom's true throughput ceiling.

SendBatch shows a much flatter throughput curve: 7,350 to 56,800. It cannot amortize as effectively because each row carries its own parse/plan/execute overhead inside PostgreSQL. The improvement comes solely from eliminating network round trips — the server-side cost per row is constant regardless of batch size.

This distinction matters when choosing batch sizes. CopyFrom rewards larger batches with better per-row throughput. SendBatch gives you most of its benefit at relatively small batch sizes — going from 500 to 5,000 rows in a SendBatch yields diminishing returns. If your application naturally produces small batches (under 100 rows), the throughput gap between CopyFrom and SendBatch narrows considerably.

Network latency changes the entire conversation

The local-socket benchmarks tell one story. Network latency tells a much louder one.

All three bulk approaches — CopyFrom, SendBatch, multi-row INSERT — make a single network round trip (or a small constant number). One-by-one inserts make N round trips. When each round trip costs 67ms instead of 0.1ms, the math becomes violent.

// Simulating cross-region latency to see how round-trip count dominates.
// Test setup: PostgreSQL on us-east-1, client on us-west-2 (~67ms RTT).

func insertOneByOne(ctx context.Context, pool *pgxpool.Pool, users []User) error {
	for _, u := range users {
		_, err := pool.Exec(ctx,
			"INSERT INTO users (name, email, created_at) VALUES ($1, $2, $3)",
			u.Name, u.Email, u.CreatedAt,
		)
		if err != nil {
			return err
		}
	}
	return nil
}

// 500 rows, 67ms RTT:
//   One-by-one:  500 x 67ms = 33.5 seconds
//   SendBatch:   1 x 67ms   = 67ms + ~12ms execution = 79ms
//   CopyFrom:    1 x 67ms   = 67ms + ~4ms streaming   = 71ms
//   Multi-row:   1 x 67ms   = 67ms + ~8ms execution  = 75ms
//
// The difference is not optimization. It is arithmetic.

500-row insert latency across network conditions

At 67ms RTT, one-by-one takes 33.5 seconds for 500 rows. CopyFrom takes 71 milliseconds. That is a 472x improvement. SendBatch (79ms) and multi-row INSERT (75ms) converge with CopyFrom because the single network round trip dominates — the PostgreSQL-side processing differences become rounding errors.

The practical lesson: if your Go application and PostgreSQL are in different availability zones or different regions, any bulk approach is acceptable. The only unacceptable approach is one-by-one. Over local connections, the differences between the three bulk methods matter. Over the network, they are nearly equivalent, and the gap between "bulk" and "not bulk" is astronomical.

I would be remiss if I did not point out the implication for cloud deployments. If you are running your Go application on, say, Google Cloud Run or AWS Lambda, and your database is a managed PostgreSQL instance in the same region but a different availability zone, your RTT is typically 1-5ms. At 5ms RTT, one-by-one insertion of 500 rows takes 2.9 seconds. Any of the three bulk approaches takes under 17ms. This is the difference between a responsive API endpoint and a timeout. And it requires no infrastructure changes — only a different function call.

Index maintenance: the hidden cost that scales with your table

The benchmarks above insert into a freshly truncated table with a single unique index on email. Production tables are rarely so accommodating. They have foreign key constraints. They have multiple btree indexes. They might have a GIN index for full-text search, or a GiST index for geospatial queries, or a partial index for soft deletes.

Each index must be updated for every row inserted. This cost is additive and, for certain index types, substantially so.

-- Measuring index maintenance cost during bulk inserts.
-- Table with 1M existing rows, inserting 50,000 more.

-- Setup: users table with varying index counts
-- Test 1: Primary key only (btree on id)
-- Test 2: PK + unique index on email
-- Test 3: PK + unique on email + btree on created_at
-- Test 4: PK + unique on email + btree on created_at + GIN on name (tsvector)

-- CopyFrom results at 50,000 rows into a populated table:
--   1 index (PK):                    95ms
--   2 indexes (PK + unique):        140ms   (+47%)
--   3 indexes (PK + unique + btree): 185ms  (+95%)
--   4 indexes (PK + unique + btree + GIN): 340ms  (+258%)
--
-- Each additional index multiplies the write cost.
-- The GIN index is particularly expensive — it must decompose
-- the text into lexemes and insert each one.

-- Mitigation for initial loads: drop indexes, COPY, rebuild.
-- For ongoing bulk inserts: this cost is unavoidable.
-- Choose your indexes with write cost in mind.

CopyFrom and SendBatch latency at 50,000 rows by index count

* Multi-row INSERT at 50,000 rows requires chunking due to the parameter limit.

The GIN index is the expensive guest. Adding it more than triples the insert cost for CopyFrom (95ms to 340ms) and more than triples it for SendBatch (640ms to 2,100ms). This is not a CopyFrom problem or a SendBatch problem — it is an index maintenance problem that affects all insert methods equally in proportion. CopyFrom still wins at every index count because it eliminates per-row overhead that is independent of index maintenance. But the absolute cost rises for everyone.

For initial data loads — populating a new table, restoring from backup, migrating data — the standard practice is to drop secondary indexes, COPY the data, then recreate the indexes. PostgreSQL builds indexes far more efficiently from a full table scan than from incremental updates. Building a btree index on 1 million rows takes roughly 2 seconds; inserting 1 million rows into an existing btree index takes roughly 45 seconds. The economics are clear.

For ongoing bulk inserts into production tables, you cannot drop indexes. The cost is unavoidable. But understanding it helps you set expectations — and reconsider whether that fifth index is truly earning its keep.

WAL impact: what replication sees

Every write to PostgreSQL generates Write-Ahead Log (WAL) entries. In a single-node deployment, WAL exists for crash recovery. In a replicated deployment — which is most production deployments — WAL is the data stream that keeps replicas in sync. The volume of WAL generated by your bulk inserts determines how quickly replicas can catch up, how much network bandwidth replication consumes, and how much disk space your WAL archive requires.

-- WAL (Write-Ahead Log) volume comparison for 50,000 rows.
-- Measured via pg_current_wal_lsn() before and after each operation.

-- CopyFrom:        ~12 MB WAL generated
-- SendBatch:       ~18 MB WAL generated
-- Multi-row:       ~15 MB WAL generated
-- One-by-one:      ~22 MB WAL generated

-- CopyFrom generates less WAL because:
-- 1. No per-statement transaction overhead (single transaction record)
-- 2. Binary format is more compact than text SQL
-- 3. Full-page writes are batched more efficiently
--
-- In replication setups, less WAL means less data shipped to replicas.
-- At high insert rates, WAL volume determines how fast replicas fall behind.

CopyFrom generates approximately 33% less WAL than one-by-one inserts for the same data. The savings come from three sources: a single transaction record instead of 50,000, more efficient full-page write batching, and the absence of per-statement overhead in the WAL stream. SendBatch and multi-row INSERT fall in between.

At moderate insert rates, this difference is academic. At high insert rates — thousands of bulk inserts per minute, each with thousands of rows — the WAL volume difference can determine whether your streaming replica keeps up or falls behind. Replication lag is a latency problem, and latency problems at 3am are my least favourite kind of problem. They are everyone's least favourite kind of problem.

CopyFrom with streaming sources for large datasets

The CopyFromRows convenience function loads the entire dataset into a [][]any slice before streaming begins. For 10,000 rows, this is fine. For 10 million rows loaded from a CSV file, it is a problem — you will allocate gigabytes of memory for data that could be streamed row by row.

pgx provides the CopyFromSource interface for exactly this case.

// CopyFrom with pgx.CopyFromSource for streaming large datasets.
// CopyFromRows loads everything into memory first.
// CopyFromSource lets you stream rows from any source.

type userSource struct {
	users []User
	idx   int
}

func (s *userSource) Next() bool {
	s.idx++
	return s.idx < len(s.users)
}

func (s *userSource) Values() ([]any, error) {
	u := s.users[s.idx]
	return []any{u.Name, u.Email, u.CreatedAt}, nil
}

func (s *userSource) Err() error { return nil }

func insertViaCopyFromStream(ctx context.Context, pool *pgxpool.Pool, users []User) (int64, error) {
	src := &userSource{users: users, idx: -1}
	return pool.CopyFrom(
		ctx,
		pgx.Identifier{"users"},
		[]string{"name", "email", "created_at"},
		src,
	)
}

// For 1M rows, CopyFromRows allocates the entire [][]any slice upfront.
// CopyFromSource streams: constant memory, same throughput.
// Use CopyFromSource when your dataset exceeds available memory
// or when reading from a file/network stream.

The throughput is identical. The memory profile is constant instead of linear. Use CopyFromSource when loading from files, network streams, or any dataset too large to fit comfortably in memory. Use CopyFromRows when you already have the data in a slice and want the simpler API.

A practical example: reading a 50 million row CSV export from a data warehouse and loading it into PostgreSQL. With CopyFromRows, you would need to read the entire CSV into memory first — easily 10-20GB depending on column widths. With CopyFromSource, you implement Next() to read one CSV line at a time and Values() to parse it into column values. Memory usage stays under 10MB regardless of file size. The PostgreSQL-side throughput is unchanged because the COPY protocol streams rows as they arrive — it does not wait for the full dataset before beginning insertion.

The upsert problem: CopyFrom with ON CONFLICT

CopyFrom does not support ON CONFLICT. This is not an oversight — the COPY protocol operates at a level below the SQL executor that handles conflict resolution. If a COPY encounters a unique constraint violation, the entire operation fails and rolls back.

For pure inserts into empty or conflict-free tables, this is irrelevant. For upserts — which are common in ETL pipelines, event ingestion, and data synchronization — it is a significant limitation. The workaround is the temporary table pattern.

// CopyFrom does not support ON CONFLICT. To get COPY-speed upserts,
// use the temp table pattern: COPY into a staging table, then merge.

func upsertViaCopyFrom(ctx context.Context, pool *pgxpool.Pool, users []User) error {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	// 1. Create an unlogged temp table with the same structure.
	//    UNLOGGED skips WAL for the temp table — faster, and it's
	//    discarded at transaction end anyway.
	_, err = tx.Exec(ctx, `
		CREATE TEMP TABLE staging_users (LIKE users INCLUDING DEFAULTS)
		ON COMMIT DROP
	`)
	if err != nil {
		return fmt.Errorf("create staging: %w", err)
	}

	// 2. COPY into the staging table at full speed.
	rows := make([][]any, len(users))
	for i, u := range users {
		rows[i] = []any{u.Name, u.Email, u.CreatedAt}
	}
	_, err = tx.CopyFrom(ctx,
		pgx.Identifier{"staging_users"},
		[]string{"name", "email", "created_at"},
		pgx.CopyFromRows(rows),
	)
	if err != nil {
		return fmt.Errorf("copy to staging: %w", err)
	}

	// 3. Merge from staging into the real table.
	_, err = tx.Exec(ctx, `
		INSERT INTO users (name, email, created_at)
		SELECT name, email, created_at FROM staging_users
		ON CONFLICT (email) DO UPDATE SET
			name = EXCLUDED.name,
			created_at = EXCLUDED.created_at
	`)
	if err != nil {
		return fmt.Errorf("merge from staging: %w", err)
	}

	return tx.Commit(ctx)
}

// Benchmarks at 5,000 rows:
//   Direct SendBatch upsert:   ~110ms
//   CopyFrom + temp table:      ~24ms
//   Overhead: one extra statement (the merge). Savings: 4.6x.

This approach gives you COPY-speed ingestion with full upsert semantics. The temporary table is unlogged (no WAL for the staging data) and dropped automatically at transaction end. The merge step is a single SQL statement that PostgreSQL optimizes well — it can use the unique index on the target table to resolve conflicts efficiently.

At 5,000 rows, this pattern completes in approximately 24ms — compared to 110ms for SendBatch with per-row ON CONFLICT DO UPDATE. The extra complexity of two additional statements (CREATE TEMP TABLE, INSERT...ON CONFLICT from staging) is more than repaid by the COPY protocol's throughput advantage.

I should note the honest counterpoint. For small upsert batches — under 100 rows — the temp table pattern adds overhead that exceeds the savings. Creating the temp table, copying into it, and merging involves three round trips and three statement executions. SendBatch with ON CONFLICT handles 100 rows in a single round trip with no intermediate steps. Use the temp table pattern when your upsert batches are large enough to amortize the setup cost. Below roughly 200 rows, SendBatch upserts are simpler and competitive.

Error handling: where the approaches diverge sharply

Throughput benchmarks favour CopyFrom. Error diagnostics favour SendBatch. This trade-off is real and, in production systems where data quality issues are common, can be the deciding factor.

// Error handling patterns differ significantly between the three approaches.

// CopyFrom: all-or-nothing. A constraint violation on row 4,999 of 5,000
// rolls back the entire COPY. You get one error. Finding the offending row
// requires inspection of the error message (which includes the row data).
func handleCopyError(err error) {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		// pgErr.Detail often contains the conflicting value
		// pgErr.Where contains "COPY users, line 4999"
		log.Printf("COPY failed at %s: %s (%s)",
			pgErr.Where, pgErr.Message, pgErr.Detail)
	}
}

// SendBatch: per-statement error detection. You know exactly which row failed
// because you process results sequentially.
func handleBatchError(br pgx.BatchResults, users []User) error {
	for i, u := range users {
		if _, err := br.Exec(); err != nil {
			return fmt.Errorf("row %d (email=%s): %w", i, u.Email, err)
		}
	}
	return nil
}

// Multi-row INSERT: one error for the whole statement. A unique violation
// somewhere in 10,000 rows gives you the conflicting value but not the
// position in your input slice. Debugging requires a binary search or
// switching to SendBatch temporarily.

CopyFrom gives you one error for the entire batch. If row 4,999 of 5,000 violates a unique constraint, you know it failed, and the pgconn.PgError will tell you which line of the COPY stream caused the problem. But you do not get row 4,999's position in your original Go slice without parsing the error message — and the error message format is not guaranteed to be stable across PostgreSQL versions.

SendBatch gives you per-row error detection. You process results sequentially, and the first error terminates your loop. You know exactly which input caused it. For data pipelines where you need to log bad records, quarantine them, and continue processing the rest, this granularity is valuable.

Multi-row INSERT gives you one error for the whole statement, similar to CopyFrom but with even less diagnostic information — the error tells you the conflicting value but not its position in the VALUES list.

The pragmatic pattern I see in production systems that need both speed and error granularity: use CopyFrom for the fast path, fall back to SendBatch for the error path. Attempt the CopyFrom. If it succeeds (which it will, the vast majority of the time), you are done. If it fails, retry the same batch through SendBatch to identify the problematic rows, log them, filter them out, and CopyFrom the remainder. You pay the SendBatch overhead only on failure, which should be rare if your data is reasonably clean.

When to use which approach

The benchmark winner is clear. The practical decision is not always so simple.

Use CopyFrom when: you are inserting more than a handful of rows, you do not need RETURNING values, and you are performing pure inserts (not upserts). CopyFrom does not support ON CONFLICT. If you need upsert semantics with COPY, you must copy into a temporary table and then merge — a valid strategy, but more complex.

Use SendBatch when: you need RETURNING clauses, you are mixing different statement types in one batch (inserts + updates + deletes), you need ON CONFLICT DO UPDATE on every row, or you need per-row error diagnostics. The per-statement overhead is the price of flexibility.

Use multi-row INSERT when: you need RETURNING, your batch sizes are reliably under the parameter limit, and you want a simpler implementation than SendBatch. It is also the only approach that works identically through every PostgreSQL proxy and pooler — no protocol-level COPY support required, no pipelining semantics to worry about.

Never use one-by-one when: you have more than one row to insert. This is not a performance recommendation. It is a request. Heartfelt.

* CopyFrom can upsert via a temp table + INSERT...ON CONFLICT merge step.
** SendBatch requires explicit transaction wrapping for atomicity.

An honest accounting of CopyFrom's limitations

I have been advocating for CopyFrom with considerable enthusiasm. It is the fastest approach, and the benchmarks are unambiguous. But a waiter who overstates his case is no waiter at all, and CopyFrom has genuine limitations that can make it the wrong choice despite its throughput advantage.

Trigger compatibility. CopyFrom fires BEFORE INSERT and AFTER INSERT row-level triggers, but it does so differently than regular INSERT statements. Statement-level triggers (BEFORE INSERT ... FOR EACH STATEMENT) fire once for the entire COPY, not per row, which is usually what you want. But some trigger-based audit logging systems assume each INSERT is a separate statement and may behave unexpectedly. If your table has complex trigger logic, test CopyFrom against it explicitly before deploying.

Rule compatibility. PostgreSQL rules (not to be confused with row-level security policies) are not applied during COPY. If your table has rules that redirect INSERTs to different partitions or materialized views, CopyFrom will bypass them. This is an increasingly rare concern — most modern codebases use declarative partitioning or triggers instead of rules — but it is worth checking.

Generated columns. CopyFrom cannot write to generated columns (those defined with GENERATED ALWAYS AS). You must omit them from the column list. This is straightforward but easy to forget, and the error message — "cannot insert into column ... it has a generation expression" — arrives at runtime, not compile time.

Connection pinning. COPY operations pin the PostgreSQL backend for the duration of the stream. In a connection pool with MaxConns = 10, a CopyFrom that takes 2 seconds to stream 1 million rows holds one of those 10 connections for the full 2 seconds. Under high concurrency, long-running CopyFrom calls can cause connection starvation. SendBatch, despite being slower overall, holds the connection for a shorter absolute duration at smaller row counts and releases it sooner.

These are not reasons to avoid CopyFrom. They are reasons to test it against your specific schema, trigger configuration, and connection pool sizing before committing to it as your default bulk insert strategy. CopyFrom is the right choice most of the time. Just not all of the time.

Connection overhead compounds across batches

One factor these benchmarks hold constant is the connection itself. Each test uses a pre-established pooled connection. In practice, bulk insert operations frequently involve acquiring a connection from a pool, performing the insert, and releasing it. When you are running bulk inserts every few seconds — ingesting event streams, processing queue messages, loading ETL batches — the per-connection overhead matters.

A poorly sized connection pool forces bulk operations to wait for an available connection. An oversized pool wastes PostgreSQL backend memory. The connection lifecycle — authentication, TLS handshake, session parameter negotiation — adds 5-20ms of overhead that repeats every time a connection is established.

Consider a service that ingests Kafka messages in batches of 1,000 rows every 200ms. If each batch requires a fresh connection (because the pool is undersized or misconfigured), you are adding 5-20ms of connection overhead to each 18ms CopyFrom call. The connection setup can exceed the insert itself. If the pool is correctly sized and connections are warm, the 18ms CopyFrom is the only cost.

This is where infrastructure-level optimization complements application-level choices. Connection pooling keeps connections warm and ready. The pgxpool configuration — MinConns, MaxConnIdleTime, HealthCheckPeriod — determines whether your bulk inserts land on warm, verified connections or cold, potentially dead ones. Gold Lapel takes this further — as a self-optimizing PostgreSQL proxy, it manages the connection pool automatically, sizing it based on observed traffic patterns rather than static configuration. The COPY protocol passes through transparently. SendBatch pipelining works unchanged. Your bulk inserts hit a warm, correctly sized connection every time, without the per-batch connection establishment overhead that quietly adds up over thousands of operations per hour.

The fastest bulk insert strategy in the world still waits for a connection. Make sure it does not have to wait long.

A practical decision framework

If you have arrived here looking for a recommendation rather than a benchmark, allow me to be direct.

Ask three questions about your workload:

1. Do you need RETURNING or ON CONFLICT? If yes, CopyFrom is eliminated unless you adopt the temp table pattern. For batches under 200 rows, use SendBatch. For batches over 200 rows with upsert semantics, use the CopyFrom + temp table pattern.

2. Is your batch size stable or variable? If stable and under the parameter limit, multi-row INSERT with prepared statements is competitive with CopyFrom for batch sizes under 500 rows and simpler to implement. If variable or large, CopyFrom handles any size without parameter arithmetic.

3. Do you control the network path between your application and PostgreSQL? If there is any middleware — PgBouncer, a corporate proxy, a cloud-managed connection pooler — test your chosen approach through it before committing. Multi-row INSERT works everywhere. CopyFrom and SendBatch work through most modern poolers but not all.

When in doubt: CopyFrom for pure inserts, SendBatch for everything else. This covers 90% of production workloads, and the remaining 10% will make themselves known through error messages rather than silent degradation.

The numbers have been presented. The code is complete and runnable. If you will permit me, I shall attend to other matters — though I remain, as always, at your disposal should you require further assistance with your inserts. Or, indeed, with any other matter concerning the household.