The Ultimate Guide to Database Operations in Go: SQL Query Optimization and NoSQL Best Practices with Code Snippets

Why database operations in Go deserve special attention

Go sits at a sweet spot for backend services: fast, simple concurrency, and a rich standard library. But raw speed in Go won’t rescue a poorly designed query or an inefficient data model. To unlock real-world performance, you need to blend Go’s tooling with database best practices—both SQL and NoSQL.

This guide walks through practical patterns, optimization strategies, and production-ready code snippets so you can write fast, safe, and maintainable data access code in Go.

Foundations: idiomatic data access in Go

Before diving into optimizations, get the basics right.

Use context everywhere

• Always pass context.Context to DB operations to enable timeouts, cancellation, and tracing.
• Set deadlines for external dependencies; don’t let a hung DB take down your service.

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

rows, err := db.QueryContext(ctx, "SELECT id, name FROM customers WHERE status = $1", "active")
if err != nil {
    return err
}
defer rows.Close()

Tune the connection pool

The database/sql package maintains a pool; tune it to your workload.

db.SetMaxOpenConns(50)                 // Upper bound on DB connections
db.SetMaxIdleConns(25)                 // Idle connections retained
db.SetConnMaxLifetime(30 * time.Minute) // Recycle connections
db.SetConnMaxIdleTime(5 * time.Minute)  // Idle connection lifetime

• Too many connections can thrash the DB. Start small, measure, and scale.
• Use different pools for read and write (e.g., primary vs replica).

Prefer prepared statements for hot paths

Prepared statements reduce parse/plan overhead and help prevent SQL injection.

stmt, err := db.PrepareContext(ctx, `
    SELECT id, email, created_at 
    FROM users 
    WHERE tenant_id = $1 AND created_at >= $2
    ORDER BY created_at DESC
    LIMIT $3`)
if err != nil { return err }
defer stmt.Close()

rows, err := stmt.QueryContext(ctx, tenantID, since, limit)

SQL query optimization in Go

1) Start with the query plan

Use EXPLAIN/EXPLAIN ANALYZE to validate assumptions.

EXPLAIN ANALYZE
SELECT id, total_cents 
FROM orders 
WHERE tenant_id = $1 AND status = 'PAID' AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;

Indexing tips:

• Composite index: (tenant_id, status, created_at DESC)
• Order of columns matters; equality filters first, then range, then ordering columns.

2) Avoid SELECT *

Fetch only the columns you need. This reduces I/O and improves cache efficiency.

const q = `SELECT id, total_cents, currency FROM orders WHERE id = $1`

3) Eliminate N+1 queries

Pull related data in one go via joins or batched IN queries.

// BAD: N+1
for _, orderID := range orderIDs {
    db.QueryRowContext(ctx, "SELECT * FROM order_items WHERE order_id = $1", orderID)
}

// BETTER: batched fetch
query := `
    SELECT order_id, sku, qty 
    FROM order_items 
    WHERE order_id = ANY($1)`
rows, err := db.QueryContext(ctx, query, pq.Array(orderIDs))

4) Pagination: prefer keyset over offset

OFFSET/LIMIT slows down as OFFSET grows. Keyset pagination is faster and stable.

• Offset pagination:

const q = `
    SELECT id, created_at, total_cents 
    FROM orders 
    WHERE tenant_id = $1
    ORDER BY created_at DESC, id DESC
    LIMIT $2 OFFSET $3`
rows, err := db.QueryContext(ctx, q, tenantID, limit, offset)

• Keyset pagination:

type Page struct {
    AfterID        int64
    AfterCreatedAt time.Time
}

const qKeyset = `
    SELECT id, created_at, total_cents
    FROM orders
    WHERE tenant_id = $1
      AND (created_at, id) < ($2, $3)
    ORDER BY created_at DESC, id DESC
    LIMIT $4`

// First page: use max values or COALESCE logic
rows, err := db.QueryContext(ctx, qKeyset, tenantID, lastCreatedAt, lastID, limit)

Ensure you have an index supporting ORDER BY and filters, e.g., (tenant_id, created_at DESC, id DESC).

5) Batch writes and bulk insert

Group inserts to reduce round trips.

// Multi-row insert (Postgres example)
func InsertOrders(ctx context.Context, db *sql.DB, orders []Order) error {
    const base = `INSERT INTO orders (tenant_id, id, total_cents, currency) VALUES `
    // Build values placeholders
    vals := make([]string, 0, len(orders))
    args := make([]any, 0, len(orders)*4)
    for i, o := range orders {
        n := i*4
        vals = append(vals, fmt.Sprintf("($%d,$%d,$%d,$%d)", n+1, n+2, n+3, n+4))
        args = append(args, o.TenantID, o.ID, o.TotalCents, o.Currency)
    }
    q := base + strings.Join(vals, ",")
    _, err := db.ExecContext(ctx, q, args...)
    return err
}

For very large batches or CSV pipelines, consider pgx’s CopyFrom for Postgres.

6) Use transactions wisely

Wrap multi-step operations in a transaction; set isolation only as needed.

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelReadCommitted,
})
if err != nil { return err }
defer func() {
    if err != nil {
        _ = tx.Rollback()
    } else {
        err = tx.Commit()
    }
}()

// Debit and credit in one atomic op
if _, err = tx.ExecContext(ctx, `
    UPDATE accounts 
    SET balance_cents = balance_cents - $1 
    WHERE id = $2 AND balance_cents >= $1`, amt, fromID); err != nil { return err }

if _, err = tx.ExecContext(ctx, `
    UPDATE accounts 
    SET balance_cents = balance_cents + $1 
    WHERE id = $2`, amt, toID); err != nil { return err }

• Use CHECK constraints or row-level locks when necessary:
  • SELECT ... FOR UPDATE to lock specific rows.
  • But avoid locking more than needed.

7) Handle NULLs and types correctly

Use sql.NullString, sql.NullInt64, etc., to avoid scanning errors.

var middle sql.NullString
err := db.QueryRowContext(ctx, "SELECT middle_name FROM users WHERE id=$1", id).Scan(&middle)
if middle.Valid {
    fmt.Println(middle.String)
}

8) Choose the right library for the job

• database/sql is low-level, predictable, fast.
• sqlx adds ergonomics for struct scanning.
• pgx for Postgres gives better performance and features (CopyFrom, statement caching).
• ORM (e.g., GORM) can speed development but don’t forget to inspect produced SQL.

A production-ready SQL repository pattern

Organize your data access with clear boundaries and context-aware methods.

type Order struct {
    ID         int64
    TenantID   int64
    TotalCents int64
    Currency   string
    CreatedAt  time.Time
}

type OrderRepo struct {
    db *sql.DB
}

func NewOrderRepo(db *sql.DB) *OrderRepo { return &OrderRepo{db: db} }

func (r *OrderRepo) GetRecent(ctx context.Context, tenantID int64, since time.Time, limit int) ([]Order, error) {
    const q = `
        SELECT id, tenant_id, total_cents, currency, created_at
        FROM orders
        WHERE tenant_id = $1 AND created_at >= $2
        ORDER BY created_at DESC, id DESC
        LIMIT $3`
    rows, err := r.db.QueryContext(ctx, q, tenantID, since, limit)
    if err != nil { return nil, err }
    defer rows.Close()

    var res []Order
    for rows.Next() {
        var o Order
        if err := rows.Scan(&o.ID, &o.TenantID, &o.TotalCents, &o.Currency, &o.CreatedAt); err != nil {
            return nil, err
        }
        res = append(res, o)
    }
    return res, rows.Err()
}

Tip: provide methods that accept both *sql.DB and *sql.Tx so callers can reuse the same connection in a transaction. One approach is to define an interface with ExecContext, QueryContext, QueryRowContext that both DB and Tx satisfy.

Read vs write paths and replicas

• Use separate connections/pools for read replicas and primary writes.
• Consistency caveat: replicas may lag. Avoid read-after-write from replicas unless you use session-level consistency features.

type Store struct {
    Write *sql.DB
    Read  *sql.DB
}

func (s *Store) GetOrderForRead(ctx context.Context, id int64) (Order, error) {
    return queryOrder(ctx, s.Read, id)
}

NoSQL best practices in Go

MongoDB: model your data by query

Mongo rewards schema design tailored to access patterns.

• Prefer embedding for frequently accessed related data (denormalization).
• Reference and join in app code (or $lookup) when relationships are large or mutable.

Indexing and projections

Create indexes that match your most common queries.

coll := client.Database("shop").Collection("orders")
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

// Compound index: tenant_id + status + created_at desc
_, err := coll.Indexes().CreateOne(ctx, mongo.IndexModel{
    Keys:    bson.D{{Key: "tenant_id", Value: 1}, {Key: "status", Value: 1}, {Key: "created_at", Value: -1}},
    Options: options.Index().SetBackground(true),
})
if err != nil { log.Fatal(err) }

// Query with projection
cur, err := coll.Find(ctx,
    bson.M{"tenant_id": tenantID, "status": "PAID"},
    options.Find().
        SetProjection(bson.M{"items": 0}). // exclude large arrays if not needed
        SetSort(bson.D{{Key: "created_at", Value: -1}}).
        SetLimit(50))

Keyset pagination with Mongo

Use a stable sort and a “created_at + id” cursor.

filter := bson.M{
    "tenant_id": tenantID,
    "created_at": bson.M{"$lt": lastCreatedAt},
}
opts := options.Find().
    SetSort(bson.D{{Key: "created_at", Value: -1}, {Key: "_id", Value: -1}}).
    SetLimit(50)
cur, err := coll.Find(ctx, filter, opts)

Create a matching index: { tenant_id: 1, created_at: -1, _id: -1 }.

TTL indexes and archiving

Use TTL to expire ephemeral data.

_, _ = coll.Indexes().CreateOne(ctx, mongo.IndexModel{
    Keys:    bson.D{{Key: "expires_at", Value: 1}},
    Options: options.Index().SetExpireAfterSeconds(0),
})

Bulk writes and transactions

• BulkWrite reduces round trips:

models := []mongo.WriteModel{
    mongo.NewInsertOneModel().SetDocument(doc1),
    mongo.NewUpdateOneModel().
        SetFilter(bson.M{"_id": id}).
        SetUpdate(bson.M{"$set": bson.M{"status": "PAID"}}),
}
res, err := coll.BulkWrite(ctx, models, options.BulkWrite().SetOrdered(false))

• Transactions are available but add overhead; use for multi-document invariants only.

Contexts and timeouts

Mongo operations should always have tight timeouts. For heavy aggregations, consider server-side limits and indexes to support the pipeline.

Redis: caching, rate limiting, and coordination

Redis shines for fast lookups and ephemeral state.

Caching database results

val, err := rdb.Get(ctx, cacheKey).Result()
if err == redis.Nil {
    // Miss: query DB
    data, err := repo.GetOrder(ctx, id)
    if err != nil { return err }
    b, _ := json.Marshal(data)
    _ = rdb.SetEX(ctx, cacheKey, b, 5*time.Minute).Err()
    return data, nil
} else if err != nil {
    return err
}
// Hit
var out Order
_ = json.Unmarshal([]byte(val), &out)

Avoid cache stampede by using request coalescing (e.g., singleflight) and adding jitter to TTLs.

var g singleflight.Group
v, err, _ := g.Do(cacheKey, func() (any, error) {
    // load and set cache
    return load()
})

Distributed locks and idempotency

Use SET NX with TTL; keep critical sections short.

ok, err := rdb.SetNX(ctx, "lock:order:123", "1", 10*time.Second).Result()
if err != nil || !ok { return errors.New("busy") }
defer func() { _ = rdb.Del(ctx, "lock:order:123").Err() }()

Better: use a library that implements Redlock semantics when you truly need distributed locks.

Rate limiting token bucket (simplified)

-- Lua script executed with EVAL:
-- KEYS[1]=key, ARGV[1]=now, ARGV[2]=rate, ARGV[3]=capacity, ARGV[4]=ttl
local tokens = redis.call("GET", KEYS[1])
if not tokens then
  redis.call("SETEX", KEYS[1], ARGV[4], ARGV[3]-1)
  return 1
end
tokens = tonumber(tokens)
if tokens > 0 then
  redis.call("DECR", KEYS[1])
  return 1
end
return 0

Use go-redis’s Eval to run atomically.

Cassandra/ScyllaDB: model by access pattern

• Rows are partitioned by a partition key; design tables around your queries.
• Avoid unbounded partitions. Choose time-bucketing or multi-partition keys.

cluster := gocql.NewCluster("127.0.0.1")
cluster.Consistency = gocql.Quorum
session, _ := cluster.CreateSession()
defer session.Close()

// Table with partition on tenant_id and day bucket; clustering by created_at desc
// CREATE TABLE orders_by_tenant_day (tenant_id bigint, day date, created_at timestamp, id bigint, total_cents bigint, PRIMARY KEY ((tenant_id, day), created_at, id)) WITH CLUSTERING ORDER BY (created_at DESC);

day := time.Now().UTC().Format("2006-01-02")
iter := session.Query(`
    SELECT id, total_cents FROM orders_by_tenant_day
    WHERE tenant_id=? AND day=? LIMIT 50`, tenantID, day).Iter()

• Tune Consistency (ONE, QUORUM, LOCAL_QUORUM) per operation.
• Avoid large ALLOW FILTERING queries; if you need a filter, add it to the key or create a new table tailored for that query.

Reliability patterns: retries, backoff, idempotency

Database operations fail: deadlocks, timeouts, transient network blips. Handle them gracefully.

Classify errors

• Retry transient errors (e.g., Postgres deadlock detected).
• Do not retry application errors (unique constraint violation) unless idempotent semantics are in place.

A simple retry with backoff

func Retry(ctx context.Context, attempts int, base time.Duration, fn func() error) error {
    delay := base
    for i := 0; i < attempts; i++ {
        err := fn()
        if err == nil { return nil }
        // Check context or classify error here
        if errors.Is(err, context.Canceled) || errors.Is(err, context.DeadlineExceeded) {
            return err
        }
        // TODO: add driver-specific checks for transient errors
        select {
        case <-time.After(delay):
            delay *= 2
        case <-ctx.Done():
            return ctx.Err()
        }
    }
    return fmt.Errorf("retry exhausted")
}

Use this around small, idempotent operations or around transaction bodies that can be retried safely. For tx retries, re-open the transaction each attempt.

Observability: measure what matters

Log slow queries

• Wrap ExecContext/QueryContext to log duration and SQL with placeholders (never log secret values).
• Set thresholds (e.g., > 200ms) to flag slow queries.

Metrics

• Track pool stats: in-use connections, waits, timeouts, error rates.
• Export metrics to Prometheus.

stats := db.Stats()
// stats.InUse, stats.Idle, stats.WaitCount, etc.

Tracing with OpenTelemetry

Instrument database/sql for spans.

import "github.com/XSAM/otelsql"

db, err := otelsql.Open("postgres", dsn,
    otelsql.WithAttributes(semconv.DBSystemPostgreSQL),
    otelsql.WithSQLCommenter(true),
)

• Use context propagation to link DB spans to incoming requests.
• For Mongo and Redis, use their respective OpenTelemetry integrations.

Migrations and testing

Migrations

Use a migration tool to manage schema changes, e.g., golang-migrate.

m, err := migrate.New(
    "file://migrations",
    "postgres://user:pass@localhost:5432/app?sslmode=disable",
)
if err != nil { log.Fatal(err) }
if err := m.Up(); err != nil && err != migrate.ErrNoChange {
    log.Fatal(err)
}

• Keep migrations small and idempotent.
• Lock DDL during critical writes if necessary.

Integration tests with ephemeral databases

Use Testcontainers to spin up real DBs in CI.

func TestOrderRepo(t *testing.T) {
    ctx := context.Background()
    req := testcontainers.ContainerRequest{
        Image:        "postgres:16",
        ExposedPorts: []string{"5432/tcp"},
        Env: map[string]string{
            "POSTGRES_USER":     "test",
            "POSTGRES_PASSWORD": "test",
            "POSTGRES_DB":       "testdb",
        },
        WaitingFor: wait.ForListeningPort("5432/tcp"),
    }
    pg, err := testcontainers.GenericContainer(ctx, testcontainers.GenericContainerRequest{
        ContainerRequest: req,
        Started:          true,
    })
    if err != nil { t.Fatal(err) }
    defer pg.Terminate(ctx)

    host, _ := pg.Host(ctx)
    port, _ := pg.MappedPort(ctx, "5432")
    dsn := fmt.Sprintf("postgres://test:test@%s:%s/testdb?sslmode=disable", host, port.Port())

    db, err := sql.Open("postgres", dsn)
    if err != nil { t.Fatal(err) }
    defer db.Close()

    // Run migrations, then integration tests against db
}

• Seed test data once per suite for speed.
• Clean up with transactions or truncation between tests.

Practical SQL patterns and anti-patterns

Do

• Create selective indexes matching your filters and sorts.
• Use prepared statements for hot queries.
• Employ keyset pagination for large tables.
• Batch writes and prefer server-side operations when possible.
• Limit large result sets and stream rows, scanning incrementally.

Don’t

• Don’t rely on OFFSET for deep pagination.
• Don’t SELECT * from wide tables.
• Don’t open/close DB per request; reuse a global db with pooling.
• Don’t ignore rows.Err() after iteration.
• Don’t build SQL with string concatenation of user input.

Bringing it together: an end-to-end example

Below is a minimal service component that blends several best practices: context with timeouts, repository pattern, prepared statements, keyset pagination, and caching with Redis.

type Service struct {
    store *Store // has Read/Write *sql.DB
    rdb   *redis.Client
}

func (s *Service) ListRecentPaidOrders(ctx context.Context, tenantID int64, lastCreatedAt time.Time, lastID int64, limit int) ([]Order, error) {
    // Cache key using cursor parameters
    cacheKey := fmt.Sprintf("orders:%d:%d:%d:%d", tenantID, lastCreatedAt.UnixNano(), lastID, limit)

    if b, err := s.rdb.Get(ctx, cacheKey).Bytes(); err == nil {
        var out []Order
        if json.Unmarshal(b, &out) == nil {
            return out, nil
        }
    }

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

    const q = `
        SELECT id, tenant_id, total_cents, currency, created_at
        FROM orders
        WHERE tenant_id = $1
          AND (created_at, id) < ($2, $3)
        ORDER BY created_at DESC, id DESC
        LIMIT $4`
    rows, err := s.store.Read.QueryContext(ctx, q, tenantID, lastCreatedAt, lastID, limit)
    if err != nil { return nil, err }
    defer rows.Close()

    res := make([]Order, 0, limit)
    for rows.Next() {
        var o Order
        if err := rows.Scan(&o.ID, &o.TenantID, &o.TotalCents, &o.Currency, &o.CreatedAt); err != nil {
            return nil, err
        }
        res = append(res, o)
    }
    if err := rows.Err(); err != nil { return nil, err }

    // Cache with TTL and jitter
    ttl := 2*time.Minute + time.Duration(rand.Intn(30))*time.Second
    if b, err := json.Marshal(res); err == nil {
        _ = s.rdb.SetEX(context.Background(), cacheKey, b, ttl).Err()
    }
    return res, nil
}

This example:

• Uses keyset pagination with a matching index.
• Reads from a replica via s.store.Read.
• Caches results to reduce DB load, with a small jitter to avoid stampedes.

Performance checklist

• Use context with deadlines for all DB calls.
• Tune connection pools based on workload and DB capacity.
• Validate query plans with EXPLAIN and add selective composite indexes.
• Replace OFFSET pagination with keyset pagination.
• Batch writes; use server-side COPY/Bulk APIs when available.
• Wrap multi-step operations in transactions with the lowest sufficient isolation.
• Cache frequent, read-heavy queries with Redis; implement invalidation or TTLs.
• Monitor with metrics and traces; log slow queries.
• Implement retries with backoff for transient errors and ensure idempotency.
• Test with real databases via containers; manage schema with migrations.

Final thoughts

Go gives you powerful building blocks for database access, but performance and correctness hinge on your data model and queries. Start with clean, context-aware code, prove your assumptions with query plans, and adopt patterns that scale: keyset pagination, thoughtful indexing, batching, and caching.

For NoSQL, design for your queries, not the other way around. Keep operational complexity in check with observability, timeouts, and circuit breakers. With these practices, your Go services will handle database workloads predictably and efficiently—today and as traffic grows.