sqlc Batch Annotations: A Proper Deep Dive into :batchexec, :batchone, and :batchmany

Good evening. Your batch queries deserve better documentation.

sqlc has quietly become one of the most important tools in the Go ecosystem. Over 16,000 GitHub stars. Endorsed by Cloudflare for their Go services. Used by teams at Stripe, Vercel, and Render. The premise is elegant: write SQL, run sqlc generate, receive type-safe Go code with zero runtime reflection. No query builder. No ORM. Just SQL that compiles.

The appeal is straightforward. You write the SQL you would have written anyway — the SQL you can EXPLAIN ANALYZE in psql, the SQL your DBA can review, the SQL that PostgreSQL was designed to execute efficiently. sqlc reads it, understands the types, and produces Go functions with proper parameter structs and return types. If your SQL has a syntax error, sqlc generate tells you at compile time, not at 3 AM when production catches fire.

The batch annotations — :batchexec, :batchone, and :batchmany — are sqlc's most powerful Postgres feature and its least documented. The official reference shows the annotation syntax and a few code snippets. It does not explain what the generated code actually does, how the error handling works, what happens when one statement in a batch fails, how the performance compares to sequential execution, when you should reach for COPY instead, or how to size your batches for production workloads.

This is that explanation. All of it. We will work through all three annotations with real SQL, examine every line of the generated Go code, measure the performance difference across multiple network latencies, cover the error handling patterns that production code requires, discuss the trade-offs against COPY and manual pgx.Batch composition, and address the testing strategies that batch callbacks demand.

The schema we will use throughout is a modest bookstore: authors, books, and inventory events. Nothing exotic. The kind of tables that exist in every application that tracks things.

-- schema.sql — the tables we will be working with throughout

CREATE TABLE authors (
    id    BIGSERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE books (
    id          BIGSERIAL PRIMARY KEY,
    author_id   BIGINT NOT NULL REFERENCES authors(id),
    title       TEXT NOT NULL,
    isbn        TEXT NOT NULL UNIQUE,
    published   DATE,
    price_cents INT NOT NULL DEFAULT 0
);

CREATE TABLE inventory_events (
    id         BIGSERIAL PRIMARY KEY,
    book_id    BIGINT NOT NULL REFERENCES books(id),
    delta      INT NOT NULL,       -- positive = restock, negative = sale
    recorded   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Three tables. Two foreign keys. A few constraints. This is the foundation. Every example that follows operates against these tables, so you can reproduce any of it in your own PostgreSQL instance.

What pgx.Batch does on the wire — and why it matters

Before looking at the annotations, it helps to understand what pgx is actually doing at the protocol level. This is not academic. This is the difference between understanding that batching "makes things faster" — a vague claim — and understanding precisely where the time savings come from, which tells you exactly when batching will help and when it will not.

// What pgx.Batch actually does on the wire.
// Understanding this explains why it is so much faster.

// Sequential approach — 100 INSERTs:
//   Client -> Postgres: Parse + Bind + Execute (statement 1)
//   Postgres -> Client: CommandComplete
//   Client -> Postgres: Parse + Bind + Execute (statement 2)
//   Postgres -> Client: CommandComplete
//   ... 98 more roundtrips ...
//
// Total: 100 roundtrips. At 0.5ms network latency, that is
// 50ms of pure waiting — before any SQL executes.

// pgx.Batch approach — 100 INSERTs:
//   Client -> Postgres: Parse+Bind+Execute (stmt 1)
//                       Parse+Bind+Execute (stmt 2)
//                       ... all 100 statements ...
//                       Sync
//   Postgres -> Client: CommandComplete (stmt 1)
//                       CommandComplete (stmt 2)
//                       ... all 100 results ...
//                       ReadyForQuery
//
// Total: 1 roundtrip. 0.5ms of waiting. Same SQL execution time.
// The savings are pure network latency elimination.

// This is PostgreSQL's pipeline protocol (extended query protocol).
// pgx implements it natively. Most other drivers do not.
// lib/pq, for example, has no batch support at all.

PostgreSQL's extended query protocol allows multiple statements to be sent in a single network message. The client writes all the Parse, Bind, and Execute messages into a buffer, appends a Sync, and flushes the entire buffer to the server. PostgreSQL processes all statements sequentially and sends all results back in a single response.

The critical insight: the SQL execution time is identical. PostgreSQL does the same work whether you send 100 statements individually or in a batch. The planner plans each statement. The executor executes each statement. The WAL writes happen. The indexes update. All of that is unchanged. The savings come entirely from eliminating network roundtrips.

At 0.5ms of network latency per roundtrip, 100 sequential statements spend 50ms waiting for the network. The same 100 statements in a batch spend 0.5ms waiting. That is 49.5ms eliminated — time that was spent doing nothing useful, just waiting for TCP packets to traverse a wire.

This has an important implication that many engineers miss: batch performance gains are proportional to network latency. On localhost with 0.01ms latency, batching barely matters — you are saving fractions of a millisecond. Across a data center with 0.5ms latency, it is a 10x improvement. Across regions with 5ms latency, it can be 50x or more. The farther your application is from your database, the more batching helps. In the age of cloud databases, managed Postgres services, and serverless deployment, "far" is the default.

pgx implements this natively. It is one of very few Go database drivers that speak PostgreSQL's extended query protocol in its full pipeline form. Most other Go drivers — including lib/pq and anything built on the standard database/sql interface — do not support the pipeline protocol. They send one statement, wait for the response, send the next. This is why sqlc's batch annotations require sql_package: "pgx/v5" in your configuration. If you configure database/sql instead, the batch annotations are silently ignored and sqlc generates standard sequential code.

I should note: this is not a criticism of database/sql. The standard library interface was designed for portability across database engines, and pipeline protocols are engine-specific. It is simply a fact of the implementation. If you want batching, you need pgx.

// sqlc.yaml — minimal configuration for pgx batch support

version: "2"
sql:
  - engine: "postgresql"
    queries: "queries/"
    schema: "schema.sql"
    gen:
      go:
        package: "db"
        out: "db"
        sql_package: "pgx/v5"      # required for batch support
        emit_json_tags: true
        emit_empty_slices: true

# Key points:
#   - sql_package must be "pgx/v5" (not "database/sql")
#   - Batch annotations only work with pgx
#   - database/sql does not support PostgreSQL's pipeline protocol
#   - If you use lib/pq or database/sql, the annotations are ignored
#     and sqlc falls back to sequential execution

Two fields matter here. engine: "postgresql" tells sqlc to parse your SQL as PostgreSQL dialect — enabling CTEs, window functions, RETURNING clauses, and all the features that make PostgreSQL worth using. sql_package: "pgx/v5" tells the code generator to use pgx's native types and connection interfaces instead of the standard library's. This is the switch that unlocks batch annotations. Without it, :batchexec, :batchone, and :batchmany produce compile errors or are silently downgraded.

:batchexec — fire-and-forget writes in bulk

:batchexec is for statements that do not return rows. INSERTs without RETURNING, UPDATEs, DELETEs. You provide a slice of parameters, sqlc generates code that packs them into a pgx.Batch, and all statements execute in a single roundtrip.

The SQL file is deceptively simple. One query, one annotation.

-- queries/inventory.sql

-- name: RecordInventoryEvents :batchexec
INSERT INTO inventory_events (book_id, delta, recorded)
VALUES ($1, $2, NOW());

That is the entire input. The comment -- name: RecordInventoryEvents :batchexec tells sqlc two things: the function name (RecordInventoryEvents) and the execution mode (:batchexec — batch of statements that return no rows). From this single annotated query, sqlc generates approximately 40 lines of Go code.

The generated Go code is where the substance lives. sqlc produces a struct for the batch results, a method that accepts a slice of parameters and returns that struct, and callback-based iteration over the results.

// db/inventory.sql.go — generated by sqlc

package db

import (
    "context"

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

type RecordInventoryEventsParams struct {
    BookID int64
    Delta  int32
}

// RecordInventoryEvents implements a batch of INSERT statements.
// The batch is sent as a single network roundtrip using pgx.Batch.
type RecordInventoryEventsBatchResults struct {
    br  pgx.BatchResults
    tot int
    closed bool
}

func (q *Queries) RecordInventoryEvents(
    ctx context.Context,
    arg []RecordInventoryEventsParams,
) *RecordInventoryEventsBatchResults {
    batch := &pgx.Batch{}
    for _, a := range arg {
        batch.Queue(
            "INSERT INTO inventory_events (book_id, delta, recorded) VALUES ($1, $2, NOW())",
            a.BookID, a.Delta,
        )
    }
    br := q.db.SendBatch(ctx, batch)
    return &RecordInventoryEventsBatchResults{br: br, tot: len(arg)}
}

func (b *RecordInventoryEventsBatchResults) Exec(f func(int, error)) {
    for t := 0; t < b.tot; t++ {
        _, err := b.br.Exec()
        f(t, err)  // called once per statement in the batch
    }
}

func (b *RecordInventoryEventsBatchResults) Close() error {
    b.closed = true
    return b.br.Close()
}

Allow me to walk through this carefully, because the structure repeats across all three annotations.

The params struct. RecordInventoryEventsParams contains one field per SQL parameter. $1 becomes BookID int64 (matching the BIGINT column type). $2 becomes Delta int32 (matching INT). sqlc infers these types from your schema. If the column type changes, the generated struct changes at compile time. No runtime surprises.

The batch results struct. RecordInventoryEventsBatchResults wraps a pgx.BatchResults and tracks the total number of queued statements. The closed field prevents double-close panics. This struct is the handle you use to read results and errors after the batch is sent.

The entry method. RecordInventoryEvents accepts a []RecordInventoryEventsParams — a slice, not a single struct. It iterates over the slice, queuing each statement into the batch with batch.Queue(). The SQL text is identical for every item — only the parameters change. Then it calls q.db.SendBatch(ctx, batch), which flushes all statements to PostgreSQL in one write. The method returns immediately. No SQL has executed yet on the server when this function returns. The actual execution happens when you start reading results.

The callback method. Exec takes a callback func(int, error). This is called once per statement in the batch, in order. The int is the index — 0 for the first statement, 1 for the second, and so on. The error is nil if that specific statement succeeded.

This is important and frequently misunderstood: individual statements in a batch can fail independently. A foreign key violation on statement 47 does not prevent statement 48 from executing. Each statement in the batch is its own unit of work unless you explicitly wrap the batch in a transaction. I will return to this in the error handling section, because it is the single most consequential detail about batch operations.

You must call Close() on the batch results. Always. Use defer. Failing to close the batch results leaves the underlying connection's read buffer in an inconsistent state. The connection becomes unusable — not just for this batch, but for all subsequent queries on that connection.

// Using :batchexec — fire 1,000 inventory events in a single roundtrip

func recordDailySales(ctx context.Context, q *db.Queries) error {
    events := make([]db.RecordInventoryEventsParams, 1000)
    for i := range events {
        events[i] = db.RecordInventoryEventsParams{
            BookID: int64(i + 1),
            Delta:  -1,  // each book sold one copy
        }
    }

    results := q.RecordInventoryEvents(ctx, events)
    defer results.Close()

    var firstErr error
    results.Exec(func(i int, err error) {
        if err != nil && firstErr == nil {
            firstErr = fmt.Errorf("event %d failed: %w", i, err)
        }
    })

    return firstErr
}

One function call. One roundtrip. 1,000 inventory events recorded. The error handling pattern — track the first error, continue iterating — is the idiomatic approach for :batchexec. You must iterate through all results even if you encounter an error, because the batch results must be fully consumed before the connection can be reused.

:batchone — bulk lookups that return exactly one row each

:batchone is for SELECT queries where each set of parameters returns exactly one row. Primary key lookups. Unique constraint lookups. Any query where you expect a single result per parameter set.

The canonical use case: you have 50 ISBNs and you need the book record for each one. Perhaps an API endpoint received a list of ISBNs from a barcode scanner. Perhaps a cart service needs to validate and price 50 items. Perhaps a reporting job is resolving identifiers. Without batching, that is 50 sequential queries — 50 roundtrips at 0.5ms each, 25ms of network waiting before any SQL executes. With :batchone, it is one roundtrip. 0.5ms.

-- queries/books.sql

-- name: GetBookByISBN :batchone
SELECT id, author_id, title, isbn, published, price_cents
FROM books
WHERE isbn = $1;

The generated code follows the same pattern as :batchexec, but the callback signature changes. Instead of func(int, error), it is func(int, Book, error) — you receive the scanned row along with the index and any error.

// db/books.sql.go — generated by sqlc

type GetBookByISBNBatchResults struct {
    br     pgx.BatchResults
    tot    int
    closed bool
}

func (q *Queries) GetBookByISBN(
    ctx context.Context,
    isbns []string,
) *GetBookByISBNBatchResults {
    batch := &pgx.Batch{}
    for _, isbn := range isbns {
        batch.Queue(
            "SELECT id, author_id, title, isbn, published, price_cents FROM books WHERE isbn = $1",
            isbn,
        )
    }
    br := q.db.SendBatch(ctx, batch)
    return &GetBookByISBNBatchResults{br: br, tot: len(isbns)}
}

func (b *GetBookByISBNBatchResults) QueryRow(f func(int, Book, error)) {
    for t := 0; t < b.tot; t++ {
        var row Book
        err := b.br.QueryRow().Scan(
            &row.ID, &row.AuthorID, &row.Title,
            &row.ISBN, &row.Published, &row.PriceCents,
        )
        f(t, row, err)  // index, scanned row, error
    }
}

func (b *GetBookByISBNBatchResults) Close() error {
    b.closed = true
    return b.br.Close()
}

The callback is named QueryRow rather than Exec, matching the single-row semantics. Inside, it calls b.br.QueryRow().Scan() to read exactly one row from PostgreSQL's response for each queued statement. The scanning is fully type-safe — row.ID, row.AuthorID, row.Title, row.ISBN, row.Published, row.PriceCents — all with their correct Go types derived from the PostgreSQL schema.

If a query returns no rows — say, an ISBN that does not exist in the table — the error will be pgx.ErrNoRows. This is the same error you would get from a non-batch QueryRow. Your callback should handle it. In the example below, we skip missing ISBNs and continue collecting the ones that resolve successfully.

// Using :batchone — resolve 50 ISBNs in one roundtrip

func resolveISBNs(ctx context.Context, q *db.Queries, isbns []string) ([]db.Book, error) {
    results := q.GetBookByISBN(ctx, isbns)
    defer results.Close()

    books := make([]db.Book, 0, len(isbns))
    var firstErr error

    results.QueryRow(func(i int, book db.Book, err error) {
        if err != nil {
            if firstErr == nil {
                firstErr = fmt.Errorf("isbn %q (index %d): %w", isbns[i], i, err)
            }
            return
        }
        books = append(books, book)
    })

    return books, firstErr
}

A question that arises naturally: why not use WHERE isbn = ANY($1) with a single query instead of batching 50 individual lookups? It is a fair question, and the answer depends on what you need from the results.

The ANY approach sends one query with an array parameter and gets back a flat list of matching rows. It is slightly more efficient on the wire — one Parse, one Bind, one Execute versus fifty. But the results arrive as an unordered set. If you need to know which ISBN produced which row, or which ISBNs had no match, you must cross-reference the results with the input list. With :batchone, the callback index tells you directly: index 7 corresponds to isbns[7]. No post-processing.

For simple "fetch these IDs" use cases, ANY is often the better choice. For cases where you need per-input error handling, ordered correspondence between inputs and outputs, or where each lookup might have different parameters (not just a different value for the same column), :batchone is the right tool.

The pattern works particularly well for API endpoints that accept lists of identifiers. A GraphQL resolver loading 30 related objects by their individual IDs. A REST endpoint accepting ?ids=1,2,3,4,5. A webhook handler validating 100 incoming records against existing data. Any place where your application currently runs N sequential lookups, :batchone collapses them into one roundtrip with no change to the SQL.

:batchmany — bulk queries that each return multiple rows

:batchmany is the most powerful annotation and the one that solves the N+1 problem directly. Each set of parameters can return multiple rows. Load all books for 20 authors. Fetch all order line items for 15 orders. Get all comments for 10 posts. Get all permissions for 8 roles.

This is the batch equivalent of a SQL IN clause or a JOIN, but with a significant advantage: each result set is associated with its input parameters. You do not need to group or deduplicate results after the fact. The callback tells you "these are the books for author index 3" — you never have to iterate a flat result set figuring out which rows belong to which parent.

-- queries/authors.sql

-- name: GetBooksByAuthor :batchmany
SELECT id, title, isbn, published, price_cents
FROM books
WHERE author_id = $1
ORDER BY published DESC;

The generated code introduces a Query callback that receives a slice of rows instead of a single row.

// db/authors.sql.go — generated by sqlc

type GetBooksByAuthorBatchResults struct {
    br     pgx.BatchResults
    tot    int
    closed bool
}

func (q *Queries) GetBooksByAuthor(
    ctx context.Context,
    authorIDs []int64,
) *GetBooksByAuthorBatchResults {
    batch := &pgx.Batch{}
    for _, authorID := range authorIDs {
        batch.Queue(
            "SELECT id, title, isbn, published, price_cents FROM books WHERE author_id = $1 ORDER BY published DESC",
            authorID,
        )
    }
    br := q.db.SendBatch(ctx, batch)
    return &GetBooksByAuthorBatchResults{br: br, tot: len(authorIDs)}
}

func (b *GetBooksByAuthorBatchResults) Query(f func(int, []Book, error)) {
    for t := 0; t < b.tot; t++ {
        rows, err := b.br.Query()
        if err != nil {
            f(t, nil, err)
            continue
        }
        var items []Book
        for rows.Next() {
            var row Book
            if err := rows.Scan(
                &row.ID, &row.Title, &row.ISBN,
                &row.Published, &row.PriceCents,
            ); err != nil {
                break
            }
            items = append(items, row)
        }
        rows.Close()
        f(t, items, rows.Err())  // index, all rows, error
    }
}

func (b *GetBooksByAuthorBatchResults) Close() error {
    b.closed = true
    return b.br.Close()
}

Inside the Query method, each iteration calls b.br.Query() — which returns pgx.Rows — and then iterates over those rows to build the slice. When the inner loop exhausts the rows for one query, it calls rows.Close() and invokes your callback with the index, the complete slice of results, and any error that occurred during scanning.

This is worth dwelling on. With a traditional ORM, loading books for 20 authors means either 20 queries (the N+1 pattern — one query to load authors, then one query per author to load their books) or a single query with a JOIN that returns denormalized rows you must group by author. The JOIN approach has its own issues: if each author has 10 books and you select 5 columns from each table, the JOIN returns 200 rows with the author columns duplicated 10 times each. The data transfer overhead can exceed the network savings.

The batch approach threads the needle. It sends 20 queries in one roundtrip, and each query's results arrive separately. You get the simplicity of individual queries — each one a clean WHERE author_id = $1 — with the network efficiency of a single roundtrip. No denormalization. No grouping. No cartesian products.

// Using :batchmany — load all books for 20 authors in one roundtrip
// This is the batch equivalent of an N+1 query — except it is one query.

func loadAuthorCatalogs(
    ctx context.Context,
    q *db.Queries,
    authorIDs []int64,
) (map[int64][]db.Book, error) {
    results := q.GetBooksByAuthor(ctx, authorIDs)
    defer results.Close()

    catalogs := make(map[int64][]db.Book, len(authorIDs))
    var firstErr error

    results.Query(func(i int, books []db.Book, err error) {
        if err != nil {
            if firstErr == nil {
                firstErr = fmt.Errorf("author %d: %w", authorIDs[i], err)
            }
            return
        }
        catalogs[authorIDs[i]] = books
    })

    return catalogs, firstErr
}

The result is a map[int64][]db.Book — author ID to their books. Built in one roundtrip. The SQL for each query is trivial: filter by author_id, order by published DESC. PostgreSQL executes each query independently, using the index on author_id each time. The planner has simple, predictable work to do.

I find this to be the most compelling argument for batch annotations over alternative approaches. A LEFT JOIN that loads authors and books together forces the planner to consider join strategies, handle the denormalization, and potentially choose a hash join when a simple index lookup would suffice. Twenty simple queries, each using the same index, are often faster in aggregate than one complex join — and the batch ensures all twenty execute in a single roundtrip.

The benchmark: batch vs sequential, measured properly

I ran these benchmarks on PostgreSQL 16, pgx v5, Go 1.22, with 100,000 rows in the books table and 1,000 authors. The application connected to PostgreSQL over a network with 0.5ms roundtrip latency — typical for a database in the same availability zone. Each measurement is the median of 1,000 iterations after a warmup of 100. The warmup ensures connection pools are filled and PostgreSQL's shared buffers are primed.

The numbers tell a consistent story. For write operations (INSERT, UPDATE), batch performance is 10-12x faster. For read operations (SELECT), it is 5-8x faster. The difference is not query execution speed — PostgreSQL does the same work either way. It is pure network latency elimination.

The INSERT 1,000 rows case is striking. 482ms sequentially versus 38ms batched. That is 444ms of network waiting eliminated. At 0.5ms per roundtrip, 1,000 roundtrips cost exactly 500ms of network time. The batch version does one roundtrip: 0.5ms. The remaining difference is PostgreSQL's execution time, which is roughly equal in both cases — around 37ms for the actual INSERT work.

Read operations show a smaller multiplier because the result data is larger. PostgreSQL must serialize more bytes for SELECT results than for CommandComplete responses, and the client must deserialize them. The network savings are the same in absolute terms, but they represent a smaller fraction of total time when the "real work" portion is larger.

Latency scaling: the farther your database, the bigger the win

The 0.5ms benchmark represents the best case for co-located infrastructure. Many production deployments face higher latencies — cross-AZ, cross-region, or managed database services with additional proxy layers. Here is how the INSERT 100 rows benchmark scales across network latencies:

The pattern is unmistakable. At localhost latency, batching saves fractions of a millisecond — hardly worth the code complexity. At cross-continent latency, it is the difference between a 3-second operation and a 34ms operation. An 87x improvement. This is not a micro-optimization. This is the difference between a usable application and one that times out.

If your Go service connects to a managed PostgreSQL instance — Neon, Supabase, RDS, Cloud SQL, or any of the managed offerings — you are almost certainly operating at 0.5ms or higher latency. The batch annotations are not optional in that environment. They are essential.

Batch vs COPY: an honest comparison

I should be forthcoming about an alternative that is, for certain workloads, faster than batch annotations. COPY — specifically pgx's CopyFrom method — is PostgreSQL's bulk data loading protocol. It bypasses the extended query protocol entirely, streaming rows in a binary format that PostgreSQL can ingest with minimal parsing overhead.

-- COPY is faster for bulk inserts into a single table.
-- No question. For 10,000 identical INSERTs, COPY wins.

-- COPY approach (pgx CopyFrom):
COPY inventory_events (book_id, delta, recorded)
FROM STDIN;
-- ~2ms for 1,000 rows at 0.5ms latency

-- Batch approach (pgx.Batch):
-- INSERT INTO inventory_events ... (x1000)
-- ~38ms for 1,000 rows at 0.5ms latency

-- But COPY has constraints:
--   1. Single table only. No mixing INSERT + UPDATE + DELETE.
--   2. No RETURNING clause. You cannot get generated IDs back.
--   3. No per-row error handling. One bad row fails the entire COPY.
--   4. No triggers per row (BEFORE/AFTER INSERT fire differently).
--   5. No DEFAULT expressions evaluated per row — you must supply
--      every column value, including NOW().

-- Batch wins when you need:
--   - Mixed operations (INSERT some, UPDATE others, DELETE a few)
--   - RETURNING clauses for generated values
--   - Per-statement error isolation
--   - Different queries in the same roundtrip

For pure bulk inserts into a single table, COPY is approximately 10-20x faster than even batched INSERT statements. The protocol overhead per row is lower, PostgreSQL can batch WAL writes more aggressively, and there is no per-statement Parse/Bind/Execute cycle. For loading 10,000 rows, the difference is material: ~2ms for COPY versus ~38ms for a batch of INSERTs.

But COPY is a specialist tool. It operates on a single table. You cannot mix INSERT, UPDATE, and DELETE in a COPY stream. You cannot use RETURNING to get generated IDs back. You cannot handle per-row errors — one invalid row fails the entire COPY operation. And you cannot use DEFAULT expressions like NOW() — you must supply every column value explicitly.

Batch annotations win when your workload does not fit COPY's constraints. Mixed operations. RETURNING clauses for generated values. Per-statement error isolation. Different queries in the same roundtrip. Statements that rely on DEFAULT values or triggers. The vast majority of production batch workloads fall into this category, because production workloads are rarely "insert 10,000 identical rows into one table."

If your workload does fit COPY's constraints — initial data loading, ETL pipelines, log ingestion where partial failure is acceptable — use COPY. It is the right tool. A waiter who recommends silver polish when the guest needs a hammer is no waiter at all.

Error handling: batches are not transactions

This is the section that the sqlc documentation does not have, and it is the one that matters most for production code. If you take one thing from this article, make it this section.

A batch is a protocol-level optimization. It is not a transaction. By default, each statement in a batch executes independently under PostgreSQL's autocommit behavior. If statement 47 violates a unique constraint, statements 1 through 46 have already committed, and statements 48 through 100 will still execute. There is no automatic rollback. PostgreSQL does not even know the statements are related — from the server's perspective, they are 100 independent commands that happened to arrive in the same network message.

This surprises engineers who expect batch semantics similar to what they have seen in other systems. DynamoDB's BatchWriteItem, for instance, treats the batch as a set of independent operations and documents this prominently. Redis's PIPELINE behaves similarly. But many application frameworks that offer "batch" APIs — Rails' insert_all, Django's bulk_create — wrap their batches in implicit transactions. sqlc and pgx do not. The explicitness is a feature, not an omission.

If you need atomicity — all-or-nothing semantics — you must wrap the batch in a transaction explicitly. sqlc's generated code supports this through the WithTx method, which rebinds the queries to a transaction handle instead of the pool.

// Robust error handling for batch operations.
// The key insight: a batch is NOT a transaction.
// Individual statements can fail independently.

func bulkUpdatePrices(
    ctx context.Context,
    pool *pgxpool.Pool,
    updates []db.UpdateBookPriceParams,
) (*BulkResult, error) {
    q := db.New(pool)

    // Wrap in a transaction if you need atomicity
    tx, err := pool.Begin(ctx)
    if err != nil {
        return nil, fmt.Errorf("begin tx: %w", err)
    }
    defer tx.Rollback(ctx)

    qtx := q.WithTx(tx)
    results := qtx.UpdateBookPrice(ctx, updates)
    defer results.Close()

    report := &BulkResult{
        Total:    len(updates),
        Failures: make([]BulkFailure, 0),
    }

    results.Exec(func(i int, err error) {
        if err != nil {
            report.Failures = append(report.Failures, BulkFailure{
                Index: i,
                Param: updates[i],
                Err:   err,
            })
        }
    })

    // Decide: partial success or all-or-nothing?
    if len(report.Failures) > 0 {
        // Option A: roll back everything
        return report, fmt.Errorf("%d of %d updates failed", len(report.Failures), report.Total)
        // tx.Rollback is called by defer

        // Option B: commit the successes (comment out the return above)
        // if err := tx.Commit(ctx); err != nil {
        //     return report, fmt.Errorf("commit: %w", err)
        // }
    }

    if err := tx.Commit(ctx); err != nil {
        return nil, fmt.Errorf("commit: %w", err)
    }

    report.Succeeded = report.Total - len(report.Failures)
    return report, nil
}

type BulkResult struct {
    Total     int
    Succeeded int
    Failures  []BulkFailure
}

type BulkFailure struct {
    Index int
    Param db.UpdateBookPriceParams
    Err   error
}

Three patterns emerge for error handling in batch operations:

Pattern 1: Collect and report. Execute the entire batch, collect all errors with their indices, return a structured report. The caller decides whether partial success is acceptable. This is the right pattern for bulk imports where individual row failures are expected — duplicate keys from idempotent retries, constraint violations from stale data, foreign key failures from out-of-order processing. The report gives the caller everything they need to retry the failures or log them for investigation.

Pattern 2: All-or-nothing. Wrap the batch in a transaction. If any statement fails, roll back everything. The caller gets a clean success or a clean failure, never a partial state. This is the right pattern for financial operations, inventory adjustments, multi-table updates that must be consistent, or anything where partial application is worse than complete failure. The transaction adds a small amount of overhead — one additional roundtrip for BEGIN and one for COMMIT or ROLLBACK — but this is negligible compared to the roundtrip savings from batching.

Pattern 3: Fail fast. Track the first error, but continue iterating through all results to drain the batch. You must always drain the batch — skipping the iteration leaves unread data in the connection's read buffer, which corrupts the protocol state. Close the results, return the first error. This is the right pattern for operations where the first failure logically invalidates the entire batch — if statement 1 creates a parent record and it fails, there is no point in processing statements 2 through 100 that reference it. But you still must consume the results.

In all three patterns, always call Close(). Always use defer. A batch that is not closed leaks the underlying pgx.BatchResults, which holds a reference to the connection's read buffer. The connection becomes unusable — not just for this batch, but for all subsequent queries on that connection. In a pooled environment, this means one leaked batch can poison an entire connection, causing cascading failures across unrelated requests.

I have seen production incidents caused by exactly this: a batch operation that returned early on error without closing the batch results. The connection returned to the pool in a corrupted state. The next request that drew that connection received garbled responses — bytes intended for the abandoned batch results, interpreted as the response to a completely different query. The error messages were bewildering. "unexpected message type" from pgx. Random scan errors. Data that appeared to come from the wrong table. All because of a missing defer results.Close().

Batch sizing: how many statements are too many?

A natural question arises once you begin batching: is there a limit? Can you send 10,000 statements in a single batch? 100,000? A million?

The practical answer is yes, you can, but you should not. Several constraints apply:

Memory. pgx builds the entire batch message in memory before sending it. Each queued statement adds the SQL text (or prepared statement name) plus the serialized parameters to an in-memory buffer. For 10,000 simple INSERTs with two integer parameters each, this is modest — perhaps 2-3 MB. For 10,000 SELECTs that each return 20 columns, the response buffer holding all results can be substantial. PostgreSQL also allocates memory for the batch on its side.

Timeouts. A batch executes as a single protocol exchange. If your statement_timeout or connection timeout is 30 seconds and the batch takes 35 seconds to execute, the entire batch fails. There is no partial progress — you do not get "the first 8,000 succeeded." The timeout applies to the aggregate execution time of all statements.

Lock contention. If your batch statements are all updating the same table, they acquire row-level locks. A batch of 10,000 UPDATEs on the same table holds all those locks simultaneously until the batch completes (or until the transaction commits, if wrapped). This can create contention with concurrent operations on the same rows.

The sweet spot for most workloads is 100 to 1,000 statements per batch. Below 100, the per-roundtrip overhead of the batch itself (building the message, parsing the response) makes the savings marginal. Above 1,000, you begin to see diminishing returns — the network savings are already captured, and you are adding memory pressure and lock duration.

For large workloads, chunk your input into batches of a fixed size.

// Batch size matters. Too small and you lose the benefit.
// Too large and you hit PostgreSQL memory limits or timeout.

const maxBatchSize = 500

func batchInsertWithChunking(
    ctx context.Context,
    q *db.Queries,
    events []db.RecordInventoryEventsParams,
) error {
    for start := 0; start < len(events); start += maxBatchSize {
        end := start + maxBatchSize
        if end > len(events) {
            end = len(events)
        }

        chunk := events[start:end]
        results := q.RecordInventoryEvents(ctx, chunk)

        var chunkErr error
        results.Exec(func(i int, err error) {
            if err != nil && chunkErr == nil {
                chunkErr = fmt.Errorf("event %d (global %d): %w",
                    i, start+i, err)
            }
        })
        results.Close()

        if chunkErr != nil {
            return chunkErr
        }
    }
    return nil
}

The chunking pattern is straightforward: iterate through your input in fixed-size windows, execute each chunk as a batch, check for errors, continue. Each batch gets its own roundtrip, but each roundtrip carries hundreds of statements instead of one. The total roundtrip count drops from N to N/500, which captures the vast majority of the latency savings.

I should note that the optimal batch size depends on your specific workload, network conditions, and PostgreSQL configuration. 500 is a reasonable default. If your statements are complex (joins, subqueries, CTEs), consider smaller batches — 50 to 100. If they are trivial (single-column INSERTs), you can go larger — 1,000 to 5,000. Profile in your environment. The right number is the one that balances network savings against memory and timeout constraints.

Mixed operations: combining different queries in one batch

sqlc's batch annotations generate separate batch methods for each annotated query. RecordInventoryEvents produces its own pgx.Batch. GetBookByISBN produces its own. Each method manages its own batch lifecycle. This is clean and type-safe, but it means you cannot combine an INSERT and a SELECT into a single roundtrip using the generated code alone.

-- queries/mixed.sql
-- Batch annotations work per-query, but you can combine them
-- in application code using pgx.Batch directly.

-- name: CreateBook :batchone
INSERT INTO books (author_id, title, isbn, price_cents)
VALUES ($1, $2, $3, $4)
RETURNING id, author_id, title, isbn, published, price_cents;

-- name: UpdateBookPrice :batchexec
UPDATE books SET price_cents = $2 WHERE id = $1;

-- name: DeleteBook :batchexec
DELETE FROM books WHERE id = $1;

When you need to combine different operations — create some books, update prices on others, delete a few — into a single roundtrip, you can compose a pgx.Batch manually. This is not a limitation of sqlc; it is a consequence of the type-safety guarantee. A generated batch method knows the exact SQL text and parameter types for its query. Mixing queries of different shapes requires dynamic dispatch, which is what the manual approach provides.

// When you need different operations in a single roundtrip,
// you can build a pgx.Batch manually. sqlc's generated batch
// methods each use their own batch internally, but nothing
// stops you from composing at the pgx level.

func syncCatalog(
    ctx context.Context,
    pool *pgxpool.Pool,
    toCreate []CreateBookParams,
    toUpdate []UpdatePriceParams,
    toDelete []int64,
) error {
    tx, err := pool.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx)

    batch := &pgx.Batch{}

    // Queue creates
    for _, c := range toCreate {
        batch.Queue(
            "INSERT INTO books (author_id, title, isbn, price_cents) VALUES ($1, $2, $3, $4)",
            c.AuthorID, c.Title, c.ISBN, c.PriceCents,
        )
    }

    // Queue updates
    for _, u := range toUpdate {
        batch.Queue(
            "UPDATE books SET price_cents = $2 WHERE id = $1",
            u.ID, u.PriceCents,
        )
    }

    // Queue deletes
    for _, id := range toDelete {
        batch.Queue("DELETE FROM books WHERE id = $1", id)
    }

    br := tx.SendBatch(ctx, batch)
    defer br.Close()

    total := len(toCreate) + len(toUpdate) + len(toDelete)
    for i := 0; i < total; i++ {
        if _, err := br.Exec(); err != nil {
            return fmt.Errorf("statement %d failed: %w", i, err)
        }
    }

    return tx.Commit(ctx)
}

The manual approach sacrifices sqlc's type safety — you are writing raw SQL strings and managing parameter positions yourself. Use it when the roundtrip savings justify it. For catalog sync operations that touch hundreds of records across multiple operations, one roundtrip instead of three is worth the ergonomic trade-off. For two queries where you save 0.5ms, use two separate sqlc batch calls.

A pragmatic middle ground: use sqlc-generated batches for homogeneous operations (all INSERTs, all SELECTs) and reserve manual batch composition for the rare cases where mixed operations in a single roundtrip are genuinely necessary.

Prepared statements and batch interactions

pgx's handling of prepared statements interacts with batch operations in a way that deserves attention, particularly for long-running services where the same batch queries execute thousands of times.

// pgx.Batch and prepared statements: what actually happens.
//
// By default, pgx uses the extended query protocol for each
// statement in the batch. This means each statement sends:
//   Parse -> Bind -> Execute
//
// If you use the same query text multiple times (which batch
// annotations do — every item in the batch uses identical SQL),
// pgx will automatically use a prepared statement after the
// first execution. The prepare_threshold config controls this.
//
// With prepared statements, subsequent batch items skip Parse:
//   Bind -> Execute  (using the already-parsed statement)
//
// This saves PostgreSQL parse time — meaningful for complex
// queries, negligible for simple INSERTs.
//
// In sqlc-generated batch code, every item has identical SQL,
// so pgx's automatic preparation kicks in after the first item.
// The first statement: Parse + Bind + Execute (full cost).
// Statements 2 through N: Bind + Execute (no parse overhead).

// You can control this in your pool config:
config, _ := pgxpool.ParseConfig(connString)
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeCacheDescribe
// Or disable prepared statements entirely:
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

By default, pgx uses a "describe cache" that automatically prepares statements after they have been executed a certain number of times (controlled by prepare_threshold, which defaults to 5). On the first execution, pgx sends the full SQL text via Parse. On subsequent executions, it reuses the prepared statement, sending only Bind and Execute — skipping the Parse step entirely.

Within a batch, every item has identical SQL text. For a batch of 1,000 INSERTs, the SQL string INSERT INTO inventory_events (book_id, delta, recorded) VALUES ($1, $2, NOW()) is repeated 1,000 times. pgx recognizes this and, after the first item hits the prepare threshold, reuses the prepared statement for all subsequent items. The first few items pay the Parse cost; the rest skip it.

This matters most for complex queries. A simple INSERT's Parse time is negligible — a few microseconds. But a SELECT with multiple JOINs, CTEs, or subqueries can take 1-5ms to parse. In a batch of 100 such queries, avoiding 99 redundant Parse operations saves 100-500ms. The prepared statement cache turns this from a repeated cost into a one-time cost per connection.

One caveat: if you are using PgBouncer in transaction pooling mode, prepared statements do not persist across transactions. Each time your batch acquires a connection, the statements must be prepared again. This is a known limitation of PgBouncer's architecture. If batch performance with prepared statements matters to you and you are using PgBouncer, consider session pooling mode or switching to pgcat, which supports prepared statement forwarding.

Testing batch operations

The callback-based API that sqlc generates for batch results creates a testing challenge. You cannot simply assert on a return value — the results arrive inside a callback, and the assertions must happen either inside the callback or after collecting results from it.

// Testing batch operations requires care.
// The callback pattern makes assertions less straightforward
// than a simple return value.

func TestRecordInventoryEvents(t *testing.T) {
    ctx := context.Background()
    pool := setupTestDB(t)  // your test helper
    q := db.New(pool)

    // Seed test data
    _, err := pool.Exec(ctx,
        "INSERT INTO authors (id, name, email) VALUES (1, 'Test', 'test@example.com')")
    require.NoError(t, err)
    _, err = pool.Exec(ctx,
        "INSERT INTO books (id, author_id, title, isbn) VALUES (1, 1, 'Book', '978-0-000')")
    require.NoError(t, err)

    events := []db.RecordInventoryEventsParams{
        {BookID: 1, Delta: 10},   // restock
        {BookID: 1, Delta: -3},   // sale
        {BookID: 1, Delta: -2},   // sale
    }

    results := q.RecordInventoryEvents(ctx, events)
    defer results.Close()

    var errors []error
    results.Exec(func(i int, err error) {
        errors = append(errors, err)
    })

    // Verify all succeeded
    for i, err := range errors {
        assert.NoError(t, err, "event %d should succeed", i)
    }

    // Verify net effect: 10 - 3 - 2 = 5
    var total int
    err = pool.QueryRow(ctx,
        "SELECT COALESCE(SUM(delta), 0) FROM inventory_events WHERE book_id = 1",
    ).Scan(&total)
    require.NoError(t, err)
    assert.Equal(t, 5, total)
}

func TestBatchWithInvalidFK(t *testing.T) {
    ctx := context.Background()
    pool := setupTestDB(t)
    q := db.New(pool)

    // Book ID 9999 does not exist — FK violation
    events := []db.RecordInventoryEventsParams{
        {BookID: 1, Delta: 5},      // valid
        {BookID: 9999, Delta: -1},   // invalid FK
        {BookID: 1, Delta: 3},       // valid
    }

    results := q.RecordInventoryEvents(ctx, events)
    defer results.Close()

    var errors []error
    results.Exec(func(i int, err error) {
        errors = append(errors, err)
    })

    // Without a transaction, individual statements fail independently
    assert.NoError(t, errors[0], "first event should succeed")
    assert.Error(t, errors[1], "second event should fail (FK violation)")
    assert.NoError(t, errors[2], "third event should succeed despite #2 failing")
}

The first test verifies the happy path: three inventory events, all valid, all inserting successfully. The pattern is to collect errors into a slice inside the callback, then assert on the slice after the callback completes. The second assertion — checking the net delta via a raw query — verifies that the batch actually wrote the expected data, not just that it reported no errors.

The second test is more revealing. It submits a batch with one invalid item (a foreign key violation for a non-existent book ID) sandwiched between two valid items. Without a transaction, the valid items succeed and the invalid one fails independently. This test documents a behavior that surprises many engineers and is essential to verify in your test suite.

A few testing practices I have found valuable for batch operations:

Test the error callback contract. Verify that the callback is called exactly len(input) times. If it is called fewer times, results were dropped. If more, something is very wrong.

Test partial failures without transactions. Submit a batch where some items will fail (constraint violations, invalid FKs) and verify that the successful items persisted. This documents the non-transactional behavior explicitly.

Test partial failures with transactions. Wrap the same batch in a transaction, verify that the failure rolls back everything, and confirm no data was written.

Test empty batches. Submit a batch with zero items. The generated code handles this correctly — SendBatch sends an empty batch, and the iteration loop executes zero times. But it is worth confirming, because edge cases in callback-based APIs are easy to overlook.

Test Close() behavior. Verify that calling Close() before iterating all results does not panic or corrupt the connection. pgx handles this gracefully, but your wrapper code might not.

The honest limits of batch annotations

I have spent considerable time explaining why batch annotations are valuable. Allow me to balance the account with their genuine limitations.

pgx only. Batch annotations are not available with database/sql. If your codebase uses the standard library interface — perhaps for portability across databases, perhaps because other libraries in your stack require it — you cannot use batch annotations without migrating to pgx. This is not a trivial migration for large codebases. pgx's API is different from database/sql in meaningful ways: connection management, context handling, type scanning, and error types all differ.

PostgreSQL only. sqlc supports MySQL and SQLite in addition to PostgreSQL, but batch annotations only work with PostgreSQL. The pipeline protocol that makes batching efficient is a PostgreSQL-specific feature. If you are targeting multiple databases, batch annotations are not available to you.

Callback ergonomics. The callback-based API is less ergonomic than a simple "return a slice" interface. You must handle the callback, manage error collection, and remember to close the batch. Generics in Go have improved this somewhat — community libraries like pgx-batch-results provide helper functions — but the core API remains callback-driven. For simple use cases, the ceremony can feel disproportionate to the benefit.

No conditional logic. Each item in a batch is independent. You cannot say "if statement 5 returned X, execute statement 6 with Y." If your workflow requires conditional branching based on intermediate results, a batch cannot express it. You need separate roundtrips, or you need to move the logic into a PostgreSQL function.

Debugging complexity. When a batch fails, the error includes the index but not the parameters. In a batch of 1,000 items, "statement 847 failed: unique_violation" tells you which statement but not what data caused the violation. Your error handling code must map the index back to the input parameters, which the examples in this article do. But if your error handling is sloppy — just logging the error without the index or the params — debugging batch failures becomes an exercise in frustration.

Monitoring blind spots. PostgreSQL's pg_stat_statements extension tracks query statistics. A batch of 1,000 identical INSERTs shows up as one entry with 1,000 calls. This is correct and useful. But a manual mixed batch — 500 INSERTs, 300 UPDATEs, 200 DELETEs — shows up as three separate pg_stat_statements entries with no indication that they were executed together. There is no "batch" concept in PostgreSQL's monitoring. This can make it harder to correlate application-level batch operations with database-level metrics.

These are genuine constraints. For straightforward Go services that use PostgreSQL exclusively via pgx, none of them are disqualifying. For complex, multi-database, standard-library-dependent codebases, some of them may be.

sqlc and Gold Lapel: clean SQL that optimizes itself

I have one remaining observation, and it concerns why sqlc's approach to SQL generation is particularly well-suited to proxy-level optimization.

ORMs generate SQL dynamically. The same logical query can produce different SQL text depending on which fields are selected, which eager-loading strategy is active, which query builder methods were chained, or what version of the ORM is installed. A database proxy that optimizes by recognizing query patterns must first normalize these variations — strip whitespace, canonicalize aliases, identify structural equivalence. This is solvable but expensive, and it introduces a window of uncertainty: is this a new query pattern, or a variant of one the proxy has seen before?

sqlc generates SQL at compile time. Each named query produces identical SQL text on every execution. GetBookByISBN always sends exactly SELECT id, author_id, title, isbn, published, price_cents FROM books WHERE isbn = $1. Every time. The text is the fingerprint. No normalization needed. No ambiguity.

// Gold Lapel + sqlc: the optimization pairing that makes sense.
//
// sqlc generates SQL that is:
//   1. Predictable — same query text every time, no ORM surprises
//   2. Clean — no unnecessary subqueries, no SELECT *, no cartesian joins
//   3. Parameterized — $1, $2 placeholders, never string interpolation
//   4. Visible — every query lives in a .sql file you can read and review
//
// This is exactly the kind of SQL that Gold Lapel optimizes best.
//
// Gold Lapel's pattern detection works by fingerprinting queries.
// sqlc's output is already fingerprinted by design — each named
// query produces identical SQL text on every execution. The proxy
// recognizes the pattern instantly, skipping the normalization
// step that ORMs require.
//
// The auto-indexing engine sees:
//   "WHERE isbn = $1" executed 10,000 times/hour → creates the index
//   "WHERE author_id = $1 ORDER BY published DESC" → composite index
//   "WHERE book_id = $1" in the batch INSERT → confirms FK index exists
//
// Query rewriting is straightforward because the SQL is already clean.
// No need to untangle ORM-generated subqueries or redundant JOINs.
//
// Connection string change. That is the entire integration:
connString := "postgresql://user:pass@goldlapel-proxy:5433/mydb"
pool, _ := pgxpool.New(ctx, connString)
q := db.New(pool)
// Every sqlc query now flows through Gold Lapel. Done.

Gold Lapel sits between your application and PostgreSQL as a transparent proxy. It observes query traffic, identifies patterns, and optimizes automatically — creating indexes for frequently-filtered columns, rewriting inefficient query patterns, managing materialized views for expensive repeated aggregations.

With sqlc, every query is already a clean, stable pattern. The proxy's auto-indexing engine sees WHERE isbn = $1 executed 10,000 times per hour and creates the index. It sees WHERE author_id = $1 ORDER BY published DESC in the batch query and creates a composite index that serves both the filter and the sort. No guesswork. No normalization overhead. The SQL that sqlc generates is exactly the SQL that a human DBA would write and a proxy can optimize.

Batch operations add another dimension. When Gold Lapel observes 100 identical statements arriving in a single batch, it confirms its pattern detection with 100 data points in one observation window. Index recommendations converge faster. Workload profiles are more accurate. The batch is not just faster on the wire — it gives the optimization layer more signal per roundtrip.

The integration is a go get. Install the Gold Lapel Go wrapper, call goldlapel.Start(), and your sqlc queries — batch and otherwise — flow through Gold Lapel automatically. The SQL does not change. The generated code does not change. The queries get faster over time as the proxy learns your workload.

A summary of which annotation serves which purpose

For reference, as you decide which annotation to reach for:

:batchexec — statements that modify data and return no rows. INSERT (without RETURNING), UPDATE, DELETE. The callback gives you func(index int, err error). Use it for bulk writes where you need per-statement error reporting.

:batchone — queries that return exactly one row per input. Primary key lookups, unique constraint lookups, aggregate queries that always produce a single result. The callback gives you func(index int, row T, err error). Use it when you have N identifiers and need to resolve each one.

:batchmany — queries that return zero or more rows per input. Parent-child lookups, filtered searches per entity, any query where the result set size varies. The callback gives you func(index int, rows []T, err error). Use it to solve N+1 problems without JOINs.

All three annotations produce the same underlying pgx.Batch mechanism. The difference is in the generated callback signature, which reflects how many rows each statement is expected to produce. Choose based on your query's cardinality, not on the operation type.

Write your SQL. Annotate it. Let sqlc generate the code. Let the infrastructure handle the rest. That is, after all, how these things ought to work.