Database Operations and Optimization in Go

Introduction

Efficient database operations are critical for data processing applications. This guide covers query optimization, connection management, and performance tuning in Go.

Proper database optimization ensures your applications can handle large datasets efficiently while maintaining responsiveness.

Database Connection Management

Connection Pooling

package main

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

// ConfigureConnectionPool configures database connection pool
func ConfigureConnectionPool(db *sql.DB) {
	// Set maximum number of open connections
	db.SetMaxOpenConns(25)

	// Set maximum number of idle connections
	db.SetMaxIdleConns(5)

	// Set maximum lifetime of a connection
	db.SetConnMaxLifetime(5 * time.Minute)

	// Set maximum idle time for a connection
	db.SetConnMaxIdleTime(10 * time.Minute)
}

// Example usage
func ConnectionPoolExample() {
	db, err := sql.Open("postgres", "connection_string")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	ConfigureConnectionPool(db)

	// Verify connection
	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}

	fmt.Println("Connected to database")
}

Good: Proper Database Operations

package main

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

// DatabaseManager manages database operations
type DatabaseManager struct {
	db *sql.DB
}

// NewDatabaseManager creates a new database manager
func NewDatabaseManager(db *sql.DB) *DatabaseManager {
	ConfigureConnectionPool(db)
	return &DatabaseManager{db: db}
}

// QueryWithContext executes a query with context
func (dm *DatabaseManager) QueryWithContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	// Add timeout if not already set
	if _, ok := ctx.Deadline(); !ok {
		var cancel context.CancelFunc
		ctx, cancel = context.WithTimeout(ctx, 30*time.Second)
		defer cancel()
	}

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

// QueryRow executes a query returning a single row
func (dm *DatabaseManager) QueryRow(ctx context.Context, query string, args ...interface{}) *sql.Row {
	if _, ok := ctx.Deadline(); !ok {
		var cancel context.CancelFunc
		ctx, cancel = context.WithTimeout(ctx, 30*time.Second)
		defer cancel()
	}

	return dm.db.QueryRowContext(ctx, query, args...)
}

// Exec executes a statement
func (dm *DatabaseManager) Exec(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
	if _, ok := ctx.Deadline(); !ok {
		var cancel context.CancelFunc
		ctx, cancel = context.WithTimeout(ctx, 30*time.Second)
		defer cancel()
	}

	return dm.db.ExecContext(ctx, query, args...)
}

// BatchInsert performs batch insert
func (dm *DatabaseManager) BatchInsert(ctx context.Context, query string, batchSize int, data [][]interface{}) error {
	tx, err := dm.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	stmt, err := tx.PrepareContext(ctx, query)
	if err != nil {
		return err
	}
	defer stmt.Close()

	for i, row := range data {
		if _, err := stmt.ExecContext(ctx, row...); err != nil {
			return err
		}

		// Commit in batches
		if (i+1)%batchSize == 0 {
			if err := tx.Commit(); err != nil {
				return err
			}

			tx, err = dm.db.BeginTx(ctx, nil)
			if err != nil {
				return err
			}

			stmt, err = tx.PrepareContext(ctx, query)
			if err != nil {
				return err
			}
		}
	}

	return tx.Commit()
}

// Transaction executes a transaction
func (dm *DatabaseManager) Transaction(ctx context.Context, fn func(*sql.Tx) error) error {
	tx, err := dm.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	if err := fn(tx); err != nil {
		tx.Rollback()
		return err
	}

	return tx.Commit()
}

// GetStats returns database statistics
func (dm *DatabaseManager) GetStats() sql.DBStats {
	return dm.db.Stats()
}

Bad: Improper Database Operations

package main

import (
	"database/sql"
)

// BAD: No connection pooling configuration
func BadDatabaseSetup(db *sql.DB) {
	// Uses default settings
	// No optimization
}

// BAD: No context timeout
func BadQuery(db *sql.DB, query string) {
	// No timeout - could hang indefinitely
	db.Query(query)
}

// BAD: No transaction handling
func BadBatchInsert(db *sql.DB, query string, data [][]interface{}) {
	// No transaction
	// No error handling
	for _, row := range data {
		db.Exec(query, row...)
	}
}

Problems:

No connection pooling
No timeout management
No transaction handling
No error handling

Query Optimization

Index Usage

package main

import (
	"context"
	"database/sql"
)

// CreateIndexes creates database indexes
func CreateIndexes(ctx context.Context, db *sql.DB) error {
	indexes := []string{
		"CREATE INDEX IF NOT EXISTS idx_user_email ON users(email)",
		"CREATE INDEX IF NOT EXISTS idx_order_user_id ON orders(user_id)",
		"CREATE INDEX IF NOT EXISTS idx_order_date ON orders(created_at)",
		"CREATE INDEX IF NOT EXISTS idx_product_category ON products(category_id)",
	}

	for _, index := range indexes {
		if _, err := db.ExecContext(ctx, index); err != nil {
			return err
		}
	}

	return nil
}

// QueryWithIndex uses indexed columns
func QueryWithIndex(ctx context.Context, db *sql.DB, email string) (*sql.Row, error) {
	// Uses indexed column
	query := "SELECT id, name, email FROM users WHERE email = $1"
	return db.QueryRowContext(ctx, query, email), nil
}

// BadQuery doesn't use indexes
func BadQueryNoIndex(ctx context.Context, db *sql.DB, name string) (*sql.Rows, error) {
	// Doesn't use indexed column - full table scan
	query := "SELECT id, name, email FROM users WHERE name LIKE $1"
	return db.QueryContext(ctx, query, "%"+name+"%")
}

Prepared Statements

package main

import (
	"context"
	"database/sql"
)

// PreparedStatementCache caches prepared statements
type PreparedStatementCache struct {
	db        *sql.DB
	stmts     map[string]*sql.Stmt
}

// NewPreparedStatementCache creates a new cache
func NewPreparedStatementCache(db *sql.DB) *PreparedStatementCache {
	return &PreparedStatementCache{
		db:    db,
		stmts: make(map[string]*sql.Stmt),
	}
}

// GetStatement gets or creates a prepared statement
func (psc *PreparedStatementCache) GetStatement(ctx context.Context, name, query string) (*sql.Stmt, error) {
	if stmt, exists := psc.stmts[name]; exists {
		return stmt, nil
	}

	stmt, err := psc.db.PrepareContext(ctx, query)
	if err != nil {
		return nil, err
	}

	psc.stmts[name] = stmt
	return stmt, nil
}

// Close closes all prepared statements
func (psc *PreparedStatementCache) Close() error {
	for _, stmt := range psc.stmts {
		if err := stmt.Close(); err != nil {
			return err
		}
	}
	return nil
}

Performance Monitoring

package main

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

// QueryMetrics tracks query performance
type QueryMetrics struct {
	Query       string
	Duration    time.Duration
	RowsAffected int64
	Error       error
}

// MonitoredQuery executes a query with monitoring
func MonitoredQuery(db *sql.DB, query string, args ...interface{}) (*sql.Rows, QueryMetrics) {
	start := time.Now()

	rows, err := db.Query(query, args...)

	metrics := QueryMetrics{
		Query:    query,
		Duration: time.Since(start),
		Error:    err,
	}

	return rows, metrics
}

// PrintMetrics prints query metrics
func PrintMetrics(metrics QueryMetrics) {
	fmt.Printf("Query: %s\n", metrics.Query)
	fmt.Printf("Duration: %v\n", metrics.Duration)
	if metrics.Error != nil {
		fmt.Printf("Error: %v\n", metrics.Error)
	}
}

Best Practices

1. Use Connection Pooling

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)

2. Set Timeouts

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

3. Use Prepared Statements

stmt, _ := db.Prepare("SELECT * FROM users WHERE id = ?")
defer stmt.Close()

4. Create Indexes

CREATE INDEX idx_user_email ON users(email)

Common Pitfalls

1. No Connection Pooling

Always configure connection pooling.

2. No Timeout Management

Always set timeouts for queries.

3. Missing Indexes

Create indexes on frequently queried columns.

4. No Transaction Handling

Use transactions for multi-statement operations.

Resources

Summary

Efficient database operations are essential. Key takeaways:

Configure connection pooling
Set appropriate timeouts
Use prepared statements
Create indexes strategically
Use transactions for consistency
Monitor query performance
Optimize queries regularly

By mastering database operations, you can build high-performance data applications.