Understanding Database Bottlenecks in Go Applications
Database bottlenecks occur when the interaction between your Go application and the database becomes the limiting factor in overall system performance. These bottlenecks manifest as slow query execution, connection pool exhaustion, transaction contention, or excessive network latency between the application and database server. In Go's concurrent architecture, what might seem like a minor delay can cascade into goroutine pile-ups, increased memory pressure, and degraded throughput across the entire service.
A database bottleneck isn't always obvious. Your application may handle thousands of requests per second with ease, yet a single unoptimized query running under a lock can stall dozens of goroutines waiting for a connection from the pool. The database often becomes the silent constraint that limits horizontal scaling efforts—you can add more application instances, but if all of them contend for the same database resources, performance eventually plateaus or degrades.
Common Symptoms of Database Bottlenecks
- Escalating p99 latency – While average response times look healthy, tail latency spikes indicate goroutines queuing for database connections
- Connection pool saturation – The pool's wait count metric rises steadily, meaning goroutines are blocked waiting for an available connection
- High database CPU with low application CPU – The database server works hard while your Go service sits idle waiting for results
- Transaction deadlocks and retry storms – Contention on hot rows forces repeated transaction retries, amplifying load
- Memory pressure from result sets – Large unmarshaled result sets consume application memory, triggering garbage collection pauses
Why Detecting and Resolving Database Bottlenecks Matters
🚀 Deploy your AI agent in 10 minutes
Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.
Try it free →Database bottlenecks directly impact user experience, infrastructure costs, and system reliability. An e-commerce checkout that takes three seconds instead of 200 milliseconds due to a contended inventory row translates directly to cart abandonment. A reporting endpoint that scans millions of rows without proper indexing can saturate database CPU, starving other queries and causing cascading failures across seemingly unrelated services.
From an operational standpoint, undetected database bottlenecks force premature scaling. Teams often add more database replicas or upgrade to larger instances when the real fix is a five-line query optimization or an additional index. This wastes cloud spend and increases architectural complexity. Furthermore, in Go's concurrency model—where each HTTP handler typically runs in its own goroutine—a blocked database call doesn't just affect that one request. It holds onto memory, prevents the goroutine from being garbage collected quickly, and contributes to the overall degradation curve when the system approaches saturation.
The Go-Specific Impact
Go's database/sql package provides connection pooling out of the box, but it's easy to misconfigure. The default settings include an unbounded SetMaxOpenConns in older versions, which can overwhelm the database. Even with proper configuration, the pool's FIFO behavior means that during a latency spike, all connections can become occupied by slow queries, starving fast queries that could complete in microseconds. Understanding Go's database interaction patterns is critical because the language's high concurrency capability amplifies both throughput potential and the damage caused by blocking operations.
Detection Strategies and Tools
1. Instrumenting Connection Pool Metrics
The most immediate signal of a database bottleneck is connection pool behavior. Go's database/sql.DB object exposes statistics via the Stats() method. You should expose these metrics in your monitoring system and alert on them aggressively.
package main
import (
"context"
"database/sql"
"fmt"
"net/http"
"time"
_ "github.com/lib/pq"
"github.com/prometheus/client_golang/prometheus"
"github.com/prometheus/client_golang/prometheus/promauto"
"github.com/prometheus/client_golang/prometheus/promhttp"
)
var (
maxOpenConnections = promauto.NewGauge(prometheus.GaugeOpts{
Name: "db_max_open_connections",
Help: "Maximum number of open connections to the database",
})
openConnections = promauto.NewGauge(prometheus.GaugeOpts{
Name: "db_open_connections",
Help: "Number of currently open connections",
})
inUseConnections = promauto.NewGauge(prometheus.GaugeOpts{
Name: "db_in_use_connections",
Help: "Number of connections currently in use",
})
idleConnections = promauto.NewGauge(prometheus.GaugeOpts{
Name: "db_idle_connections",
Help: "Number of idle connections",
})
waitCount = promauto.NewCounter(prometheus.CounterOpts{
Name: "db_wait_count_total",
Help: "Total number of connection wait events",
})
waitDuration = promauto.NewCounter(prometheus.CounterOpts{
Name: "db_wait_duration_seconds_total",
Help: "Total time spent waiting for connections",
})
)
func collectDBStats(db *sql.DB) {
stats := db.Stats()
maxOpenConnections.Set(float64(stats.MaxOpenConnections))
openConnections.Set(float64(stats.OpenConnections))
inUseConnections.Set(float64(stats.InUse))
idleConnections.Set(float64(stats.Idle))
waitCount.Set(float64(stats.WaitCount))
waitDuration.Set(stats.WaitDuration.Seconds())
}
func main() {
db, err := sql.Open("postgres",
"host=localhost port=5432 user=app dbname=production sslmode=disable")
if err != nil {
panic(err)
}
// Critical configuration
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(2 * time.Minute)
// Collect stats every 10 seconds
go func() {
ticker := time.NewTicker(10 * time.Second)
defer ticker.Stop()
for range ticker.C {
collectDBStats(db)
}
}()
// Expose metrics endpoint
http.Handle("/metrics", promhttp.Handler())
http.ListenAndServe(":2112", nil)
}Watch the WaitCount and WaitDuration metrics closely. A non-zero wait count means goroutines are queuing for connections—this is your early warning that the connection pool is the bottleneck. If wait duration grows beyond a few milliseconds, you have a capacity problem.
2. Query-Level Observability with Tracing
Connection pool metrics tell you that a bottleneck exists; tracing tells you which queries cause it. OpenTelemetry integration with the database calls provides end-to-end visibility into query execution time, allowing you to pinpoint exactly which SQL statements consume the most time.
package main
import (
"context"
"database/sql"
"fmt"
"net/http"
_ "github.com/lib/pq"
"go.opentelemetry.io/otel"
"go.opentelemetry.io/otel/attribute"
"go.opentelemetry.io/otel/codes"
"go.opentelemetry.io/otel/exporters/stdout/stdouttrace"
sdktrace "go.opentelemetry.io/otel/sdk/trace"
"go.opentelemetry.io/otel/trace"
)
var tracer trace.Tracer
func initTracer() {
exporter, _ := stdouttrace.New(stdouttrace.WithPrettyPrint())
provider := sdktrace.NewTracerProvider(
sdktrace.WithBatcher(exporter),
)
otel.SetTracerProvider(provider)
tracer = otel.Tracer("db-tracer")
}
// tracedQuery executes a query with OpenTelemetry instrumentation
func tracedQuery(ctx context.Context, db *sql.DB, query string, args ...interface{}) (*sql.Rows, error) {
ctx, span := tracer.Start(ctx, "SQL Query",
trace.WithAttributes(
attribute.String("db.system", "postgresql"),
attribute.String("db.statement", query),
),
)
defer span.End()
rows, err := db.QueryContext(ctx, query, args...)
if err != nil {
span.SetStatus(codes.Error, err.Error())
span.RecordError(err)
return nil, err
}
span.SetAttributes(attribute.Int("db.rows_affected", 0))
return rows, nil
}
// slowQueryDetector wraps a query and logs if it exceeds a threshold
func slowQueryDetector(ctx context.Context, db *sql.DB, threshold time.Duration,
query string, args ...interface{}) (*sql.Rows, error) {
start := time.Now()
rows, err := db.QueryContext(ctx, query, args...)
elapsed := time.Since(start)
if elapsed > threshold {
fmt.Printf("SLOW QUERY [%v]: %s | args: %v\n", elapsed, query, args)
}
return rows, err
}
func main() {
initTracer()
db, _ := sql.Open("postgres",
"host=localhost port=5432 user=app dbname=production sslmode=disable")
db.SetMaxOpenConns(25)
ctx := context.Background()
// Use traced query for observability
rows, err := tracedQuery(ctx, db,
"SELECT id, email, created_at FROM users WHERE status = $1", "active")
if err != nil {
fmt.Printf("Query failed: %v\n", err)
return
}
defer rows.Close()
for rows.Next() {
var id int
var email string
var createdAt time.Time
rows.Scan(&id, &email, &createdAt)
fmt.Printf("User: %d, %s\n", id, email)
}
}3. Database-Side Profiling
While Go-side instrumentation is essential, database-side profiling reveals the queries that consume the most CPU, execute most frequently, or cause the most logical I/O. For PostgreSQL, use the pg_stat_statements extension; for MySQL, use the performance_schema.events_statements_summary_by_digest table. Here's a Go-based collector that periodically fetches top queries from PostgreSQL:
package main
import (
"context"
"database/sql"
"fmt"
"time"
_ "github.com/lib/pq"
)
// TopQuery represents a high-impact database query
type TopQuery struct {
QueryText string
Calls int64
TotalTimeMs float64
MeanTimeMs float64
RowsReturned int64
SharedBlocksHit int64
SharedBlocksRead int64
}
// collectTopQueries fetches the most expensive queries from pg_stat_statements
func collectTopQueries(ctx context.Context, db *sql.DB, limit int) ([]TopQuery, error) {
query := `
SELECT
query,
calls,
total_exec_time::float8 AS total_time_ms,
mean_exec_time::float8 AS mean_time_ms,
rows,
shared_blks_hit,
shared_blks_read
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
AND query NOT LIKE '%pg_stat_%'
ORDER BY total_exec_time DESC
LIMIT $1`
rows, err := db.QueryContext(ctx, query, limit)
if err != nil {
return nil, fmt.Errorf("failed to query pg_stat_statements: %w", err)
}
defer rows.Close()
var results []TopQuery
for rows.Next() {
var tq TopQuery
err := rows.Scan(
&tq.QueryText,
&tq.Calls,
&tq.TotalTimeMs,
&tq.MeanTimeMs,
&tq.RowsReturned,
&tq.SharedBlocksHit,
&tq.SharedBlocksRead,
)
if err != nil {
return nil, fmt.Errorf("scan error: %w", err)
}
results = append(results, tq)
}
return results, rows.Err()
}
func main() {
db, err := sql.Open("postgres",
"host=localhost port=5432 user=app dbname=production sslmode=disable")
if err != nil {
panic(err)
}
defer db.Close()
ctx := context.Background()
// Run every 30 seconds
ticker := time.NewTicker(30 * time.Second)
defer ticker.Stop()
for {
select {
case <-ticker.C:
topQueries, err := collectTopQueries(ctx, db, 10)
if err != nil {
fmt.Printf("Error collecting top queries: %v\n", err)
continue
}
fmt.Println("\n=== Top 10 Most Expensive Queries ===")
for i, tq := range topQueries {
fmt.Printf("%d. Calls: %d | Total Time: %.2fms | Mean: %.2fms | Query: %.100s...\n",
i+1, tq.Calls, tq.TotalTimeMs, tq.MeanTimeMs, tq.QueryText)
}
}
}
}Pay attention to the ratio of shared_blks_read to shared_blks_hit. A high read ratio indicates queries that bypass the database buffer cache and go to disk—these are prime candidates for index optimization.
Resolution Techniques
1. Connection Pool Tuning
Misconfigured connection pools are the most common and easiest-to-fix bottleneck. The key parameters interact in subtle ways:
package main
import (
"database/sql"
"fmt"
"math"
"time"
)
// optimalPoolConfig calculates reasonable pool settings based on workload
func optimalPoolConfig(
expectedConcurrentQueries int,
avgQueryDuration time.Duration,
databaseMaxConnections int,
) (maxOpen, maxIdle int, maxLifetime time.Duration) {
// Little's Law: L = λ × W
// L = connections needed, λ = query arrival rate, W = average query duration
// For safety, add 30% buffer
requiredConnections := int(math.Ceil(float64(expectedConcurrentQueries) * 1.3))
// Never exceed 80% of database's max connections
maxDatabaseConnections := int(float64(databaseMaxConnections) * 0.8)
if requiredConnections < maxDatabaseConnections {
maxOpen = requiredConnections
} else {
maxOpen = maxDatabaseConnections
fmt.Printf("WARNING: Required connections (%d) exceeds 80%% of database limit (%d)\n",
requiredConnections, maxDatabaseConnections)
}
// Idle connections should be ~40% of max open for burst absorption
maxIdle = int(float64(maxOpen) * 0.4)
// Recycle connections every 30 minutes to distribute across replicas
maxLifetime = 30 * time.Minute
return maxOpen, maxIdle, maxLifetime
}
func main() {
// Example: 100 concurrent users, queries take 50ms avg, DB allows 200 connections
maxOpen, maxIdle, maxLifetime := optimalPoolConfig(100, 50*time.Millisecond, 200)
fmt.Printf("Recommended settings:\n")
fmt.Printf(" MaxOpenConns: %d\n", maxOpen)
fmt.Printf(" MaxIdleConns: %d\n", maxIdle)
fmt.Printf(" ConnMaxLifetime: %v\n", maxLifetime)
db, _ := sql.Open("postgres", "host=localhost...")
db.SetMaxOpenConns(maxOpen)
db.SetMaxIdleConns(maxIdle)
db.SetConnMaxLifetime(maxLifetime)
db.SetConnMaxIdleTime(5 * time.Minute) // Close idle connections after 5 min
}A common mistake is setting SetMaxOpenConns too high relative to database capacity. Each connection consumes a database backend process and memory (typically 2-10 MB per connection on PostgreSQL). If your Go service has 50 instances each with 100 max open connections, that's 5,000 potential connections hitting a database that may only support 500. Always calculate the global connection ceiling across all instances.
2. Query Optimization with Prepared Statements
Prepared statements reduce parse time and protect against SQL injection. In Go, you can prepare once and reuse across many executions:
package main
import (
"context"
"database/sql"
"fmt"
"sync"
"time"
)
// PreparedStatementCache holds pre-compiled statements
type PreparedStatementCache struct {
mu sync.RWMutex
statements map[string]*sql.Stmt
db *sql.DB
}
func NewPreparedStatementCache(db *sql.DB) *PreparedStatementCache {
return &PreparedStatementCache{
db: db,
statements: make(map[string]*sql.Stmt),
}
}
// GetOrPrepare returns an existing prepared statement or creates one
func (c *PreparedStatementCache) GetOrPrepare(ctx context.Context, key, query string) (*sql.Stmt, error) {
// Try read lock first for fast path
c.mu.RLock()
stmt, exists := c.statements[key]
c.mu.RUnlock()
if exists {
return stmt, nil
}
// Slow path: prepare and store
c.mu.Lock()
defer c.mu.Unlock()
// Double-check after acquiring write lock
if stmt, exists := c.statements[key]; exists {
return stmt, nil
}
stmt, err := c.db.PrepareContext(ctx, query)
if err != nil {
return nil, fmt.Errorf("failed to prepare %s: %w", key, err)
}
c.statements[key] = stmt
return stmt, nil
}
// CloseAll cleans up all prepared statements
func (c *PreparedStatementCache) CloseAll() {
c.mu.Lock()
defer c.mu.Unlock()
for key, stmt := range c.statements {
stmt.Close()
delete(c.statements, key)
}
}
// Example usage: batch insert with prepared statement
func batchInsertUsers(ctx context.Context, cache *PreparedStatementCache, users []User) error {
stmt, err := cache.GetOrPrepare(ctx, "insert_user",
"INSERT INTO users (name, email, created_at) VALUES ($1, $2, $3)")
if err != nil {
return err
}
now := time.Now()
for _, user := range users {
_, err := stmt.ExecContext(ctx, user.Name, user.Email, now)
if err != nil {
return fmt.Errorf("insert failed for %s: %w", user.Email, err)
}
}
return nil
}
type User struct {
Name string
Email string
}However, be cautious with prepared statements in environments with many distinct query patterns. Each prepared statement consumes database resources. If your application generates thousands of unique queries (common in ORM-heavy code), you may want to limit prepared statement usage to the most frequent queries only.
3. Implementing Read/Write Splitting
For read-heavy workloads, directing queries to read replicas relieves pressure on the primary database. Here's a pattern that routes queries based on context:
package main
import (
"context"
"database/sql"
"fmt"
"strings"
)
// DBCluster holds primary and replica connections
type DBCluster struct {
Primary *sql.DB
Replicas []*sql.DB
rrCounter uint64 // Round-robin counter for replica selection
}
// readContextKey is used to force read-only queries to replicas
type readOnlyContextKey struct{}
// WithReadOnly returns a context that routes queries to replicas
func WithReadOnly(ctx context.Context) context.Context {
return context.WithValue(ctx, readOnlyContextKey{}, true)
}
// IsReadOnly checks if the context requests read-only routing
func IsReadOnly(ctx context.Context) bool {
v, ok := ctx.Value(readOnlyContextKey{}).(bool)
return ok && v
}
// SelectDB chooses the appropriate database based on context and query type
func (c *DBCluster) SelectDB(ctx context.Context, query string) *sql.DB {
// If context explicitly requests read-only, use replica
if IsReadOnly(ctx) {
return c.selectReplica()
}
// Heuristic: SELECT queries without FOR UPDATE can go to replicas
upperQuery := strings.ToUpper(strings.TrimSpace(query))
if strings.HasPrefix(upperQuery, "SELECT") &&
!strings.Contains(upperQuery, "FOR UPDATE") &&
!strings.Contains(upperQuery, "FOR SHARE") {
return c.selectReplica()
}
// Everything else goes to primary
return c.Primary
}
func (c *DBCluster) selectReplica() *sql.DB {
if len(c.Replicas) == 0 {
return c.Primary // Fallback to primary if no replicas
}
// Simple round-robin; use atomic increment in production
idx := int(c.rrCounter % uint64(len(c.Replicas)))
c.rrCounter++
return c.Replicas[idx]
}
// QueryContext routes the query to the appropriate database
func (c *DBCluster) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
db := c.SelectDB(ctx, query)
return db.QueryContext(ctx, query, args...)
}
// ExecContext always routes to primary for write operations
func (c *DBCluster) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
return c.Primary.ExecContext(ctx, query, args...)
}
func main() {
primary, _ := sql.Open("postgres", "host=primary.example.com...")
replica1, _ := sql.Open("postgres", "host=replica1.example.com...")
replica2, _ := sql.Open("postgres", "host=replica2.example.com...")
cluster := &DBCluster{
Primary: primary,
Replicas: []*sql.DB{replica1, replica2},
}
ctx := context.Background()
// This SELECT will hit a replica
rows, err := cluster.QueryContext(ctx,
"SELECT id, name FROM products WHERE category = $1", "electronics")
if err != nil {
fmt.Printf("Query error: %v\n", err)
return
}
defer rows.Close()
// Force a specific query to use replica even if it looks like a write
readCtx := WithReadOnly(ctx)
rows2, err := cluster.QueryContext(readCtx,
"SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", 12345)
if err != nil {
// This will fail because FOR UPDATE requires primary
fmt.Printf("Expected error on replica with FOR UPDATE: %v\n", err)
}
defer rows2.Close()
}Be aware of replication lag when implementing read/write splitting. If your application writes data and immediately reads it back, that read must go to the primary. Use context markers or explicit API flags to indicate "read-your-own-writes" consistency requirements.
4. Strategic Indexing and Query Restructuring
Sometimes the bottleneck isn't the Go code at all—it's missing database indexes. Use the profiling data from pg_stat_statements to identify slow queries, then analyze their execution plans. Here's how to automate EXPLAIN collection for slow queries:
package main
import (
"context"
"database/sql"
"fmt"
"time"
)
// ExplainPlan captures the execution plan for a query
type ExplainPlan struct {
NodeType string
RelationName string
Cost float64
Rows int64
ActualTime float64
}
// analyzeSlowQuery runs EXPLAIN ANALYZE on a query that exceeds the threshold
func analyzeSlowQuery(ctx context.Context, db *sql.DB, query string, args ...interface{}) ([]ExplainPlan, error) {
// Wrap the query with EXPLAIN ANALYZE
explainQuery := "EXPLAIN (ANALYZE, COSTS, BUFFERS, FORMAT JSON) " + query
rows, err := db.QueryContext(ctx, explainQuery, args...)
if err != nil {
return nil, fmt.Errorf("EXPLAIN failed: %w", err)
}
defer rows.Close()
var planJSON string
for rows.Next() {
rows.Scan(&planJSON)
}
// In production, parse the JSON properly
// For brevity, we just log it
fmt.Printf("Execution Plan:\n%s\n", planJSON)
return nil, nil // Simplified; real implementation would parse JSON
}
// indexAdvisor suggests missing indexes based on slow query patterns
func indexAdvisor(query string, explainPlans []ExplainPlan) []string {
suggestions := []string{}
// Heuristic: look for sequential scans on large tables
for _, node := range explainPlans {
if node.NodeType == "Seq Scan" && node.Rows > 10000 {
// Suggest an index (simplified—real implementation would parse WHERE clauses)
suggestions = append(suggestions,
fmt.Sprintf("Consider adding an index on %s for query: %.100s",
node.RelationName, query))
}
}
return suggestions
}
func main() {
db, _ := sql.Open("postgres", "host=localhost...")
ctx := context.Background()
// Identify slow queries first (using previous techniques)
slowQuery := "SELECT * FROM orders WHERE status = $1 AND created_at > $2"
threshold := 100 * time.Millisecond
// Time the query
start := time.Now()
rows, err := db.QueryContext(ctx, slowQuery, "pending", time.Now().AddDate(0, -1, 0))
elapsed := time.Since(start)
rows.Close()
if elapsed > threshold || err != nil {
fmt.Printf("Query took %v - analyzing...\n", elapsed)
analyzeSlowQuery(ctx, db, slowQuery, "pending", time.Now().AddDate(0, -1, 0))
}
}5. Implementing Caching Layers
For read-heavy workloads where data changes infrequently, a cache in front of the database dramatically reduces load. Here's a pattern using an in-memory cache with periodic refresh:
package main
import (
"context"
"database/sql"
"fmt"
"sync"
"time"
)
// CacheEntry holds cached data with metadata
type CacheEntry struct {
Data interface{}
ExpiresAt time.Time
TTL time.Duration
}
// QueryCache provides a thread-safe cache for database query results
type QueryCache struct {
mu sync.RWMutex
store map[string]*CacheEntry
}
func NewQueryCache() *QueryCache {
return &QueryCache{
store: make(map[string]*CacheEntry),
}
}
// Get retrieves cached data if still valid
func (c *QueryCache) Get(key string) (interface{}, bool) {
c.mu.RLock()
defer c.mu.RUnlock()
entry, exists := c.store[key]
if !exists {
return nil, false
}
if time.Now().After(entry.ExpiresAt) {
return nil, false
}
return entry.Data, true
}
// Set stores data in the cache with a TTL
func (c *QueryCache) Set(key string, data interface{}, ttl time.Duration) {
c.mu.Lock()
defer c.mu.Unlock()
c.store[key] = &CacheEntry{
Data: data,
ExpiresAt: time.Now().Add(ttl),
TTL: ttl,
}
}
// Invalidate removes a specific cache entry
func (c *QueryCache) Invalidate(key string) {
c.mu.Lock()
defer c.mu.Unlock()
delete(c.store, key)
}
// PurgeExpired removes all expired entries
func (c *QueryCache) PurgeExpired() {
c.mu.Lock()
defer c.mu.Unlock()
now := time.Now()
for key, entry := range c.store {
if now.After(entry.ExpiresAt) {
delete(c.store, key)
}
}
}
// CachedQueryService combines database access with caching
type CachedQueryService struct {
db *sql.DB
cache *QueryCache
}
// GetUserByID fetches a user, using cache when possible
func (s *CachedQueryService) GetUserByID(ctx context.Context, userID int) (*User, error) {
cacheKey := fmt.Sprintf("user:%d", userID)
// Try cache first
if cached, ok := s.cache.Get(cacheKey); ok {
return cached.(*User), nil
}
// Cache miss - query database
var user User
err := s.db.QueryRowContext(ctx,
"SELECT id, name, email FROM users WHERE id = $1", userID).
Scan(&user.ID, &user.Name, &user.Email)
if err != nil {
return nil, err
}
// Store in cache with 5-minute TTL
s.cache.Set(cacheKey, &user, 5*time.Minute)
return &user, nil
}
// InvalidateUser removes a user from cache after an update
func (s *CachedQueryService) InvalidateUser(userID int) {
cacheKey := fmt.Sprintf("user:%d", userID)
s.cache.Invalidate(cacheKey)
}
type User struct {
ID int
Name string
Email string
}
func main() {
db, _ := sql.Open("postgres", "host=localhost...")
cache := NewQueryCache()
// Background purging of expired entries every minute
go func() {
ticker := time.NewTicker(1 * time.Minute)
defer ticker.Stop()
for range ticker.C {
cache.PurgeExpired()
}
}()
service := &CachedQueryService{db: db, cache: cache}
ctx := context.Background()
user, err := service.GetUserByID(ctx, 42)
if err != nil {
fmt.Printf("Error: %v\n", err)
return
}
fmt.Printf("User: %s <%s>\n", user.Name, user.Email)
// After updating the user in the database
service.InvalidateUser(42)
}6. Transaction and Lock Contention Management
Transaction contention is a subtle but devastating bottleneck. When multiple goroutines compete for locks on the same rows, throughput collapses. The solution often involves keeping transactions short, using optimistic concurrency, or restructuring data access patterns.
package main
import (
"context"
"database/sql"
"fmt"
"time"
)
// OptimisticLockingUpdate performs an update with version checking
// This avoids holding database locks while the user thinks
func OptimisticLockingUpdate(ctx context.Context, db *sql.DB,
productID int, expectedVersion int, newQuantity int) error {
// Start transaction
tx, err := db.BeginTx(ctx, &sql.TxOptions{
Isolation: sql.LevelReadCommitted,
ReadOnly: false,
})
if err != nil {
return fmt.Errorf("begin tx: %w", err)
}
defer tx.Rollback() // Will be no-op after Commit
// Attempt update only if version matches
result, err := tx.ExecContext(ctx,
`UPDATE inventory
SET quantity = $1, version = version + 1, updated_at = $2
WHERE product_id = $3 AND version = $4`,
newQuantity, time.Now(), productID, expectedVersion)
if err != nil {
return fmt.Errorf("update failed: %w", err)
}
rowsAffected, err := result.RowsAffected()
if err != nil {
return fmt.Errorf("rows affected error: %w", err)
}
if rowsAffected == 0 {
// Version mismatch - someone else updated the row
return fmt.Errorf("concurrent modification detected for product %d (version %d)",
productID, expectedVersion)
}
return tx.Commit()
}
// BulkTransfer performs multiple account transfers in a single transaction
// This reduces round trips but must be short to avoid lock contention
func BulkTransfer(ctx context.Context, db *sql.DB, transfers []Transfer) error {
tx, err := db.BeginTx(ctx, &sql.TxOptions{
Isolation: sql.LevelSerializable,
})
if err != nil {
return err
}
defer tx.Rollback()
// Prepare statements for reuse within transaction
debitStmt, err := tx.PrepareContext(ctx,
"UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1")
if err != nil {
return fmt.Errorf("prepare debit: %w", err)
}
defer debitStmt.Close()
creditStmt, err := tx.PrepareContext(ctx,
"UPDATE accounts SET balance = balance + $1 WHERE id = $2")
if err != nil {
return fmt.Errorf("prepare credit: %w", err)
}
defer creditStmt.Close()
// Sort transfers by account ID to prevent deadlocks
// (both transactions acquire locks in the same order)
for _, t := range transfers {
// Debit source account
result, err := debitStmt.ExecContext(ctx, t.Amount, t.FromAccount)
if err != nil {
return fmt.Errorf("debit %d: %w", t.FromAccount, err)
}
affected, _ := result.RowsAffected()
if affected == 0 {
return fmt.Errorf("insufficient funds for account %d", t.FromAccount)
}
// Credit destination account
_, err = creditStmt.ExecContext(ctx, t.Amount, t.ToAccount)
if err != nil {
return fmt.Errorf("credit %d: %w", t.ToAccount, err)
}
}
return tx.Commit()
}
type Transfer struct {
FromAccount int
ToAccount int
Amount float64
}
For hot-row contention (like an inventory counter that gets updated hundreds of times per second), consider moving to a queue-based approach where updates are batched, or use database-specific features like PostgreSQL advisory locks or queue tables to serialize contentious operations without blocking all queries.
Best Practices for Go Database Performance
1. Always Set Connection Pool Limits Explicitly
Never rely on defaults. Always call SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime immediately after sql.Open. Document the rationale