Database Operations and Optimization in Go

Database performance in Go comes down to three layers: connection pool configuration, query efficiency, and reducing round-trips. Most production database problems fall into one of these categories, and each has clear measurement and fix strategies.

For foundational database/sql patterns see Go database fundamentals. For query building see Go SQL query building.

Measuring Before Optimizing

Before changing anything, establish a baseline. The db.Stats() method shows what’s happening in the connection pool:

func logDBStats(db *sql.DB, interval time.Duration) {
    ticker := time.NewTicker(interval)
    for range ticker.C {
        s := db.Stats()
        slog.Info("db pool",
            slog.Int("open", s.OpenConnections),
            slog.Int("in_use", s.InUse),
            slog.Int("idle", s.Idle),
            slog.Int64("wait_count", s.WaitCount),
            slog.Duration("wait_duration", s.WaitDuration),
            slog.Int64("max_idle_closed", s.MaxIdleClosed),
            slog.Int64("max_lifetime_closed", s.MaxLifetimeClosed),
        )
    }
}

Key signals:

WaitCount > 0 — goroutines waiting for a connection → increase MaxOpenConns
MaxIdleClosed > MaxLifetimeClosed — idle connections being discarded → increase MaxIdleConns or reduce MaxIdleTime
InUse ≈ OpenConnections constantly → connection exhaustion under peak load

Expose this as a Prometheus gauge and alert on WaitCount > 0 in production.

Connection Pool Sizing

The defaults are too conservative for most production services:

func configureProd(db *sql.DB) {
    // MaxOpenConns: total connections to the database
    // Formula: min(database_max_connections / num_app_instances, practical_limit)
    // PostgreSQL default max_connections is 100; with 4 app instances → 25 per instance
    db.SetMaxOpenConns(25)

    // MaxIdleConns: warm connections ready to use immediately
    // Should be ≤ MaxOpenConns; typically 50–75% of MaxOpenConns
    db.SetMaxIdleConns(15)

    // ConnMaxLifetime: rotate connections to pick up config changes,
    // prevent TCP state buildup, and handle database restarts
    db.SetConnMaxLifetime(30 * time.Minute)

    // ConnMaxIdleTime: close connections idle longer than this
    // Keeps the pool from holding connections when traffic is low
    db.SetConnMaxIdleTime(10 * time.Minute)
}

Run a load test, observe db.Stats() under peak load, and tune until WaitCount stays near zero at the load level you expect.

Batch Inserts

Inserting rows one at a time in a loop has two costs per row: a network round-trip and a transaction overhead. Batching eliminates both:

// ❌ N round-trips for N rows
for _, user := range users {
    _, err := db.ExecContext(ctx,
        "INSERT INTO users (name, email) VALUES ($1, $2)",
        user.Name, user.Email)
    if err != nil { return err }
}

// ✅ One round-trip for all rows — 10–100x faster for large batches
func batchInsertUsers(ctx context.Context, db *sql.DB, users []User) error {
    if len(users) == 0 {
        return nil
    }

    // Build multi-value INSERT
    valueStrings := make([]string, len(users))
    valueArgs := make([]any, 0, len(users)*2)

    for i, u := range users {
        valueStrings[i] = fmt.Sprintf("($%d, $%d)", i*2+1, i*2+2)
        valueArgs = append(valueArgs, u.Name, u.Email)
    }

    query := "INSERT INTO users (name, email) VALUES " +
        strings.Join(valueStrings, ",")

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

For very large batches (100k+ rows), chunk them to avoid exceeding PostgreSQL’s parameter limit (~65,535):

func batchInsertChunked(ctx context.Context, db *sql.DB, users []User, chunkSize int) error {
    for i := 0; i < len(users); i += chunkSize {
        end := min(i+chunkSize, len(users))
        if err := batchInsertUsers(ctx, db, users[i:end]); err != nil {
            return fmt.Errorf("chunk %d-%d: %w", i, end, err)
        }
    }
    return nil
}

Query Optimization with EXPLAIN ANALYZE

When a query is slow, EXPLAIN ANALYZE shows what PostgreSQL actually does — whether it uses indexes, how many rows it processes, and where the time goes:

// Run EXPLAIN ANALYZE to understand query execution
func explainQuery(ctx context.Context, db *sql.DB, query string, args ...any) {
    rows, err := db.QueryContext(ctx, "EXPLAIN ANALYZE "+query, args...)
    if err != nil {
        log.Printf("EXPLAIN failed: %v", err)
        return
    }
    defer rows.Close()

    fmt.Println("Query plan:")
    for rows.Next() {
        var line string
        rows.Scan(&line)
        fmt.Println("  ", line)
    }
}

Look for:

Seq Scan on large tables — usually means a missing index
Rows Removed by Filter: N where N is large — the WHERE clause is applied after reading many rows
Hash Join or Nested Loop with large row counts — consider a different join strategy
cost=... vs actual time=... — large discrepancy means stale statistics (run ANALYZE table_name)

Indexing Strategy

The most impactful optimization is usually adding the right index. Key patterns:

-- Index columns that appear in WHERE clauses
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'completed';

-- Composite index for common query patterns — column order matters
-- This index helps: WHERE user_id = ? AND created_at > ?
-- But NOT: WHERE created_at > ? (user_id not first)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- Partial index: much smaller, faster for common query
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Index for sorting to avoid filesort
CREATE INDEX idx_products_price ON products(price DESC);

Index every foreign key column — queries that join on them without an index cause full table scans on the child table.

Read Replica Routing

For read-heavy services, route SELECT queries to read replicas and write queries to the primary:

type DB struct {
    primary  *sql.DB
    replicas []*sql.DB
    counter  atomic.Uint64
}

func (d *DB) ReadDB() *sql.DB {
    if len(d.replicas) == 0 {
        return d.primary
    }
    // Round-robin across replicas
    idx := d.counter.Add(1) % uint64(len(d.replicas))
    return d.replicas[idx]
}

func (d *DB) WriteDB() *sql.DB {
    return d.primary
}

// Usage
func (r *UserRepo) GetUser(ctx context.Context, id string) (*User, error) {
    var u User
    err := r.db.ReadDB().QueryRowContext(ctx,
        "SELECT id, name FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.Name)
    return &u, err
}

func (r *UserRepo) CreateUser(ctx context.Context, u *User) error {
    _, err := r.db.WriteDB().ExecContext(ctx,
        "INSERT INTO users (id, name) VALUES ($1, $2)", u.ID, u.Name)
    return err
}

Be aware of replication lag — reads from replicas may be slightly behind the primary. For operations where you just wrote and immediately read (e.g., create then redirect to a detail page), route the read to the primary.

Application-Level Query Cache

For expensive, frequently-called queries with stable results, cache at the application layer:

type CachedUserStore struct {
    db    *sql.DB
    cache map[string]*User
    mu    sync.RWMutex
    ttl   time.Duration
    exp   map[string]time.Time
}

func (s *CachedUserStore) GetUser(ctx context.Context, id string) (*User, error) {
    // Fast path: check cache
    s.mu.RLock()
    if u, ok := s.cache[id]; ok {
        if time.Now().Before(s.exp[id]) {
            s.mu.RUnlock()
            return u, nil
        }
    }
    s.mu.RUnlock()

    // Slow path: query database
    u, err := s.queryUser(ctx, id)
    if err != nil {
        return nil, err
    }

    s.mu.Lock()
    s.cache[id] = u
    s.exp[id] = time.Now().Add(s.ttl)
    s.mu.Unlock()

    return u, nil
}

For production caching, use Redis or Memcached rather than in-process maps — they survive restarts, scale across instances, and support TTL natively.

Measuring Query Latency

Instrument every database call for observability:

type InstrumentedDB struct {
    db      *sql.DB
    hist    *prometheus.HistogramVec
}

func (d *InstrumentedDB) QueryRowContext(ctx context.Context, query, operation string, args ...any) *sql.Row {
    start := time.Now()
    defer func() {
        d.hist.WithLabelValues(operation).Observe(time.Since(start).Seconds())
    }()
    return d.db.QueryRowContext(ctx, query, args...)
}

Expose P50, P95, P99 latency per operation in Grafana. A sudden increase in P99 latency often signals a slow query from a missing index or table growth hitting a tipping point.

Summary

Monitor db.Stats() in production — WaitCount > 0 means the pool is undersized
Batch inserts (multi-value INSERT) are 10–100x faster than individual inserts for bulk operations
EXPLAIN ANALYZE shows the actual query plan — look for Seq Scan on large tables and large row filters
Index foreign keys and columns in WHERE/ORDER BY clauses; partial indexes for common filtered queries
Route SELECT to read replicas, INSERT/UPDATE/DELETE to primary — be aware of replication lag
Cache expensive, stable queries at the application layer; use Redis for multi-instance deployments

Database Operations and Optimization in Go

Measuring Before Optimizing

Connection Pool Sizing

Batch Inserts

Query Optimization with EXPLAIN ANALYZE

Indexing Strategy

Read Replica Routing

Application-Level Query Cache

Measuring Query Latency

Summary

Resources

Comments

Share this article

👍 Was this article helpful?