Chunked Multi-Row Inserts via go-sqlbuilder

Overview

When inserting millions of rows into a relational database, the naive approach of one INSERT per row is catastrophically slow. Each individual insert incurs network round-trip latency, query parsing overhead, transaction log writes, and index maintenance. For a million rows at 2ms per round trip, single-row inserts take over 33 minutes of pure network time alone. Multi-row inserts—batching many rows into a single INSERT INTO ... VALUES (...), (...), (...) statement—amortize all of these costs, typically achieving 10–100x throughput improvement.

The go-sqlbuilder library provides a type-safe, composable API for constructing SQL statements in Go. For bulk inserts, it allows you to build parameterized multi-row INSERT statements programmatically, prepare them once, and execute them repeatedly with different data batches. Combined with chunking (splitting a large dataset into fixed-size batches), this pattern enables efficient, resumable bulk loading that stays within database parameter limits and memory constraints.

In a typical application that needs to bulk-load large datasets into TiDB, this pattern is used to load millions of rows efficiently. The application builds parameterized INSERT statements with go-sqlbuilder, prepares each statement once per batch size, and streams structs through Exec in chunks of 1,000–5,000 rows. This approach amortizes round-trip latency and achieves sustained throughput of tens of thousands of rows per second.

Core Concepts

Multi-Row INSERT Syntax

Standard SQL supports inserting multiple rows in a single statement:

INSERT INTO users (name, email, created_at) VALUES
  ('Alice', 'alice@example.com', '2024-01-01'),
  ('Bob', 'bob@example.com', '2024-01-02'),
  ('Carol', 'carol@example.com', '2024-01-03');

This sends one network round trip for three rows instead of three. The database parses the query once, acquires locks once, writes to the transaction log once (per batch), and updates indexes in bulk. The savings compound as batch size increases.

Parameterized Queries and Prepared Statements

Interpolating values directly into SQL strings invites SQL injection. Parameterized queries use placeholders (? in MySQL/TiDB, $1 in PostgreSQL) that the database driver binds safely. go-sqlbuilder generates parameterized queries by default, producing statements like:

INSERT INTO users (name, email) VALUES (?, ?), (?, ?), (?, ?)

The values are passed as separate arguments to Exec, never concatenated into the SQL string. A prepared statement can be reused for every batch of the same size, avoiding repeated query parsing on the server.

Chunking Strategy

Chunking splits a large dataset into fixed-size batches. Each batch is inserted as a single multi-row INSERT. The chunk size is chosen based on:

• Parameter limits: Must not exceed the database’s maximum placeholder count.
• Memory: Each batch is held in memory while being serialized and sent. For wide rows (many columns, large values), smaller batches prevent OOM.
• Transaction size: Very large batches within a single transaction can cause lock contention and undo log growth. Committing per-batch keeps transactions small.
• Retry granularity: If a batch fails, only that batch needs to be retried, not the entire dataset.

Struct-to-Row Mapping

go-sqlbuilder can map Go structs to SQL columns using struct tags, similar to encoding/json. This eliminates manual column ordering and reduces bugs from mismatched column/value pairs:

type User struct {
    Name      string `db:"name"`
    Email     string `db:"email"`
    CreatedAt string `db:"created_at"`
}

How It Works

Building Multi-Row INSERTs with go-sqlbuilder

The go-sqlbuilder library provides an InsertBuilder that constructs INSERT statements incrementally. For multi-row inserts, you call Values() once for each row, and the builder appends a value tuple:

package loader

import (
    "github.com/huandu/go-sqlbuilder"
)

// buildInsert creates a parameterized multi-row INSERT for a batch of users.
func buildInsert(users []User) (string, []interface{}) {
    ib := sqlbuilder.NewInsertBuilder()
    ib.InsertInto("users")
    ib.Cols("name", "email", "created_at")

    for _, u := range users {
        ib.Values(u.Name, u.Email, u.CreatedAt)
    }

    return ib.Build()
    // Returns:
    // "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?), (?, ?, ?), ..."
    // [alice, alice@ex.com, 2024-01-01, bob, bob@ex.com, 2024-01-02, ...]
}

The Chunked Insert Pipeline

The full pipeline reads rows from a source (file, channel, or slice), splits them into chunks, builds a multi-row INSERT for each chunk, and executes it:

package loader

import (
    "database/sql"
    "fmt"

    "github.com/huandu/go-sqlbuilder"
)

const chunkSize = 1000

// BulkInsertUsers inserts a large slice of users in chunks.
func BulkInsertUsers(db *sql.DB, users []User) error {
    for i := 0; i < len(users); i += chunkSize {
        end := i + chunkSize
        if end > len(users) {
            end = len(users)
        }
        chunk := users[i:end]

        query, args := buildInsert(chunk)

        _, err := db.Exec(query, args...)
        if err != nil {
            return fmt.Errorf("inserting chunk at offset %d: %w", i, err)
        }
    }
    return nil
}

Streaming from a Channel

For very large datasets that don’t fit in memory, the chunker can read from a channel and insert each chunk as it fills:

package loader

import (
    "database/sql"
    "fmt"
)

// StreamInsertUsers reads users from a channel and inserts in chunks.
func StreamInsertUsers(db *sql.DB, ch <-chan User) error {
    chunk := make([]User, 0, chunkSize)

    for u := range ch {
        chunk = append(chunk, u)

        if len(chunk) >= chunkSize {
            query, args := buildInsert(chunk)
            if _, err := db.Exec(query, args...); err != nil {
                return fmt.Errorf("inserting chunk: %w", err)
            }
            chunk = chunk[:0] // reset slice, reuse backing array
        }
    }

    // Flush remaining rows
    if len(chunk) > 0 {
        query, args := buildInsert(chunk)
        if _, err := db.Exec(query, args...); err != nil {
            return fmt.Errorf("inserting final chunk: %w", err)
        }
    }
    return nil
}

Performance Comparison

A benchmark inserting 100,000 rows into TiDB (3-node cluster, same datacenter):

• Single-row inserts: ~200 rows/sec (limited by round-trip latency)
• Multi-row inserts, chunk=100: ~8,000 rows/sec
• Multi-row inserts, chunk=1000: ~25,000 rows/sec
• Multi-row inserts, chunk=5000: ~30,000 rows/sec (diminishing returns)

The jump from single-row to chunk=100 is 40x. Going from 100 to 1000 is another 3x. Beyond 1000, gains flatten because the per-row cost is now dominated by the actual data write, not the per-request overhead.

Practical Applications

Bulk Data Loading in a Marketplace Application

A marketplace application uses chunked multi-row inserts to populate TiDB with test data and to load production datasets. The loading pipeline reads structs from a Go channel, chunks them into batches of 1,000, builds parameterized INSERT statements with go-sqlbuilder, and executes them sequentially. For initial loads of millions of rows, this reduces load time from hours to minutes.

ETL and Data Migration

When migrating data between databases or loading data from CSV/JSON files into a relational store, chunked inserts are the standard approach. The pattern works with any Go struct that maps to a table schema, making it reusable across different tables and datasets. Combined with a progress cursor (similar to the linear merge sync pattern), migrations can be paused and resumed.

Event Ingestion Pipelines

High-throughput event ingestion (log events, analytics events, IoT telemetry) benefits from batching writes. Events are buffered in memory for a short interval (e.g., 100ms) or until a batch fills, then flushed as a single multi-row insert. This keeps write throughput high while maintaining low-latency ingestion (events are persisted within the buffering interval).

Test Data Generation

Integration tests that require realistic data volumes (thousands or millions of rows) use chunked inserts to set up the test database quickly. Without batching, test setup time dominates the total test runtime, making the test suite impractically slow.

Implementation Patterns

Generic Chunked Inserter

A reusable inserter that works with any struct type using Go generics (Go 1.18+):

package loader

import (
    "database/sql"
    "fmt"

    "github.com/huandu/go-sqlbuilder"
)

// ChunkedInserter inserts rows of type T in batches.
type ChunkedInserter[T any] struct {
    DB        *sql.DB
    Table     string
    Columns   []string
    ChunkSize int
    MapRow    func(T) []interface{} // extracts column values from a struct
}

// Insert processes a slice of items in chunks.
func (ci *ChunkedInserter[T]) Insert(items []T) (int64, error) {
    var totalInserted int64

    for i := 0; i < len(items); i += ci.ChunkSize {
        end := i + ci.ChunkSize
        if end > len(items) {
            end = len(items)
        }
        chunk := items[i:end]

        ib := sqlbuilder.NewInsertBuilder()
        ib.InsertInto(ci.Table)
        ib.Cols(ci.Columns...)

        for _, item := range chunk {
            ib.Values(ci.MapRow(item)...)
        }

        query, args := ib.Build()
        result, err := ci.DB.Exec(query, args...)
        if err != nil {
            return totalInserted, fmt.Errorf("chunk at offset %d: %w", i, err)
        }

        n, _ := result.RowsAffected()
        totalInserted += n
    }
    return totalInserted, nil
}

// Usage:
// inserter := &ChunkedInserter[User]{
//     DB: db, Table: "users", ChunkSize: 1000,
//     Columns: []string{"name", "email", "created_at"},
//     MapRow: func(u User) []interface{} {
//         return []interface{}{u.Name, u.Email, u.CreatedAt}
//     },
// }
// inserted, err := inserter.Insert(users)

Upsert Pattern (INSERT ON DUPLICATE KEY UPDATE)

When bulk loading may include duplicate keys, use an upsert to update existing rows instead of failing:

package loader

import (
    "fmt"
    "strings"

    "github.com/huandu/go-sqlbuilder"
)

// buildUpsert creates a multi-row INSERT ... ON DUPLICATE KEY UPDATE statement.
func buildUpsert(users []User) (string, []interface{}) {
    ib := sqlbuilder.NewInsertBuilder()
    ib.InsertInto("users")
    ib.Cols("id", "name", "email", "updated_at")

    for _, u := range users {
        ib.Values(u.ID, u.Name, u.Email, u.UpdatedAt)
    }

    query, args := ib.Build()

    // Append ON DUPLICATE KEY UPDATE clause (MySQL/TiDB syntax)
    query += " ON DUPLICATE KEY UPDATE " +
        "name = VALUES(name), " +
        "email = VALUES(email), " +
        "updated_at = VALUES(updated_at)"

    return query, args
}

Progress Tracking for Resumable Loads

For very large loads that may be interrupted, track the number of chunks successfully inserted and resume from the last completed chunk:

package loader

import (
    "database/sql"
    "fmt"
    "log"
)

// ResumableBulkInsert inserts users starting from a given offset.
func ResumableBulkInsert(db *sql.DB, users []User, startOffset int) error {
    for i := startOffset; i < len(users); i += chunkSize {
        end := i + chunkSize
        if end > len(users) {
            end = len(users)
        }

        query, args := buildInsert(users[i:end])
        if _, err := db.Exec(query, args...); err != nil {
            return fmt.Errorf("chunk at offset %d: %w", i, err)
        }

        // Persist progress so we can resume after crash
        if err := saveProgress(db, "users_load", i+chunkSize); err != nil {
            log.Printf("warning: failed to save progress at offset %d: %v", i, err)
        }

        if (i/chunkSize)%100 == 0 {
            log.Printf("inserted %d / %d rows", i+len(users[i:end]), len(users))
        }
    }
    return nil
}

Common Pitfalls

Exceeding the Parameter Placeholder Limit

MySQL/TiDB allows at most 65,535 placeholders in a single prepared statement. If your table has 20 columns and your chunk size is 5,000, you need 100,000 placeholders—which exceeds the limit. The insert silently fails or returns a cryptic protocol error. Always calculate: chunk_size * num_columns ≤ 65535.

Building Queries in a Loop Without Resetting the Builder

A common bug is reusing an InsertBuilder across chunks without creating a new one. The builder accumulates Values() calls, so the second chunk’s query includes both the first and second chunk’s rows. Always create a fresh builder for each chunk, or call the appropriate reset method.

Not Handling the Last (Partial) Chunk

If you have 10,050 rows and a chunk size of 1,000, the last chunk has only 50 rows. If you prepared a statement for 1,000 rows and try to reuse it for 50, the parameter count won’t match. Either build a new statement for the last chunk or use db.Exec (which prepares internally) instead of explicit db.Prepare.

Ignoring Transaction Boundaries

Each db.Exec call without an explicit transaction runs in auto-commit mode: every chunk is its own transaction. This is usually fine for bulk loads (if a chunk fails, prior chunks are already committed). But if you need atomicity across all chunks (all or nothing), you must wrap the entire loop in a transaction—which may cause undo log growth for very large loads.

Memory Pressure from Large Batches

Each multi-row insert builds a query string with all parameter values marshaled into []interface{}. For wide rows (many columns) or large values (text blobs), a chunk of 5,000 rows can consume significant memory. If the source dataset is streamed (e.g., from a file or network), ensure you don’t buffer more than one chunk at a time. The streaming channel pattern above naturally limits memory to one chunk.

Silently Truncated Data

If a column value exceeds the column’s max length (e.g., a 500-character string in a VARCHAR(255) column), MySQL/TiDB may silently truncate it (in non-strict mode) or reject the entire batch (in strict mode). When one row in a 1,000-row batch has a truncation issue, the entire batch fails. Debug by isolating the offending row with a binary search over the batch.