N+1 Effect #

// Code that looks clean and logical
func GetOrderList(ctx context.Context) ([]OrderResponse, error) {
    // Query 1: fetch all orders
    orders, err := db.QueryContext(ctx,
        "SELECT id, user_id, total, status FROM orders LIMIT 20")
    // ...scan rows into []Order

    var result []OrderResponse
    for _, order := range orders {
        // Queries 2 to 21: fetch the user for EACH order
        var user User
        db.QueryRowContext(ctx,
            "SELECT id, name, email FROM users WHERE id = ?",
            order.UserID,
        ).Scan(&user.ID, &user.Name, &user.Email)

        result = append(result, OrderResponse{
            OrderID: order.ID,
            Total:   order.Total,
            Status:  order.Status,
            User:    user,
        })
    }
    return result, nil
}

// Three-level nested N+1 — very common in unmaintained code
func GetOrdersWithDetails(ctx context.Context) ([]OrderDetail, error) {
    // Query 1: fetch orders
    orders, _ := db.QueryContext(ctx, "SELECT id, user_id FROM orders LIMIT 20")

    for _, order := range orders {  // N = 20 orders
        // Queries 2-21: fetch the user per order
        db.QueryRowContext(ctx, "SELECT name FROM users WHERE id = ?", order.UserID)

        // Queries 22-41: fetch items per order
        items, _ := db.QueryContext(ctx,
            "SELECT id, product_id, qty FROM order_items WHERE order_id = ?", order.ID)

        for _, item := range items {  // M = say 5 items per order
            // Queries 42-141: fetch the product per item
            db.QueryRowContext(ctx,
                "SELECT name, price FROM products WHERE id = ?", item.ProductID)
        }
    }
}

// ANTI-PATTERN: N+1 queries
func GetOrdersN1(ctx context.Context) ([]OrderResponse, error) {
    orders, _ := db.QueryContext(ctx, "SELECT id, user_id, total FROM orders LIMIT 20")
    for _, o := range orders {
        db.QueryRowContext(ctx, "SELECT name FROM users WHERE id = ?", o.UserID)
        // N additional queries
    }
    // ...
}

// CORRECT: one query with a JOIN
func GetOrdersWithJoin(ctx context.Context, db *sqlx.DB) ([]OrderResponse, error) {
    type row struct {
        OrderID    int64   `db:"order_id"`
        Total      float64 `db:"total"`
        Status     string  `db:"status"`
        UserID     int64   `db:"user_id"`
        UserName   string  `db:"user_name"`
        UserEmail  string  `db:"user_email"`
    }

    var rows []row
    err := db.SelectContext(ctx, &rows, `
        SELECT
            o.id    AS order_id,
            o.total,
            o.status,
            u.id    AS user_id,
            u.name  AS user_name,
            u.email AS user_email
        FROM orders o
        JOIN users u ON o.user_id = u.id
        ORDER BY o.created_at DESC
        LIMIT 20
    `)
    if err != nil {
        return nil, err
    }

    result := make([]OrderResponse, len(rows))
    for i, r := range rows {
        result[i] = OrderResponse{
            OrderID: r.OrderID,
            Total:   r.Total,
            Status:  r.Status,
            User: UserInfo{
                ID:    r.UserID,
                Name:  r.UserName,
                Email: r.UserEmail,
            },
        }
    }
    return result, nil
}
// Total queries: 1 — regardless of the order count

// CORRECT: batch query with IN for one-to-many relationships
func GetOrdersWithItems(ctx context.Context, db *sqlx.DB) ([]OrderWithItems, error) {
    // Query 1: fetch all orders
    var orders []Order
    err := db.SelectContext(ctx, &orders,
        "SELECT id, user_id, total, status FROM orders ORDER BY created_at DESC LIMIT 20")
    if err != nil {
        return nil, err
    }

    if len(orders) == 0 {
        return nil, nil
    }

    // Collect all order IDs
    orderIDs := make([]int64, len(orders))
    for i, o := range orders {
        orderIDs[i] = o.ID
    }

    // Query 2: fetch ALL items for all orders at once
    query, args, err := sqlx.In(
        "SELECT id, order_id, product_id, qty, price FROM order_items WHERE order_id IN (?)",
        orderIDs,
    )
    if err != nil {
        return nil, err
    }

    var items []OrderItem
    err = db.SelectContext(ctx, &items, db.Rebind(query), args...)
    if err != nil {
        return nil, err
    }

    // Mapping in the application layer — O(n), no additional queries
    itemsByOrderID := make(map[int64][]OrderItem)
    for _, item := range items {
        itemsByOrderID[item.OrderID] = append(itemsByOrderID[item.OrderID], item)
    }

    result := make([]OrderWithItems, len(orders))
    for i, order := range orders {
        result[i] = OrderWithItems{
            Order: order,
            Items: itemsByOrderID[order.ID],  // O(1) map lookup
        }
    }
    return result, nil
}
// Total queries: 2 — regardless of the order or item count

// Three levels at once: orders → items → products
// Only 3 queries total, whatever the data volume
func GetOrdersComplete(ctx context.Context, db *sqlx.DB) ([]OrderComplete, error) {
    // Query 1: orders
    var orders []Order
    db.SelectContext(ctx, &orders, "SELECT ... FROM orders LIMIT 20")

    orderIDs := extractIDs(orders)  // helper to extract an ID slice

    // Query 2: all items for the selected orders
    var items []OrderItem
    query, args, _ := sqlx.In("SELECT ... FROM order_items WHERE order_id IN (?)", orderIDs)
    db.SelectContext(ctx, &items, db.Rebind(query), args...)

    productIDs := extractProductIDs(items)  // collect all product_ids from the items

    // Query 3: all needed products
    var products []Product
    query, args, _ = sqlx.In("SELECT ... FROM products WHERE id IN (?)", productIDs)
    db.SelectContext(ctx, &products, db.Rebind(query), args...)

    // Mapping in the application layer
    productMap := buildProductMap(products)
    itemMap := buildItemMap(items, productMap)

    return buildOrderComplete(orders, itemMap), nil
    // Total: 3 queries — vs 141 queries with N+1
}

// ANTI-PATTERN: lazy loading in GORM — a hidden N+1
var orders []Order
db.Find(&orders)  // 1 query
for _, order := range orders {
    // Every access to .User triggers a new query if not yet loaded
    fmt.Println(order.User.Name)  // N queries — an N+1!
}

// CORRECT: explicit Preload — GORM does batch queries automatically
var orders []Order
db.Preload("User").Preload("Items").Find(&orders)
// → 1 query for orders
// → 1 query for all users (SELECT ... WHERE id IN (...))
// → 1 query for all items (SELECT ... WHERE order_id IN (...))
// Total: 3 queries, not N+1

// Or for nested relationships:
db.Preload("Items.Product").Find(&orders)
// → 1 orders query
// → 1 items query
// → 1 products query
// Still 3 queries total

// Middleware to count queries per request
type QueryCounter struct {
    db    *sql.DB
    count int64
}

func (qc *QueryCounter) reset() { atomic.StoreInt64(&qc.count, 0) }
func (qc *QueryCounter) increment() { atomic.AddInt64(&qc.count, 1) }
func (qc *QueryCounter) get() int64 { return atomic.LoadInt64(&qc.count) }

// Wrapper counting every query
func (qc *QueryCounter) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
    qc.increment()
    return qc.db.QueryContext(ctx, query, args...)
}

// In the HTTP middleware (Fiber/Go):
func QueryCountMiddleware(counter *QueryCounter) fiber.Handler {
    return func(c *fiber.Ctx) error {
        counter.reset()
        err := c.Next()

        count := counter.get()
        if count > 20 {
            // Log a warning — a likely N+1
            log.Warn("high query count",
                "path", c.Path(),
                "method", c.Method(),
                "query_count", count,
            )
        }

        // Include it in a response header for debugging
        c.Set("X-Query-Count", strconv.FormatInt(count, 10))
        return err
    }
}

// sqlx with a custom logger
type LoggingDB struct {
    *sqlx.DB
    logger *zap.Logger
}

func (ldb *LoggingDB) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
    start := time.Now()
    rows, err := ldb.DB.QueryContext(ctx, query, args...)
    ldb.logger.Debug("sql query",
        zap.String("query", query),
        zap.Any("args", args),
        zap.Duration("duration", time.Since(start)),
    )
    return rows, err
}

// In tests, you can count queries and assert:
func TestGetOrderList_ShouldNotCauseN1(t *testing.T) {
    counter := &QueryCounter{}
    // ... run GetOrderList
    assert.LessOrEqual(t, counter.get(), int64(3),
        "GetOrderList should not exceed 3 queries (orders + users + items)")
}

// Prometheus metric
var queriesPerRequest = prometheus.NewHistogramVec(
    prometheus.HistogramOpts{
        Name:    "http_db_queries_per_request",
        Help:    "Number of DB queries executed per HTTP request",
        Buckets: []float64{1, 2, 5, 10, 20, 50, 100, 500},
    },
    []string{"method", "path"},
)

// At the end of the request middleware:
queriesPerRequest.WithLabelValues(method, path).Observe(float64(queryCount))

// N+1 to an external API — just as dangerous
func EnrichOrdersWithShipping(orders []Order) ([]OrderWithShipping, error) {
    var result []OrderWithShipping
    for _, order := range orders {
        // One HTTP call per order — an N+1 to the shipping service!
        shipping, err := shippingService.GetStatus(order.TrackingNumber)
        // ...
    }
    return result, nil
}

// CORRECT: batch call to the external API
func EnrichOrdersWithShippingBatch(orders []Order) ([]OrderWithShipping, error) {
    trackingNumbers := extractTrackingNumbers(orders)

    // One call for all tracking numbers at once
    shippingMap, err := shippingService.GetStatusBatch(trackingNumbers)
    // ...

    for _, order := range orders {
        shipping := shippingMap[order.TrackingNumber]
        // mapping...
    }
}

// ✗ Anti-pattern 1: queries inside loops — the classic N+1
for _, order := range orders {
    db.QueryRow("SELECT name FROM users WHERE id = ?", order.UserID)
}
// ✓ Solution: JOIN or batch IN

// ✗ Anti-pattern 2: ORM lazy loading without Preload
db.Find(&orders)
for _, o := range orders {
    fmt.Println(o.User.Name)  // triggers a query per access
}
// ✓ Solution: db.Preload("User").Find(&orders)

// ✗ Anti-pattern 3: "batching" that still queries per item because the loop is outside the batch
for _, orderID := range orderIDs {
    // This is still an N+1 — a batch must be one call for all IDs at once
    db.QueryRow("SELECT * FROM items WHERE order_id = ?", orderID)
}
// ✓ Solution: sqlx.In with all IDs at once

// ✗ Anti-pattern 4: per-item caching but with a high miss rate
for _, order := range orders {
    user, ok := cache.Get(order.UserID)
    if !ok {
        // Cache miss → per-item query → still an N+1 if the miss rate is high
        db.QueryRow("SELECT * FROM users WHERE id = ?", order.UserID)
    }
}
// ✓ Solution for cache misses: batch-fetch all misses at once

// ✗ Anti-pattern 5: N+1 to external HTTP APIs inside loops
for _, item := range items {
    price, _ := pricingAPI.GetPrice(item.ProductID)  // an HTTP call per item!
}
// ✓ Solution: pricingAPI.GetPriceBatch(productIDs)  // one call for all