Performance Optimization
Learning Objectives: By the end of this lesson, you will be able to:
- Identify and resolve the N+1 query problem
- Use Preloading and Joins effectively
- Implement batch operations for bulk data processing
- Configure connection pooling for high-performance applications
- Create and manage database indexes strategically
- Profile queries and identify performance bottlenecks
- Apply caching strategies with GORM
1. Welcome and Introduction (5 minutes)
Icebreaker Activity
Have you ever noticed an application that works perfectly with 100 records but crawls to a halt with 10,000? Performance optimization is the difference between an application that delights users and one that frustrates them. Today, we’ll learn techniques that can make your GORM-based applications 10x or even 100x faster.
Lesson Overview
- Duration: 150 minutes
- Prerequisites: Lessons 1-7 completed, understanding of associations
- Materials Needed: Working GORM setup with sample data (thousands of records)
2. Learning Objectives and Session Outline (5 minutes)
What We’ll Cover Today:
- N+1 Query Problem - Understanding and resolving the most common performance issue
- Preloading Strategies - Eager loading associated data efficiently
- Batch Operations - Processing large datasets without memory issues
- Connection Pooling - Optimizing database connections
- Indexing Strategies - Speeding up queries with proper indexes
- Query Profiling - Finding and fixing slow queries
- Caching - Reducing database load with strategic caching
Success Criteria:
By the end of this session, you should be able to identify performance bottlenecks and apply appropriate optimization techniques.
3. Foundation Knowledge (15-20 minutes)
Core Concepts
The N+1 Query Problem
The N+1 problem occurs when you execute one query to fetch N records, then N additional queries to fetch related data for each record. This is the most common performance killer in ORM-based applications.
Real-World Analogy: Imagine ordering food for 100 people. The N+1 approach is like making 100 separate trips to the kitchen, one for each person. The optimized approach (preloading) is making one trip to get all 100 meals at once.
Why This Matters: A page that works fine in development with 10 users can become unusably slow in production with 1000 users, simply because of N+1 queries.
Performance Metrics to Track
- Query Count: Number of database queries per request
- Query Duration: Time spent in database
- Connection Usage: Number of active/idle connections
- Memory Usage: RAM consumed by query results
- Throughput: Requests handled per second
Key Terminology
- Eager Loading: Loading associated records in advance
- Lazy Loading: Loading associated records on demand (causes N+1)
- Batch Size: Number of records processed together
- Connection Pool: Reusable database connections
- Query Plan: Database’s strategy for executing a query
- Index Scan vs Table Scan: Using index vs reading entire table
4. Detailed Explanation and Examples (25-30 minutes)
Understanding Query Performance
┌────────────────────────────────────────────────────────────────┐
│ Performance Optimization Layers │
├────────────────────────────────────────────────────────────────┤
│ │
│ Application Layer │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ • Batch processing │ │
│ │ • Caching (Redis, in-memory) │ │
│ │ • Pagination │ │
│ │ • Field selection (only fetch needed columns) │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
│ ORM Layer (GORM) │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ • Preloading (avoid N+1) │ │
│ │ • Joins instead of separate queries │ │
│ │ • Prepared statements │ │
│ │ • Connection pooling │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
│ Database Layer │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ • Indexes on queried columns │ │
│ │ • Query optimization (EXPLAIN) │ │
│ │ • Table partitioning │ │
│ │ • Database configuration tuning │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────┘
Common Misconceptions
-
Misconception: “Adding more indexes always helps” Reality: Indexes speed up reads but slow down writes. Too many indexes hurt insert/update performance.
-
Misconception: “Preload always loads everything” Reality: Preload can be conditional and filtered to load only what you need.
-
Misconception: “Raw SQL is always faster than GORM” Reality: GORM generates efficient SQL. The main overhead is in reflection, which is minimal for most use cases.
5. Live Coding Demonstration (20-25 minutes)
Some illustrative price/total fields below are float64. Avoid floating-point arithmetic for production money: use integer minor units or an exact decimal representation even if the SQL column is DECIMAL.
Example 1: The N+1 Problem and Solutions
package main
import (
"fmt"
"time"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
type Author struct {
ID uint `gorm:"primaryKey"`
Name string `gorm:"size:100"`
Books []Book
}
type Book struct {
ID uint `gorm:"primaryKey"`
Title string `gorm:"size:200"`
AuthorID uint `gorm:"index"`
Author Author
Reviews []Review
}
type Review struct {
ID uint `gorm:"primaryKey"`
BookID uint `gorm:"index"`
Content string `gorm:"type:text"`
Rating int
}
func seedData(db *gorm.DB) {
// Create 100 authors with 10 books each, 5 reviews per book
for i := 1; i <= 100; i++ {
author := Author{Name: fmt.Sprintf("Author %d", i)}
db.Create(&author)
for j := 1; j <= 10; j++ {
book := Book{
Title: fmt.Sprintf("Book %d by Author %d", j, i),
AuthorID: author.ID,
}
db.Create(&book)
for k := 1; k <= 5; k++ {
db.Create(&Review{
BookID: book.ID,
Content: fmt.Sprintf("Review %d", k),
Rating: k,
})
}
}
}
}
func main() {
// Enable logging to see all queries
db, _ := gorm.Open(sqlite.Open("perf.db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
db.AutoMigrate(&Author{}, &Book{}, &Review{})
// Uncomment to seed data
// seedData(db)
// ===== N+1 PROBLEM DEMONSTRATION =====
fmt.Println("\n=== N+1 Problem (BAD) ===")
start := time.Now()
var books []Book
db.Find(&books) // 1 query for books
for _, book := range books {
var author Author
db.First(&author, book.AuthorID) // N queries for authors!
fmt.Printf("Book: %s by %s\n", book.Title, author.Name)
}
// Total: 1 + N queries
fmt.Printf("Time: %v\n", time.Since(start))
// ===== SOLUTION 1: PRELOAD =====
fmt.Println("\n=== Solution 1: Preload (GOOD) ===")
start = time.Now()
var booksWithAuthor []Book
db.Preload("Author").Find(&booksWithAuthor) // 2 queries total
for _, book := range booksWithAuthor {
fmt.Printf("Book: %s by %s\n", book.Title, book.Author.Name)
}
// Total: 2 queries (books + authors)
fmt.Printf("Time: %v\n", time.Since(start))
// ===== SOLUTION 2: JOINS =====
fmt.Println("\n=== Solution 2: Joins (BEST for filtering) ===")
start = time.Now()
var booksJoined []Book
db.Joins("Author").Find(&booksJoined) // 1 query with JOIN
for _, book := range booksJoined {
fmt.Printf("Book: %s by %s\n", book.Title, book.Author.Name)
}
// Total: 1 query
fmt.Printf("Time: %v\n", time.Since(start))
// ===== NESTED PRELOAD =====
fmt.Println("\n=== Nested Preload ===")
start = time.Now()
var booksWithReviews []Book
db.Preload("Author").
Preload("Reviews").
Find(&booksWithReviews)
// 3 queries: books, authors, reviews
for _, book := range booksWithReviews[:3] { // First 3 only
fmt.Printf("Book: %s, Reviews: %d\n", book.Title, len(book.Reviews))
}
fmt.Printf("Time: %v\n", time.Since(start))
// ===== CONDITIONAL PRELOAD =====
fmt.Println("\n=== Conditional Preload ===")
// Only preload 5-star reviews
var booksFiltered []Book
db.Preload("Reviews", "rating = ?", 5).
Preload("Author").
Find(&booksFiltered)
// Preload with custom query
db.Preload("Reviews", func(db *gorm.DB) *gorm.DB {
return db.Order("rating DESC").Limit(3)
}).Find(&booksFiltered)
// ===== PRELOAD ALL =====
// Useful for deeply nested associations
db.Preload(clause.Associations).Find(&booksFiltered)
}
Example 2: Batch Operations
package main
import (
"fmt"
"time"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/clause"
)
type Product struct {
ID uint `gorm:"primaryKey"`
SKU string `gorm:"size:50;uniqueIndex"`
Name string `gorm:"size:200"`
Price float64 `gorm:"type:decimal(10,2)"`
Stock int
CreatedAt time.Time
}
func main() {
db, _ := gorm.Open(sqlite.Open("batch.db"), &gorm.Config{})
db.AutoMigrate(&Product{})
// ===== BATCH CREATE =====
fmt.Println("=== Batch Create ===")
// Generate 10,000 products
products := make([]Product, 10000)
for i := range products {
products[i] = Product{
SKU: fmt.Sprintf("SKU-%05d", i),
Name: fmt.Sprintf("Product %d", i),
Price: float64(i) * 0.99,
Stock: i * 10,
}
}
// BAD: Create one by one
start := time.Now()
for _, p := range products[:100] {
db.Create(&p) // 100 individual inserts
}
fmt.Printf("Individual creates (100): %v\n", time.Since(start))
// GOOD: Create in batches
start = time.Now()
db.CreateInBatches(products, 1000) // Batch of 1000
fmt.Printf("Batch creates (10000): %v\n", time.Since(start))
// ===== BATCH UPDATE =====
fmt.Println("\n=== Batch Update ===")
// BAD: Update one by one
start = time.Now()
var productsToUpdate []Product
db.Limit(100).Find(&productsToUpdate)
for _, p := range productsToUpdate {
db.Model(&p).Update("price", p.Price*1.1)
}
fmt.Printf("Individual updates: %v\n", time.Since(start))
// GOOD: Single batch update
start = time.Now()
db.Model(&Product{}).Where("id <= ?", 100).
Update("price", gorm.Expr("price * ?", 1.1))
fmt.Printf("Batch update: %v\n", time.Since(start))
// ===== BATCH DELETE =====
fmt.Println("\n=== Batch Delete ===")
// Delete in batches to avoid locking
batchSize := 1000
for {
result := db.Where("stock = ?", 0).Limit(batchSize).Delete(&Product{})
if result.RowsAffected == 0 {
break
}
fmt.Printf("Deleted %d records\n", result.RowsAffected)
}
// ===== BATCH FIND WITH ITERATION =====
fmt.Println("\n=== Batch Find ===")
// BAD: Load all into memory
// var allProducts []Product
// db.Find(&allProducts) // Could be millions!
// GOOD: Use FindInBatches
var processedCount int64
db.Model(&Product{}).FindInBatches(&[]Product{}, 100, func(tx *gorm.DB, batch int) error {
products := tx.Statement.Dest.(*[]Product)
for _, p := range *products {
// Process each product
_ = p.Name
processedCount++
}
fmt.Printf("Processed batch %d\n", batch)
return nil
})
fmt.Printf("Total processed: %d\n", processedCount)
// ===== ROWS ITERATION (Most memory efficient) =====
fmt.Println("\n=== Rows Iteration ===")
rows, _ := db.Model(&Product{}).Where("price > ?", 100).Rows()
defer rows.Close()
var count int
for rows.Next() {
var product Product
db.ScanRows(rows, &product)
// Process one at a time - minimal memory
count++
}
fmt.Printf("Processed %d rows\n", count)
// ===== UPSERT IN BATCHES =====
fmt.Println("\n=== Batch Upsert ===")
productsToUpsert := []Product{
{SKU: "SKU-00001", Name: "Updated Product 1", Price: 199.99},
{SKU: "SKU-00002", Name: "Updated Product 2", Price: 299.99},
{SKU: "NEW-00001", Name: "New Product", Price: 99.99},
}
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "sku"}},
DoUpdates: clause.AssignmentColumns([]string{"name", "price"}),
}).CreateInBatches(productsToUpsert, 100)
}
Example 3: Connection Pooling
package main
import (
"context"
"database/sql"
"fmt"
"sync"
"time"
"gorm.io/driver/postgres"
"gorm.io/gorm"
)
type Order struct {
ID uint `gorm:"primaryKey"`
OrderNum string `gorm:"size:20;uniqueIndex"`
Total float64 `gorm:"type:decimal(12,2)"`
Status string `gorm:"size:20"`
CreatedAt time.Time
}
// DatabaseConfig for production settings
type DatabaseConfig struct {
DSN string
MaxIdleConns int
MaxOpenConns int
ConnMaxLifetime time.Duration
ConnMaxIdleTime time.Duration
}
func NewDatabase(config DatabaseConfig) (*gorm.DB, error) {
db, err := gorm.Open(postgres.Open(config.DSN), &gorm.Config{
PrepareStmt: true, // Cache prepared statements
})
if err != nil {
return nil, err
}
sqlDB, err := db.DB()
if err != nil {
return nil, err
}
// Connection pool configuration
sqlDB.SetMaxIdleConns(config.MaxIdleConns) // Idle connections to keep
sqlDB.SetMaxOpenConns(config.MaxOpenConns) // Max concurrent connections
sqlDB.SetConnMaxLifetime(config.ConnMaxLifetime) // Max time a connection can be reused
sqlDB.SetConnMaxIdleTime(config.ConnMaxIdleTime) // Max time connection can be idle
return db, nil
}
// MonitorConnections logs connection pool stats
func MonitorConnections(db *gorm.DB, interval time.Duration) {
sqlDB, _ := db.DB()
go func() {
for {
stats := sqlDB.Stats()
fmt.Printf("Connections: Open=%d, InUse=%d, Idle=%d, WaitCount=%d\n",
stats.OpenConnections,
stats.InUse,
stats.Idle,
stats.WaitCount,
)
time.Sleep(interval)
}
}()
}
// SimulateLoad demonstrates connection pool behavior
func SimulateLoad(db *gorm.DB, concurrency int) {
var wg sync.WaitGroup
for i := 0; i < concurrency; i++ {
wg.Add(1)
go func(id int) {
defer wg.Done()
// Simulate database operations
for j := 0; j < 10; j++ {
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
var orders []Order
db.WithContext(ctx).Limit(10).Find(&orders)
cancel()
time.Sleep(10 * time.Millisecond)
}
}(i)
}
wg.Wait()
}
func main() {
// Production configuration
config := DatabaseConfig{
DSN: "host=localhost user=postgres password=secret dbname=test",
MaxIdleConns: 10, // Keep 10 connections ready
MaxOpenConns: 100, // Allow up to 100 concurrent connections
ConnMaxLifetime: time.Hour, // Reconnect after 1 hour
ConnMaxIdleTime: 10 * time.Minute, // Close idle connections after 10 minutes
}
db, err := NewDatabase(config)
if err != nil {
panic(err)
}
// Monitor connection pool
MonitorConnections(db, 5*time.Second)
// Simulate concurrent load
fmt.Println("Starting load simulation...")
SimulateLoad(db, 50)
fmt.Println("Load simulation complete")
// Allow time to see final stats
time.Sleep(10 * time.Second)
}
Example 4: Indexing Strategies
package main
import (
"fmt"
"time"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
// User with strategic indexes
type User struct {
ID uint `gorm:"primaryKey"`
// Single column indexes for common lookups
Email string `gorm:"size:255;uniqueIndex"`
Username string `gorm:"size:50;uniqueIndex"`
// Index for filtering
Status string `gorm:"size:20;index"`
Role string `gorm:"size:20;index"`
// Composite index for combined queries
// Index on (status, role) for WHERE status=? AND role=?
// Named composite index
CreatedAt time.Time `gorm:"index:idx_user_created_status,priority:1"`
DeletedAt gorm.DeletedAt `gorm:"index:idx_user_created_status,priority:2"`
}
// Order with indexes on foreign keys and common queries
type Order struct {
ID uint `gorm:"primaryKey"`
// Always index foreign keys
UserID uint `gorm:"index"`
// Composite index for date range queries
Status string `gorm:"size:20;index:idx_order_status_date"`
CreatedAt time.Time `gorm:"index:idx_order_status_date"`
// Index for amount-based queries
Total float64 `gorm:"type:decimal(12,2);index"`
}
// Product with full-text search consideration
type Product struct {
ID uint `gorm:"primaryKey"`
SKU string `gorm:"size:50;uniqueIndex"`
Name string `gorm:"size:200;index"` // For LIKE queries (limited help)
CategoryID uint `gorm:"index"`
BrandID uint `gorm:"index"`
// Composite index for category+brand filtering
// Also covers queries on just category (prefix)
_ struct{} `gorm:"index:idx_product_cat_brand,priority:1" gorm:"column:category_id"`
_ struct{} `gorm:"index:idx_product_cat_brand,priority:2" gorm:"column:brand_id"`
// Index for sorting and range queries
Price float64 `gorm:"type:decimal(10,2);index"`
Stock int `gorm:"index"`
IsActive bool `gorm:"index"`
}
// IndexAnalyzer helps analyze query performance
type IndexAnalyzer struct {
db *gorm.DB
}
func (a *IndexAnalyzer) ExplainQuery(query string, args ...interface{}) {
var result []map[string]interface{}
// SQLite uses EXPLAIN QUERY PLAN
// PostgreSQL/MySQL use EXPLAIN ANALYZE
explainQuery := "EXPLAIN QUERY PLAN " + query
a.db.Raw(explainQuery, args...).Scan(&result)
fmt.Printf("Query Plan for: %s\n", query)
for _, row := range result {
fmt.Printf(" %v\n", row)
}
}
func main() {
db, _ := gorm.Open(sqlite.Open("index.db"), &gorm.Config{})
db.AutoMigrate(&User{}, &Order{}, &Product{})
analyzer := &IndexAnalyzer{db: db}
// ===== QUERY ANALYSIS =====
fmt.Println("=== Index Analysis ===\n")
// Good: Uses unique index
analyzer.ExplainQuery("SELECT * FROM users WHERE email = ?", "test@example.com")
// Good: Uses composite index
analyzer.ExplainQuery("SELECT * FROM orders WHERE status = ? AND created_at > ?",
"pending", time.Now().AddDate(0, 0, -7))
// Bad: Full table scan (no index on description)
// analyzer.ExplainQuery("SELECT * FROM products WHERE description LIKE ?", "%keyword%")
// ===== CREATING INDEXES PROGRAMMATICALLY =====
migrator := db.Migrator()
// Create single column index
if !migrator.HasIndex(&Product{}, "idx_product_price") {
migrator.CreateIndex(&Product{}, "idx_product_price")
}
// Create composite index
// migrator.CreateIndex(&Order{}, "idx_user_status", "UserID", "Status")
// Drop unused index
// migrator.DropIndex(&Product{}, "idx_unused")
// ===== INDEX HINTS (MySQL specific) =====
// db.Clauses(hints.UseIndex("idx_user_email")).Find(&users)
// db.Clauses(hints.ForceIndex("idx_order_date")).Find(&orders)
// ===== BENCHMARK QUERIES =====
fmt.Println("\n=== Query Benchmarks ===")
// Seed some data for benchmarking
for i := 0; i < 1000; i++ {
db.Create(&User{
Email: fmt.Sprintf("user%d@example.com", i),
Username: fmt.Sprintf("user%d", i),
Status: []string{"active", "inactive", "pending"}[i%3],
Role: []string{"user", "admin", "moderator"}[i%3],
})
}
// Indexed query
start := time.Now()
var user User
db.Where("email = ?", "user500@example.com").First(&user)
fmt.Printf("Indexed lookup: %v\n", time.Since(start))
// Non-indexed query (if no index)
start = time.Now()
var users []User
db.Where("status = ? AND role = ?", "active", "admin").Find(&users)
fmt.Printf("Composite query: %v, found %d\n", time.Since(start), len(users))
}
Example 5: Query Profiling and Optimization
package main
import (
"context"
"fmt"
"log"
"os"
"time"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
// CustomLogger for query profiling
type QueryLogger struct {
logger.Interface
SlowThreshold time.Duration
queries []QueryLog
}
type QueryLog struct {
Query string
Duration time.Duration
Rows int64
Error error
}
func NewQueryLogger(slowThreshold time.Duration) *QueryLogger {
return &QueryLogger{
Interface: logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags),
logger.Config{
SlowThreshold: slowThreshold,
LogLevel: logger.Info,
IgnoreRecordNotFoundError: true,
Colorful: true,
},
),
SlowThreshold: slowThreshold,
}
}
func (l *QueryLogger) Trace(ctx context.Context, begin time.Time, fc func() (string, int64), err error) {
duration := time.Since(begin)
sql, rows := fc()
l.queries = append(l.queries, QueryLog{
Query: sql,
Duration: duration,
Rows: rows,
Error: err,
})
// Log slow queries
if duration > l.SlowThreshold {
fmt.Printf("⚠️ SLOW QUERY (%v): %s\n", duration, sql)
}
l.Interface.Trace(ctx, begin, fc, err)
}
func (l *QueryLogger) GetStats() {
var totalDuration time.Duration
var slowCount int
for _, q := range l.queries {
totalDuration += q.Duration
if q.Duration > l.SlowThreshold {
slowCount++
}
}
fmt.Printf("\n=== Query Statistics ===\n")
fmt.Printf("Total Queries: %d\n", len(l.queries))
fmt.Printf("Total Time: %v\n", totalDuration)
fmt.Printf("Slow Queries: %d\n", slowCount)
if len(l.queries) > 0 {
fmt.Printf("Avg Time: %v\n", totalDuration/time.Duration(len(l.queries)))
}
}
func (l *QueryLogger) Reset() {
l.queries = nil
}
// Example models
type Post struct {
ID uint `gorm:"primaryKey"`
Title string `gorm:"size:255;index"`
Content string `gorm:"type:text"`
UserID uint `gorm:"index"`
ViewCount int `gorm:"index"`
CreatedAt time.Time `gorm:"index"`
Comments []Comment
}
type Comment struct {
ID uint `gorm:"primaryKey"`
PostID uint `gorm:"index"`
Content string `gorm:"type:text"`
CreatedAt time.Time
}
func main() {
// Setup with custom logger
queryLogger := NewQueryLogger(100 * time.Millisecond)
db, _ := gorm.Open(sqlite.Open("profile.db"), &gorm.Config{
Logger: queryLogger,
})
db.AutoMigrate(&Post{}, &Comment{})
// Seed data
seedProfileData(db)
// ===== PROFILE DIFFERENT QUERY PATTERNS =====
fmt.Println("=== Profiling Queries ===\n")
// Pattern 1: N+1 (Bad)
queryLogger.Reset()
fmt.Println("Pattern 1: N+1 Query")
var posts []Post
db.Limit(10).Find(&posts)
for _, p := range posts {
var comments []Comment
db.Where("post_id = ?", p.ID).Find(&comments)
}
queryLogger.GetStats()
// Pattern 2: Preload (Good)
queryLogger.Reset()
fmt.Println("\nPattern 2: Preload")
db.Preload("Comments").Limit(10).Find(&posts)
queryLogger.GetStats()
// Pattern 3: Select specific fields
queryLogger.Reset()
fmt.Println("\nPattern 3: Select Fields")
type PostSummary struct {
ID uint
Title string
ViewCount int
}
var summaries []PostSummary
db.Model(&Post{}).Select("id", "title", "view_count").Limit(10).Find(&summaries)
queryLogger.GetStats()
// Pattern 4: Aggregation
queryLogger.Reset()
fmt.Println("\nPattern 4: Aggregation")
type Stats struct {
TotalPosts int64
TotalViews int64
TotalComments int64
}
var stats Stats
db.Model(&Post{}).Select("COUNT(*) as total_posts, SUM(view_count) as total_views").Scan(&stats)
db.Model(&Comment{}).Count(&stats.TotalComments)
queryLogger.GetStats()
// ===== OPTIMIZATION TECHNIQUES =====
fmt.Println("\n=== Optimization Techniques ===\n")
// Use Debug() to see SQL
db.Debug().Where("view_count > ?", 100).Find(&posts)
// Use DryRun to see SQL without executing
stmt := db.Session(&gorm.Session{DryRun: true}).
Where("created_at > ?", time.Now().AddDate(0, -1, 0)).
Find(&posts).Statement
fmt.Printf("Generated SQL: %s\n", stmt.SQL.String())
// Explain query (database specific)
var result []map[string]interface{}
db.Raw("EXPLAIN QUERY PLAN SELECT * FROM posts WHERE view_count > ?", 100).Scan(&result)
fmt.Printf("Query Plan: %v\n", result)
}
func seedProfileData(db *gorm.DB) {
var count int64
db.Model(&Post{}).Count(&count)
if count > 0 {
return
}
for i := 0; i < 100; i++ {
post := Post{
Title: fmt.Sprintf("Post %d", i),
Content: "Content here...",
UserID: uint(i % 10),
ViewCount: i * 10,
}
db.Create(&post)
for j := 0; j < 5; j++ {
db.Create(&Comment{
PostID: post.ID,
Content: fmt.Sprintf("Comment %d", j),
})
}
}
}
6. Hands-On Practice (30-40 minutes)
Exercise 1: Fix N+1 Problem (15 minutes)
Objective: Identify and fix N+1 queries
Solution:
package main
import (
"fmt"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
type Department struct {
ID uint `gorm:"primaryKey"`
Name string `gorm:"size:100"`
Employees []Employee
}
type Employee struct {
ID uint `gorm:"primaryKey"`
Name string `gorm:"size:100"`
DepartmentID uint `gorm:"index"`
Projects []Project `gorm:"many2many:employee_projects"`
}
type Project struct {
ID uint `gorm:"primaryKey"`
Name string `gorm:"size:100"`
}
// BAD: N+1 problem
func getDepartmentsBad(db *gorm.DB) {
var departments []Department
db.Find(&departments)
for _, dept := range departments {
var employees []Employee
db.Where("department_id = ?", dept.ID).Find(&employees)
for _, emp := range employees {
var projects []Project
db.Model(&emp).Association("Projects").Find(&projects)
fmt.Printf("%s - %s: %d projects\n", dept.Name, emp.Name, len(projects))
}
}
}
// GOOD: Optimized with preloading
func getDepartmentsGood(db *gorm.DB) {
var departments []Department
db.Preload("Employees.Projects").Find(&departments)
for _, dept := range departments {
for _, emp := range dept.Employees {
fmt.Printf("%s - %s: %d projects\n", dept.Name, emp.Name, len(emp.Projects))
}
}
}
func main() {
db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
db.AutoMigrate(&Department{}, &Employee{}, &Project{})
fmt.Println("Optimized Query:")
getDepartmentsGood(db)
}
Exercise 2: Batch Processing (10 minutes)
Objective: Process 100,000 records efficiently
Exercise 3: Add Strategic Indexes (15 minutes)
Objective: Identify and add indexes for a given query pattern
7. Common Pitfalls and Troubleshooting (10 minutes)
Frequent Mistakes
-
Problem: Preload loads too much data Cause: Preloading without conditions Solution: Use conditional preload or select specific fields
-
Problem: Batch size too large Cause: Memory constraints Solution: Tune batch size based on available memory
-
Problem: Connection pool exhaustion Cause: Connections not returned to pool Solution: Use context timeouts, check for leaks
Debugging Tips
- Enable query logging to see all SQL
- Use database EXPLAIN to understand query plans
- Monitor connection pool stats
- Profile memory usage during batch operations
8. Best Practices and Professional Tips (5 minutes)
Industry Standards
- Always use Preload for known associations
- Set appropriate connection pool limits
- Add indexes for all foreign keys
- Use batch operations for bulk data
- Monitor query performance in production
Next Steps in Your Learning Journey
- Learn about database-specific optimizations
- Study caching strategies (Redis, in-memory)
- Explore read replicas for scaling
- Practice with large datasets
9. Summary and Review (5 minutes)
Key Takeaways
- N+1 is the #1 performance killer - always use Preload or Joins
- Batch operations save time and memory for bulk data
- Connection pooling is essential for production
- Strategic indexes can 10x query performance
Quick Review Questions
- What causes the N+1 problem?
- When to use Preload vs Joins?
- How do you configure connection pooling?
- What columns should always have indexes?
For Instructors
Teaching Tips
- Use query logging to visualize N+1
- Show real performance numbers
- Demonstrate with datasets of varying sizes
- Use database explain plans
Assessment Ideas
- Identify N+1 in given code
- Optimize slow queries
- Design indexes for query patterns
- Configure connection pool for given load
Adaptation Notes
- For beginners: Focus on N+1 and basic preloading
- For advanced: Cover profiling and advanced indexing
- Use realistic datasets for demonstrations