Database Performance in Go

Optimizing database interactions in Go — connection pooling, prepared statements, batch operations, pgx vs database/sql, ORM overhead, Redis pipelining, and query performance patterns.

Introduction

Database interactions are frequently a bottleneck in Go applications. While Go's database/sql package provides a stable, standardized interface, it often leaves substantial performance on the table. This deep dive covers connection pooling mechanics, prepared statement optimization, pgx's performance advantages, batch operations, ORM overhead analysis, and Redis pipelining patterns that can yield 50-200% throughput improvements with proper tuning.

Connection Pool Tuning

The database/sql package manages connections through a built-in pool, but the defaults are conservative and often suboptimal.

Understanding the Defaults

// database/sql defaults
MaxOpenConns: unlimited (dangerous!)
MaxIdleConns: 2
ConnMaxLifetime: 0 (no limit)
ConnMaxIdleTime: 0 (no limit)

These defaults create three critical problems:

Unlimited Open Connections: Under load, your application can exhaust database connection limits and system file descriptors
Minimal Idle Connections: With only 2 idle connections, every spike requires opening new connections (TCP handshake + auth overhead)
No Connection Recycling: Long-lived connections accumulate memory leaks and protocol state corruption

Calculating Optimal Pool Size

Connection pool sizing requires understanding your workload. The formula depends on three variables:

PoolSize = (Number of CPU Cores) × 2-4 + (Expected Pending Requests)

More precisely, using Little's Law for queueing systems:

L = λ × W

Where:

L = average connections in use
λ = request arrival rate (queries/sec)
W = average query latency (seconds)

For example, with 8 cores, 1000 queries/second, and 50ms average latency:

Connections in Use = 1000 × 0.05 = 50
Optimal Pool Size = 50 + buffer = 60-80

Connection Pool Configuration

db, err := sql.Open("postgres", connString)
if err != nil {
	log.Fatal(err)
}

// CPU-based sizing
numCores := runtime.NumCPU()
maxOpenConns := numCores * 4 // 4x CPU count for I/O bound workload
maxIdleConns := numCores * 2  // Keep half for idle

db.SetMaxOpenConns(maxOpenConns)
db.SetMaxIdleConns(maxIdleConns)
db.SetConnMaxLifetime(5 * time.Minute)      // Recycle after 5 minutes
db.SetConnMaxIdleTime(2 * time.Minute)      // Close idle connections after 2 minutes

ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
defer cancel()

if err := db.PingContext(ctx); err != nil {
	log.Fatal(err)
}

Connection Pool Lifecycle Diagram

┌─────────────────────────────────────────────────────┐
│           Connection Pool State Machine              │
├─────────────────────────────────────────────────────┤
│                                                      │
│   ┌──────────────┐                                   │
│   │   Idle Pool  │                                   │
│   │ (MaxIdle=4)  │◄────────────┐                     │
│   └──────────────┘             │                     │
│          ▲                      │ Query completes,   │
│          │                      │ return to idle     │
│          │ No idle available    │                     │
│          │ Open new if count<   │                     │
│          │ MaxOpenConns         │                     │
│          │                      │                     │
│   ┌──────────────┐         ┌────────────┐            │
│   │ Create Conn  │◄────────┤ Query Wait │            │
│   │   (TCP auth) │ Get idle │  Queue    │            │
│   └──────────────┘         └────────────┘            │
│          │                      ▲                     │
│          └──────────────────────┘                     │
│                                                      │
│   ┌──────────────────────────────────────────┐       │
│   │ ConnMaxIdleTime expired?                 │       │
│   │ Delete connection, decrement idle count  │       │
│   └──────────────────────────────────────────┘       │
│                                                      │
└─────────────────────────────────────────────────────┘

Monitoring Connection Pool Health

type PoolStats struct {
	OpenConnections  int
	InUseConnections int
	IdleConnections  int
	WaitCount        int64
	WaitDuration     time.Duration
	MaxIdleClosed    int64
	MaxLifetimeClosed int64
}

func monitorConnectionPool(db *sql.DB, interval time.Duration) {
	ticker := time.NewTicker(interval)
	defer ticker.Stop()

	for range ticker.C {
		stats := db.Stats()

		// Alert if connections trending toward max
		if stats.OpenConnections > (stats.MaxOpenConnections * 80 / 100) {
			log.Printf("WARNING: Pool at 80%% capacity: %d/%d",
				stats.OpenConnections, stats.MaxOpenConnections)
		}

		// Track queue buildup
		if stats.WaitCount > 0 {
			log.Printf("Connection wait events: %d (avg: %v)",
				stats.WaitCount,
				time.Duration(stats.WaitDuration.Nanoseconds()/stats.WaitCount))
		}

		// Monitor closed connections
		log.Printf("Pool stats: Open=%d InUse=%d Idle=%d MaxLifetimeClosed=%d",
			stats.OpenConnections,
			stats.OpenConnections-stats.IdleConnections,
			stats.IdleConnections,
			stats.MaxLifetimeClosed)
	}
}

// Use in main
go monitorConnectionPool(db, 30*time.Second)

Prepared Statements

Prepared statements provide two performance benefits: query parsing bypass and attack prevention.

Performance Mechanics

// Without prepared statement
// 1. Parse SQL: 1-5% overhead
// 2. Validate: 2-8% overhead
// 3. Optimize: 5-15% overhead
// 4. Compile: 2-5% overhead
// 5. Execute: dominant

// With prepared statement
// Steps 1-4 done once
// Execute called multiple times

// Benchmark: 100 parameterized queries
// Without prepare: 15-25ms
// With prepare: 2-4ms per batch (10x improvement)

Implementation Pattern

// Create prepared statement once during initialization
stmt, err := db.PrepareContext(ctx,
	`SELECT id, name, email FROM users WHERE id = $1`)
if err != nil {
	log.Fatal(err)
}
defer stmt.Close()

// Reuse for multiple queries
for userID := 1; userID <= 1000; userID++ {
	var id int
	var name, email string

	err := stmt.QueryRowContext(ctx, userID).Scan(&id, &name, &email)
	if err != nil {
		log.Printf("Error querying user %d: %v", userID, err)
	}
}

Connection Pool Interaction Pitfall

A critical issue: prepared statements are connection-specific in database/sql. When a pooled connection returns to idle, the prepared statement becomes invalid.

// DANGEROUS: Prepared statement becomes invalid
stmt, err := db.Prepare(`SELECT * FROM users WHERE id = $1`)
// stmt is bound to connection A

// Pool may return connection A to idle pool
// Another goroutine gets connection B from pool
// Both think they have the prepared statement!
// Result: "prepared statement does not exist" errors

// CORRECT: Use db.PrepareContext instead of connection.Prepare
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

stmt, err := db.PrepareContext(ctx,
	`SELECT * FROM users WHERE id = $1`)
// database/sql handles statement pooling internally

pgx vs database/sql

pgx provides a PostgreSQL-native driver with significant performance advantages.

Performance Comparison: pgx Native vs database/sql

// Benchmark setup
const queryCount = 10000
var testID = 42

// database/sql approach
func benchmarkDatabaseSQL(b *testing.B) {
	db, _ := sql.Open("postgres", "postgres://user:pass@localhost/testdb")
	defer db.Close()

	b.ResetTimer()
	for i := 0; i < b.N; i++ {
		var id int
		var name string
		err := db.QueryRow(`SELECT id, name FROM users WHERE id = $1`, testID).
			Scan(&id, &name)
		if err != nil {
			b.Fatal(err)
		}
	}
}

// pgx native approach
func benchmarkPgxNative(b *testing.B) {
	ctx := context.Background()
	conn, _ := pgx.Connect(ctx, "postgres://user:pass@localhost/testdb")
	defer conn.Close(ctx)

	b.ResetTimer()
	for i := 0; i < b.N; i++ {
		var id int
		var name string
		err := conn.QueryRow(ctx,
			`SELECT id, name FROM users WHERE id = $1`, testID).
			Scan(&id, &name)
		if err != nil {
			b.Fatal(err)
		}
	}
}

// Results (typical):
// BenchmarkDatabaseSQL-8:  ~8,500 ops/sec
// BenchmarkPgxNative-8:    ~11,200 ops/sec (31% faster)

Key pgx Advantages

1. Binary Protocol: pgx sends/receives binary data, avoiding text parsing overhead.

// database/sql (text protocol)
// Integer 42 sent as: "42" (3 bytes)
// Parsed with strconv.ParseInt -> reflection overhead

// pgx (binary protocol)
// Integer 42 sent as: \x00\x2a (2 bytes, little-endian)
// Direct memory copy, zero parsing

2. COPY Protocol: Bulk insert via streaming binary data.

// Bulk insert 100k rows
rows := make([][]interface{}, 100000)
for i := 0; i < 100000; i++ {
	rows[i] = []interface{}{i, fmt.Sprintf("user_%d", i), "[email protected]"}
}

// database/sql: ~50 seconds (multi-row INSERT)
// INSERT INTO users (id, name, email) VALUES ($1,$2,$3),($4,$5,$6),...

// pgx COPY protocol: ~2 seconds
tag, err := conn.CopyFromRows(rows).Send(ctx)
if err != nil {
	log.Fatal(err)
}
log.Printf("Copied %d rows", tag.RowsAffected())

3. Batch Queries: Multiple statements in single roundtrip.

ctx := context.Background()

batch := &pgx.Batch{}
batch.Queue(`SELECT COUNT(*) FROM users`)
batch.Queue(`SELECT COUNT(*) FROM products`)
batch.Queue(`SELECT AVG(price) FROM products`)

results := conn.SendBatch(ctx, batch)
defer results.Close()

var userCount int
err := results.QueryRow().Scan(&userCount)

var productCount int
err = results.QueryRow().Scan(&productCount)

var avgPrice float64
err = results.QueryRow().Scan(&avgPrice)

// All three queries in single roundtrip (~1ms)
// vs database/sql: 3 separate roundtrips (~3-5ms)

database/sql Wrapper Mode

pgx offers pgx/v5/stdlib for drop-in database/sql compatibility, but with caveats:

// Using pgx as database/sql driver
import (
	"github.com/jackc/pgx/v5/stdlib"
)

db := sql.OpenDB(stdlib.OpenDB(
	config.pgxConfig{
		// pgx configuration
	}))

// Benefits: Some pgx advantages
// Drawbacks: Still limited by database/sql interface
// - No COPY protocol access
// - No batch query interface
// - Context handling less optimal

Recommendation: Use pgx directly for new code; database/sql wrapper for legacy codebases.

Batch Operations

Reducing roundtrips is critical for database performance.

INSERT Batching: Multi-row vs COPY

// Setup: 50,000 user records

// Strategy 1: Single INSERT with multiple rows
// Time: ~8 seconds for 50k rows
func insertMultiRow(ctx context.Context, db *sql.DB) error {
	query := `INSERT INTO users (name, email) VALUES `
	args := []interface{}{}

	for i := 0; i < 50000; i++ {
		if i > 0 {
			query += ","
		}
		query += fmt.Sprintf("($%d,$%d)", i*2+1, i*2+2)
		args = append(args, fmt.Sprintf("user_%d", i), fmt.Sprintf("user%d@example.com", i))
	}

	_, err := db.ExecContext(ctx, query, args...)
	return err
}

// Strategy 2: pgx COPY protocol
// Time: ~0.3 seconds for 50k rows (27x faster!)
func insertCOPY(ctx context.Context, conn *pgx.Conn) error {
	type User struct {
		name  string
		email string
	}

	rows := make([][]interface{}, 50000)
	for i := 0; i < 50000; i++ {
		rows[i] = []interface{}{
			fmt.Sprintf("user_%d", i),
			fmt.Sprintf("user%d@example.com", i),
		}
	}

	tag, err := conn.CopyFromRows(rows).
		Insert(ctx, "users", "name", "email").
		Exec(ctx)
	if err != nil {
		return err
	}

	log.Printf("Inserted %d rows", tag.RowsAffected())
	return nil
}

// Strategy 3: Batch inserts with smaller chunks
// Time: ~1.2 seconds for 50k rows
func insertBatchChunked(ctx context.Context, db *sql.DB, chunkSize int) error {
	stmt, err := db.PrepareContext(ctx,
		`INSERT INTO users (name, email) VALUES ($1, $2)`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	for i := 0; i < 50000; i += chunkSize {
		end := i + chunkSize
		if end > 50000 {
			end = 50000
		}

		// Execute chunk
		for j := i; j < end; j++ {
			_, err := stmt.ExecContext(ctx,
				fmt.Sprintf("user_%d", j),
				fmt.Sprintf("user%d@example.com", j))
			if err != nil {
				return err
			}
		}
	}

	return nil
}

pgx Batch Queries

// Multiple independent queries in single roundtrip
ctx := context.Background()
conn, _ := pgx.Connect(ctx, "postgres://...")

// Without batch: 3 roundtrips (~3-5ms)
var totalUsers int
conn.QueryRow(ctx, `SELECT COUNT(*) FROM users`).Scan(&totalUsers)

var totalProducts int
conn.QueryRow(ctx, `SELECT COUNT(*) FROM products`).Scan(&totalProducts)

var avgPrice float64
conn.QueryRow(ctx, `SELECT AVG(price) FROM products`).Scan(&avgPrice)

// With batch: 1 roundtrip (~1ms)
batch := &pgx.Batch{}
batch.Queue(`SELECT COUNT(*) FROM users`)
batch.Queue(`SELECT COUNT(*) FROM products`)
batch.Queue(`SELECT AVG(price) FROM products`)

results := conn.SendBatch(ctx, batch)

results.QueryRow().Scan(&totalUsers)
results.QueryRow().Scan(&totalProducts)
results.QueryRow().Scan(&avgPrice)

results.Close()

Bulk Read Patterns

For reading large datasets, query patterns matter significantly.

// Strategy 1: WHERE IN (expensive, limited by query length)
// Time: ~200ms for 10k IDs
func readWhereIn(ctx context.Context, db *sql.DB, ids []int) error {
	placeholders := make([]string, len(ids))
	args := make([]interface{}, len(ids))

	for i, id := range ids {
		placeholders[i] = fmt.Sprintf("$%d", i+1)
		args[i] = id
	}

	query := `SELECT id, name FROM users WHERE id IN (` +
		strings.Join(placeholders, ",") + `)`

	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return err
	}
	defer rows.Close()

	// Process rows...
	return rows.Err()
}

// Strategy 2: Temporary table (for large ID sets > 1000)
// Time: ~80ms for 10k IDs
func readViaTempTable(ctx context.Context, conn *pgx.Conn, ids []int) error {
	// Create temp table
	_, err := conn.Exec(ctx, `CREATE TEMP TABLE temp_ids (id INT)`)
	if err != nil {
		return err
	}
	defer conn.Exec(ctx, `DROP TABLE temp_ids`)

	// Bulk insert IDs
	tag, err := conn.CopyFromSlice(ctx,
		"temp_ids", []string{"id"},
		pgx.CopyFromRows(ids))
	if err != nil {
		return err
	}
	log.Printf("Copied %d IDs", tag)

	// Join on temp table
	rows, err := conn.Query(ctx, `
		SELECT u.id, u.name FROM users u
		JOIN temp_ids t ON u.id = t.id`)
	if err != nil {
		return err
	}
	defer rows.Close()

	// Process rows...
	return rows.Err()
}

// Strategy 3: UNNEST array (PostgreSQL-specific, fastest)
// Time: ~30ms for 10k IDs
func readViaUnnest(ctx context.Context, conn *pgx.Conn, ids []int) error {
	rows, err := conn.Query(ctx, `
		SELECT u.id, u.name FROM users u
		WHERE u.id = ANY($1)`, ids)
	if err != nil {
		return err
	}
	defer rows.Close()

	// Process rows...
	return rows.Err()
}

ORM Overhead Analysis

Object-relational mappers provide convenience but exact performance costs in exchanges for abstraction.

GORM vs sqlx vs Raw SQL Benchmarks

// Test entity
type User struct {
	ID    int
	Name  string
	Email string
	Age   int
}

// Benchmark: Select 10,000 rows

// 1. Raw database/sql
func benchmarkRawSQL(b *testing.B) {
	db, _ := sql.Open("postgres", connStr)
	defer db.Close()

	b.ResetTimer()
	for i := 0; i < b.N; i++ {
		rows, _ := db.Query(`SELECT id, name, email, age FROM users LIMIT 10000`)
		for rows.Next() {
			var u User
			rows.Scan(&u.ID, &u.Name, &u.Email, &u.Age)
		}
		rows.Close()
	}
}

// 2. sqlx
func benchmarkSqlx(b *testing.B) {
	db := sqlx.MustOpen("postgres", connStr)
	defer db.Close()

	b.ResetTimer()
	for i := 0; i < b.N; i++ {
		var users []User
		db.Select(&users, `SELECT id, name, email, age FROM users LIMIT 10000`)
	}
}

// 3. GORM
func benchmarkGORM(b *testing.B) {
	db, _ := gorm.Open(postgres.Open(connStr))

	b.ResetTimer()
	for i := 0; i < b.N; i++ {
		var users []User
		db.Limit(10000).Find(&users)
	}
}

// Results (selecting 10k rows, 4 fields each):
// Raw SQL:        ~45ms (baseline)
// sqlx:           ~52ms (15% overhead)
// GORM:           ~180ms (4x overhead!)
//
// Allocations:
// Raw SQL:        ~450 allocs
// sqlx:           ~460 allocs (reflection for scanning)
// GORM:          ~5,200 allocs (hooks, callbacks, query building)

Reflection Cost in ORMs

GORM's overhead primarily comes from:

Reflection-based field mapping
Hook/callback system
Query builder abstraction
Batch processing wrapper

// GORM internals simplified
func (db *DB) Select(dest interface{}, conds ...interface{}) *DB {
	// 1. Reflect on dest to find struct fields
	scope := db.scopes[db.Statement]
	t := reflect.TypeOf(dest)
	v := reflect.ValueOf(dest)

	// 2. Build query with hooks
	db.callbacks.create().Execute(db)

	// 3. Execute query
	rows, _ := db.DB().QueryContext(...)

	// 4. Reflect to scan (field-by-field)
	for rows.Next() {
		item := reflect.New(t.Elem())
		for i := 0; i < t.Elem().NumField(); i++ {
			field := t.Elem().Field(i)
			// Map column to struct field using tags
			// Scan reflection value
		}
	}

	return db
}

When to Use Each Approach

// Use Raw SQL when:
// - Query is called millions of times (hot path)
// - Performance is critical (< 10ms latency requirement)
// - Query is simple and stable
// - You need features like COPY, ARRAY operations, or CTEs

// Use sqlx when:
// - Performance matters (15% acceptable overhead)
// - You want type-safe scanning without reflection complexity
// - Building modular, queryable functions
// - Testing with mock implementations

// Use GORM when:
// - Development speed matters more than performance
// - You have complex relationships and associations
// - You need migrations and plugins
// - Performance is not critical (batch operations, offline processing)

// Decision tree:
if queryLatencyRequirement < 5*time.Millisecond {
	// Use raw SQL or pgx
} else if queryLatencyRequirement < 20*time.Millisecond {
	// Use sqlx
} else {
	// GORM acceptable if development productivity is critical
}

sqlx.StructScan vs Manual Scanning

// Method 1: sqlx.StructScan (reflection-based)
type User struct {
	ID    int    `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

func getUsers(db *sqlx.DB) ([]User, error) {
	var users []User
	err := db.Select(&users, `SELECT id, name, email FROM users`)
	// sqlx reflects on User struct to map columns
	return users, err
}

// Performance: ~52ms for 10k rows

// Method 2: Manual scanning (direct)
func getUsersManual(db *sql.DB) ([]User, error) {
	rows, err := db.Query(`SELECT id, name, email FROM users`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	users := make([]User, 0, 10000)
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
			return nil, err
		}
		users = append(users, u)
	}

	return users, rows.Err()
}

// Performance: ~45ms for 10k rows (15% faster)

// Recommendation: Use sqlx for convenience in non-critical paths,
// manual scanning for hot paths where 10-15% matters

Query Performance Patterns

Efficient query construction and execution patterns.

Avoiding SELECT *

// PROBLEMATIC: SELECT * loads all columns
// Even if you only need 2 of 10 columns
// bandwidth: 10x larger payload
// reflection: scanning unused columns allocates memory

func getUsersSlow(db *sql.DB) error {
	rows, err := db.Query(`SELECT * FROM users LIMIT 10000`)
	if err != nil {
		return err
	}
	defer rows.Close()

	// Scanning only id and name, but received all 10 columns
	for rows.Next() {
		var id int
		var name string
		var unused1, unused2, unused3, unused4, unused5, unused6, unused7, unused8 string

		rows.Scan(&id, &name, &unused1, &unused2, &unused3, &unused4, &unused5, &unused6, &unused7, &unused8)
	}

	return rows.Err()
}

// EFFICIENT: Select specific columns
func getUsersFast(db *sql.DB) error {
	rows, err := db.Query(`SELECT id, name FROM users LIMIT 10000`)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		rows.Scan(&id, &name)
	}

	return rows.Err()
}

// Performance improvement:
// Bandwidth: 80% less (10 cols -> 2 cols)
// Scan time: ~20% faster (fewer Scan calls)
// Memory: 50% less allocation

Index-Aware Query Construction

// Query optimization requires index awareness
type UserQuery struct {
	db      *sql.DB
	filters map[string]interface{}
}

func (uq *UserQuery) ByID(id int) *UserQuery {
	// PRIMARY KEY index: very fast
	uq.filters["id"] = id
	return uq
}

func (uq *UserQuery) ByEmail(email string) *UserQuery {
	// Email has UNIQUE index: very fast
	uq.filters["email"] = email
	return uq
}

func (uq *UserQuery) ByAge(minAge, maxAge int) *UserQuery {
	// Age has compound index (age, created_at): fast with proper ordering
	uq.filters["age_min"] = minAge
	uq.filters["age_max"] = maxAge
	return uq
}

func (uq *UserQuery) ByName(name string) *UserQuery {
	// Name has LIKE-only index: slower
	// Consider: Does this query need the full name, or can we use ID?
	uq.filters["name"] = name
	return uq
}

func (uq *UserQuery) Execute(ctx context.Context) ([]User, error) {
	// Build query optimally based on filters
	if id, ok := uq.filters["id"]; ok {
		// Use primary key
		return uq.executeByID(ctx, id.(int))
	}

	if email, ok := uq.filters["email"]; ok {
		// Use unique index
		return uq.executeByEmail(ctx, email.(string))
	}

	if minAge, ok := uq.filters["age_min"]; ok {
		// Use compound index
		return uq.executeByAgeRange(ctx, minAge.(int), uq.filters["age_max"].(int))
	}

	// Fallback: full table scan (expensive)
	return uq.executeFullScan(ctx)
}

Pagination: OFFSET vs Cursor-Based

// OFFSET pagination: simple but inefficient for large offsets
// SELECT * FROM users ORDER BY id OFFSET 1000000 LIMIT 100
// Database must read and skip 1M rows!

func offsetPagination(ctx context.Context, db *sql.DB, page, pageSize int) ([]User, error) {
	offset := (page - 1) * pageSize

	rows, err := db.QueryContext(ctx, `
		SELECT id, name FROM users
		ORDER BY id
		LIMIT $1 OFFSET $2`,
		pageSize, offset)

	// Problem: OFFSET 1000000 scans 1M rows internally
	// Each page request gets slower
	// Performance: page 1 (10ms), page 100 (800ms), page 1000 (8s)

	return scanUsers(rows)
}

// CURSOR-based pagination: efficient regardless of position
// SELECT * FROM users WHERE id > 999900 ORDER BY id LIMIT 100
// Database uses index to jump to position!

type PaginationCursor struct {
	LastID int
	Limit  int
}

func cursorPagination(ctx context.Context, db *sql.DB, cursor PaginationCursor) ([]User, error) {
	rows, err := db.QueryContext(ctx, `
		SELECT id, name FROM users
		WHERE id > $1
		ORDER BY id
		LIMIT $2`,
		cursor.LastID, cursor.Limit)

	// Performance: constant time regardless of position
	// All pages: ~10ms
	// Better for: infinite scroll, large datasets

	return scanUsers(rows)
}

// Mixed approach: keyset pagination for best UX
type KeysetCursor struct {
	LastID        int
	LastCreatedAt time.Time
	Limit         int
	SortOrder     string // ASC or DESC
}

func keysetPagination(ctx context.Context, db *sql.DB, cursor KeysetCursor) ([]User, error) {
	var query string
	var args []interface{}

	if cursor.SortOrder == "ASC" {
		query = `
			SELECT id, name, created_at FROM users
			WHERE (created_at, id) > ($1, $2)
			ORDER BY created_at ASC, id ASC
			LIMIT $3`
		args = []interface{}{cursor.LastCreatedAt, cursor.LastID, cursor.Limit}
	} else {
		query = `
			SELECT id, name, created_at FROM users
			WHERE (created_at, id) < ($1, $2)
			ORDER BY created_at DESC, id DESC
			LIMIT $3`
		args = []interface{}{cursor.LastCreatedAt, cursor.LastID, cursor.Limit}
	}

	return scanUsersWithTime(db.QueryContext(ctx, query, args...))
}

Query Result Scanning Performance

type ScanStats struct {
	method    string
	timeMs    float64
	allocCount int
	allocBytes int64
}

// Method 1: rows.Scan (direct)
// Time: 8ms for 10k rows, 4 cols
// Allocs: ~20k (minimal)

func scanDirect(rows *sql.Rows) {
	for rows.Next() {
		var id int
		var name, email string
		var age int
		rows.Scan(&id, &name, &email, &age)
	}
}

// Method 2: sqlx.StructScan (reflection)
// Time: 9.5ms for 10k rows
// Allocs: ~25k (reflection overhead)

func scanStructScan(rows *sqlx.Rows) {
	var u User
	for rows.Next() {
		rows.StructScan(&u)
	}
}

// Method 3: Manual field mapping with unsafe (dangerous but fast)
// Time: 7.2ms for 10k rows
// Allocs: ~10k (no reflection)
// WARNING: Unsafe! Only if schema is guaranteed stable

func scanManualUnsafe(rows *sql.Rows) {
	type userRow struct {
		id    int
		name  string
		email string
		age   int
	}

	for rows.Next() {
		var u userRow
		rows.Scan(&u.id, &u.name, &u.email, &u.age)
		// Use u
	}
}

// Recommendation: Use rows.Scan for hot paths (99% of cases)

Context and Timeouts

Proper context usage prevents resource leaks and ensures responsive cancellation.

// Context timeout pattern for database queries
func queryWithTimeout(ctx context.Context, db *sql.DB, userID int) (User, error) {
	// Create child context with 5 second timeout
	queryCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel() // Always call cancel to free resources

	var u User
	err := db.QueryRowContext(queryCtx,
		`SELECT id, name, email FROM users WHERE id = $1`,
		userID).Scan(&u.ID, &u.Name, &u.Email)

	if err == context.DeadlineExceeded {
		// Query took > 5 seconds
		// Database might still be executing!
		return User{}, fmt.Errorf("query timeout")
	}

	return u, err
}

// Context cancellation and connection return
func fetchUsersCancelable(ctx context.Context, db *sql.DB, userIDs []int) ([]User, error) {
	// When context is cancelled, query execution stops
	rows, err := db.QueryContext(ctx,
		`SELECT id, name FROM users WHERE id = ANY($1)`,
		userIDs)
	if err != nil {
		return nil, err
	}
	defer rows.Close() // CRITICAL: returns connection to pool

	var users []User
	for rows.Next() {
		select {
		case <-ctx.Done():
			// Parent context cancelled (e.g., HTTP request ended)
			// Return immediately, rows.Close() returns conn to pool
			return users, ctx.Err()
		default:
		}

		var u User
		rows.Scan(&u.ID, &u.Name)
		users = append(users, u)
	}

	return users, rows.Err()
}

// Connection pool leak detection
func detectLeaks(db *sql.DB, checkInterval time.Duration) {
	ticker := time.NewTicker(checkInterval)
	defer ticker.Stop()

	var prevOpen int
	for range ticker.C {
		stats := db.Stats()

		// If idle connections not decreasing, leak suspected
		if stats.OpenConnections == stats.IdleConnections &&
			stats.OpenConnections > (runtime.NumCPU() * 4) {
			log.Printf("LEAK DETECTED: %d idle connections, no queries active",
				stats.OpenConnections)
		}

		// Track open connection growth
		if stats.OpenConnections > prevOpen {
			log.Printf("Connections increasing: %d -> %d",
				prevOpen, stats.OpenConnections)
		}
		prevOpen = stats.OpenConnections
	}
}

Redis Performance

Redis provides in-memory caching and data operations. Pipelining is critical for performance.

go-redis Pipelining

// Without pipelining: N roundtrips
func setUsersCacheSlow(ctx context.Context, rc *redis.Client, users []User) error {
	for _, u := range users {
		json, _ := json.Marshal(u)
		// Each Set = 1 roundtrip (1-2ms)
		// 100 users = 100-200ms
		rc.Set(ctx, fmt.Sprintf("user:%d", u.ID), json, 1*time.Hour)
	}
	return nil
}

// With pipelining: 1 roundtrip for N commands
func setUsersCacheFast(ctx context.Context, rc *redis.Client, users []User) error {
	pipe := rc.Pipeline()

	for _, u := range users {
		json, _ := json.Marshal(u)
		pipe.Set(ctx, fmt.Sprintf("user:%d", u.ID), json, 1*time.Hour)
	}

	// All 100 commands in single roundtrip (2-3ms!)
	_, err := pipe.Exec(ctx)
	return err
}

// Batch pipelining for memory efficiency
func setUsersCacheBatched(ctx context.Context, rc *redis.Client, users []User, batchSize int) error {
	for i := 0; i < len(users); i += batchSize {
		end := i + batchSize
		if end > len(users) {
			end = len(users)
		}

		pipe := rc.Pipeline()
		for _, u := range users[i:end] {
			json, _ := json.Marshal(u)
			pipe.Set(ctx, fmt.Sprintf("user:%d", u.ID), json, 1*time.Hour)
		}

		_, err := pipe.Exec(ctx)
		if err != nil {
			return err
		}
	}
	return nil
}

Connection Pooling Configuration

rc := redis.NewClient(&redis.Options{
	Addr:     "localhost:6379",
	DB:       0,
	Password: "", // only if required

	// Connection pool sizing
	PoolSize:     runtime.NumCPU() * 2,       // Connections per CPU
	MinIdleConns: runtime.NumCPU(),           // Keep idle connections
	MaxRetries:   3,                          // Retry failed commands

	// Timeouts
	DialTimeout:  5 * time.Second,            // TCP connect timeout
	ReadTimeout:  3 * time.Second,            // Read timeout
	WriteTimeout: 3 * time.Second,            // Write timeout

	// TLS for production
	TLSConfig: &tls.Config{
		// TLS configuration
	},
})

// Verify connection
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

if err := rc.Ping(ctx).Err(); err != nil {
	log.Fatal(err)
}

Lua Scripting vs Multiple Roundtrips

// Transaction without Lua: multiple roundtrips
// 1. GET user:1
// 2. Increment locally
// 3. SET user:1

func incrementCounterSlow(ctx context.Context, rc *redis.Client) error {
	// Roundtrip 1: GET
	val, err := rc.Get(ctx, "counter").Int()
	if err != nil && err != redis.Nil {
		return err
	}

	val++

	// Roundtrip 2: SET
	return rc.Set(ctx, "counter", val, 0).Err()
	// Race condition if another client increments between GET and SET!
}

// Transaction with Lua: single roundtrip, atomic
func incrementCounterFast(ctx context.Context, rc *redis.Client) error {
	script := redis.NewScript(`
		local current = tonumber(redis.call('GET', KEYS[1])) or 0
		redis.call('SET', KEYS[1], current + 1)
		return current + 1
	`)

	val, err := script.Run(ctx, rc, []string{"counter"}).Result()
	// Single roundtrip, atomically incremented
	// No race condition

	return err
}

// Real-world example: rate limiting with Lua
func checkRateLimit(ctx context.Context, rc *redis.Client, userID string, limit int64, window time.Duration) (bool, error) {
	script := redis.NewScript(`
		local key = KEYS[1]
		local limit = tonumber(ARGV[1])
		local window = tonumber(ARGV[2])

		local current = tonumber(redis.call('GET', key)) or 0
		if current < limit then
			redis.call('INCR', key)
			redis.call('EXPIRE', key, window)
			return 1
		else
			return 0
		end
	`)

	allowed, err := script.Run(ctx, rc,
		[]string{fmt.Sprintf("ratelimit:%s", userID)},
		limit,
		int64(window.Seconds())).Bool()

	return allowed, err
}

Redis Cluster Performance

// Single instance: high throughput, single server bottleneck
rc := redis.NewClient(&redis.Options{
	Addr: "localhost:6379",
})

// Cluster: distributed across multiple nodes
// Better: horizontal scalability, failover
// Tradeoffs: some commands unavailable, pipeline across nodes less efficient
rcCluster := redis.NewClusterClient(&redis.ClusterOptions{
	Addrs: []string{
		"node1:6379",
		"node2:6379",
		"node3:6379",
	},
	PoolSize:     runtime.NumCPU() * 2,
	ReadOnly:     true, // Can read from replicas
	RouteByLatency: true, // Route to nearest node
})

// Performance characteristics:
// Single instance: ~100k ops/sec, one server needed
// Cluster 3 nodes: ~300k ops/sec, distributed load
// Both handle ~1-2ms latency with pipelining

Connection Pool Leak Prevention

// Pattern: resource wrapper with automatic cleanup
type QueryRunner struct {
	db *sql.DB

	// Leak detection
	openQueries int64
	maxOpen     int64
}

func NewQueryRunner(db *sql.DB, maxConcurrent int) *QueryRunner {
	return &QueryRunner{
		db:      db,
		maxOpen: int64(maxConcurrent),
	}
}

func (qr *QueryRunner) Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	open := atomic.AddInt64(&qr.openQueries, 1)
	if open > qr.maxOpen {
		atomic.AddInt64(&qr.openQueries, -1)
		return nil, fmt.Errorf("too many concurrent queries: %d > %d", open, qr.maxOpen)
	}

	rows, err := qr.db.QueryContext(ctx, query, args...)
	if err != nil {
		atomic.AddInt64(&qr.openQueries, -1)
		return nil, err
	}

	// Wrap rows to track closure
	return &trackedRows{rows, qr}, nil
}

type trackedRows struct {
	rows *sql.Rows
	qr   *QueryRunner
}

func (tr *trackedRows) Close() error {
	defer atomic.AddInt64(&tr.qr.openQueries, -1)
	return tr.rows.Close()
}

func (tr *trackedRows) Next() bool {
	return tr.rows.Next()
}

func (tr *trackedRows) Scan(dest ...interface{}) error {
	return tr.rows.Scan(dest...)
}

func (tr *trackedRows) Err() error {
	return tr.rows.Err()
}

// Usage
qr := NewQueryRunner(db, 100)
rows, err := qr.Query(ctx, "SELECT * FROM users")
if err != nil {
	log.Fatal(err)
}
defer rows.Close() // Tracked automatically

Conclusion

Effective database optimization in Go requires understanding connection pooling mechanics, choosing the right tools for the job, and measuring performance critically. Key takeaways:

Connection pooling: Size pools based on CPU cores and query latency, not guesswork
Prepared statements: Use db.PrepareContext, not connection-bound statements
pgx for PostgreSQL: 20-40% faster than database/sql with COPY protocol support
Batch operations: Reduce roundtrips aggressively (COPY, batch queries, pipelining)
ORM overhead: GORM adds 4x overhead; use for convenience, not performance
Query patterns: Index-aware queries, cursor pagination, selective columns
Context handling: Always use timeouts and defer Close() to prevent leaks
Redis pipelining: Batch commands to reduce roundtrip latency by 50-99%

Measure everything with realistic workloads. Generic optimization advice often falls flat against actual query patterns and dataset characteristics.

On this page