sqlc Batch Operations and Copy From Support With pgx

Inserting rows one at a time is correct and slow, and the slowness is almost entirely the network. Each call is a round trip to the server, and when the server is across a VPC rather than on the same host, that round trip, not PostgreSQL, sets the pace. pgx offers two ways around it and sqlc generates the code for both: :batchexec for many statements in one trip, and :copyfrom for the COPY protocol.

This article times all three approaches on 20,000 rows, then checks what each does when one row is bad. It uses PostgreSQL 18.6 in Docker over loopback, sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6, and it continues the query annotations reference. Plain pgx versions of both techniques are in the pgx batch article and the pgx COPY article, and the topic sits inside the database cornerstone.

The three queries

All three insert the same two columns into a table with a unique constraint on both, which the failure test needs:

create table events (
    id   bigserial primary key,
    kind text not null,
    n    int not null,
    unique (kind, n)
);

-- name: InsertOne :exec
insert into events (kind, n) values ($1, $2);

-- name: InsertBatch :batchexec
insert into events (kind, n) values ($1, $2);

-- name: Load :copyfrom
insert into events (kind, n) values ($1, $2);

-- name: CountEvents :one
select count(*) from events;

-- name: Truncate :exec
truncate events restart identity;

InsertOne is an ordinary :exec. InsertBatch has the same SQL but the kind :batchexec, and Load is :copyfrom. sqlc turns the first into a method on one row, the second into a method that takes a slice and returns a batch object, and the third into a method that takes a slice and returns the number of rows copied.

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5

Nothing special is needed in the configuration except sql_package: pgx/v5, since the database/sql code path does not have these kinds. A project configured for database/sql gets an error from sqlc generate for any :batch* or :copyfrom annotation.

The measurement

package main

import (
	"context"
	"fmt"
	"log"
	"time"

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

const dsn = "postgres://postgres:[email protected]:55432/sqlc0692?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

const rows = 20000

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)

	one := make([]InsertOneParams, rows)
	batch := make([]InsertBatchParams, rows)
	load := make([]LoadParams, rows)
	for i := range one {
		one[i] = InsertOneParams{"k", int32(i)}
		batch[i] = InsertBatchParams{"k", int32(i)}
		load[i] = LoadParams{"k", int32(i)}
	}
	report := func(name string, d time.Duration) {
		n, _ := q.CountEvents(ctx)
		fmt.Printf("%-28s %6d rows in %5d ms (%6.0f rows/ms)\n", name, n, d.Milliseconds(), float64(n)/float64(d.Milliseconds()+1))
		must(q.Truncate(ctx))
	}

	t := time.Now()
	for _, p := range one {
		must(q.InsertOne(ctx, p))
	}
	report("one call per row", time.Since(t))

	t = time.Now()
	br := q.InsertBatch(ctx, batch)
	br.Exec(func(i int, err error) { must(err) })
	must(br.Close())
	report(":batchexec, one round trip", time.Since(t))

	t = time.Now()
	n, err := q.Load(ctx, load)
	must(err)
	_ = n
	report(":copyfrom, COPY protocol", time.Since(t))

	// COPY is all or nothing: one duplicate row aborts the whole load.
	dup := append(load[:100:100], LoadParams{"k", 5})
	_, err = q.Load(ctx, dup)
	c, _ := q.CountEvents(ctx)
	fmt.Printf("copyfrom with a duplicate: error = %v | rows stored = %d\n", err != nil, c)
	fmt.Println("   ", err)

	// A batch is not: without a transaction each statement commits on its own.
	must(q.Truncate(ctx))
	dupB := append(batch[:100:100], InsertBatchParams{"k", 5}, InsertBatchParams{"k", 1000})
	br = q.InsertBatch(ctx, dupB)
	failed := 0
	br.Exec(func(i int, err error) {
		if err != nil {
			failed++
		}
	})
	_ = br.Close()
	c, _ = q.CountEvents(ctx)
	fmt.Printf("batchexec with a duplicate: %d failed, rows stored = %d\n", failed, c)
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0692
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

one call per row              20000 rows in  8304 ms (     2 rows/ms)
:batchexec, one round trip    20000 rows in   114 ms (   174 rows/ms)
:copyfrom, COPY protocol      20000 rows in    41 ms (   476 rows/ms)
copyfrom with a duplicate: error = true | rows stored = 0
    ERROR: duplicate key value violates unique constraint "events_kind_n_key" (SQLSTATE 23505)
batchexec with a duplicate: 2 failed, rows stored = 0

Those numbers are one run each on one machine, with the server in a container on the same host, so read them as orders of magnitude and not as benchmarks. Per row the first method cost about 0.4 ms, the second about 6 microseconds and the third about 2. The ratios (roughly 70 times and 200 times) will be larger across a real network, where a round trip costs more than the loopback's few hundred microseconds, and smaller for a server that is slow at indexing, since the work of building the unique index is the same for all three.

Why the batch is so much faster. :batchexec queues all 20,000 statements and sends them in one write, then reads the results back. The server still executes 20,000 inserts. What is gone is 19,999 waits for a reply. The generated method returns a batch object, and the loop in the program runs a callback for each result:

br := q.InsertBatch(ctx, batch)
br.Exec(func(i int, err error) { must(err) })
must(br.Close())

Always call Close. It flushes the batch and reports an error the callbacks did not see, and a batch left open holds its connection.

Why copy is faster still. COPY skips the per statement parse and plan and loads rows through a bulk path. pgx streams them in its binary format. It is the best choice for loading thousands of rows, and the only one of the three that is not a sequence of INSERT statements, which has consequences covered below.

What a bad row does

The second half of the program inserts 100 good rows plus one that duplicates an existing key, first through :copyfrom and then through :batchexec.

:copyfrom is all or nothing. The load returned ERROR: duplicate key value violates unique constraint "events_kind_n_key" (SQLSTATE 23505) and the table held 0 rows afterwards. Even the 100 valid rows were gone. COPY runs as one statement, and a statement that fails leaves nothing behind. That is a good property for a data load (it is either done or not) and a poor one for a feed where one bad record should not stop the other 9,999.

:batchexec also left 0 rows, and its callbacks did not warn about it. The batch held 100 good inserts, a duplicate and one more good insert. The callback reported two failures, the duplicate and the statement after it, and the table ended empty. The first 100 callbacks reported no error, because those statements did execute. But pgx sends a batch as one unit inside an implicit transaction when you are not in an explicit one, so when one statement failed the whole unit rolled back.

That is the trap in the callback style. A callback that says "no error" means that statement ran without an error, not that its effect survived. The final word is in Close() and, for an explicit transaction, in Commit. If you need partial success, the batch has to be one that the server treats as independent, which pgx does not do with the default settings. The options are to deduplicate in Go, to use on conflict do nothing in the SQL so a duplicate is not an error, or to split the input into smaller batches and accept that a failed batch loses its own rows only. The upsert article covers the on conflict forms, and the bulk insert article compares the approaches across libraries.

Choosing

Copy has two other limits worth knowing. It cannot use returning, so you do not get generated ids back. And its statement must be a plain insert listing the columns to load, with no on conflict clause, because COPY has no equivalent.

Checklist

• Use :copyfrom for bulk loads where all or nothing is acceptable.
• Wrap a :batchexec in an explicit transaction when you want to choose the atomicity yourself, as the transactions article shows.
• Always call Close on a batch and check its error.
• Chunk very large inputs (a few thousand rows per batch) so memory and the length of a failed unit stay bounded.
• Measure on your own network. The loopback numbers here flatter the one call per row version less than a cloud database would.

The generated code is described in sqlc's batch documentation, and the underlying behaviour in pgx's Batch and CopyFrom documentation, and PostgreSQL's COPY reference.