pgx Named Arguments and Batch Queries in Go Explained

Two small features of pgx remove a surprising amount of friction. Named arguments let you write @email in SQL instead of counting $1, $2, $7. Batches send many statements to the server in one network round trip, which turns the cost of a loop of small writes from the sum of their latencies into about one. This article uses both against PostgreSQL 18.6 from Go 1.25.6, measures the difference, and works through what a batch does when a statement fails, which is the part that decides whether it is safe to use.

It belongs to the database cornerstone, builds on the pgx tour and compares with bulk loading by COPY and the options measured in bulk inserts.

Named arguments

package main

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

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

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

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

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	for _, q := range []string{
		"drop table if exists items668",
		"create table items668 (id int primary key, name text not null, qty int not null default 0)",
	} {
		_, err := pool.Exec(ctx, q)
		must(err)
	}

	// 1. Named arguments: @name in the SQL, a map in the call, and no argument order to get wrong.
	_, err = pool.Exec(ctx, "insert into items668 (id, name, qty) values (@id, @name, @qty)", pgx.NamedArgs{"id": 1, "name": "bolt", "qty": 100})
	must(err)
	var name string
	must(pool.QueryRow(ctx, "select name from items668 where id = @id", pgx.NamedArgs{"id": 1}).Scan(&name))
	fmt.Println("1. NamedArgs: inserted and read back", name)

	// 2. A batch with a callback per statement, so each result is handled where it was queued.
	batch := &pgx.Batch{}
	for i := 2; i <= 4; i++ {
		batch.Queue("insert into items668 (id, name, qty) values ($1, $2, $3)", i, fmt.Sprintf("item %d", i), i*10).Exec(func(ct pgconn.CommandTag) error {
			fmt.Printf("   callback: %s\n", ct)
			return nil
		})
	}
	var total int
	batch.Queue("select sum(qty) from items668").QueryRow(func(row pgx.Row) error { return row.Scan(&total) })
	must(pool.SendBatch(ctx, batch).Close())
	fmt.Println("2. batch of 4 statements with callbacks; the total quantity read back:", total)

	// 3. One round trip against many. Same 500 inserts, one at a time and in a batch.
	start := time.Now()
	for i := 1000; i < 1500; i++ {
		_, err := pool.Exec(ctx, "insert into items668 (id, name) values ($1, 'x')", i)
		must(err)
	}
	loop := time.Since(start)
	start = time.Now()
	b2 := &pgx.Batch{}
	for i := 2000; i < 2500; i++ {
		b2.Queue("insert into items668 (id, name) values ($1, 'x')", i)
	}
	must(pool.SendBatch(ctx, b2).Close())
	batched := time.Since(start)
	fmt.Printf("3. 500 inserts: a loop %v, one batch %v\n", loop.Round(time.Millisecond), batched.Round(time.Millisecond))

	// 4. A failing statement: the batch stops there, and the error says which statement.
	b3 := &pgx.Batch{}
	b3.Queue("insert into items668 (id, name) values (5000, 'fine')")
	b3.Queue("insert into items668 (id, name) values (1, 'duplicate of the first row')")
	b3.Queue("insert into items668 (id, name) values (5001, 'never reached')")
	br := pool.SendBatch(ctx, b3)
	for i := 0; i < 3; i++ {
		_, err := br.Exec()
		var pg *pgconn.PgError
		switch {
		case err == nil:
			fmt.Printf("4. statement %d: ok\n", i+1)
		case errors.As(err, &pg):
			fmt.Printf("   statement %d: %s (%s)\n", i+1, pg.Message, pg.Code)
		default:
			fmt.Printf("   statement %d: %v\n", i+1, err)
		}
	}
	_ = br.Close()
	var survived, never int
	must(pool.QueryRow(ctx, "select count(*) filter (where id = 5000), count(*) filter (where id = 5001) from items668").Scan(&survived, &never))
	fmt.Printf("   afterwards: row 5000 exists %d, row 5001 exists %d (the batch ran as one implicit transaction, so the first insert was undone too)\n", survived, never)

	// 5. In a transaction the whole batch is all or nothing.
	tx, err := pool.Begin(ctx)
	must(err)
	b4 := &pgx.Batch{}
	b4.Queue("insert into items668 (id, name) values (6000, 'in a transaction')")
	b4.Queue("insert into items668 (id, name) values (1, 'duplicate')")
	err = tx.SendBatch(ctx, b4).Close()
	_ = tx.Rollback(ctx)
	var in int
	must(pool.QueryRow(ctx, "select count(*) from items668 where id = 6000").Scan(&in))
	fmt.Printf("5. batch inside a transaction: Close returned an error (%v), row 6000 exists %d after rollback\n", err != nil, in)

	// 6. Queries in a batch are read in order.
	b5 := &pgx.Batch{}
	b5.Queue("select name from items668 where id = $1", 1)
	b5.Queue("select count(*) from items668")
	br = pool.SendBatch(ctx, b5)
	must(br.QueryRow().Scan(&name))
	var n int
	must(br.QueryRow().Scan(&n))
	_ = br.Close()
	fmt.Printf("6. results come back in queue order: %q then a count of %d, and Len() was %d\n", name, n, b5.Len())
}

1. NamedArgs: inserted and read back bolt
   callback: INSERT 0 1
   callback: INSERT 0 1
   callback: INSERT 0 1
2. batch of 4 statements with callbacks; the total quantity read back: 190
3. 500 inserts: a loop 472ms, one batch 4ms
4. statement 1: ok
   statement 2: duplicate key value violates unique constraint "items668_pkey" (23505)
   statement 3: duplicate key value violates unique constraint "items668_pkey" (23505)
   afterwards: row 5000 exists 0, row 5001 exists 0 (the batch ran as one implicit transaction, so the first insert was undone too)
5. batch inside a transaction: Close returned an error (true), row 6000 exists 0 after rollback
6. results come back in queue order: "bolt" then a count of 1004, and Len() was 2

pgx.NamedArgs is a map[string]any. When you pass it as the only argument, pgx rewrites @name placeholders in the SQL into $1, $2 and so on, and orders the values to match. The first block inserts a row with @id, @name and @qty and reads it back with @id. The benefits are practical. A statement with eight parameters stops being an exercise in counting. The same name can appear twice in the SQL and be sent once. And reordering columns in the SQL does not require reordering the arguments. The cost is a small rewrite per call, which is negligible, and the fact that a name that is missing from the map is an error at run time.

Take care with SQL that already contains an @ that is not a placeholder: PostgreSQL operators such as @> for containment would be misread, so use positional arguments for those statements or write the operator with spaces, and test the statement. Mixing named and positional arguments in one call is not supported.

A batch is one round trip

pgx.Batch collects statements with Queue(sql, args...), and pool.SendBatch(ctx, batch) sends all of them at once and returns a pgx.BatchResults from which you read each statement's result. Every query that you send singly pays a round trip: the client writes the statement and waits for the reply before sending the next. A batch writes them all, then reads all the replies.

The third block shows the effect with 500 single row inserts. In a loop, each insert waited for its reply: 472ms, about a millisecond each against a database in a local container. As one batch: 4ms. The statements are the same and the server does the same work. What disappeared is 499 round trips. With a database in another availability zone, where a round trip costs a millisecond or two, the loop takes seconds and the batch stays near the cost of the longest single trip.

A batch is not the same as one big statement. Each queued statement is still parsed and planned (prepared statements are cached per connection), each has its own parameters, and each returns its own result. That is its advantage over a hand built multi row VALUES: heterogeneous statements, per statement results and no limit of 65,535 parameters in one statement.

Reading the results

The second block queues three inserts, each with a callback, and a select. Queue(...) returns a *pgx.QueuedQuery on which you set what to do with the result: .Exec(func(pgconn.CommandTag) error) for a statement, .Query(func(pgx.Rows) error) for rows, or .QueryRow(func(pgx.Row) error) for one row. The program printed the command tag of each insert from its callback and filled total from the select's callback, then closed the results with a single Close. The total read back was 190, the sum of the rows inserted so far. Callbacks keep the code that handles a result next to the statement that produced it, which matters when a batch holds thirty statements.

Without callbacks, you read results yourself, in the order the statements were queued: br.Exec() for each statement and br.Query() or br.QueryRow() for each query. The sixth block does it: the first result was a name, the second a count, and Len() reported the two queued statements. Reading out of order, or skipping a result, returns the wrong data or an error. And always call Close on the results: it reads any results you have not read, reports the batch's error and frees the connection. A connection whose batch was not closed is stuck until it is.

When a statement fails

The fourth block is the one to understand. The batch queues three inserts: a good one with id 5000, a duplicate of an existing id, and one with id 5001. Reading the results one by one: statement 1 returned no error, statement 2 returned duplicate key value violates unique constraint "items668_pkey" with code 23505, and statement 3 returned the same error. After the batch, neither 5000 nor 5001 existed.

Both facts come from how the protocol works. PostgreSQL stops processing the batch at the first error, so statement 3 never ran, and every later result reports that error. And the whole batch ran as one implicit transaction, because pgx sends the statements followed by one Sync, and PostgreSQL treats everything up to a Sync as a single transaction when none was opened explicitly. The error rolled back statement 1 too. So a batch outside a transaction is atomic: all of it or none of it. That is good to know, because it is often what you want. It is bad to forget, because the row from the successful first statement is gone and no error says so for that statement.

If the statements are independent and each should succeed or fail on its own, a batch is the wrong shape: send them separately, or in several batches, or use COPY with a staging table.

Inside a transaction

The fifth block sends a batch through tx.SendBatch in an explicit transaction: the second statement failed, Close returned an error and, after Rollback, the first row was not there. With an explicit transaction, a failed batch aborts the transaction: until you roll back, every further statement fails with current transaction is aborted. Return the error and roll back, as BeginFunc does. For a batch that must be part of a larger unit of work, this is the form to use: queue related writes, send them in the transaction, and let one error undo everything.

Choosing and sizing

• Use a batch for many small statements that are sent together: inserting a handful of related rows, loading a page of data from several tables, applying a list of updates. It is the cheapest change that removes round trip cost.
• Use COPY for large volumes of rows into one table; it is faster still, as pgx COPY measures.
• Keep batches to a few hundred or a few thousand statements. A batch holds its statements in memory and its results until read, and one that is too large delays the first error.
• Do not put statements that depend on each other's results in one batch, since you cannot read a result before sending the next statement. Use a transaction for that, or a single statement with returning and a CTE.
• Remember the implicit transaction. If partial success is acceptable, do not batch.

The API is documented in the pgx package reference for Batch and NamedArgs, and the protocol behaviour behind the implicit transaction in the PostgreSQL manual's page on the extended query protocol.