GORM Upsert Batch Insert and Bulk Update Without Surprises

Writing many rows is where an ORM's convenience either helps or hurts. A loop that calls Create per row is the first version everybody writes, and it is slow in a way that scales with the row count. This article times five ways of inserting 10000 rows with GORM on PostgreSQL 18.6, hits PostgreSQL's parameter limit on purpose, and then covers upserts and bulk updates, with the numbers from one run.

It follows the GORM tutorial and transactions. The same problem at the driver level, with COPY for the fastest path, is in bulk insert in Go and pgx COPY. The ORM-wide view of cost is in GORM performance mistakes.

The lab

The Metric model has a unique name, a value, and six small integer columns, which makes each row bind 8 values. The program resets the table before each timing, so each insert method starts from an empty table.

package main

import (
	"fmt"
	"gorm.io/gorm/clause"
	"log"
	"strings"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

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

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type Metric struct {
	ID               uint
	Name             string `gorm:"uniqueIndex"`
	Value            int
	A, B, C, D, E, F int
}

func rows(n, from int) []Metric {
	out := make([]Metric, n)
	for i := range out {
		out[i] = Metric{Name: fmt.Sprintf("m%07d", from+i), Value: i}
	}
	return out
}

func main() {
	db := connect("l0718", nil)
	must(db.AutoMigrate(&Metric{}))
	reset := func() { db.Exec("truncate metrics restart identity") }
	run := func(label string, fn func() error) {
		reset()
		s := time.Now()
		err := fn()
		msg := "ok"
		if err != nil {
			msg = strings.Split(err.Error(), "\n")[0]
		}
		fmt.Printf("%-34s %-8s %s\n", label, time.Since(s).Round(time.Millisecond), msg)
	}

	const n = 10000
	run("one Create per row", func() error {
		for _, m := range rows(n, 0) {
			m := m
			if err := db.Create(&m).Error; err != nil {
				return err
			}
		}
		return nil
	})
	run("one Create per row, skip default tx", func() error {
		fast := db.Session(&gorm.Session{SkipDefaultTransaction: true})
		for _, m := range rows(n, 0) {
			m := m
			if err := fast.Create(&m).Error; err != nil {
				return err
			}
		}
		return nil
	})
	run("Create(slice) of 10000", func() error { return db.Create(rows(n, 0)).Error })
	run("CreateInBatches 1000", func() error { return db.CreateInBatches(rows(n, 0), 1000).Error })
	run("CreateInBatches 5000", func() error { return db.CreateInBatches(rows(n, 0), 5000).Error })

	// Upsert semantics.
	reset()
	db.Create(rows(3, 0))
	res := db.Clauses(clause.OnConflict{DoNothing: true}).Create(rows(5, 0))
	fmt.Println("DoNothing: inserted", res.RowsAffected, "of 5 (3 already existed)")

	in := rows(5, 0)
	for i := range in {
		in[i].Value = 1000 + i
	}
	res = db.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "name"}},
		DoUpdates: clause.AssignmentColumns([]string{"value"}),
	}).Create(&in)
	fmt.Println("DoUpdates(value): rows affected", res.RowsAffected)
	var first Metric
	db.Where("name = ?", "m0000000").Take(&first)
	fmt.Println("m0000000 value after upsert:", first.Value)

	res = db.Clauses(clause.OnConflict{UpdateAll: true, Columns: []clause.Column{{Name: "name"}}}).Create(rows(2, 0))
	fmt.Println("UpdateAll: rows affected", res.RowsAffected)

	// Bulk update in one statement versus a loop.
	reset()
	db.CreateInBatches(rows(n, 0), 1000)
	s := time.Now()
	db.Model(&Metric{}).Where("value < ?", 5000).Update("a", gorm.Expr("a + 1"))
	fmt.Println("one UPDATE for 5000 rows:", time.Since(s).Round(time.Millisecond))
	s = time.Now()
	var ms []Metric
	db.Where("value < ?", 5000).Find(&ms)
	for _, m := range ms {
		db.Model(&m).Update("b", m.B+1)
	}
	fmt.Println("load and update 5000 rows one by one:", time.Since(s).Round(time.Millisecond))
}

one Create per row                 8.293s   ok
one Create per row, skip default tx 4.525s   ok
Create(slice) of 10000             54ms     extended protocol limited to 65535 parameters
CreateInBatches 1000               80ms     ok
CreateInBatches 5000               89ms     ok
DoNothing: inserted 2 of 5 (3 already existed)
DoUpdates(value): rows affected 5
m0000000 value after upsert: 1000
UpdateAll: rows affected 2
one UPDATE for 5000 rows: 53ms
load and update 5000 rows one by one: 4.361s

Five ways to insert 10000 rows

One row at a time costs a round trip each. The loop made 10000 round trips, each wrapped in its own transaction, and took 8.293 s, 0.83 ms per row. This is the number to remember: on a local database, every statement costs most of a millisecond before any data work, and against a remote database it is more. Nothing in this loop is wrong, it is just the worst way to move a lot of rows.

The default transaction is half of that. GORM wraps each write in BEGIN and COMMIT, which is three statements for every insert. With SkipDefaultTransaction: true set on a session, the same loop took 4.525 s. It is a cheap win for write heavy code that does not depend on hooks or associations being atomic. If you use hooks or nested creates, leave it on, since the transaction is what makes them atomic. The right fix for the loop, though, is not to have one.

A single giant slice hits a hard limit. db.Create(rows(10000)) builds one multi row INSERT with 8 bind parameters per row, 80000 in total. PostgreSQL's protocol caps a statement at 65535 parameters, and pgx reports extended protocol limited to 65535 parameters. It failed in 54 ms without inserting anything. The limit is on parameters, not rows, so it depends on the width of the model: with 8 columns the largest single insert is 8191 rows. A model with 20 columns reaches the limit at 3276 rows. The code that works in tests with 500 rows fails in production on the day a batch job sends 10000.

CreateInBatches is the answer. It splits the slice into chunks and issues one multi row insert per chunk, all inside one transaction. Batches of 1000 took 80 ms and batches of 5000 took 89 ms: both about 100 times faster than the loop, and the difference between them is noise. Choose a size that keeps batch × columns well below 65535, and in the low thousands for most models. Bigger batches stop helping because the cost is no longer round trips, and they make a failure roll back more work.

For millions of rows, none of these is the fastest tool. COPY through pgx streams rows with no per statement parameters, and it is the right choice for imports. Use GORM's batches for the thousands, COPY for the millions, and drop to the pgx connection (db.ConnPool, or a separate pgx pool) for it.

Upserts with OnConflict

An upsert inserts a row or updates it when a key already exists. GORM expresses it with clause.OnConflict. The lab starts from 3 existing rows and tries three variants:

DoNothing. Clauses(clause.OnConflict{DoNothing: true}).Create(rows(5, 0)) sent 5 rows where 3 already existed. The output: DoNothing: inserted 2 of 5 (3 already existed). RowsAffected counts only the rows actually inserted, which is how you learn how many were new. This is the "insert if missing" pattern, and it avoids the aborted transaction problem described in the transactions article, because no error is raised.

DoUpdates. With Columns: name and DoUpdates: clause.AssignmentColumns([]string{"value"}), the 5 rows were written with new values and rows affected came back as 5: the 3 existing rows were updated and the 2 new ones inserted. Reading m0000000 back gave value after upsert: 1000. The Columns list names the conflict target, and it must match a unique index or constraint, or PostgreSQL rejects the statement.

UpdateAll. UpdateAll: true updates every non key column from the incoming row. It reports 2 rows affected for 2 rows of input. It is short to write and dangerous: any column present in the struct is overwritten, including zero values for fields the caller did not set. If the incoming struct comes from a partial payload, it wipes data. Prefer an explicit column list for DoUpdates.

Two finer points. AssignmentColumns takes the value from the incoming row (excluded.value). To compute from the existing value instead, use clause.Assignments(map[string]any{"value": gorm.Expr("metrics.value + excluded.value")}), which makes a counter that adds up across upserts in one statement and with no race. And an upsert on a table with a hook needs care: hooks run for the incoming struct, not for the row that was updated.

Bulk update

The last two timings compare updating 5000 rows two ways:

• One statement. Model(&Metric{}).Where("value < ?", 5000).Update("a", gorm.Expr("a + 1")) changed 5000 rows in 53 ms.
• Load and loop. Find, then Update per row, took 4.361 s for the same rows, about 80 times longer.

The single statement runs inside the database, with no round trips per row. The expression form (gorm.Expr) increments in place, so it is also correct under concurrency, where read then write loses updates. The cost of the single statement is that hooks do not run per row and updated_at is set once for the statement. If per row logic must run, put it in the SQL (a CASE expression, or an UPDATE ... FROM join), or accept the loop and batch it inside a transaction.

For different values per row (update each id to its own number), build a single statement with UPDATE ... FROM (VALUES ...) through Raw/Exec, or upsert with DoUpdates on the primary key. Either is one round trip.

Things that go wrong

• Partial failure. CreateInBatches runs in one transaction by default, so a failed batch rolls back every batch. If you want partial progress, call it per chunk in your own loop and record how far you got.
• IDs. After a batch insert GORM fills each struct's ID from RETURNING, but only if you passed pointers. The lab passes a slice value to keep it short, which means the IDs are not written back.
• Memory. A slice of a million structs is a lot of memory. Stream from the source and flush every few thousand rows.
• Locks. A big transaction holds row locks until commit. A long import on a hot table blocks other writers, so size it accordingly.

Checklist

• Use CreateInBatches, with a size that keeps rows times columns far below 65535.
• Never loop Create over a large set. SkipDefaultTransaction roughly halves it and does not fix it.
• Use OnConflict with DoNothing for insert if missing, and DoUpdates with named columns for upsert.
• Avoid UpdateAll when the input is partial.
• Update many rows with one statement and gorm.Expr, not a load and a loop.
• Use COPY for imports in the millions.

The clause API is in the GORM create guide, and the parameter limit is part of the PostgreSQL protocol message formats.