Bulk Insert in Go Fast With COPY and Batches in Postgres

An import job, a nightly sync and an analytics ingest all end up asking the same question: how fast can this Go program put a hundred thousand rows into PostgreSQL? The slow answer is a loop with an INSERT inside, and it is slow for reasons that have nothing to do with the data. This article measures five ways of loading rows on Go 1.25.6 with pgx v5.11.0 and PostgreSQL 18.6, shows what each is good for, and lists the limits that break the fast ones.

It is part of the database cornerstone and has a pgx specific companion in pgx COPY. For single statement upserts, see upserts with ON CONFLICT.

What makes row by row slow

An insert that takes the server a few microseconds costs far more in the Go program. Each Exec sends the statement, waits for the answer and only then continues: one network round trip, which is about 100 to 200 microseconds even to a database on the same machine and milliseconds across an availability zone. In autocommit mode every statement is also its own transaction, so each insert waits for the server to flush the write ahead log to disk (an fsync). Round trips and commits dominate. The cure is the same for both: do more work per trip and fewer commits.

The five approaches

package main

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

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

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

const rows = 20000

type event struct {
	UserID int32
	Kind   string
	Score  float64
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	evs := make([]event, rows)
	for i := range evs {
		evs[i] = event{int32(i % 500), "click", float64(i) / 7}
	}
	reset := func() {
		for _, q := range []string{"drop table if exists events616", "create table events616 (id bigserial primary key, user_id int not null, kind text not null, score float8 not null)"} {
			if _, err := pool.Exec(ctx, q); err != nil {
				log.Fatal(err)
			}
		}
	}
	count := func() (n int) {
		_ = pool.QueryRow(ctx, "select count(*) from events616").Scan(&n)
		return
	}
	report := func(label string, d time.Duration) {
		fmt.Printf("%-48s %8v  %9.0f rows/s  (table has %d rows)\n", label, d.Round(time.Millisecond), rows/d.Seconds(), count())
	}
	const ins = "insert into events616 (user_id, kind, score) values ($1, $2, $3)"

	// 1. One round trip and one commit per row.
	reset()
	start := time.Now()
	for _, e := range evs[:2000] { // 2,000 rows only: this is slow
		if _, err := pool.Exec(ctx, ins, e.UserID, e.Kind, e.Score); err != nil {
			log.Fatal(err)
		}
	}
	d := time.Since(start)
	fmt.Printf("%-48s %8v  %9.0f rows/s  (table has %d rows)\n", "1. autocommit, one INSERT per row (2,000 rows)", d.Round(time.Millisecond), 2000/d.Seconds(), count())

	// 2. One transaction, still one round trip per row.
	reset()
	start = time.Now()
	tx, _ := pool.Begin(ctx)
	for _, e := range evs {
		if _, err := tx.Exec(ctx, ins, e.UserID, e.Kind, e.Score); err != nil {
			log.Fatal(err)
		}
	}
	_ = tx.Commit(ctx)
	report("2. one transaction, one INSERT per row", time.Since(start))

	// 3. One transaction, statements sent as a pipeline.
	reset()
	start = time.Now()
	tx, _ = pool.Begin(ctx)
	batch := &pgx.Batch{}
	for _, e := range evs {
		batch.Queue(ins, e.UserID, e.Kind, e.Score)
	}
	br := tx.SendBatch(ctx, batch)
	for range evs {
		if _, err := br.Exec(); err != nil {
			log.Fatal(err)
		}
	}
	_ = br.Close()
	_ = tx.Commit(ctx)
	report("3. one transaction, pgx.Batch pipeline", time.Since(start))

	// 4. One statement per 1,000 rows, using unnest over arrays.
	reset()
	start = time.Now()
	for lo := 0; lo < rows; lo += 1000 {
		users, kinds, scores := []int32{}, []string{}, []float64{}
		for _, e := range evs[lo : lo+1000] {
			users, kinds, scores = append(users, e.UserID), append(kinds, e.Kind), append(scores, e.Score)
		}
		if _, err := pool.Exec(ctx, "insert into events616 (user_id, kind, score) select * from unnest($1::int[], $2::text[], $3::float8[])", users, kinds, scores); err != nil {
			log.Fatal(err)
		}
	}
	report("4. unnest, 1,000 rows per statement", time.Since(start))

	// 5. COPY.
	reset()
	start = time.Now()
	n, err := pool.CopyFrom(ctx, pgx.Identifier{"events616"}, []string{"user_id", "kind", "score"},
		pgx.CopyFromSlice(len(evs), func(i int) ([]any, error) { return []any{evs[i].UserID, evs[i].Kind, evs[i].Score}, nil }))
	if err != nil {
		log.Fatal(err)
	}
	report(fmt.Sprintf("5. COPY (CopyFrom reported %d)", n), time.Since(start))

	// The limit that makes a hand built multi-row VALUES fail.
	args := make([]any, 0, 70000)
	q := "insert into events616 (user_id, kind, score) values "
	for i := 0; i < 22000; i++ {
		if i > 0 {
			q += ","
		}
		q += fmt.Sprintf("($%d,$%d,$%d)", i*3+1, i*3+2, i*3+3)
		args = append(args, int32(i), "x", 1.0)
	}
	_, err = pool.Exec(ctx, q, args...)
	fmt.Println("6. multi-row VALUES with 66,000 parameters:", err)
}

1. autocommit, one INSERT per row (2,000 rows)      818ms       2444 rows/s  (table has 2000 rows)
2. one transaction, one INSERT per row             4.388s       4558 rows/s  (table has 20000 rows)
3. one transaction, pgx.Batch pipeline              110ms     182406 rows/s  (table has 20000 rows)
4. unnest, 1,000 rows per statement                  83ms     239913 rows/s  (table has 20000 rows)
5. COPY (CopyFrom reported 20000)                    28ms     717491 rows/s  (table has 20000 rows)
6. multi-row VALUES with 66,000 parameters: extended protocol limited to 65535 parameters

The program loads rows of three columns into a fresh table each time. The first approach uses 2,000 rows because it is too slow for 20,000; the rest load 20,000. The column "rows/s" is computed from the row count of each run.

1. Autocommit, one INSERT per row. 2,000 rows took 818ms, 2,444 rows per second. Every row paid a round trip and a commit. This is what for _, e := range events { db.Exec(...) } does, and at 100,000 rows it would take 40 seconds.

2. One transaction, one INSERT per row. 20,000 rows took 4.388s, 4,558 rows per second, about twice as fast. Removing the per row commit helped, and the round trip per row is still there. A transaction is the cheapest improvement and the smallest.

3. pgx.Batch in one transaction. 20,000 rows in 110ms, 182,406 rows per second, about 40 times faster than approach 2. A batch queues the statements and sends them together; the server runs them one after another and the client reads the results. The statements are the same and the round trips are gone. Reading every result matters: the loop calls br.Exec() once per queued statement before br.Close(), which is how errors surface.

4. One statement per 1,000 rows with unnest. 20,000 rows in 83ms, 239,913 rows per second. The statement is insert ... select * from unnest($1::int[], $2::text[], $3::float8[]): the rows travel as three arrays and the server turns them into rows. It parses and plans one statement per batch, and the number of parameters is three however many rows there are, which sidesteps the limit below.

5. COPY. 20,000 rows in 28ms, 717,491 rows per second. pool.CopyFrom uses PostgreSQL's binary COPY ... FROM STDIN, the bulk loading protocol, which skips most of the per statement machinery. It is the fastest and the least flexible.

The ordering is stable across runs: per row inserts last, a batch and unnest in the middle, COPY first. The absolute numbers move with hardware and network. On a remote database the per row approaches get slower in proportion to latency, and the pipelined ones barely do, so the gaps widen.

The parameter limit

The last line of the output is the trap in the "obvious" fast way. Building one insert ... values ($1,$2,$3),($4,$5,$6),... string with 22,000 rows and 66,000 parameters failed with extended protocol limited to 65535 parameters. The wire protocol stores the parameter count in 16 bits. With three columns the ceiling is 21,845 rows per statement, with ten columns 6,553. A loader that works on a test file and fails on production data usually hit this. Chunk the rows (1,000 per statement is a comfortable size), or use unnest or COPY, which do not have the problem.

Choosing

COPY has real limits. It cannot handle on conflict, so one duplicate key aborts the whole load; the common workaround is to COPY into an unlogged staging table and then run insert ... select ... on conflict from it. It does not return generated values. It bypasses rules but not triggers or constraints, so those still run and can slow it down. Values must match the column types exactly: pgx encodes Go values in the binary format, and an int into a smallint column fails in a way a text based COPY would not.

Making any of them faster

• Do it in one transaction so there is one commit, and keep that transaction short enough that a failure does not lose hours of work: commit every 10,000 to 100,000 rows if the load is resumable.
• Drop or defer indexes and constraints on a table you are filling from empty, and create them afterwards. Maintaining five indexes costs more than the insert. For a table that stays live, do not.
• Use an unlogged or staging table for loads that can be redone; unlogged tables skip the write ahead log and are far faster, and are lost on a crash.
• Parallelise carefully. Two or four loaders on separate connections can help when one is bound by the client, and none when the disk is. Test it.
• Run analyze afterwards so the planner sees the new table's size.
• Set synchronous_commit = off for the session when losing the last fraction of a second on a crash is acceptable. It removes the fsync wait per commit and is a reasonable trade for re-runnable imports.

Memory is the Go side's concern: stream with CopyFromFunc or CopyFromRows over a channel or a scanner, and never build a slice of every row when the source is a file. Pass a context with a deadline appropriate for the whole load, and treat cancellation as an abort.

When this advice does not apply

For a few hundred rows, any of the approaches finishes in a blink and clarity wins: use a loop in a transaction. For a web request that inserts one row, none of this matters, and the cost to watch is the single round trip. For loading a file once, the psql copy command or pg_restore may be easier than any Go code. The COPY protocol is documented in the PostgreSQL manual's page on COPY and populating a database, which also covers the index and checkpoint tips listed above.